Intro

An flexible api for Cloudflare D1 and sqlite.

It has an simple api of Prisma and a powerful query with Kysely, runtime transform and validation model with zod.

Feature

• validation and parse model by zod (json text string on sqlite)
• remote call from your local app to worker or between worker by binding service
• api like primsa (support 1 level relation)
• unit testing D1 on local.

Install

npm install kysely-zod-sqlite

Usage

Define zod schema

Define zod and use it for kysely model.

1import {z} from zod
2import {
3  zJsonObject,
4  zJsonSchema,
5  zRelationOne,
6  zBoolean,
7  zDate,
8} from 'kysely-zod-sqlite';
9export const userSchema = z.object({
10  id: z.string(),
11  name: z.string(),
12  email: z.string().optional(),
13  data: zJsonObject<UserData>(),  // it use JSON.parse
14  config: zJsonSchema(z.object({  // it use zod.parse
15    language:z.string(),
16    status: z.enum(['busy', 'working' ]),
17  })), 
18  created_at: zDate(), //custom parse sqlite date
19  updated_at: zDate(),
20  isDelete: zBoolean(), // parse boolean 1,0 or you can use z.coerce.boolean()
21});
22export const postSchema = z.object({
23  id: z.string(),
24  name: z.string(),
25  user_id: z.string(),
26  is_published: zBoolean,
27  data: z.string(),
28  created_at: zDate,
29  updated_at: zDate,
30});
31// define a relation
32export const postRelationSchema = postSchema.extend({
33  user: zRelationOne({
34    schema: userSchema,
35    ref: 'user_id',
36    refTarget: 'id',
37    table: 'test_users',
38  }),
39});
40export const userRelationSchema = userSchema.extend({
41  posts: zRelationMany({
42    schema: postSchema,
43    refTarget: 'user_id',
44    ref: 'id',
45    table: 'test_posts',
46  }),
47});
48export type PostTable = z.infer<typeof postRelationSchema>;
49export type UserTable = z.infer<typeof userRelationSchema>;
50// define an api Database
51export const dbSchema = z.object({
52  test_users: userRelationSchema,
53  test_posts: postRelationSchema,
54});
55export type DbSchema = typeof dbSchema;

use schema to define api

1export class TestApi extends SqliteApi<DbSchema> {
2  
3  get test_users() {
4    return this.table('test_users');
5  } // api like prisma
6  get test_posts() {
7    return this.table('test_posts');
8  }
9}
10const config = {}; 
11const api = new TestApi({
12  schema: dbSchema,
13  config: {},
14  kysely: createKyselySqlite({
15    driver: new BetterDriver(new Database(':memory:'), config),
16    schema: dbSchema,
17  }),
18})

Usage

prisma similar api

1const post = await api.test_posts.selectFirst({
2  where: { name: 'test' },
3  include: {
4    user: true, // query 1 level relation
5  },
6})
7// access relation and json data 🔥
8const language = post.user.config.language
9await api.test_users.updateOne({
10  where: {
11    name: {
12      like: 'user%', // it use kysely operation  = ('name' , 'like', 'user%') 
13    }, 
14  },
15  data: { name: 'test' },
16});

If you want to write a complex query you can use kysely

1const data = await api.ky // this is a reference of kysely builder
2    .selectFrom('test_posts')
3    .limit(1)
4    .innerJoin('test_users', 'test_posts.user_id', 'test_users.id')
5    .selectAll()
6    .execute();

Driver

Local enviroment and unit test

1import { BetterSqlite3Driver } from 'kysely-zod-sqlite/driver/sqlite-driver';
2const api = new TestApi({
3  config,
4  schema: dbSchema,
5  kysely: createKyselySqlite({
6    driver: new BetterDriver(new Database(':memory:'), config),
7    schema: dbSchema,
8  }),
9});

Working inside worker and pages

1import { D1Driver } from 'kysely-zod-sqlite/driver/d1-driver';
2const api = new TestApi({
3  config,
4  schema: dbSchema,
5  kysely: createKyselySqlite({
6    driver: new FetchDriver({
7      apiKey: process.env.API_KEY!,
8      apiUrl: process.env.API_URL!,
9    }),
10    schema: dbSchema,
11  }),
12});

Working outside cloudflare worker, pages

You need to deploy a custom worker then you can connect to it on your app

worker

1import { FetchDriver } from 'kysely-zod-sqlite/driver/fetch-driver';
2const api = new TestApi({
3  config,
4  schema: dbSchema, 
5  kysely: createKyselySqlite({
6    driver: new FetchDriver({
7      apiKey: process.env.API_KEY!,
8      apiUrl: process.env.API_URL!,
9    }),
10    schema: dbSchema,
11  }),
12});

