Typesafe Database Queries on the Edge

What is the Edge?

Edge computing is the new hottest thing in the web dev ecosystem, and rightfully so. If you don’t know what the edge computing is, it’s a way to run your code as close to your users as possible through globally distributed edge servers. This results in really low latency and no cold starts. But to stay performant, they have a limited runtime and code size limitations (1 MB on Workers).

Prisma

Prisma is an ORM that lets you write your database schema in it’s special .prisma syntax.

It has first-class support for PostgreSQL, MySQL, SQLite, SQL Server, CockroachDB and even MongoDB. Prisma then generates TypeScript types based on the schema, which lets your query your database using the Prisma Client in a typesafe manner. It’s fantastic.

Prisma Migrate is another great tool from the Prisma team to run database migrations without hassle.

But it also has a lot of problems. The Rust based core of Prisma is approximately a 13 MB binary. Which means in serverless environments, the cold start times are awful because it takes a lot of time to spin up the Prisma binary. And you can pretty much forget running Prisma on the edge.

PlanetScale

PlanetScale is a serverless MySQL database provider which is based on Vitess. You get the scaling benefits of Vitess without the need to manage it yourself.

The PlanetScale team recently released their database driver which lets your query your PlanetScale database using the Fetch API. This means you can use this library to query your database in edge environments which is HUGE.

Although it’s a great library, it doesn’t provide an typesafety.

Kysely

Enter Kysely. It’s a typesafe SQL query builder. You give it your schema as a TypeScript type and you can get wonderful typesafety and autocomplete while using the query builder. Take a look at this GIF from the Kysely Readme.

It works in serverless and edge environments, even on Deno! It also has support for using the PlanetScale database driver using kysely-planetscale. This is perfect.

The only problem is that defining schemas in TypeScript is rough. It also doesn’t have anything like Prisma Migrate to manage database migrations.

The Idea

Prisma is good at defining schemas, generating TypeScript types, and handling database migrations.

Kysely along with the PlanetScale database driver are good for writing SQL in a typesafe manner on the edge.

What if we combine both of these?

Implementing the Idea

Enough talking let’s get to some actual code.

Setting Up a Database on PlanetScale

First create an account on PlanetScale and create a database. You can just sign in with your GitHub account and you’re good to go.

Setting Up Prisma

I’ll be using the newly released SolidStart but this should work in all frameworks that support edge environments such as Next.js, Remix, SvelteKit, etc.

Install Prisma and the Prisma Client.

npm install -D prisma && npm install @prisma/client

Now generate a .env file and a starter schema using prisma init.

npx prisma init --datasouce-provider mysql

This command will create a schema.prisma file inside a prisma folder. It will also create a .env file. Add your connection string from PlanetScale in the DATABASE_URL variable.

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

Since PlanetScale, rather Vitess doesn’t support foreign key constraints, we need to set the referentialIntegrity property in prisma.

// prisma/schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

Now make an Example model. Models in Prisma represent the tables in a SQL database.

// prisma/schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

model Example {
  id         String    @id @default(cuid())
  createdAt  DateTime  @default(now())
  updatedAt  DateTime  @updatedAt
  text       String
}

Push this schema to PlanetScale using prisma db push.

npx prisma db push

This command will also run prisma generate which generates the TypeScript types based on your schema. It is also recommended to add prisma generate as a postinstall in package.json so that whenever you install the dependencies, it will generate the types for you.

// package.json

"scripts": {
    "postinstall": "prisma generate"
}

That is it for the Prisma setup.

Setting Up Kysely

Make sure you add the DATABASE_USERNAME and DATABASE_PASSWORD environment variables from PlanetScale.

Install Kysely, the Kysely PlanetScale Dialect, and the PlanetScale databse driver.

npm install kysely kysely-planetscale @planetscale/database

Create a src/server/db.ts or whatever file makes sense to you and add the following code.

// src/server/db.ts

import type { Example } from "@prisma/client/edge";
import { Kysely } from "kysely";
import { PlanetScaleDialect } from "kysely-planetscale";

interface Database {
  Example: Example;
}

export const db = new Kysely<Database>({
  dialect: new PlanetScaleDialect({
    host: "aws.connect.psdb.cloud",
    username: process.env.DATABASE_USERNAME,
    password: process.env.DATABASE_PASSWORD,
  }),
});

Here we’re importing the Example type that we had defined in our schema. If you go to definition of that type, you will see that that type has all the fields we had defined in our schema correctly typed.

export type Example = {
  id: string;
  createdAt: Date;
  updatedAt: Date;
  text: string;
};

The Database interface will contain all our types. So let’s say we also have a SecondExample field in our database, we will have to import that type and add it in the Database interface. This is one of the limitations of this approach, it requires you to add new types manually.

Then we’re exporting a Kysely instance that takes the Database interface as a generic. The PlanetScaleDialect tells Kysely to use the PlanetScale database driver to run the SQL queries.

Using Kysely

Note: This might look different in your framework but the Kysely code will be the same.

// src/routes/index.tsx

import { db } from "~/server/db";

export const routeData = () => {
  return createServerData$(async () => {
    const examples = await db
      .selectFrom("Example")
      .selectAll()
      .orderBy("createdAt", "desc")
      .execute();

    console.log(examples);

    return examples;
  });
};

You will notice which typing this is that it’s all beautifully autocompleted for you. Everything is fully typesafe.

SolidStart also has end-to-end typesafety, so even in the UI code, the typesafety is maintained. This is similar to Remix patterns. I’m also using UnoCSS in this example.

// src/routes/index.tsx

export default function Home() {
  const examples = useRouteData<typeof routeData>();

  return (
    <main class="flex flex-col items-center h-screen bg-#050505 font-sans">
      <div class="flex flex-col gap-y-2">
        <For each={examples()}>
          {(example) => {
            return <p class="text-white">{example.text}</p>;
          }}
        </For>
      </div>
    </main>
  );
}

You will notice that we didn’t have to write any types ourselves for this. Everything is typesafe and we didn’t even write any TypeScript. It’s all inferred.

Limitations

This way of doing things can break when using @map because the casing is different in the types. It also doesn’t support Prisma.Decimal because you have to use the special Prisma object to use it. Mark Lawlor has some insane TypeScript code to work around this problem but it’s still very “hacky”. You can see his gist here.

Update: A wonderful package called prisma-kysely created by Valtýr does all of this for you. You should probably use it instead of the approach I’ve shown here.

Conclusion

Hopefully that all made sense. I think it is pretty cool. That’s it really. Thanks for reading!