Prisma Kysely Extension

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

Get started

Click the Use this template button and provide details for your Client extension

Install the dependencies:

npm install prisma-extension-kysely kysely

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 kyselyExtension from "prisma-extension-kysely";
import type { DB } from "./prisma/generated/types";

const prisma = new PrismaClient().$extends(
  kyselyExtension({
    kysely: (driver) =>
      new Kysely({
        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 remeber 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 prisma = new PrismaClient().$extends(
  kyselyExtension({
    kysely: (driver) =>
      new Kysely({
        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 = {
  kysely: (driver) =>
    new Kysely({
      dialect: {
        createAdapter: () => new SqliteAdapter(),
        createDriver: () => driver,
        createIntrospector: (db) => new SqliteIntrospector(db),
        createQueryCompiler: () => new SqliteQueryCompiler(),
      },
    }),
};

// Initialize the replica client(s) and add the Kysely extension
const replicaClient = new PrismaClient({
  datasourceUrl: "YOUR_REPLICA_URL", // Replace this with your replica's URL!
  log: [{ level: "query", emit: "event" }],
}).$extends(kyselyExtension(kyselyExtensionArgs));

// Initialize the primary client and add the Kysely extension and the read replicas extension
const prisma = new PrismaClient()
  .$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 make 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!

// Don't do this! It won't work as expected.
readReplicas({
  url: "postgresql://user:password@localhost:5432/dbname",
});

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
npx prisma db push
npm run dev

Learn more

• Kysely
• prisma-kysely

License

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