@icewolf/cloudflare-db-connect v0.0.8

db-connect

:warning: This was an experimental project and is not supported.

Connect your SQL database to Cloudflare Workers. Import this lightweight Javascript library to execute commands or cache queries from a database through an Argo Tunnel. Although designed for Workers, this library can be used in any environment that has access to the Fetch and SubtleCrypto APIs.

Installation

npm i -s @cloudflare/db-connect

Example

import { DbConnect } from '@cloudflare/db-connect'

const db = new DbConnect({
  host: 'sql.mysite.com', // Hostname of an Argo Tunnel
  clientId: 'xxx', // Client ID of an Access service token
  clientSecret: 'xxx' // Client Secret of an Access service token
})

async function findBirthday(name) {
  const resp = await db.submit({
    statement: 'SELECT * FROM users WHERE name = ? LIMIT 1',
    arguments: [ name ],
    cacheTtl: 60
  })

  if(!resp.ok) {
    return new Error('oops! could not find user.')
  }

  const users = await resp.json()
  // [ { "id": 1111,
  //   "name": "Matthew",
  //   "birthday": "2009-07-01" } ]

  return users[0].birthday
}

findBirthday('Matthew').then(bday => console.log(bday))

Quickstart

db-connect requires that you setup Cloudflare Access, Argo Tunnel, and Workers. You can use the quickstart command below or read the quickstart file for details on how to set this up yourself.

npm i -g @cloudflare/db-connect
db-connect-quickstart

Databases

db-connect supports the following database drivers out-of-the-box. If your database is not explicitly on the list it may still be supported. For instance, MariaDB uses the MySQL protocol and CockroachDB uses the PostgreSQL protocol.

• PostgreSQL
• MySQL
• MSSQL
• Clickhouse
• SQLite3

Documentation

new DbConnect(options)

import { DbConnect } from '@cloudflare/db-connect'

const db = new DbConnect({
  host,         // required, hostname of your Argo Tunnel running in db-connect mode.
  clientId,     // recommended, client id from your Access service token.
  clientSecret, // recommended, client secret from your Access service token.
})

Promise<Response> db.ping()

import { DbConnect } from '@cloudflare/db-connect'

const db = new DbConnect({...})

async function myPing() {
  const resp = await db.ping()
  if(resp.ok) {
    return true
  }
  throw new Error(await resp.text())
}

new Command(options)

import { Command } from '@cloudflare/db-connect'

const cmd = new Command({
  statement, // required, the database statement to submit.
  arguments, // optional, either an array or object of arguments.
  mode,      // optional, type of command as either 'query' or 'exec'.
  isolation, // optional, type of transaction isolation, defaults to 'none' for no transactions.
  timeout,   // optional, number of seconds before a timeout, defaults to infinite.
  cacheTtl,  // optional, number of seconds to cache responses, defaults to -1.
  staleTtl,  // optional, after cacheTtl expires, number of seconds to serve stale, defaults to -1.
})

Promise<Response> db.submit(command)

import { DbConnect, Command } from '@cloudflare/db-connect'

const db = new DbConnect({...})

const cmd = new Command({
  statement: 'SELECT COUNT(*) AS n FROM books',
  cacheTtl: 60
})

async function mySubmit() {
  const resp = await db.submit(cmd)
  if(resp.ok) {
    return await resp.json() // [ { "n": 1234 } ]
  }
  throw new Error(await resp.text())
}

Testing

If you want to test db-connect without a database you can use the following command to create an in-memory SQLite3 database:

cloudflared db-connect --playground