Topics

Drizzle Team•2y ago

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

36 Replies

joostschuurOP•2y ago

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.

Andrii Sherman•2y ago

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)

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)

joostschuurOP•2y ago

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

Andrii Sherman•2y ago

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

joostschuurOP•2y ago

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' });

Andrii Sherman•2y ago

which is extends from node-postgres Pool yeah, just run migration on db and that's should be it

joostschuurOP•2y ago

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

Andrii Sherman•2y ago

just use Drizzle 😅 if something won't work - feel free to ping us here

joostschuurOP•2y ago

BTW, I was happy to get this random ordering working

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

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.

Andrii Sherman•2y ago

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(*)`})

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

just small tip

joostschuurOP•2y ago

Gotcha

Andrii Sherman•2y ago

it all will be in web docs

joostschuurOP•2y ago

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

GitHub

Add check constraints to docs · Issue #310 · drizzle-team/drizzle-o...

joostschuurOP•2y ago

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

Andrii Sherman•2y ago

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

joostschuurOP•2y ago

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

joostschuurOP•2y ago

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

No description

Andrii Sherman•2y ago

🫡

joostschuurOP•2y ago

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(),

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

Andrii Sherman•2y ago

yeah, we tried to copy sql syntax so you write text and then [] [] same here text.array.array

joostschuurOP•2y ago

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

Andrii Sherman•2y ago

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

joostschuurOP•2y ago

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,
      })
);

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.

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()`)

.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>'.

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>'.

joostschuurOP•2y ago

https://ai-mandarin-flashcards.vercel.app/

Create Next App

Generated by create next app

Andrii Sherman•2y ago

Try to use createClient function from Vercel Instead of createPool It should be imported from same path

joostschuurOP•2y ago

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,
      })
);

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,
      })
);

Andrii Sherman•2y ago

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

joostschuurOP•2y ago

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

Andrii Sherman•2y ago

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

joostschuurOP•2y ago

No worries. Also late here too (London) Thanks for trying to figure this out with me

Andrii Sherman•2y ago

🫡

drizzle

joostschuurOP•2y ago

(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.

No description

joostschuurOP•2y ago

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 ...
}

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.');
}

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' });
}

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.

joostschuurOP•2y ago

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

GitHub

Trying to use a local DB with Vercel Postgres fails due to strict p...

I thought I could use Vercel Postgres with a local DB for offline development with a custom connection string that points to a local DB... import { createPool, sql } from '@vercel/postgres'...

McLean 25•2y ago

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

joostschuurOP•2y ago

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

GitHub

learnchinese.club/db.ts at 4eca033b376aea76a6aedbb86c1d316e48e0bc82...

AI generated flashcards for Mandarin Chinese. Contribute to jschuur/learnchinese.club development by creating an account on GitHub.

Drizzle Team Join

The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!

9.8KMembers

View on Discord

Want results from more Discord servers?

Add your server