Drizzle Team•4w ago

How to keep dev and production DBs in sync?

I'm using Drizzle in a SvelteKit project. My local database is Postgres run via Docker. My production and staging databases are Postgres on Railway, also via a Docker instance. I'm wondering how to keep these in sync? Local development with drizzle-kit generate and drizzle-kit migrate is working well, however I'm running into an issue where a PR I'm merging has so many schema changes that that the migration files generated for my local config aren't successfully being applied to my production database. I get errors like these:

{

  severity_local: 'NOTICE',

  severity: 'NOTICE',

  code: '42P06',

  message: 'schema "drizzle" already exists, skipping',

  file: 'schemacmds.c',

  line: '132',

  routine: 'CreateSchemaCommand'

}

{

  severity_local: 'NOTICE',

  severity: 'NOTICE',

  code: '42P07',

  message: 'relation "__drizzle_migrations" already exists, skipping',

  file: 'parse_utilcmd.c',

  line: '207',

  routine: 'transformCreateStmt'

}

Migration failed 🚨: PostgresError: syntax error at or near "q"

    at ErrorResponse (file:///app/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:788:26)

    at handle (file:///app/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:474:6)

    at TLSSocket.data (file:///app/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:315:9)

    at TLSSocket.emit (node:events:519:28)

    at addChunk (node:internal/streams/readable:559:12)

    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)

    at TLSSocket.Readable.push (node:internal/streams/readable:390:5)

    at TLSWrap.onStreamRead (node:internal/stream_base_commons:191:23) {

  severity_local: 'ERROR',

  severity: 'ERROR',

  code: '42601',

  position: '1',

  file: 'scan.l',

  line: '1241',

  routine: 'scanner_yyerror'

}

{

  severity_local: 'NOTICE',

  severity: 'NOTICE',

  code: '42P06',

  message: 'schema "drizzle" already exists, skipping',

  file: 'schemacmds.c',

  line: '132',

  routine: 'CreateSchemaCommand'

}

{

  severity_local: 'NOTICE',

  severity: 'NOTICE',

  code: '42P07',

  message: 'relation "__drizzle_migrations" already exists, skipping',

  file: 'parse_utilcmd.c',

  line: '207',

  routine: 'transformCreateStmt'

}

Migration failed 🚨: PostgresError: syntax error at or near "q"

    at ErrorResponse (file:///app/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:788:26)

    at handle (file:///app/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:474:6)

    at TLSSocket.data (file:///app/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:315:9)

    at TLSSocket.emit (node:events:519:28)

    at addChunk (node:internal/streams/readable:559:12)

    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)

    at TLSSocket.Readable.push (node:internal/streams/readable:390:5)

    at TLSWrap.onStreamRead (node:internal/stream_base_commons:191:23) {

  severity_local: 'ERROR',

  severity: 'ERROR',

  code: '42601',

  position: '1',

  file: 'scan.l',

  line: '1241',

  routine: 'scanner_yyerror'

}

I think that I'm missing a reliable process that I can use for local > staging > production development. Thanks!

4 Replies

opaldraggy•4w ago

@Budi more details would be useful about the migration you are trying to run. The error message implies a syntax error in the SQL that's being applied; so it would be useful to know which migration it's failing on. Have you tried applying the migrations one at a time until you find the failing one? Also, I would consider minimizing changes per migration to be useful best practice. Limited to one easily-described logical change.

BudiOP•4w ago

Thanks for the response. How can I run individual migrations? I'm lacking an understanding of the workflow I should use to separate my local dev migrations vs. my production ones. The best solution I could find was the one provided here (https://github.com/drizzle-team/drizzle-orm/discussions/2832?sort=top#discussioncomment-10463438). Creating two different Drizzle config files for dev and production respectively. I have now done this as such:

drizzle-dev.config.ts:

export default defineConfig({
    schema: './src/lib/database/schema.ts',
    out: './.drizzle/migrations/dev',
    dialect: 'postgresql',
    dbCredentials: {
        url: getDbUrl() as string,
        ssl: process.env.NODE_ENV === 'production' ? 'require' : undefined
    },
    strict: true
})

drizzle-dev.config.ts:

export default defineConfig({
    schema: './src/lib/database/schema.ts',
    out: './.drizzle/migrations/dev',
    dialect: 'postgresql',
    dbCredentials: {
        url: getDbUrl() as string,
        ssl: process.env.NODE_ENV === 'production' ? 'require' : undefined
    },
    strict: true
})

drizzle-prod.config.ts:

export default defineConfig({
    schema: './src/lib/database/schema.ts',
    out: './.drizzle/migrations',
    dialect: 'postgresql',
    dbCredentials: {
        url: getDbUrl() as string,
        ssl: process.env.NODE_ENV === 'production' ? 'require' : undefined
    },
    strict: true
})

drizzle-prod.config.ts:

export default defineConfig({
    schema: './src/lib/database/schema.ts',
    out: './.drizzle/migrations',
    dialect: 'postgresql',
    dbCredentials: {
        url: getDbUrl() as string,
        ssl: process.env.NODE_ENV === 'production' ? 'require' : undefined
    },
    strict: true
})

And I've defined my scripts as such:

package.json:

        "db:generate": "dotenvx run -- drizzle-kit generate --config drizzle-dev.config.ts",
        "db:migrate": "dotenvx run -- drizzle-kit migrate --config drizzle-dev.config.ts",
        "db:clear": "dotenvx run -- vite-node .src/lib/database/clear.ts",
        "db:seed": "dotenvx run -- vite-node .src/lib/database/seed.ts",
        "db:studio": "dotenvx run -- drizzle-kit studio --config drizzle-dev.config.ts",
        "db:prod:generate": "NODE_ENV=production dotenvx run -- drizzle-kit generate --config drizzle-prod.config.ts",
        "db:prod:migrate": "NODE_ENV=production dotenvx run -- vite-node .drizzle/migrate.ts",

package.json:

        "db:generate": "dotenvx run -- drizzle-kit generate --config drizzle-dev.config.ts",
        "db:migrate": "dotenvx run -- drizzle-kit migrate --config drizzle-dev.config.ts",
        "db:clear": "dotenvx run -- vite-node .src/lib/database/clear.ts",
        "db:seed": "dotenvx run -- vite-node .src/lib/database/seed.ts",
        "db:studio": "dotenvx run -- drizzle-kit studio --config drizzle-dev.config.ts",
        "db:prod:generate": "NODE_ENV=production dotenvx run -- drizzle-kit generate --config drizzle-prod.config.ts",
        "db:prod:migrate": "NODE_ENV=production dotenvx run -- vite-node .drizzle/migrate.ts",

GitHub

Migrations folder structure v3 · drizzle-team drizzle-orm · Discuss...

Based on production feedback our migrations folder structure completely unusable due to git incompatibility Current migrations folder structure looks like below 📦 <project root> └ 📂 drizzle ├...

BudiOP•4w ago

I've now succeeded by doing pnpm db:prod:generate and pnpm db:prod:migrate but it required me dropping the existing tables on the database. Is there a command I can use to first introspect the current database state to ensure the generate will create the right SQL? I'm using Railway for hosting which creates a new environment of my app and database for each PR. This DB will have its own URLs which are dynamically injected by Railway. I believe I'm successfully accessing these with how I've configured my drizzle-*.config.ts files. I'd love to know what a decent process would be for testing a PR in a staging environment, and then running those same migrations in my production environment. I would think I need to do this: 1. Create PR which spawns a staging env and db. Run generate on that staging db to generate the migration files. Then add migrate in my start scripts (I'm building with Docker) to have the migrations be run on deployment (when I commit these changes to Github). 2. Assuming state between the staging and production environments are the same, this should mean that when merging to main, the same migration files that have been generated the staging db can be applied to production db. Is this the right way to do this? Any suggestions, articles or references you could share wrt how to do this best would be much appreciated. I think I will write a blog post about this once I figure it out, since it's been hard to find any resources for this process and the Drizzle docs aren't explicit about it either.

Luxaritas•4w ago

I'm lacking an understanding of the workflow I should use to separate my local dev migrations vs. my production ones.

You shouldn’t need to, unless I’m missing something about your use case. The idea is that when you go to do some development, you run migrate to get your local DB to the same state as prod (or whatever branch you’re working on), then you make drizzle schema changes, generate new migrations, migrate your local DB, and test. Then when you’re code gets merged, you migrate to apply any new migrations in that merge in the same way you migrated in your development environment - you started in the same state, ran the same code, and ended in the same state The errors you posted above seem to indicate something else went wrong (it’s quite surprising to me that you’d see a syntax error on one DB but not the other if there both running equivalent Postgres versions!)

Gaming

Programming

How to keep dev and production DBs in sync?