Run vercel-postgres in production and locally through neon with drizzle orm

[sebnapi]

Summary

Hi,

I come from a 'serverfull' background and was surprised how hard it seems to get this working:
I want to develop locally with a docker run postgres instance, and run vercel-postgres in production.

I tried to adapt the example here: https://github.com/vercel/examples/tree/main/storage/postgres-drizzle

But this example is already outdated and doesn't use dirzzle-kit to generate migrations, when I started to add everything I was running into outdated syntax and once I updated all packages the site was 'bricked'.

Then I used this example: https://github.com/vercel/examples/tree/main/storage/postgres-pgvector

This one has at least a drizzle.config.ts is using drizzle-kit and isn't outdated. Once I've added the possibility to run a postgres instance locally I ran into:

#50177

Then I found this example which adds the Neon proxy: https://github.com/vvo/local-vercel-postgres-development
And I adapted it like this:

src/db.ts

import { neonConfig } from "@neondatabase/serverless";

if (process.env.VERCEL_ENV === "development") {
  neonConfig.wsProxy = (host) => `${host}:54330/v1`;
  neonConfig.useSecureWebSocket = false;
  neonConfig.pipelineTLS = false;
  neonConfig.pipelineConnect = false;
}

import { sql } from '@vercel/postgres';
import { drizzle } from 'drizzle-orm/vercel-postgres';

export const db = drizzle(sql)

async function executeQuery() {
  const result = await db.execute('select 1');
  console.log(result);
}

executeQuery();

// export * from "@vercel/postgres";

drizzle.config.ts

import { defineConfig } from 'drizzle-kit'
import { config } from 'dotenv';

config({ path: '.env.development' });

export default defineConfig({
  schema: './src/schema/*.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.POSTGRES_URL!,
  },
  // prints all statements that will be executed: https://orm.drizzle.team/kit-docs/config-reference#verbose
  verbose: true,
  // always ask for your confirmation to execute statements: https://orm.drizzle.team/kit-docs/config-reference#strict
  // strict: true,
})

Running npx drizzle-kit generate --name=init
Generated the correct migration scripts.

But running npx drizzle-kit migrate results in

No config path provided, using default 'drizzle.config.ts'
Reading config file './drizzle.config.ts'
Using '@vercel/postgres' driver for database querying
Warning '@vercel/postgres' can only connect to remote Neon/Vercel Postgres/Supabase instances through a websocket
ErrorEvent {
[Symbol(kTarget)]: WebSocket {
_events: [Object: null prototype] {
error: [Function],
message: [Function],
close: [Function],
open: [Function]
},
_eventsCount: 4,
_maxListeners: undefined,
_binaryType: 'arraybuffer',
_closeCode: 1006,
_closeFrameReceived: false,
_closeFrameSent: false,
_closeMessage: ,
_closeTimer: null,
_errorEmitted: true,
_extensions: {},
_paused: false,
_protocol: '',
_readyState: 3,
_receiver: null,
_sender: null,
_socket: null,
_bufferedAmount: 0,
_isServer: false,
_redirects: 0,
_autoPong: true,
_url: 'wss://localhost/v2',
_req: null,
[Symbol(shapeMode)]: false,
[Symbol(kCapture)]: false
},
[Symbol(kType)]: 'error',
[Symbol(kError)]: Error: read ECONNRESET
at TLSWrap.onStreamRead (node:internal/stream_base_commons:218:20)
at TLSWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
errno: -54,
code: 'ECONNRESET',
syscall: 'read'
},
[Symbol(kMessage)]: 'read ECONNRESET'
}

It would be great if there would be a full working starter kit that facilitates a professional level environment that just works. I'm coming originally from the python world and the amount of complexity for this simple task seems unreasonable, as @vvo learned vercel/storage#123 (comment) here.

Maybe we can use this discussion to build a bullet proof starter. I'm unsure how far I am, maybe it's 95% done ...

Additional information

./src/schema/user.ts

import * as p from "drizzle-orm/pg-core";
import { InferInsertModel, InferSelectModel } from 'drizzle-orm'


export const UsersTable = p.pgTable("users", {
  id: p.serial().primaryKey(),
  name: p.text(),
})

export type User = InferSelectModel<typeof UsersTable>
export type NewUser = InferInsertModel<typeof UsersTable>

### Example

_No response_

[mmikhan]

Posted as an idea to write an official documentation for it here #71534

[issacclee]

Summary

Hi,

I come from a 'serverfull' background and was surprised how hard it seems to get this working: I want to develop locally with a docker run postgres instance, and run vercel-postgres in production.

I tried to adapt the example here: vercel/examples@main/storage/postgres-drizzle

But this example is already outdated and doesn't use dirzzle-kit to generate migrations, when I started to add everything I was running into outdated syntax and once I updated all packages the site was 'bricked'.

Then I used this example: vercel/examples@main/storage/postgres-pgvector

