workers-qb

Zero dependencies Query Builder for Cloudflare D1 Workers
This module provides a simple standardized interface while keeping the benefits and speed of using raw queries over a traditional ORM.
workers-qb
is not intended to provide ORM-like functionality, rather to make it easier to interact with the database from
code for direct SQL access using convenient wrapper methods.
Read the documentation Here!
Features
- Zero dependencies.
- Fully typed/TypeScript support
- SQL Type checking with compatible IDE’s
- Insert/Update/Select/Delete/Join queries
- On Conflict for Inserts and Updates
- Create/drop tables
- Keep where conditions simple in code
- Bulk insert
- Named parameters (waiting for full support in D1)
Installation
npm install workers-qb
Basic Usage
import { D1QB } from 'workers-qb'
const qb = new D1QB(env.DB)
const fetched = await qb.fetchOne({
tableName: 'employees',
fields: 'count(*) as count',
where: {
conditions: 'active = ?1',
params: [true],
},
})
console.log(`Company has ${fetched.results.count} active employees`)
Fetching a single record
const qb = new D1QB(env.DB)
const fetched = await qb.fetchOne({
tableName: 'employees',
fields: 'count(*) as count',
where: {
conditions: 'department = ?1',
params: ['HQ'],
},
})
console.log(`There are ${fetched.results.count} employees in the HR department`)
Fetching multiple records
import { OrderTypes } from 'workers-qb'
const qb = new D1QB(env.DB)
const fetched = await qb.fetchAll({
tableName: 'employees',
fields: ['role', 'count(*) as count'],
where: {
conditions: 'department = ?1',
params: ['HR'],
},
groupBy: 'role',
orderBy: {
count: OrderTypes.DESC,
},
})
console.log(`Roles in the HR department:`)
fetched.results.forEach((employee) => {
console.log(`${employee.role} has ${employee.count} employees`)
})
Inserting rows
import { Raw } from 'workers-qb'
const qb = new D1QB(env.DB)
const inserted = await qb.insert({
tableName: 'employees',
data: {
name: 'Joe',
role: 'manager',
department: 'store',
created_at: new Raw('CURRENT_TIMESTAMP'),
},
returning: '*',
})
console.log(inserted) // This will contain the data after SQL triggers and primary keys that are automated
Bulk Inserting rows
import { Raw } from 'workers-qb'
const qb = new D1QB(env.DB)
const inserted = await qb.insert({
tableName: 'employees',
data: [
{
name: 'Joe',
role: 'manager',
department: 'store',
created_at: new Raw('CURRENT_TIMESTAMP'),
},
{
name: 'John',
role: 'employee',
department: 'store',
created_at: new Raw('CURRENT_TIMESTAMP'),
},
{
name: 'Mickael',
role: 'employee',
department: 'store',
created_at: new Raw('CURRENT_TIMESTAMP'),
},
],
})
Updating rows
const updated = await qb.update({
tableName: 'employees',
data: {
role: 'CEO',
department: 'HQ',
},
where: {
conditions: 'id = ?1',
params: [123],
},
})
console.log(`Lines affected in this query: ${updated.changes}`)
Deleting rows
const deleted = await qb.delete({
tableName: 'employees',
where: {
conditions: 'id = ?1',
params: [123],
},
})
console.log(`Lines affected in this query: ${deleted.changes}`)
Dropping and creating tables
const created = await qb.createTable({
tableName: 'testTable',
schema: `
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL
`,
ifNotExists: true,
})
const dropped = await qb.dropTable({
tableName: 'testTable',
})
Development
Set up tools and environment
You need to have Node.js installed. Node includes npm as its default package manager.
Open the whole package folder with a good code editor, preferably Visual Studio Code. Consider installing VS Code extensions ES Lint and Prettier.
In the VS Code top menu: Terminal -> New Terminal
Install dependencies
Install dependencies with npm:
npm i