PostgreSQLSessionStorage throw error: relation "shopify_sessions" already exists

[chuangbo]

Issue summary

Initialize context with PostgreSQLSessionStorage throws error: relation "shopify_sessions" already exists.

Expected behavior

No error.

Actual behavior

error: relation "shopify_sessions" already exists
    at Parser.parseErrorMessage (.../node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (.../node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (.../node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (.../node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:527:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

Steps to reproduce the problem

Shopify.Context.initialize({
  ...
  SESSION_STORAGE: new Shopify.Session.PostgreSQLSessionStorage(
    new URL(process.env.DATABASE_URL)
  ),
});

Reduced test case

The best way to get your bug fixed is to provide a reduced test case.

---

Checklist

• I have described this issue in a way that is actionable (if possible)

[h4gen]

Hi!

This is still an Issue for me with "@shopify/shopify-app-session-storage-postgresql": "^1.0.0"

        const message = name === 'notice' ? new messages_1.NoticeMessage(length, messageValue) : new messages_1.DatabaseError(messageValue, length, name);
                                                                                                 ^
error: relation "shopify_sessions" already exists
    at Parser.parseErrorMessage (.../node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (.../node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (.../node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (.../node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:527:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {

Best,
Hagen

[kelreel]

Yes, this problem remains on version 1.0.1

[SeanMythen]

I'm also getting this error, and it prevents my app from re-deploying if the shopify_sessions table already exists.

would adding something like CREATE TABLE IF NOT EXISTS prevent this error?

edit: I've tested it a bit more and adding that seems to work... I'm not sure it it's a true fix, someone with more experience with this will need to confirm.

[yadsgroup]

Hi there, any news about this issue?

[conspireagency]

Use the patched version, there’s a thread in closed issues. Or go the custom route from the docs, it uses redis but you can always adapt to use Postgres with whatever orm etc you prefer =)On Jan 1, 2023, at 5:22 PM, yadsgroup ***@***.***> wrote: Hi there, any news about this issue? —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[yadsgroup]

Hi @conspireagency, I'm facing the same issue with the patched version. Will try to go for a custom adapter

[conspireagency]

Interesting. At one point the simple fix was merged into V3 or whatever branch, but you could also fork. Believe it was just 1 line that always returned that the database table didn’t exist even if it does.On Jan 1, 2023, at 5:36 PM, yadsgroup ***@***.***> wrote: Hi @conspireagency, I'm facing the same issue with the patched version. Will try to go for a custom adapter —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: ***@***.***>

[SeanMythen]

@yadsgroup did adding IF NOT EXISTS work for you?

[yadsgroup]

@yadsgroup did adding IF NOT EXISTS work for you?

It worked when modify directly the file from node_module but I didn't succeed in forking the repo to have a stable solution (I'm not a Node expert 😬)

[SeanMythen]

@yadsgroup did adding IF NOT EXISTS work for you?

It worked when modify directly the file from node_module but I didn't succeed in forking the repo to have a stable solution (I'm not a Node expert 😬)

Great! and yes, I manually copied and modified the file into a different folder so that instead of having it download the package on deployment, it just uses the custom file I modified.

[yadsgroup]

@yadsgroup did adding IF NOT EXISTS work for you?

It worked when modify directly the file from node_module but I didn't succeed in forking the repo to have a stable solution (I'm not a Node expert 😬)

Great! and yes, I manually copied and modified the file into a different folder so that instead of having it download the package on deployment, it just uses the custom file I modified.

Thanks, will try 😊

[oguz-kara]

Hi, I just did -> web/node_modules/@shopify/shopify-app-session-storage-postgresql/build/cjs/postgresql.js
and change createTable() function with this

async createTable() {
    const hasSessionTable = await this.hasSessionTable();
    if (!hasSessionTable) {
      const query = `
        CREATE TABLE IF NOT EXISTS ${this.options.sessionTableName} (
          id varchar(255) NOT NULL PRIMARY KEY,
          shop varchar(255) NOT NULL,
          state varchar(255) NOT NULL,
          isOnline boolean NOT NULL,
          scope varchar(255),
          expires integer,
          onlineAccessInfo varchar(255),
          accessToken varchar(255)
        )
      `;
      await this.query(query);
    }
  }

it only adds CREATE TABLE IF NOT EXISTS query to the function..
and problem will be fixed.

[SeanMythen]

yes, that will fix the issue locally, but when you go to deploy, it won't use that file unless you specifiy it to. @oguz-kara

[conspireagency]

You’d want to fork the repo, using that forked version as your dependency.Best,Daniel Andradewww.ConspireAgency.comSent on the goOn Jan 18, 2023, at 9:52 AM, SeanMythen ***@***.***> wrote: yes, that will fix the issue locally, but when you go to deploy, it won't use that file unless you specifiy it to. @oguz-kara —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: ***@***.***>