This one has at least a drizzle.config.ts is using drizzle-kit and isn't outdated. Once I've added the possibility to run a postgres instance locally I ran into:

#50177

Then I found this example which adds the Neon proxy: vvo/local-vercel-postgres-development And I adapted it like this:

src/db.ts

import { neonConfig } from "@neondatabase/serverless";

if (process.env.VERCEL_ENV === "development") {
  neonConfig.wsProxy = (host) => `${host}:54330/v1`;
  neonConfig.useSecureWebSocket = false;
  neonConfig.pipelineTLS = false;
  neonConfig.pipelineConnect = false;
}

import { sql } from '@vercel/postgres';
import { drizzle } from 'drizzle-orm/vercel-postgres';

export const db = drizzle(sql)

async function executeQuery() {
  const result = await db.execute('select 1');
  console.log(result);
}

executeQuery();

// export * from "@vercel/postgres";

drizzle.config.ts

import { defineConfig } from 'drizzle-kit'
import { config } from 'dotenv';

config({ path: '.env.development' });

export default defineConfig({
  schema: './src/schema/*.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.POSTGRES_URL!,
  },
  // prints all statements that will be executed: https://orm.drizzle.team/kit-docs/config-reference#verbose
  verbose: true,
  // always ask for your confirmation to execute statements: https://orm.drizzle.team/kit-docs/config-reference#strict
  // strict: true,
})

Running npx drizzle-kit generate --name=init Generated the correct migration scripts.

But running npx drizzle-kit migrate results in

No config path provided, using default 'drizzle.config.ts'
Reading config file './drizzle.config.ts'
Using '@vercel/postgres' driver for database querying
Warning '@vercel/postgres' can only connect to remote Neon/Vercel Postgres/Supabase instances through a websocket
ErrorEvent {
[Symbol(kTarget)]: WebSocket {
_events: [Object: null prototype] {
error: [Function],
message: [Function],
close: [Function],
open: [Function]
},
_eventsCount: 4,
_maxListeners: undefined,
_binaryType: 'arraybuffer',
_closeCode: 1006,
_closeFrameReceived: false,
_closeFrameSent: false,
_closeMessage: ,
_closeTimer: null,
_errorEmitted: true,
_extensions: {},
_paused: false,
_protocol: '',
_readyState: 3,
_receiver: null,
_sender: null,
_socket: null,
_bufferedAmount: 0,
_isServer: false,
_redirects: 0,
_autoPong: true,
_url: 'wss://localhost/v2',
_req: null,
[Symbol(shapeMode)]: false,
[Symbol(kCapture)]: false
},
[Symbol(kType)]: 'error',
[Symbol(kError)]: Error: read ECONNRESET
at TLSWrap.onStreamRead (node:internal/stream_base_commons:218:20)
at TLSWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
errno: -54,
code: 'ECONNRESET',
syscall: 'read'
},
[Symbol(kMessage)]: 'read ECONNRESET'
}

It would be great if there would be a full working starter kit that facilitates a professional level environment that just works. I'm coming originally from the python world and the amount of complexity for this simple task seems unreasonable, as @vvo learned vercel/storage#123 (comment) here.

Maybe we can use this discussion to build a bullet proof starter. I'm unsure how far I am, maybe it's 95% done ...

Additional information

./src/schema/user.ts

import * as p from "drizzle-orm/pg-core";
import { InferInsertModel, InferSelectModel } from 'drizzle-orm'


export const UsersTable = p.pgTable("users", {
  id: p.serial().primaryKey(),
  name: p.text(),
})

export type User = InferSelectModel<typeof UsersTable>
export type NewUser = InferInsertModel<typeof UsersTable>

### Example

_No response_

did u manage to get the migration error solved eventually? I'm in the same situation. Next app is fine, but migration does not work.

[mjewell]

I was able to get this to work by just installing the postgres package (e.g. npm install postgres). Then when you migrate, drizzle-kit will automatically detect that and use it instead of @vercel/postgres.

No config path provided, using default 'drizzle.config.ts'
Reading config file 'abc/def/drizzle.config.ts'
Using 'postgres' driver for database querying

[koptevs]

Same situation with "drizzle-kit generate"... - drizzle-orm/neon-http/migrator solves the problem.
Source:
https://www.youtube.com/watch?v=tiSm8ZjFQP0&list=PL0Zuz27SZ-6PCLz7VMP2QQdeKa83rshe5&index=3&ab_channel=DaveGray

File:
migrate.ts:

import { db } from '@/server/db/db';
import { migrate } from 'drizzle-orm/neon-http/migrator';

const main = async () => {
    try {
        await migrate(db, {
            migrationsFolder: 'src/server/db/migrations',
        });
        console.log('Migration completed');
    } catch (error) {
        console.error('Error during migration:', error);
        process.exit(1);
    }
};
main();

then run
tsx ./src/server/db/migrate.ts

TypeScript Execute (tsx): The easiest way to run TypeScript in Node.js ( https://www.npmjs.com/package/tsx )