Prisma Kysely Extension

Important

v4.0 now requires Prisma 7. Prisma 7 makes driver adapters a required part of PrismaClient — see the Prisma 7 upgrade guide to migrate your project. If you are on Prisma 5 or 6, continue using prisma-extension-kysely v3.

Writing and maintaining raw SQL queries for Prisma can be a tedious and error-prone task. The moment you need to write a query that is not supported out-of-the-box by Prisma, you lose all of that type-safety and autocompletion. This is where prisma-extension-kysely comes in! It allows you to easily write raw SQL queries in a type-safe manner with kysely and integrate them seamlessly with Prisma.

And the best part? You can use all of your favorite kysely plugins with prisma-extension-kysely too!

You don't have to take our word for it, though:

I have to say, this is BY FAR the most amazing community package I've seen in the Prisma ecosystem!

It makes it so much more convenient to drop down to raw SQL when needed without sacrificing DX — best of both worlds! 🚀

— Nikolas Burk, DevRel @ Prisma

Features

• Type-safe — Write raw SQL queries in a type-safe manner with kysely
• Seamless integration — Use kysely queries with Prisma as if they were native
• Autocompletion — Get autocompletion for your queries in your IDE
• Type inference — Get type inference for your queries in your IDE

Compatibility

prisma-extension-kysely	Prisma	Kysely
v3.x	^5.0 | ^6.0	^0.27 | ^0.28
v4.x	^7.0	^0.27 | ^0.28

Get started

Install the dependencies:

npm install prisma-extension-kysely kysely

Install the driver adapter for your database. For example, for PostgreSQL:

npm install @prisma/adapter-pg pg

Set up the excellent prisma-kysely library to automatically generate types for your database:

npm install -D prisma-kysely

Add prisma-kysely as a generator to your schema.prisma:

generator kysely {
  provider = "prisma-kysely"
}

Generate the types:

npx prisma generate

Extend your Prisma Client:

import { PrismaPg } from "@prisma/adapter-pg";
import {
  Kysely,
  PostgresAdapter,
  PostgresIntrospector,
  PostgresQueryCompiler,
} from "kysely";
import kyselyExtension from "prisma-extension-kysely";
import type { DB } from "./generated/kysely/types"; // Your path may vary depending on your setup in schema.prisma

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
});

const prisma = new PrismaClient({ adapter }).$extends(
  kyselyExtension({
    kysely: (driver) =>
      new Kysely<DB>({
        dialect: {
          // This is where the magic happens!
          createDriver: () => driver,
          // Don't forget to customize these to match your database!
          createAdapter: () => new PostgresAdapter(),
          createIntrospector: (db) => new PostgresIntrospector(db),
          createQueryCompiler: () => new PostgresQueryCompiler(),
        },
        plugins: [
          // Add your favorite plugins here!
        ],
      }),
  }),
);

It's that simple! Now you can write raw SQL queries with kysely and use them with Prisma:

// Replace this...
const result = prisma.$queryRaw`SELECT * FROM User WHERE id = ${id}`;

// With this!
const query = prisma.$kysely
  .selectFrom("User")
  .selectAll()
  .where("id", "=", id);

// Thanks to kysely's magic, everything is type-safe!
const result = await query.execute();

// You can also execute queries without fetching the results
await prisma.$kysely.deleteFrom("User").where("id", "=", id).execute();

Transactions

Prisma's interactive transactions are fully supported by prisma-extension-kysely! Just remember to use tx.$kysely instead of prisma.$kysely, and you're good to go:

await prisma.$transaction(async (tx) => {
  await tx.$kysely
    .insertInto("User")
    .values({ id: 1, name: "John Doe" })
    .execute();

  await tx.$kysely
    .insertInto("User")
    .values({ id: 2, name: "Jane Doe" })
    .execute();
});

Don't try to use Kysely's transaction method directly, though. It's not supported by prisma-extension-kysely, and it will throw an error if you try to use it.

// Don't do this! Prefer prisma.$transaction instead.
await prisma.$kysely.transaction().execute(async (trx) => {});

Plugins

Do you love Kysely's plugins? So do we! You can use them with prisma-extension-kysely as well:

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
});

const prisma = new PrismaClient({ adapter }).$extends(
  kyselyExtension({
    kysely: (driver) =>
      new Kysely<DB>({
        dialect: {
          createDriver: () => driver,
          createAdapter: () => new PostgresAdapter(),
          createIntrospector: (db) => new PostgresIntrospector(db),
          createQueryCompiler: () => new PostgresQueryCompiler(),
        },
        // Use your favorite plugins!
        plugins: [new CamelCasePlugin()],
      }),
  }),
);

If you're using the CamelCasePlugin, don't forget to add the camelCase option to your Prisma schema too:

generator kysely {
  provider = "prisma-kysely"
  camelCase = true
}

Take a look at the camel case example to see it in action! Check out the Kysely documentation for more information about plugins.

Read Replicas

Using read replicas with prisma-extension-kysely is a breeze! Just use the excellent @prisma/extension-read-replicas extension as normal. Pay attention to how it's configured, though:

// Use a common config for primary and replica clients (or different configs)
const kyselyExtensionArgs: PrismaKyselyExtensionArgs<DB> = {
  kysely: (driver) =>
    new Kysely<DB>({
      dialect: {
        createAdapter: () => new PostgresAdapter(),
        createDriver: () => driver,
        createIntrospector: (db) => new PostgresIntrospector(db),
        createQueryCompiler: () => new PostgresQueryCompiler(),
      },
    }),
};

// Initialize the replica client(s) with its own adapter and add the Kysely extension
const replicaAdapter = new PrismaPg({
  connectionString: process.env.REPLICA_DATABASE_URL,
});
const replicaClient = new PrismaClient({ adapter: replicaAdapter }).$extends(
  kyselyExtension(kyselyExtensionArgs),
);

// Initialize the primary client with its own adapter, then add the Kysely
// extension and the read replicas extension
const primaryAdapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
});
const prisma = new PrismaClient({ adapter: primaryAdapter })
  .$extends(kyselyExtension(kyselyExtensionArgs)) // Apply the Kysely extension before the read replicas extension!
  .$extends(
    readReplicas({
      replicas: [replicaClient],
    }),
  ); // Apply the read replicas extension after the Kysely extension!

See how we're setting up the replica client as a fully-fledged Prisma client and extending it separately? That's the secret sauce! It makes sure that the replica client has a separate Kysely instance. If you try to use bare URLs, you'll run into trouble; it'll share the same Kysely instance as the primary client, and you'll get unpleasant surprises!

Also, note that we're applying the Kysely extension before the read replicas extension. This is important! If you apply the read replicas extension first, you won't get .$kysely on the primary client.

Check out the read replicas example for a runnable example!

Examples

Check out the examples directory for a sample project!

cd examples/basic
npm install
npm start

Learn more

• Kysely
• prisma-kysely
• Prisma 7 upgrade guide

License

This project is licensed under the MIT License - see the LICENSE file for details.