# @vercel/postgres-kysely

A `@vercel/postgres` wrapper for the [Kysely](https://github.com/kysely-org/kysely) query builder.

## Quick Start

**Note:** If you want to write your own queries instead of using a query builder, see [@vercel/postgres](https://npmjs.org/package/@vercel/postgres).

### Install

```bash
pnpm install @vercel/postgres-kysely
```

### Creating a Kysely Database Object

[Kysely](https://github.com/kysely-org/kysely) is a peer dependency of this project, so you need to install it as a dependency for your project:

```bash
pnpm i kysely
```

Specify a schema:

```typescript
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](https://github.com/kysely-org/kysely) connection. Note: your database connection
string will be automatically retrieved from your environment variables. This uses `createPool` from
`@vercel/postgres` under the hood.

```typescript
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](https://github.com/kysely-org/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`:

```shell
pnpm install --save-dev dotenv dotenv-expand
```

```js
// 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`:

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