Using Vercel Postgres and developing locally

To confirm, if I want to use Vercel Postgres in production and a local Postgres DB for development, is the best way to just conditionally import drizzle (and other platform specific stuff like sql) from drizzle-orm/vercel-postgres or drizzle-orm/node-postgres based on NODE_ENV?

I see the sample Next.js project has been updated for Vercel Postgres: https://github.com/vercel/examples/blob/main/storage/postgres-drizzle/lib/drizzle.ts

Looks like Vercel Postgres doesn't give you a separate environment in the cloud automatically, and I accidentally started a Pro tier trial instead of going straight to Pro and now I can't even add a new DB on until the trial ends.

I guess you can use 1 import from drizzle-orm/vercel-postgres

Difference will be in providing connection config to drizzle

import { sql, createPool } from '@vercel/postgres'

const env = ... // any way you can mark env as local/prod. Maybe just process.env.STAGE

export const db = drizzle(env === 'LOCAL' ? createPool({<here will go simple node-postgres config>}) : sql)

You actually do get a connection string from Vercel, so I could just treat this as any other Postgres DB without using the Vercel adapter

Does @vercel/postgres do anything fancy like connection pooling that I'd be missing out on?

Although the solution above looks easy enough

yeah, as long as sql from Vercel will cover connection configs

and for local you just specify anything you need

looking into implementation

sql from Vercel is VercelPool type

Alright, will give this a shot shortly. Thanks!

I don't need to do any postinstall generation of stuff like Prisma needs, right? I'm already doing await migrate(db, { migrationsFolder: './drizzle' });

which is extends from node-postgres Pool

yeah, just run migration on db and that's should be it

Cool. I can't keep asking on the SST Discord how to get Prisma working again in a monorepo ;0

Dax might hurt my feelings

just use Drizzle 😅

if something won't work - feel free to ping us here

BTW, I was happy to get this random ordering working

export function getCards() {
  return db
    .select()
    .from(languageCardSchema)
    .orderBy(sql<number>`random()`)
    .limit(5);
}

Found it in some test file when searching the repo for 'random', since it wasn't in the main docs.

nice! also I think you can skip <number> in orderBy sql

sql type is useful for using in select to help infer response type properly

select({ id: sql<number>`count(*)`})

just small tip

Gotcha

it all will be in web docs

On, one last question. I manually added UNIQUE to my migration file because it looks like that's not yet supported. But there's something in GH issues here for 'add check constraints to docs' is that related to another way to do this for now? https://github.com/drizzle-team/drizzle-orm/issues/310

I think I've given you a hard time about the docs in the past on Twitter. Looking forward to the new ones!

yeah, UNIQUE constraints are not yet supported. But will be soon

you did right thing to add it manually to sql migration file

just be sure to modify sql file before applying to database

it's the only restriction

other than that you can do with sql file anything you want

Yeah, I just realised that I started my Drizzle folder from scratch again and forgot to reapply it to the latest migration file.

Alright, will try this Vercel Postgres thing later. Plenty of progress already this morning. Thanks again!

🫡

Oh, I guess one more comment: It took me a little bit of digging until I figured out you can do this with chaning .array like this. I thought I had to start with array(...) e.g.

  vocabulary: text('vocabulary').array().array().notNull(),

yeah, we tried to copy sql syntax

so you write text and then [] []

same here

text.array.array

Will keep that in mind. Have been living in Prisma land for so long.

👋

I mean you can add array not only to text

so it's easier to just have .array function in any type vs array + duplicating all types in builder

so IDE suggestions will become a mess

Should it look something like this?

import { createPool, sql } from '@vercel/postgres';
import { eq } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/vercel-postgres';
import { migrate } from 'drizzle-orm/vercel-postgres/migrator';

export const db = drizzle(
  process.env.NODE_ENV === 'production'
    ? sql
    : createPool({
        connectionString: process.env.POSTGRES_URL,
      })
);

That doesn't seem to work

n [VercelPostgresError]: VercelPostgresError - 'invalid_connection_string': This connection string is meant to be used with a direct connection. Make sure to use a pooled connection string or try `createClient()` instead.

This also seems to have stooped working:

.orderBy(sql`random()`)