Call from cloudflare pages to worker or from worker to worker

1import { FetchDriver } from 'kysely-zod-sqlite/driver/fetch-driver';
2const api = new TestApi({
3  config,
4  schema: dbSchema,
5  kysely: createKyselySqlite({
6    driver: new FetchDriver(env.D1_DB, {
7      apiKey: 'test',
8      apiUrl: 'https://{worker}.pages.dev',
9      database: 'Test',
10      bindingService: env.WORKER_BINDING,
11      // it will use env.WORKER_BINDING.fetch not a global fetch
12    }),
13    schema: dbSchema,
14  }),
15});

Multiple driver per table

1export class TestApi extends SqliteApi<Database> {
2  //... another table use a default driver
3
4  get TestLog(){
5    return this.table('TestLog',{ driver: new FetchDriver(...)});
6  }
7}
8// dynamic add schema and driver 
9const api = new TestApi(...)
10
11const extendApi = api.withTables(
12  {
13    TestExtend: z.object({
14      id: z.number().optional(),
15      name: z.string(),
16    }),
17  },
18  { testExtend: o => o.table('TestExtend',{driver: new D1Driver(...)}),}
19);
20
21const check = await extendApi.testExtend.selectFirst({
22  where: { name: 'testextend' },
23});
24

Support batch

1// raw sql query 
2await api.batchOneSmt(
3  sql`update test_users set name = ? where id = ?`, 
4  [ ['aaa', 'id1'], ['bbb', 'id2'], ]
5);
6// run kysely query with multiple value
7const check = await api.batchOneSmt(
8    api.ky
9      .updateTable('test_users')
10      .set({
11        data: sql` json_set(data, '$.value', ?)`,
12      })
13      .where('name', '=', '?'),
14    [ ['aaa', 'user0'], ['bbb', 'user1'], ]
15);
16// run multiple query on batch
17const result = await api.batchAllSmt([
18  api.ky.selectFrom('test_users').selectAll(), // kysely query
19  api.ky.insertInto('test_posts').values({
20    id: uid(),
21    name: 'post',
22    data: '',
23    is_published: true,
24    user_id: userArr[0].id,
25  }),
26  api.test_users.$selectMany({  // prisma syntax (add $ before select)
27      take: 10,
28      include: {
29        posts: true,
30      },
31      select: {
32        id: true,
33      },
34  })
35]);
36const users = result.getMany<UserTable>(0);
37const post = result.getOne<PostTable>(1);

Bulk method

working with array on batch method is difficult. when you run query depend on some condition so I create bulk. recommend use bulk for FetchDriver if you have multiple request

1const check = await api.bulk({
2  // skip that query for normal user
3  allUser: isAdmin ? api.ky.selectFrom('test_users').selectAll(): undefined; 
4  insert: api.ky.insertInto('test_posts').values({
5    id: uid(),
6    name: 'post',
7    data: '',
8    is_published: true,
9    user_id: userArr[0].id,
10  }),
11});
12// It use **key - value** to.
13const allUser = check.getMany<UserTable>('allUser'); 
14const allUser = check.getOne<any>('insert'); 
15
16//prisma query can use on bulk too. You can even run batch inside of bulk 🥰
17const check = await api.bulk({
18  user:
19    api.ky
20      .updateTable('test_users')
21      .set({
22        data: sql` json_set(data, '$.value', ?)`,
23      })
24      .where('name', '=', '?'),
25  ,
26  topUser: api.test_users.$selectMany({
27    take: 10,
28    include: {
29      posts: true,
30    },
31    select: {
32      id: true,
33    },
34  }),
35});

FAQ

Is this library an ORM?

No, it is merely a wrapper around Kysely. You can think of it as an API that uses Zod for validation and schema parsing, combined with Kysely for querying.

Different between using this library vs kysely

1api.table('aaa').insertOne({...}) // it is validation on runtime value with zod.
2api.ky.insertInto('aaa').values({...}) // it is type checking.

What is $ on table

1api.table('aaa').selectMany() // use it to get data
2api.table('aaa').$selectMany() 
3// it is kysely query you can modify that query or use it on batch

Column is null

When your database column can be null, you need to use nullable instead of optional in your model.

1access_token: z.string().nullable(),

Parse custom schema on query with join

1api.parseMany<UserTable & { dynamic: number }>(
2  data,
3 'test_users', 
4  // a joinSchema
5  z.object({  
6    dynamic: z.number(),
7  })

migration

use the migration from kysely

Thank

kysely zod @subframe7536 @ryansonshine

Links

cloudflare better-sqlite3