npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@bluebeela/workers-qb

v0.2.3

Published

Zero dependencies Query Builder for Cloudflare D1 Workers & Workers Analytic Engine

Downloads

10

Readme

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

  • [x] Zero dependencies.
  • [x] Fully typed/TypeScript support
  • [x] SQL Type checking with compatible IDE's
  • [x] Insert/Update/Select/Delete queries
  • [x] Create/drop tables
  • [x] Keep where conditions simple in code
  • [ ] Bulk insert/update
  • [ ] 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

const qb = new D1QB(env.DB)

const inserted = await qb.insert({
    tableName: "employees",
    data: {
        name: "Joe",
        role: "manager",
        department: "store",
    },
    returning: "*",
})

console.log(inserted)  // This will contain the data after SQL triggers and primary keys that are automated

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

Write your code

Write your code in src folder, and unit test in test folder.

The VS Code shortcuts for formatting of a code file are: Shift + Alt + F (Windows); Shift + Option (Alt) + F (MacOS); Ctrl + Shift + I (Linux).

Test

Test your code with Jest framework:

npm run test

Note: This project uses husky, pinst and commitlint to automatically execute test and lint commit message before every commit.

Build

Build production (distribution) files in your dist folder:

npm run build

It generates CommonJS (in dist/cjs folder), ES Modules (in dist/esm folder), bundled and minified UMD (in dist/umd folder), as well as TypeScript declaration files (in dist/types folder).

Try it before publishing

Run:

npm link

npm link will create a symlink in the global folder, which may be {prefix}/lib/node_modules/workers-qb or C:\Users<username>\AppData\Roaming\npm\node_modules\workers-qb.

Create an empty folder elsewhere, you don't even need to npm init (to generate package.json). Open the folder with VS Code, open a terminal and just run:

npm link workers-qb

This will create a symbolic link from globally-installed workers-qb to node_modules/ of the current folder.

You can then create a, for example, testsql.ts file with the content:

import { D1QB } from 'workers-qb'
const qb = new D1QB(env.DB)

console.log("Creating table...")
const created = await qb.createTable({
    tableName: "testTable",
    schema: `
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL
    `,
    ifNotExists: true,
})
console.log(created)

console.log("Inserting rows...")
const inserted = await qb.insert({
    tableName: "testTable",
    data: {
        name: "my name",
    },
    returning: "*",
})
console.log(inserted)

console.log("Selecting rows...")
const selected = await qb.fetchAll({
    tableName: "testTable",
    fields: "*"
})
console.log(selected)

If you don't see any linting errors in VS Code, if you put your mouse cursor over D1QB and see its type, then it's all good.

Whenever you want to uninstall the globally-installed workers-qb and remove the symlink in the global folder, run:

npm uninstall workers-qb -g