@vercel/postgres

@vercel/postgres 🚧

A client that works with Vercel Postgres.

Quick Start

Install

pnpm install @vercel/postgres

[RECOMMENDED] Using an ORM (Kysely)

Kysely is supported out of the box. In order to use Kysely, you need to import it and install kysely as a dependency for your project:

pnpm i kysely

Specify a schema:

import { Generated, ColumnType } from 'kysely';

interface PersonTable {
  // Columns that are generated by the database should be marked
  // using the `Generated` type. This way they are automatically
  // made optional in inserts and updates.
  id: Generated<number>;

  first_name: string;
  gender: 'male' | 'female' | 'other';

  // If the column is nullable in the database, make its type nullable.
  // Don't use optional properties. Optionality is always determined
  // automatically by Kysely.
  last_name: string | null;

  // You can specify a different type for each operation (select, insert and
  // update) using the `ColumnType<SelectType, InsertType, UpdateType>`
  // wrapper. Here we define a column `modified_at` that is selected as
  // a `Date`, can optionally be provided as a `string` in inserts and
  // can never be updated:
  modified_at: ColumnType<Date, string | undefined, never>;
}

interface PetTable {
  id: Generated<number>;
  name: string;
  owner_id: number;
  species: 'dog' | 'cat';
}

interface MovieTable {
  id: Generated<string>;
  stars: number;
}

// Keys of this interface are table names.
interface Database {
  person: PersonTable;
  pet: PetTable;
  movie: MovieTable;
}

Now you can use this type by creating a new pooled Kysely connection. Note: your database connection string will be automatically retrieved from your environment variables. This uses createPool from @vercel/postgres under the hood.

import { createKyselyPool } from '@vercel/postgres/kysely';

interface Database {
  person: PersonTable;
  pet: PetTable;
  movie: MovieTable;
}

const db = createKyselyPool<Database>();

await db
  .insertInto('pet')
  .values({ name: 'Catto', species: 'cat', owner_id: id })
  .execute();

const person = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select(['first_name', 'pet.name as pet_name'])
  .where('person.id', '=', id)
  .executeTakeFirst();

Note: If you would like to use a Client with Kysely instead, call createKyselyClient. However, it is recommended to use pooling.

For more information on using Kysely, checkout the docs: https://github.com/kysely-org/kysely

Create a Raw Pool

If you need a raw pg Pool object, you can use the createPool function.

Automatically uses process.env.POSTGRES_URL:

import { createPool } from '@vercel/postgres';

const pool = createPool();

const { rows, fields } = await pool.query(
  'SELECT * from POSTS WHERE likes > 100;',
);

To specify a connection string:

import { createPool } from '@vercel/postgres';

const pool = createPool({
  connectionString: process.env.SOME_POSTGRES_CONNECTION_STRING,
});

const { rows, fields } = await pool.query(
  'SELECT * from POSTS WHERE likes > 100;',
);

Create a Raw Client

If you need a raw pg Client object, you can use the createPool function.

Automatically uses process.env.POSTGRES_URL_NON_POOLING:

import { createClient } from '@vercel/postgres';

const client = createClient();

const { rows, fields } = await client.query(
  'SELECT * from POSTS WHERE likes > 100;',
);

To specify a connection string:

import { createClient } from '@vercel/postgres';

const client = createClient({
  connectionString: process.env.SOME_POSTGRES_CONNECTION_STRING,
});

const { rows, fields } = await client.query(
  'SELECT * from POSTS WHERE likes > 100;',
);

Get the connection url

If you just want the connection URL, you can call postgresConnectionString(type: 'pool' | 'direct'): string;. This will read from your environment variables. For the pool type, it will look for the POSTGRES_URL environment variables. For the direct type, it will look for the POSTGRES_URL_NON_POOLING environment variables.

import { postgresConnectionString } from '@vercel/postgres';

const pooledConnectionString = postgresConnectionString('pool');
const directConnectionString = postgresConnectionString('direct');

Connection Config

When using the createClient or createPool functions, you can pass in additional options alongside the connection string that conforms to VercelPostgresClientConfig or VercelPostgresPoolConfig.

Documentation

The @vercel/postgres package uses the pg package. For more detailed documentation, checkout node-postgres.