postgres-kysely

@vercel/postgres-kysely

A @vercel/postgres wrapper for the Kysely query builder.

Quick Start

Note: If you want to write your own queries instead of using a query builder, see @vercel/postgres.

Install

pnpm install @vercel/postgres-kysely

Creating a Kysely Database Object

Kysely is a peer dependency of this project, so you need to install it 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 { createKysely } from '@vercel/postgres-kysely';

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

const db = createKysely<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();

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

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.

A note for Vite users

@vercel/postgres-kysely reads database credentials from the environment variables on process.env. In general, process.env is automatically populated from your .env file during development, which is created when you run vc env pull. However, Vite does not expose the .env variables on process.env.

You can fix this in one of following two ways:

1. You can populate process.env yourself using something like dotenv-expand:

pnpm install --save-dev dotenv dotenv-expand

// vite.config.js
import dotenvExpand from 'dotenv-expand';
import { loadEnv, defineConfig } from 'vite';

export default defineConfig(({ mode }) => {
  // This check is important!
  if (mode === 'development') {
    const env = loadEnv(mode, process.cwd(), '');
    dotenvExpand.expand({ parsed: env });
  }

  return {
    ...
  };
});

2. You can provide the credentials explicitly, instead of relying on a zero-config setup. For example, this is how you could create a client in SvelteKit, which makes private environment variables available via $env/static/private:

import { createKysely } from '@vercel/postgres-kysely';
+ import { POSTGRES_URL } from '$env/static/private';

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

- const db = createKysely<Database>();
+ const db = createKysely<Database>({
+  connectionString: POSTGRES_URL,
+ });