Drizzle Team•2y ago

How to apply migrations on postgres in a serverless enviroment?

Hello, I want to apply postgres migrations to my vercel DB. I see that the push command does not work for postgres and am therefore attempting to use the migrate function like so:

// ./src/db/index.ts
import { sql } from "@vercel/postgres";
import { drizzle } from "drizzle-orm/vercel-postgres";
import { migrate } from "drizzle-orm/vercel-postgres/migrator";
import * as schema from "./schema";

export const db = drizzle(sql, { schema });

await migrate(db, { migrationsFolder: "../../drizzle" });

// ./src/db/index.ts
import { sql } from "@vercel/postgres";
import { drizzle } from "drizzle-orm/vercel-postgres";
import { migrate } from "drizzle-orm/vercel-postgres/migrator";
import * as schema from "./schema";

export const db = drizzle(sql, { schema });

await migrate(db, { migrationsFolder: "../../drizzle" });

However this does not seem like the best way to do this as that would make it so migrations are applied only one I am attempting to make a call instead of at something like build time as far as I can tell. Additionally, it is additional imports that will have to be loaded in the serverless and/or edge environment. Is there a better way to do this?

3 Replies

NinjaBunny•2y ago

you have to migrate the schema to the database

NinjaBunny•2y ago

https://orm.drizzle.team/docs/installation-and-db-connection/postgresql/postgresjs

PostgresJS – DrizzleORM

Drizzle ORM | %s

NinjaBunny•2y ago

or if you're using pg you would swap out the conection connection

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

dotenv.config({
  path: `.env.${process.env.NODE_ENV}`,
});

const main = async () => {
  console.log("running migration...");
  const config = {
    host: process.env.PG_HOST,
    database: process.env.PG_DATABASE,
    user: process.env.PG_USER,
    password: process.env.PG_PASSWORD,
    port: process.env.PG_PORT,
  };

  const connectionString = `postgres://${config.user}:${config.password}@${
    config.host
  }${process.env.NODE_ENV === "docker" ? `:${config.port}` : ""}/${
    config.database
  }`;

  const client = new Client({
    connectionString,
    ssl:
      process.env.NODE_ENV === "production"
        ? true
        : process.env.NODE_ENV === "development"
        ? true
        : false,
  });

  await client.connect();

  const db = drizzle(client);

  await migrate(db, { migrationsFolder: "./.drizzle" });
};

main()
  .catch((error) => {
    console.error(error);
  })
  .then(() => {
    console.log("migration complete");
  })
  .finally(() => {
    process.exit(1);
  });

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

dotenv.config({
  path: `.env.${process.env.NODE_ENV}`,
});

const main = async () => {
  console.log("running migration...");
  const config = {
    host: process.env.PG_HOST,
    database: process.env.PG_DATABASE,
    user: process.env.PG_USER,
    password: process.env.PG_PASSWORD,
    port: process.env.PG_PORT,
  };

  const connectionString = `postgres://${config.user}:${config.password}@${
    config.host
  }${process.env.NODE_ENV === "docker" ? `:${config.port}` : ""}/${
    config.database
  }`;

  const client = new Client({
    connectionString,
    ssl:
      process.env.NODE_ENV === "production"
        ? true
        : process.env.NODE_ENV === "development"
        ? true
        : false,
  });

  await client.connect();

  const db = drizzle(client);

  await migrate(db, { migrationsFolder: "./.drizzle" });
};

main()
  .catch((error) => {
    console.error(error);
  })
  .then(() => {
    console.log("migration complete");
  })
  .finally(() => {
    process.exit(1);
  });

Gaming

Programming

How to apply migrations on postgres in a serverless enviroment?