Drizzle Team•14mo 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•14mo 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•14mo ago

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

tomri•14mo ago

can you share the error message?

ruslan•12mo ago

change client to pool solves issue

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

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

nk•12mo ago

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

Jan Vorwerk•11mo 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•11mo 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