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.
- [x] validation and parse model by zod (json text string on sqlite)
- [x] remote call from your local app to worker or between worker by binding service
- [x] api like primsa (support 1 level relation)
- [x] unit testing D1 on local.
npm install kysely-zod-sqlite
Define zod and use it for kysely model.
import {z} from zod
import {
zJsonObject,
zJsonSchema,
zRelationOne,
zBoolean,
zDate,
} from 'kysely-zod-sqlite';
export const userSchema = z.object({
id: z.string(),
name: z.string(),
email: z.string().optional(),
data: zJsonObject<UserData>(), // it use JSON.parse
config: zJsonSchema(z.object({ // it use zod.parse
language:z.string(),
status: z.enum(['busy', 'working' ]),
})),
created_at: zDate(), //custom parse sqlite date
updated_at: zDate(),
isDelete: zBoolean(), // parse boolean 1,0 or you can use z.coerce.boolean()
});
export const postSchema = z.object({
id: z.string(),
name: z.string(),
user_id: z.string(),
is_published: zBoolean,
data: z.string(),
created_at: zDate,
updated_at: zDate,
});
// define a relation
export const postRelationSchema = postSchema.extend({
user: zRelationOne({
schema: userSchema,
ref: 'user_id',
refTarget: 'id',
table: 'test_users',
}),
});
export const userRelationSchema = userSchema.extend({
posts: zRelationMany({
schema: postSchema,
refTarget: 'user_id',
ref: 'id',
table: 'test_posts',
}),
});
export type PostTable = z.infer<typeof postRelationSchema>;
export type UserTable = z.infer<typeof userRelationSchema>;
// define an api Database
export const dbSchema = z.object({
test_users: userRelationSchema,
test_posts: postRelationSchema,
});
export type DbSchema = typeof dbSchema;
use schema to define api
export class TestApi extends SqliteApi<DbSchema> {
get test_users() {
return this.table('test_users');
} // api like prisma
get test_posts() {
return this.table('test_posts');
}
}
const config = {};
const api = new TestApi({
schema: dbSchema,
config: {},
kysely: createKyselySqlite({
driver: new BetterDriver(new Database(':memory:'), config),
schema: dbSchema,
}),
})
prisma similar api
const post = await api.test_posts.selectFirst({
where: { name: 'test' },
include: {
user: true, // query 1 level relation
},
})
// access relation and json data 🔥
const language = post.user.config.language
await api.test_users.updateOne({
where: {
name: {
like: 'user%', // it use kysely operation = ('name' , 'like', 'user%')
},
},
data: { name: 'test' },
});
If you want to write a complex query you can use kysely
const data = await api.ky // this is a reference of kysely builder
.selectFrom('test_posts')
.limit(1)
.innerJoin('test_users', 'test_posts.user_id', 'test_users.id')
.selectAll()
.execute();
import { BetterSqlite3Driver } from 'kysely-zod-sqlite/driver/sqlite-driver';
const api = new TestApi({
config,
schema: dbSchema,
kysely: createKyselySqlite({
driver: new BetterDriver(new Database(':memory:'), config),
schema: dbSchema,
}),
});
import { D1Driver } from 'kysely-zod-sqlite/driver/d1-driver';
const api = new TestApi({
config,
schema: dbSchema,
kysely: createKyselySqlite({
driver: new FetchDriver({
apiKey: process.env.API_KEY!,
apiUrl: process.env.API_URL!,
}),
schema: dbSchema,
}),
});
You need to deploy a custom worker then you can connect to it on your app
import { FetchDriver } from 'kysely-zod-sqlite/driver/fetch-driver';
const api = new TestApi({
config,
schema: dbSchema,
kysely: createKyselySqlite({
driver: new FetchDriver({
apiKey: process.env.API_KEY!,
apiUrl: process.env.API_URL!,
}),
schema: dbSchema,
}),
});
import { FetchDriver } from 'kysely-zod-sqlite/driver/fetch-driver';
const api = new TestApi({
config,
schema: dbSchema,
kysely: createKyselySqlite({
driver: new FetchDriver(env.D1_DB, {
apiKey: 'test',
apiUrl: 'https://{worker}.pages.dev',
database: 'Test',
bindingService: env.WORKER_BINDING,
// it will use env.WORKER_BINDING.fetch not a global fetch
}),
schema: dbSchema,
}),
});
export class TestApi extends SqliteApi<Database> {
//... another table use a default driver
get TestLog(){
return this.table('TestLog',{ driver: new FetchDriver(...)});
}
}
// dynamic add schema and driver
const api = new TestApi(...)
const extendApi = api.withTables(
{
TestExtend: z.object({
id: z.number().optional(),
name: z.string(),
}),
},
{ testExtend: o => o.table('TestExtend',{driver: new D1Driver(...)}),}
);
const check = await extendApi.testExtend.selectFirst({
where: { name: 'testextend' },
});
// raw sql query
await api.batchOneSmt(
sql`update test_users set name = ? where id = ?`,
[ ['aaa', 'id1'], ['bbb', 'id2'], ]
);
// run kysely query with multiple value
const check = await api.batchOneSmt(
api.ky
.updateTable('test_users')
.set({
data: sql` json_set(data, '$.value', ?)`,
})
.where('name', '=', '?'),
[ ['aaa', 'user0'], ['bbb', 'user1'], ]
);
// run multiple query on batch
const result = await api.batchAllSmt([
api.ky.selectFrom('test_users').selectAll(), // kysely query
api.ky.insertInto('test_posts').values({
id: uid(),
name: 'post',
data: '',
is_published: true,
user_id: userArr[0].id,
}),
api.test_users.$selectMany({ // prisma syntax (add $ before select)
take: 10,
include: {
posts: true,
},
select: {
id: true,
},
})
]);
const users = result.getMany<UserTable>(0);
const post = result.getOne<PostTable>(1);
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
const check = await api.bulk({
// skip that query for normal user
allUser: isAdmin ? api.ky.selectFrom('test_users').selectAll(): undefined;
insert: api.ky.insertInto('test_posts').values({
id: uid(),
name: 'post',
data: '',
is_published: true,
user_id: userArr[0].id,
}),
});
// It use **key - value** to.
const allUser = check.getMany<UserTable>('allUser');
const allUser = check.getOne<any>('insert');
//prisma query can use on bulk too. You can even run batch inside of bulk 🥰
const check = await api.bulk({
user:
api.ky
.updateTable('test_users')
.set({
data: sql` json_set(data, '$.value', ?)`,
})
.where('name', '=', '?'),
,
topUser: api.test_users.$selectMany({
take: 10,
include: {
posts: true,
},
select: {
id: true,
},
}),
});
No, It just a wrapper around kysely. You can think it is an API with zod for validation and parse schema with kysely for query
api.table('aaa').insertOne({...}) // it is validation on runtime value with zod.
api.ky.insertInto('aaa').values({...}) // it is type checking.
api.table('aaa').selectMany() // use it to get data
api.table('aaa').$selectMany()
// it is kysely query you can modify that query or use it on batch
when your database column can null. you need to use nullable not optional on your model
access_token: z.string().optional().nullable(),
api.parseMany<UserTable & { dynamic: number }>(
data,
'test_users',
// a joinSchema
z.object({
dynamic: z.number(),
})
use the migration from kysely