Building Typesafe SQL Queries in Bun

Building Typesafe SQL Queries in Bun

Code
Published:

How can we write type safe SQL queries in Bun? There is a Query Builder called Kysely. It makes writing type safe SQL easy, without adding a lot of runtime dependency overhead to our app.

Cover image

In this article, we will explore Kysely with Bun in a demo application. But first, let's clarify what Kysely is.

What is Kysely?

Kysely is a type-safe SQL Query Builder for TypeScript. It does only one thing very well. It generates SQL queries according to your dialect.

If you know SQL, using Kysely will be very straightforward.

Kysely's type-safety allows us to spot errors at compile time, which would normally occur only during execution time. Those errors are typically schema or syntax related.

And because Kysely understands our schema, we have great autocompletion when creating our queries.

With this in mind, let's directly dive in and see it in action.

Setup

Let's first set up our project with Bun. The command bun init -y lets us directly start our project with TypeScript.

Let's now install Kysely via bun add kysely .

In this demo, we will use SQLite, so let's install the kysely-bun-worker dialect. It generally makes sense to run your SQLite access with a worker or a separate process because SQLite queries will block your main thread. And this is what we want to avoid at all costs when running JavaScript on the server.

Now we can create our Kysely instance.

import { Kysely, sql } from "kysely";
import { BunWorkerDialect } from "kysely-bun-worker";
import type { Schema } from "./schema";

const dialect = new BunWorkerDialect();
export const db = new Kysely<Schema>({ dialect });

await sql`PRAGMA foreign_keys = ON;`.execute(db);

For this, we first create our dialect. If we don't configure this dialect, it will by default create an in-memory database for us. This is sufficient for our use case.

Now we are ready to go to execute SQL queries.

When using SQLite, we have to turn on foreign keys if we want to use them. Let's do this first.

Kysely provides us with an SQL tag template literal function, which we can use to execute raw SQL queries. Let's use this to enable our foreign keys. The query will be executed within our Kysely instance.

Schema

Before we write our actual migrations, let's quickly define our target schema as TypeScript definitions. In this demo, we will create basic blog posts and comments to those blog posts. So let's create the necessary types for this.

import type {
  Generated,
  Insertable,
  Selectable,
  Updateable,
} from "kysely";

export interface Schema {
  blogPost: BlogPostTable;
  comment: CommentTable;
}

export interface BlogPostTable {
  id: Generated<number>;
  title: string;
}

export type BlogPost = Selectable<BlogPostTable>;
export type NewBlogPost = Insertable<BlogPostTable>;
export type BlogPostUpdate = Updateable<BlogPostTable>;

export interface CommentTable {
  id: Generated<number>;
  postId: number;
  text: string;
}

export type Comment = Selectable<CommentTable>;
export type NewComment = Insertable<CommentTable>;
export type CommentUpdate = Updateable<CommentTable>;

Our demo blog post will simply have an id and a title . The id will be automatically generated by SQLite. We can use the generated type from Kysely to indicate this. A comment will also have an id , a postId , referencing the blog post and a text .

Those are our basic database types. Let's create a Schema type, which Kysely understands. It maps the table names to the interfaces we just created.

It also makes sense to define some types for selecting, creating and updating our database records. For this, Kysely provides some extra types. The BlogPost type is exactly the type that Kysely provides us when we select all columns from this table. The NewBlogPost type defines what we can insert into the blog post table. When we want to update a record in the blog post table, we can use a partial type of the blog post table, which is defined by this blog PostUpdate type. We do the same for our comments.

Migrations

Ok, let's now create our migration according to our defined schema.

import { Kysely } from "kysely";

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable("blogPost")
    .addColumn("id", "integer", (col) => col.primaryKey())
    .addColumn("title", "text", (col) => col.notNull())
    .execute();

  await db.schema
    .createTable("comment")
    .addColumn("id", "integer", (col) => col.primaryKey())
    .addColumn("postId", "integer", (col) =>
      col.notNull().references("blogPost.id"),
    )
    .addColumn("text", "text", (col) => col.notNull())
    .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable("comment").execute();
  await db.schema.dropTable("blogPost").execute();
}

All the DDL statements are located under the schema property on our db instance. We want to call our table blogPost , which we defined in our schema. Our first column will be the id column, which is an integer . And we want this to be our primary key column. This is auto-incremented in SQLite. We can use a third parameter to define our column constraints.

