Bunny Launcher

SQLite

SQLite for Edge Scripting for bunny.net 🐰

NPM Version

Introduction

SQLite can be used as an embedded database anywhere—including edge scripts!

The bunny-sqlite package embeds and bundles SQLite specifically for the edge scripting runtime.

Using bunny-sqlite, you can create read-only, globally-distributed SQLite databases.

There are three ways to load databases:

  • Intercepting the database in a middleware edge script (most memory efficient)
  • Embedding the database with gzip compression and base64 encoding as a string in a standalone script (fastest)
  • Fetching the database from a url in a standalone script (not recommended)

Limitations

The main limitations are from the edge scripting platform:

  • CPU Time per request is limited to 30s
  • Active memory is limited to 128MB
  • Script size is limited to 10MB, but the limit is actually 1MB due to a bug

Please note that SQLite loads the entire database into active memory.

In addition, it is recommended to open each script with SQLITE_DESERIALIZE_READONLY for readonly databases for security.

Pricing

The main charges are running the edge script itself and the bandwidth of the query result.

If you load the database from a pull zone url (e.g. https://my-pull-zone.b-cdn.net/chinook.db), you will pay for the bandwidth of the entire database in addition to the main charges.

Using the Edge Storage API is free of charge, but requires connecting to the primary storage region of your storage zone.

Transferring data from a storage zone to a middleware script is free of charge.

Intercepting

Middleware scripts work by:

  • Connecting a middleware edge script to a pull zone with a storage zone as its origin
  • Uploading the SQLite database(s) to the storage zone
  • Intercepting the pull zone response of the database to load the database and return the query result instead

Transferring data from a storage zone to a middleware script is free of charge. The main charges are running the edge script itself and the bandwidth of the query result.

Middleware scripts, while not as fast as embedding the database as a base64 encoded and gzip compressed string, are more memory efficient than embedding the database and can support larger database files.

Middleware scripts are faster, cheaper, and more private than importing from a url.

With middleware scripts, there is no need to compress and encode the database.

Embedding

The fastest way to serve SQLite database queries is by compressing it with gzip and encoding it to base64.

In bash, you can use these commands to prepare your database.

MacOS

# download example databasecurl -s -S -L -o "chinook.db" "https://cdn.jsdelivr.net/gh/lerocha/chinook-database@master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite"# prepare databasegzip -k chinook.dbbase64 -i chinook.db.gz -o chinook.db.gz.b64# copy database as stringpbcopy < chinook.db.gz.b64

Linux

# download example databasecurl -s -S -L -o "chinook.db" "https://cdn.jsdelivr.net/gh/lerocha/chinook-database@master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite"# prepare databasegzip -k chinook.dbbase64 chinook.db.gz > chinook.db.gz.b64# copy database as stringxclip -sel c chinook.db.gz.b64# copy database as string on waylandwl-copy < chinook.db.gz.b64

If you do not have bash, you can write a Node.js script to do the same thing:

TypeScript from File

import { createReadStream } from 'node:fs'import { writeFile } from 'node:fs/promises'import { buffer } from 'node:stream/consumers'import { pipeline } from 'node:stream/promises'import { createGzip } from 'node:zlib'const file = createReadStream('chinook.db')const gzip = createGzip()await pipeline(file, gzip)const base64 = (await buffer(gzip)).toString('base64')await writeFile('chinook.db', base64)

TypeScript from URL

import { Buffer } from 'node:buffer'import { writeFile } from 'node:fs/promises'const response = await fetch(  'https://cdn.jsdelivr.net/gh/lerocha/chinook-database@master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite',)if (response.ok) {  const gzip = await new Response(    response.body.pipeThrough(new CompressionStream('gzip')),  ).bytes()  const base64 = Buffer.from(gzip).toString('base64')  await writeFile('chinook.db', base64)} else {  const { status, statusText } = response  console.log({    message: response.text(),    status,    statusText,  })}

If your runtime does not support the node:buffer module, please use the uint8array-extras package.

Fetching

Common urls for a public SQLite database stored on GitHub include:

  • jsDelivr https://cdn.jsdelivr.net/gh/lerocha/chinook-database/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite
  • GitHub https://cdn.jsdelivr.net/gh/lerocha/chinook-database@master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite
const response = await fetch(  'https://cdn.jsdelivr.net/gh/lerocha/chinook-database/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite',)const uint8Array = await response.bytes()

For database files stored in bunny.net storage zones with a connected pull zone, please be aware that:

  • Using the Edge Storage API, which always fetches from the primary storage region of your storage zone, is free of charge
  • Using the url of a pull zone (e.g. https://my-pull-zone.b-cdn.net/chinook.db) charges for bandwidth, just like any other file

Instead, consider using middleware scripts if you store your database in bunny.net storage zones.

Writing the scripts

Creating the scripts

Follow bunny.net’s quickstart guide to create your standalone or middleware script.

If you created a middleware script, you will need to:

  • Create a storage zone
  • Upload the SQLite database(s) to your storage zone
  • Create a pull zone with the storage zone as its origin
  • Connect the middleware edge script with the pull zone

To configure your pull zone cache, visit:

  • The new Dashboard > CDN > Pull Zone Name > Caching > General
  • The old Panel > Pull Zones > Pull Zone Name > Caching > General

First, you will need to enable Vary Cache > URL Query String so that each SQL query is cached separately, rather than just caching the first query forever. Be sure to click the Save Vary Cache or Save Vary Configuration button.

By default, the Cache Expiration Time is set to:

  • Override 1 month for middleware scripts
  • Respect Origin Cache Control, or no cache, for standalone scripts

You may set the cache override to:

  • whatever you deem appropriate
  • Respect Origin Cache Control for no cache
  • Respect Origin Cache Control and set your own Cache-Control header for fine-tuned control

Imports

All scripts will need these imports:

import sqlite3InitModule from 'https://cdn.jsdelivr.net/npm/bunny-sqlite@0.0.1/dist/index.js'import * as BunnySDK from 'https://esm.sh/@bunny.net/edgescript-sdk@0.11.2'

Scripts decoding a database from a base64 encoded and gzip compressed string will also need to inline this function:

async function decodeBase64Gzip(base64String: string): Promise<Uint8Array> {  return await new Response(    new Response(      Uint8Array.from(        globalThis.atob(base64String.replaceAll('-', '+').replaceAll('_', '/')),        (x) => x.codePointAt(0),      ),    ).body?.pipeThrough(new DecompressionStream('gzip')),  ).bytes()}

Preparing SQLite

Intercepting

const sqlite3 = await sqlite3InitModule({  print: console.log,  printErr: console.error,})

Embedding

const databaseString = '__SQLITE_BASE64_GZIP_STRING__'const [sqlite3, uint8Array] = await Promise.all([  sqlite3InitModule({ print: console.log, printErr: console.error }),  decodeBase64Gzip(databaseString),])const p = sqlite3.wasm.allocFromTypedArray(uint8Array)const db = new sqlite3.oo1.DB()sqlite3.capi.sqlite3_deserialize(  db.pointer!,  'main',  p,  uint8Array.length,  uint8Array.length,  sqlite3.capi.SQLITE_DESERIALIZE_READONLY,)

Fetching

const [sqlite3, uint8Array] = await Promise.all([  sqlite3InitModule({ print: console.log, printErr: console.error }),  fetch(    'https://cdn.jsdelivr.net/gh/lerocha/chinook-database/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite',  ).then((response) => response.bytes()),])const p = sqlite3.wasm.allocFromTypedArray(uint8Array)const db = new sqlite3.oo1.DB()sqlite3.capi.sqlite3_deserialize(  db.pointer!,  'main',  p,  uint8Array.length,  uint8Array.length,  sqlite3.capi.SQLITE_DESERIALIZE_READONLY,)

Writing the function handler

Middleware

async function onOriginResponse({  request,  response,}: {  request: Request  response: Response}): Promise<Response> {  const uint8Array = await response.bytes()  const p = sqlite3.wasm.allocFromTypedArray(uint8Array)  const db = new sqlite3.oo1.DB()  sqlite3.capi.sqlite3_deserialize(    db.pointer!,    'main',    p,    uint8Array.length,    uint8Array.length,    sqlite3.capi.SQLITE_DESERIALIZE_READONLY,  )  const url = new URL(request.url)  const sql =    url.searchParams.get('sql') ??    "SELECT * FROM sqlite_master WHERE type='table';"  const rowMode =    url.searchParams.get('rowMode') === 'array' ? 'array' : 'object'  const result = db.exec({    rowMode,    sql,  })  const json = JSON.stringify(result)  db.close()  return new Response(json, {    headers: {      'content-type': 'application/json',    },  })}BunnySDK.net.http.servePullZone().onOriginResponse(onOriginResponse)

Standalone

BunnySDK.net.http.serve((request: Request): Promise<Response> => {  const url = new URL(request.url)  const sql =    url.searchParams.get('sql') ??    "SELECT * FROM sqlite_master WHERE type='table';"  const rowMode =    url.searchParams.get('rowMode') === 'array' ? 'array' : 'object'  const result = db.exec({    rowMode,    sql,  })  const json = JSON.stringify(result)  return new Response(json, {    headers: {      'content-type': 'application/json',    },  })})

Querying the database

To query the database, make GET requests with an encoded url:

https://edge-sqlite-gbtld.b-cdn.net/chinook.db?rowMode=object&sql=SELECT%20name%2C%20milliseconds%20FROM%20track%20LIMIT%2010

If using middleware edge scripts, you must include the database name in the url, such as chinook.db. In standalone edge scripts, the database name is optional.

The encoded parameters are:

  • rowMode, which can be object or array
  • sql, which can be any sql query string

With url encoding, the sql query SELECT name, milliseconds FROM track LIMIT 10 becomes SELECT%20name%2C%20milliseconds%20FROM%20track%20LIMIT%2010