Drizzle Team•14mo ago

Multi-service, different schema in one database (postgres)

Sorry if it's duplicate, I can't find it after trying to search for a while. My use case is: I use drizzle with multiple service, every service connect to the same database instance and same database, separate by the schema. So far so good until I try to run migration, the migration script only do migrate the first schema only. But no error occurs in the command line. Did I do anything wrong? My migration script:

import { drizzle } from 'drizzle-orm/postgres-js'
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import postgres from 'postgres'
import * as dotenv from 'dotenv'
dotenv.config({
  path: '.dev.vars'
})

const sql = postgres(process.env.X_DB_CONNECTION as string, { max: 1 })
const db = drizzle(sql)

async function main () {
  console.log('Migration start...')
  await migrate(db, { migrationsFolder: 'migration' })
  console.log('Migration completed...')
  
  process.exit(0)  
}
main().catch(err => {
  console.log(err)
  process.exit(0)
})

import { drizzle } from 'drizzle-orm/postgres-js'
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import postgres from 'postgres'
import * as dotenv from 'dotenv'
dotenv.config({
  path: '.dev.vars'
})

const sql = postgres(process.env.X_DB_CONNECTION as string, { max: 1 })
const db = drizzle(sql)

async function main () {
  console.log('Migration start...')
  await migrate(db, { migrationsFolder: 'migration' })
  console.log('Migration completed...')
  
  process.exit(0)  
}
main().catch(err => {
  console.log(err)
  process.exit(0)
})

Cli response

pnpm db:migrate

> @ampos-x/[email protected] db:migrate /Users/sittiponghaus/project/@x/apps/access-control-api
> tsx -r ./migrate.ts

Migration start...
Welcome to Node.js v19.9.0.
Type ".help" for more information.
> {
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '131',
  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 completed...

pnpm db:migrate

> @ampos-x/[email protected] db:migrate /Users/sittiponghaus/project/@x/apps/access-control-api
> tsx -r ./migrate.ts

Migration start...
Welcome to Node.js v19.9.0.
Type ".help" for more information.
> {
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '131',
  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 completed...

There're no errors but the table wasn't create. Please help suggest.

1 Reply

ハウスOP•14mo ago

push

Gaming

Programming

Multi-service, different schema in one database (postgres)