Error while seeding

[AndreaBarghigiani]

Hey w3cj, thank you so much for the video, you really went one mile ahead compared to all other tutorials that you can find out there.

I took the knowledge you distilled in the video and implemented inside a Next.js application, the thing is that I am unable to pass the table name as a variable while seeding...

I get the following error:

Query: TRUNCATE TABLE $1 RESTART IDENTITY CASCADE -- params: ["category"]
node:internal/process/promises:289
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: syntax error at or near "$1"
    at ErrorResponse (/app/node_modules/postgres/cjs/src/connection.js:788:26)
    at handle (/app/node_modules/postgres/cjs/src/connection.js:474:6)
    at Socket.data (/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:519:28)
    at Socket.emit (node:domain:488:12)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:191:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42601',
  position: '16',
  file: 'scan.l',
  line: '1241',
  routine: 'scanner_yyerror'
}

Node.js v20.13.1

I thought it was the db connector since Next.js can behave a little strange with it, so I even created a new one just for the seed file, but that didn't help 🤔

So as a test, since I read few discussions, I placed the exact table name right inside the string and that worked flawlessly 😮

I am writing just to know if you ever encounter something like that and if you know a solution about it.

For the moment I'll just additional TRUNCATE for each table, lucky me there aren't many, but I would love to know if you have a different approach.

Thank you again, for the video and especially if you're willing to take the time and answer this question.

[w3cj]

This issue is because SQL DDL statements cannot be parameterized.

When you use sql from drizzle it automatically parameterizes any variables passed in to the template string.

If you use sql.raw the query is not parameterized which should be fine for seeding BUT if you use it in a running application you might be vulnerable to SQL injection.

I use sql.raw in the seed file here: https://github.com/w3cj/bytedash/blob/main/src/db/seed.ts#L13

Read more about it here: https://orm.drizzle.team/docs/sql#sqlraw

I'll leave this issue open in case anyone else runs into this.

[AndreaBarghigiani]

Oh boy, I feel kinda embarrassed for missing the raw in your statement 😅

Thank you so much for your quick answer, it really solved my issue 🎉

[kaman1]

Thanks for the video tutorial. FYI, there is an update for drizzle-kit that caused an error when I ran the Drizzle Studio. I was able to fix it by changing the drizzle.config.ts. I'm not sure if this solution is relevant, but I figured I’d share it in case it might be helpful.

OLD CODE:

import { defineConfig } from "drizzle-kit";
import env from '@/env';

export default defineConfig({
  schema: "./src/db/schema/index.ts",
  out: "./src/db/migrations",
  driver: "pg",
  dbCredentials: {
    connectionString: env.DATABASE_URL,
  },
  verbose: true,
  strict: true,
});

UPDATED CODE:

import { defineConfig } from "drizzle-kit";
import env from "@/env";

export default defineConfig({
  schema: "./src/db/schema/index.ts",
  out: "./src/db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: env.DATABASE_URL,
  },
  verbose: true,
  strict: true,
});

[w3cj]

Thanks for the video tutorial. FYI, there is an update for drizzle-kit that caused an error when I ran the Drizzle Studio. I was able to fix it by changing the drizzle.config.ts. I'm not sure if this solution is relevant, but I figured I’d share it in case it might be helpful.

OLD CODE:

import { defineConfig } from "drizzle-kit";
import env from '@/env';

export default defineConfig({
  schema: "./src/db/schema/index.ts",
  out: "./src/db/migrations",
  driver: "pg",
  dbCredentials: {
    connectionString: env.DATABASE_URL,
  },
  verbose: true,
  strict: true,
});

UPDATED CODE:

import { defineConfig } from "drizzle-kit";
import env from "@/env";

export default defineConfig({
  schema: "./src/db/schema/index.ts",
  out: "./src/db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: env.DATABASE_URL,
  },
  verbose: true,
  strict: true,
});

Thanks! I opened a new issue with this in case others run into this error.