Basic queries

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(`Joe just got the employee id: ${inserted.results.id}`)

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',
})