@arthurfiorette/prisma-kysely

Do you like Prisma's migration flow, schema language and DX but not the limitations of the Prisma Client? Do you want to harness the raw power of SQL without losing the safety of the TypeScript type system?

Enter prisma-kysely!

Setup

1. Install prisma-kysely using your package manager of choice:

   bun add prisma-kysely
   

2. Replace (or augment) the default client generator in your schema.prisma file with the following:

   generator kysely {
       provider = "prisma-kysely"
   
       // Optionally provide a destination directory for the generated file
       // and a filename of your choice
       output = "../src/db"
       fileName = "types.ts"
       // Optionally generate runtime enums to a separate file
       enumFileName = "enums.ts"
       // Optionally add content at the start of generated files (imports, pragmas, comments, etc.)
       banner = "import type { Decimal } from 'decimal.js';"
       // For multi-line, use Prisma's triple-quoted string syntax (see below)
   }
   

3. Run prisma migrate dev or prisma generate and use your freshly generated types when instantiating Kysely!

Banner Configuration

The banner option allows you to add custom content at the start of generated files. This is particularly useful for importing custom types and libraries:

generator kysely {
    provider = "prisma-kysely"
    output = "../src/db"
    fileName = "types.ts"

    // Add custom imports for libraries you need in generated types
    banner = """
import type { Decimal } from 'decimal.js';
import { Big } from 'big.js';
import * as moment from 'moment';
import { v4 as uuid } from 'uuid';
import type { SomeType } from './custom-types';
"""
}

This will generate a file that starts with:

import type { Decimal } from "decimal.js";
import { Big } from "big.js";
import * as moment from "moment";
import { v4 as uuid } from "uuid";
import type { SomeType } from "./custom-types";

import type { ColumnType } from "kysely";
// ... rest of generated types

Motivation

Prisma's migration and schema definition workflow is undeniably great, and the typesafety of the Prisma client is top notch, but there comes a time in every Prisma user's life where the client becomes just a bit too limiting. Sometimes we just need to write our own multi table joins and squeeze that extra drop of performance out of our apps. The Prisma client offers two options: using their simplified query API or going all-in with raw SQL strings, sacrificing type safety.

This is where Kysely shines. Kysely provides a toolbox to write expressive, type-safe SQL queries with full autocompletion. The problem with Kysely though is that it's not super opinionated when it comes to schema definition and migration. What many users resort to is using something like Prisma to define the structure of their databases, and kysely-codegen to introspect their databases post-migration.

This package, prisma-kysely, is meant as a more integrated and convenient way to keep Kysely types in sync with Prisma schemas. After making the prerequisite changes to your schema file, it's just as convenient and foolproof as using Prisma's own client.

I've been using this combo for a few months now in tandem with Cloudflare's D1 for my private projects and Postgres at work. It's been a game-changer, and I hope it's just as useful for you! 😎

Config

Schema grouping

schemaGrouping controls how multi-schema models and enums are grouped.

With schemaGrouping = "namespace", all grouped schemas stay in the configured types file:

export namespace Animals {
  export type Dog = {
    id: string;
  };
}

export type DB = {
  "animals.dogs": Animals.Dog;
};

With schemaGrouping = "exports" and fileName = "types.ts", output is written to types/index.ts with schema files beside it, such as types/animals.ts:

// types/index.ts
import type * as Animals from "./animals";

export * as Animals from "./animals";

export type DB = {
  "animals.dogs": Animals.Dog;
};

// types/animals.ts
export type Dog = {
  id: string;
};

If schema-file exports make autocomplete noisy next to the main namespaced DB types, prefer a monorepo setup where Prisma and DB types live in their own package. From that package, export only the main namespaced types your app should consume.

PostgreSQL enum arrays

PostgreSQL drivers such as pg do not automatically parse user-defined enum array OIDs. See node-pg-types#56. If your app registers enum-array parsers, keep the default enumArrayType = "array". Otherwise set enumArrayType = "string" to match raw pg array literals.

Install postgres-array if you use the parser below. Register parsers once, before running queries that read enum-array columns:

import { Kysely, PostgresDialect } from "kysely";
import pg from "pg";
import { parse as parsePostgresArray } from "postgres-array";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

// Registers enum array types.
// https://github.com/brianc/node-pg-types/issues/56#issuecomment-323223477
{
  const { rows } = await pool.query<{ typarray: number | string }>(
    `
    select t.typarray
    from pg_type as t
    inner join pg_namespace as n on n.oid = t.typnamespace
    where n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
      and t.typtype = 'e'
      and t.typarray is not null
      and t.typarray != 0
  `
  );

  for (const row of rows) {
    pg.types.setTypeParser(Number(row.typarray), (value) =>
      value ? parsePostgresArray(value) : value
    );
  }
}

const db = new Kysely<DB>({
  dialect: new PostgresDialect({ pool }),
});

Per-field type overrides

In some cases, you might want to override a type for a specific field. This could be useful, for example, for constraining string types to certain literal values. Be aware though that this does not of course come with any runtime validation, and in most cases won't be guaranteed to match the actual data in the database.

That disclaimer aside, here's how it works: Add a @kyselyType(...) declaration to the Prisma docstring (deliniated using three slashes ///) for the field with your type inside the parentheses.

model User {
  id          String @id
  name        String

  /// @kyselyType('member' | 'admin')
  role        String
}

The parentheses can include any valid TS type declaration.

The output for the example above would be as follows:

export type User = {
  id: string;
  name: string;
  role: "member" | "owner";
};

Gotchas

Default values

By default (no pun intended) the Prisma Query Engine uses JS based implementations for certain default values, namely: uuid() and cuid(). This means that they don't end up getting defined as default values on the database level, and end up being pretty useless for us.

Prisma does provide a nice solution to this though, in the form of dbgenerated(). This allows us to use any valid default value expression that our database supports:

model PostgresUser {
   id    String @id @default(dbgenerated("gen_random_uuid()"))
}

model SQLiteUser {
   id    String @id @default(dbgenerated("(uuid())"))
}

Check out the Prisma Docs for more info.

Contributions

OMG you actually want to contribute? I'm so thankful! 🙇‍♂️

Here's everything you need to do (let me know if something's missing...)

1. Fork and pull the repository
2. Run bun install and bun dev to start tsc in watch mode.
3. Make changes to the source code
4. Test your changes by creating prisma/schema.prisma, running bun prisma generate and checking the output in prisma/types.ts. The provider must be set as follows to reference the dev build:

   generator kysely {
       provider = "node ./dist/bin.js"
   }
   

5. Create a pull request! If your changes make sense, I'll try my best to review and merge them quickly.

I'm not 100% sure the type maps are correct for every dialect, so any and all contributions on that front would be greatly appreciated. The same goes for any bug you come across or improvement you can think of.

Shoutouts

• I wouldn't have made this library if I hadn't used Robin Blomberg's amazing Kysely Codegen. For anyone that isn't using Prisma for migrations I wholeheartedly recommend his package.
• The implicit many-to-many table generation code is partly inspired by and partly stolen from prisma-dbml-generator. Many-too-many thanks to them!
• Igal Klebanov (@igalklebanov) and Jökull Sólberg (@jokull) for being this library's main proponents on Twitter!
• The authors and maintainers of Kysely ❤️‍🔥

+ Boyce-Codd gang unite! 💽