A simple, partly typesafe library for flexible queries against a Cloudflare D1 database
- Stay as true to SQL as possible, don't invent new lingo
- Automatic generation of types from your d1 database-schema
- Typesafe as far as possible without complicating the syntax too much
- Flexible, chain statements in any order, one big query or branching from a base-query
This project is in early days. The aim is to help out with simpler, basic SQL-queries. Not all methods have detailed types. Used in prod @ Shadr.net
yarn add d1-query
# or
npm install d1-query# Execute this in the same directory as wrangler.toml
# Writes to file "d1-query-types.ts"
bun ./node_modules/d1-query/typegen.ts// The following 2 lines can with advantages be put in a standalone "db.ts" exporting d1
import {Database} from "d1-query-types"; // auto-generated with d1-queries typegen.ts
const d1 = new D1Query<Database["blog"]>(); // create a typed startingpoint for the query
// without explicit select("something") d1-query assumes select("*")
const posts = await d1
.from("posts")
.select("id,title,text,created_at")
.where("public", "=", 1)
.orderBy("created_at DESC")
.all();const users = d1
.from("users")
.select("id,name,created_at")
.where("deleted", "=", 0)
.orderBy("created_at DESC");
const admins = await users.where("role", "=", "admin").all();
const moderators = await users.where("role", "=", "moderator").all();
const developers = await users.where("role", "=", "developer").all();Coming from Rails / Active Record this pattern can look familiar:
class PostTable extends D1Query<Database["blog"], "posts"> {
async latestPosts(limit = 10) {
return this.select("*, users.name as user_name")
.leftJoin("users")
.on("posts.user_id", "=", "users.id")
.orderBy("created_at DESC")
.limit(limit)
.all();
}
}
export const postTable = new PostTable({table: "posts"});With Remix
export async function loader({context}: LoaderFunctionArgs) {
// "DB" is the binding from wrangler.toml, "Database" is a (by d1-query:s typegen.ts) generated interface
const d1 = new D1Query<Database["blog"]>({db: context.cloudflare.env.DB});
const latestPosts = await d1.from("posts").orderBy("created_at DESC").limit(10).all();
return json({latestPosts});
}const list = await d1.sql<User>("SELECT id,name FROM users", parameters); // returns an array of User
// or
const row = await d1.sqlFirst<User>("SELECT * FROM users WHERE id = ?", parameters); // returns a User