Typesafe Database Queries on the Edge
Tags: typescript, sql, webdev
Published: 2022-11-12 16:09:07

## What is the Edge?

<br />

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](https://developers.cloudflare.com/workers/platform/limits/#worker-size)).

<br />

## Prisma

<br />

[Prisma](https://prisma.io) is an ORM that lets you write your database schema in it's special `.prisma` syntax.

<br />

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](https://prisma.io/client) in a typesafe manner. It's fantastic.

<br />

[Prisma Migrate](https://prisma.io/migrate) is another great tool from the Prisma team to run database migrations without hassle.

<br />

But it also has a lot of problems. The [Rust](https://rust-lang.org) 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.

<br />

## PlanetScale

<br />

[PlanetScale](https://planetscale.com) is a serverless MySQL database provider which is based on [Vitess](https://vitess.io). You get the scaling benefits of Vitess without the need to manage it yourself.

<br />

The PlanetScale team recently released their [database driver](https://github.com/planetscale/database-js) which lets your query your PlanetScale database using the [Fetch API](https://developer.mozilla.org/en-US/docs/Web/API/fetch). This means you can use this library to query your database in edge environments which is HUGE.

<br />

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

<br />

## Kysely

<br />

Enter [Kysely](https://github.com/koskimas/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.

![Kysely demo](https://github.com/koskimas/kysely/blob/master/assets/demo.gif?raw=true)

It works in serverless and edge environments, even on [Deno](https://deno.land)! It also has support for using the PlanetScale database driver using [kysely-planetscale](https://github.com/depot/kysely-planetscale). This is perfect.

<br />

The only problem is that defining schemas in TypeScript is rough. It also doesn't have anything like [Prisma Migrate](https://prisma.io/migrate) to manage database migrations.

<br />

## The Idea

<br />

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

<br />

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

<br />

What if we combine both of these?

<br />

## Implementing the Idea

<br />

Enough talking let's get to some actual code.

<br />

### Setting Up a Database on PlanetScale

<br />

First create an account on PlanetScale and create a database. Make sure to enable the serverless driver beta feature in the settings.

![Serverless driver beta feature enabled](https://us-east-1.tixte.net/uploads/nexxel.needs.rest/brave_nVfz1obyo5.png)

### Setting Up Prisma

<br />

I'll be using the newly released [Solid Start](https://start.solidjs.com) but this should work in all frameworks that support edge environments such as [Next.js](https://nextjs.org), [Remix](https://remix.run), [SvelteKit](https://kit.svelte.dev/), etc.

<br />

Install Prisma and the Prisma Client.

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

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

```sh
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
// 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](https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/), we need to set the `referentialIntegrity` property in prisma.

```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
// 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`.

```sh
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.

```json
// package.json

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

That is it for the Prisma setup.

<br />

### Setting Up Kysely

<br />

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

<br />

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

```sh
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.

```ts
// 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.

```ts
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.

<br />

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.

<br />

### Using Kysely

<br />

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

```ts
// 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.

<br />

Solid Start 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](https://github.com/unocss/unocss) in this example.

```ts
// 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.

<br />

### Limitations

<br />

This way of doing things can break when using [`@map`](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#map) because the casing is different in the types. It also doesn't support [`Prisma.Decimal`](https://www.prisma.io/docs/concepts/components/prisma-client/working-with-fields#working-with-decimal) because you have to use the special Prisma object to use it. [Mark Lawlor](https://github.com/marklawlor) has some insane TypeScript code to work around this problem but it's still very "hacky". You can see his gist [here](https://gist.github.com/marklawlor/b1c26eefba43539c6611a508e67ee02f).


<br />

## Conclusion

<br />

Hopefully that all made sense. I think it is pretty cool. I haven't seen anyone do this so I thought I had a moral obligation to write a blog about it. That's it really. Thanks for reading!