Drizzle Team•15mo ago

migrate with node-postgres

I had migration working well with postgresjs but need to switch to node-postgres for other reasons. But I can't get migration to work and it just hangs. The code I have is:

import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import * as schema from "~/db/schema";

const migrationClient = new Client({ connectionString: dbURL });
await migrate(drizzle(migrationClient), {
  migrationsFolder: "./migrations",
});

import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import * as schema from "~/db/schema";

const migrationClient = new Client({ connectionString: dbURL });
await migrate(drizzle(migrationClient), {
  migrationsFolder: "./migrations",
});

Also how/where do you set max with pg? TIA.

7 Replies

tomri•15mo ago

I’d recommend using the CLI instead, because it doesn’t rely on any PostgreSQL client, apparently. Add your database URI to the config file under the dbCredentials field as a connection string:

// drizzle.config.ts

import { Config } from "drizzle-kit";

export default {
  schema: "src/lib/db/schema.ts",
  out: "src/lib/db/migrations",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DB_URI!,
  },
} as Config;

// drizzle.config.ts

import { Config } from "drizzle-kit";

export default {
  schema: "src/lib/db/schema.ts",
  out: "src/lib/db/migrations",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DB_URI!,
  },
} as Config;

And nowj you can ust run:

$ bun drizzle-kit push:pg

$ bun drizzle-kit push:pg

Now, you can do migrations without any concern about the actual client. P.S.: I’m a newbie in Drizzle too and I’m just “trying to help” you. I would welcome it if you correct me if I’m wrong.

Julio BarrosOP•15mo ago

Thanks. That is a better way in general. Unfortunately, it still just hangs for me.

tomri•15mo ago

can you share the error message?

ruslan•13mo ago

change client to pool solves issue

  const pool = new Pool({
    connectionString: dbURL,
    max: 1,
  })

  const pool = new Pool({
    connectionString: dbURL,
    max: 1,
  })

nk•13mo ago

real issue is with not using await client.connect(); and await pg.end();

Jan Vorwerk•12mo ago

Had the exact same issue... the woraround to use the Pool({max: 1}) worked for me! The push command is very nice but it is not enough if you need to modify the generated migration SQL files (for instance to add data migrations)

cknarker•12mo ago

try this if you still want to use client instead of pool

import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { Client } from "pg";


// you can substitute this with process.env instead if you're not using bun
const connectionString = Bun.env.DB_CONNECTION_STRING;

if (!connectionString) {
  throw new Error("DB_CONNECTION_STRING is missing");
}

const connection = new Client({
  connectionString: connectionString,
});

export const db = drizzle(connection);

// this is required otherwise it will hang, nk mentioned this
await connection.connect();

await migrate(db, {
  migrationsFolder: "./migrations",
})
  .then(() => console.log("Migrations complete"))
  .finally(async () => await connection.end());

// use this instead if you cant use promise.finally()
// await connection.end()

import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { Client } from "pg";


// you can substitute this with process.env instead if you're not using bun
const connectionString = Bun.env.DB_CONNECTION_STRING;

if (!connectionString) {
  throw new Error("DB_CONNECTION_STRING is missing");
}

const connection = new Client({
  connectionString: connectionString,
});

export const db = drizzle(connection);

// this is required otherwise it will hang, nk mentioned this
await connection.connect();

await migrate(db, {
  migrationsFolder: "./migrations",
})
  .then(() => console.log("Migrations complete"))
  .finally(async () => await connection.end());

// use this instead if you cant use promise.finally()
// await connection.end()

Gaming

Programming

migrate with node-postgres

Did you find this page helpful?