No overload matches this call.
  Overload 1 of 2, '(builder: (aliases: { id: PgSerial<{ tableName: "language_cards"; name: "id"; data: number; driverParam: number; hasDefault: true; notNull: true; }>; hskLevel: PgSmallInt<{ tableName: "language_cards"; name: "hsklevel"; data: number; driverParam: string | number; hasDefault: false; notNull: true; }>; ... 6 more ...; audio: PgText<...>; }) => ValueOrArray<...>): PgSelect<...>', gave the following error.
    Argument of type 'Promise<QueryResult<QueryResultRow>>' is not assignable to parameter of type '(aliases: { id: PgSerial<{ tableName: "language_cards"; name: "id"; data: number; driverParam: number; hasDefault: true; notNull: true; }>; hskLevel: PgSmallInt<{ tableName: "language_cards"; name: "hsklevel"; data: number; driverParam: string | number; hasDefault: false; notNull: true; }>; ... 6 more ...; audio: Pg...'.
      Type 'Promise<QueryResult<QueryResultRow>>' provides no match for the signature '(aliases: { id: PgSerial<{ tableName: "language_cards"; name: "id"; data: number; driverParam: number; hasDefault: true; notNull: true; }>; hskLevel: PgSmallInt<{ tableName: "language_cards"; name: "hsklevel"; data: number; driverParam: string | number; hasDefault: false; notNull: true; }>; ... 6 more ...; audio: PgText<...>; }): ValueOrArray<...>'.
  Overload 2 of 2, '(...columns: (SQL<unknown> | AnyPgColumn<{}> | Aliased<unknown>)[]): PgSelect<"language_cards", { id: PgSerial<{ tableName: "language_cards"; name: "id"; data: number; driverParam: number; hasDefault: true; notNull: true; }>; ... 7 more ...; audio: PgText<...>; }, "single", Record<...>>', gave the following error.
    Argument of type 'Promise<QueryResult<QueryResultRow>>' is not assignable to parameter of type 'SQL<unknown> | AnyPgColumn<{}> | Aliased<unknown>'.

Try to use createClient function from Vercel

Instead of createPool

It should be imported from same path

Anything that hits the DB seems to cause it to just exit silently. At first it exited without any errors when it checked for migrations, then when I commented that out and reran it, it happened again the moment I try and write to the DB

import { createClient, sql } from '@vercel/postgres';
import { eq } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/vercel-postgres';
import { migrate } from 'drizzle-orm/vercel-postgres/migrator';

export const db = drizzle(
  process.env.NODE_ENV === 'production'
    ? sql
    : createClient({
        connectionString: process.env.POSTGRES_URL,
      })
);

So no errors now? Just nothing happens on query execution?

Yeah, I wrapped the migrate command in some quick console log debug statements and it just exits right in the middle. Not even a non zero exit code

That’s something I may help you tomorrow. It’s late here already
Will get back to you tomorrow with suggestions and trying to reproduce it locally

You can check, that migration path was proper and also drizzle function has 2nd param with logger: true config to enable all query logs in terminal

If nothing will help, I’ll be here tomorrow

No worries. Also late here too (London)

Thanks for trying to figure this out with me

🫡

(for tomorrow)

With the logger enabled, the only output from that I see is the query it tries to run before it silently exits.

The migration path is fine, since I used this exact code to migrate for NODE_ENV === 'production' and that created the table in the non local Vercel Postgres DB just fine.

So my randomisation problem was down to me not understanding server components and SSR. It was statically creating the page based on a random list at runtime.

In the end I fixed it by using the new server action feature:

import { getRandomCards } from '~/db/db';

async function getCards() {
  'use server';

  return getRandomCards();
}

export default async function FlashCards() {
  const cards = await getCards();

  return ...
}

Well. This explains the main issue of using a local DB: It'll return that error if my URL doesn't satisfy this check.

export function isPooledConnectionString(connectionString: string): boolean {
  return connectionString.includes('-pooler.');
}

Hah! Guess I can rename my local URL to include that anywhere.

https://github.com/vercel/storage/blob/8b51d484e3a0b3e3f0f487e7e6313b5a8274dd50/packages/postgres/src/postgres-connection-string.ts#L33

For now, I just worked around this by using each adapter/migrator separately:

import { sql } from '@vercel/postgres';
import { eq, sql as sqlDrizzle } from 'drizzle-orm';
import { drizzle as drizzleNode } from 'drizzle-orm/node-postgres';
import { drizzle as drizzleVercel } from 'drizzle-orm/vercel-postgres';
import { migrate as migrateNode } from 'drizzle-orm/node-postgres/migrator';
import { migrate as migrateVercel } from 'drizzle-orm/vercel-postgres/migrator';

import { Pool } from 'pg';

import { languageCard, NewLanguageCard } from './schema';

console.log('process.env.NODE_ENV', process.env.NODE_ENV);
console.log('process.env.POSTGRES_URL', process.env.POSTGRES_URL);

const db =
  process.env.NODE_ENV === 'production'
    ? drizzleVercel(sql)
    : drizzleNode(new Pool({ connectionString: process.env.POSTGRES_URL }));

export async function dbMigrate() {
  if (process.env.NODE_ENV === 'production')
    await migrateVercel(db, { migrationsFolder: './drizzle' });
  else await migrateNode(db, { migrationsFolder: './drizzle' });
}

So I think this wraps up this thread.

For future reference, there is mention of a PR that would help here: https://github.com/vercel/storage/issues/123#issuecomment-1537719772

@joostschuur Curious how you invoked your migrations from a Vercel project?

Whenever my lambda or util script fires up, it calls this migrate function. I just use migrator from 'drizzle-orm/vercel-postgres/migrator for production instead of the one from drizzle-orm/node-postgres/migrator for local dev. Bit of a workaround for now, but it works.

https://github.com/jschuur/learnchinese.club/blob/4eca033b376aea76a6aedbb86c1d316e48e0bc82/src/db/db.ts#L18