Now we want to add our title , which is just another column of type text. By default, the column is nullable, but we want this to be required. So let's add the necessary constraint for this. Finally, we can execute() this query and await it before executing the next query.

So let's now create our comment table. We have an id . And we want to reference a post. And we have a non-nullable text column.

This schema definition should exactly match our schema that we defined in typescript.

To have a complete migration module, let's implement the down migration as well. It's simple, we just drop the two tables we just created. Of course, we should first drop the comments table because it has a foreign key to the blog posts table.

If you already have a database in place, it may make sense to have a look at generating types in the Kysely documentation. There are multiple ways to generate your Kysely schema types from an existing database or prisma for example. But in this basic case, we write our schema from scratch.

Let's now create our migration provider.

import type { MigrationProvider } from "kysely";
import * as m01 from "./01-basic-schema";

export const migrationProvider: MigrationProvider = {
  async getMigrations() {
    return { m01 };
  },
};

To write our migration provider, we have the MigrationProvider interface exposed by Kysely. We only have to implement one method: getMigrations() . This has to be asynchronous.

The named migrations within this object will be executed in lexical order.

import { Migrator } from "kysely";
import { migrationProvider } from "./migrations";

// ...

await new Migrator({
  db,
  provider: migrationProvider,
}).migrateToLatest();

To run our migrations, let's create a migrator from Kysely and provide it our database and the migration provider.

And we simply migrate to the latest version.

Data access layer

Let's now create our data access layer for our blog posts. In SQLite, it's a bit tricky to get the last insert row ID. Let's create a separate function for this. And let's move this to a central location. For example, within our database core file.

export async function getLastInsertRowId(): Promise<number> {
  const {
    rows: [{ id }],
  } = await sql<{
    id: number;
  }>`SELECT last_insert_rowid() AS id`.execute(db);
  return id;
}

Let's now import it and create our first blog post.

import { db, getLastInsertRowId } from "../core/db";
import type { BlogPost, NewBlogPost } from "../core/schema";

export async function createPost(
  post: NewBlogPost,
): Promise<BlogPost> {
  await db.insertInto("blogPost").values(post).execute();
  const id = await getLastInsertRowId();
  return {
    id,
    ...post,
  };
}

This was the creation of a blog post.

Let's now implement selecting a blog post.

export async function getPostById(
  id: number,
): Promise<BlogPost | null> {
  const dbo = await db
    .selectFrom("blogPost")
    .selectAll()
    .where("id", "=", id)
    .executeTakeFirst();
  return dbo ?? null;
}

We will skip the update part here and introduce comments instead.

Let's now implement our create comment function in our module.

export async function createComment(
  comment: NewComment,
): Promise<Comment> {
  await db.insertInto("comment").values(comment).execute();
  const id = await getLastInsertRowId();
  return {
    id,
    ...comment,
  };
}

Now, it might be interesting to count all the comments that are attached to one blog post.

export function getAllPostsWithCommentCounts(): Promise<
  Array<{
    id: number;
    title: string;
    commentCount: number;
  }>
> {
  return db
    .selectFrom("blogPost")
    .innerJoin("comment", "comment.postId", "blogPost.id")
    .select(({ fn }) => [
      "blogPost.id",
      "blogPost.title",
      fn.count<number>("comment.id").as("commentCount"),
    ])
    .groupBy("blogPost.id")
    .execute();
}

So we select from blog post. Then we use an inner join to join the comments. We append a select clause. Here, we can use a callback function to create more advanced projections. We have the functions placed in a fn object.

This way, we can type-safely create a count select statement. We also should give it a number as generic. Finally, we have to group by the blogPost.id and execute the query.

So let's now import this function here and rerun the test.

We covered quite some ground in this article. But there is much more to learn about Kysely if you want to really use it in your projects. If you are serious about learning Kysely, I strongly recommend taking a look at the documentation . There are plenty of examples and recipes in there that you can use to get your first queries running.

I can also recommend using their playground to see how your query that you built with Kysely translates to SQL.

If you are unsure whether you want to use Kysely or another tool to query SQL databases in TypeScript, here is a post for you where I compare the various approaches to query SQL databases.

In this sense, never stop learning and have a great day!