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