WaveDB

WaveDB is SQLite with a HTTP interface.

It is a ~6MB (~2MB UPX-compressed) self-contained, zero-dependency executable that bundles SQLite 3.35.5 (2021-04-19) with JSON1, RTREE, FTS5, GEOPOLY, STAT4, and SOUNDEX.

If you are already a fan of SQLite, WaveDB acts as a thin HTTP-server wrapper that lets you access your SQLite databases over a network.

WaveDB can be used as a lightweight, cross-platform, installation-free companion SQL database for Wave apps. The h2o-wave package includes non-blocking async functions to access WaveDB.

Database files managed by WaveDB are 100% interoperable with SQLite, which means you can manage them with the sqlite3 CLI, backup/restore/transfer them as usual, or use Litestream for replication.

Install

Linux, OSX, Windows

Download the executable for your platform. No installation required.

Other Platforms

Compiling WaveDB on other platforms is easy if you have the Go and C compilers handy (uses cgo):

$ git clone --depth 1 https://github.com/h2oai/wave.git
$ cd wave
$ go build -o wavedb cmd/wavedb/main.go

Launch

Launch WaveDB (defaults to http://localhost:10100/):

$ ./wavedb
2021/05/07 14:00:41
2021/05/07 14:00:41 โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”โ”Œโ”€โ”€โ”€โ”€โ” H2O WaveDB
2021/05/07 14:00:41 โ”‚ โ”โ”Œโ”โ”โ”Œโ”€โ”˜โ”‚โ”€โ” โ”‚ DEV 20210507132041
2021/05/07 14:00:41 โ”‚ โ””โ”˜โ””โ”˜โ””โ”€โ”˜โ””โ”€โ”˜ โ”‚ ยฉ 2021 H2O.ai, Inc.
2021/05/07 14:00:41 โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜
2021/05/07 14:00:41
2021/05/07 14:00:41 listening :10100

Launch WaveDB on a different port:

$ ./wavedb -listen :8080

Launch WaveDB with verbose logging (useful during development):

$ ./wavedb -verbose

Launch WaveDB with a custom API access key/secret pair:

$ ./wavedb -access-key-id uzer -access-key-secret pa55word

Serve your existing SQLite database files using WaveDB (defaults to current directory):

$ ./wavedb -dir /path/to/my/db/files

Examples

Usage

For this example, we'll use the Chinook Sample Database from sqlitetutorial.net.

$ wget https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip
$ unzip chinook.zip
$ ls -l
-rw-r--r-- 1 elp elp 884736 May 7 12:34 chinook.db
-rwxr-xr-x 1 elp elp 9678432 May 7 13:20 wavedb
$ ./wavedb -access-key-id uzer -access-key-secret pa55word

From h2o-wave

The h2o-wave Python package provides non-blocking async functions to access WaveDB.

from h2o_wave import connect
# Create a database connection
connection = connect(key_id='uzer', key_secret='pa55word')
# Access the 'chinook' database.
# Automatically creates a new database if it does not exist.
db = connection["chinook"]
# Execute a query
results, err = await db.exec('SELECT name, composer FROM tracks LIMIT 5')
# Print results
if err:
print(err)
else:
print(results)

The query API is simple:

  1. db.exec(...): Execute one query.
  2. db.exec_many(...): Execute multiple queries.
  3. db.exec_atomic(...): Excute multiple queries as a transaction (rollback all on failure)

All three APIs support both DDL (CREATE, ALTER, DROP, etc.) and DML (SELECT, INSERT, UPDATE, etc.) statements:

result, error = db.exec('CREATE TABLE student(name TEXT, age INTEGER)')
result, error = db.exec('INSERT INTO student VALUES ("Alice", 18)')
result, error = db.exec('SELECT name, age FROM student WHERE age > 17')

Queries can be parameterized (use on user-supplied input to prevent SQL injection):

result, error = db.exec('INSERT INTO student VALUES (?, ?)', "Bob", 19)
result, error = db.exec('SELECT name, age FROM student WHERE age > ?', 17)

You can use any combination of the above in an exec_many() to batch all your queries into one request:

results, error = db.exec_many(
'CREATE TABLE student(name TEXT, age INTEGER)',
'INSERT INTO student VALUES ("Alice", 18)',
('INSERT INTO student VALUES (?, ?)', "Bob", 19),
'SELECT name, age FROM student WHERE age > 17',
('SELECT name, age FROM student WHERE age > ?', 17),
)

In the above example, substituting exec_many() with exec_atomic() executes all the queries in the batch as part of a transaction, rolling back all queries on any failures.

From curl

Use curl to send a query:

$ curl -s -u uzer:pa55word -d '{"e":{"d":"chinook", "s":[{"q": "select name, composer from tracks limit 5"}]}}' http://localhost:10100
{"r":[[["For Those About To Rock (We Salute You)","Angus Young, Malcolm Young, Brian Johnson"],["Balls to the Wall",null],["Fast As a Shark","F. Baltes, S. Kaufman, U. Dirkscneider \u0026 W. Hoffman"],["Restless and Wild","F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider \u0026 W. Hoffman"],["Princess of the Dawn","Deaffy \u0026 R.A. Smith-Diesel"]]]}

Use jq to pretty-print the response:

$ curl -s -u uzer:pa55word -d '{"e":{"d":"chinook", "s":[{"q": "SELECT name, composer FROM tracks LIMIT 5"}]}}' http://localhost:10100 | jq '.'
{
"r": [
[
[
"For Those About To Rock (We Salute You)",
"Angus Young, Malcolm Young, Brian Johnson"
],
[
"Balls to the Wall",
null
],
[
"Fast As a Shark",
"F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman"
],
[
"Restless and Wild",
"F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman"
],
[
"Princess of the Dawn",
"Deaffy & R.A. Smith-Diesel"
]
]
]
}

JSON protocol

Grammar

type request = exec_request | drop_request
type reply = exec_reply | drop_reply
type exec_request = { d: database_name, s: statement[], a: atomicity }
type database_name = string
type statement = { q: query, p: parameter[] }
type query = string
type parameter = primitive
type atomicity = 0 | 1
type exec_reply = { r: result[], e: error }
type result = row[]
type row = primitive[]
type error = string
type drop_request = { d: database_name }
type drop_reply = { e: error }
type primitive = number | string | boolean | null

Examples

Sample exec_request:

{
"e": {
"d": "chinook",
"s": [
{
"q": "select name, composer from tracks limit 5"
}
]
}
}

Sample reply:

{
"r": [
[
[
"For Those About To Rock (We Salute You)",
"Angus Young, Malcolm Young, Brian Johnson"
],
[
"Balls to the Wall",
null
],
[
"Fast As a Shark",
"F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman"
],
[
"Restless and Wild",
"F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman"
],
[
"Princess of the Dawn",
"Deaffy & R.A. Smith-Diesel"
]
]
]
}

Deployment

For production deployments:

  • Use the -access-keychain option to control API keys. See documentation.
  • Use the -tls-cert-file and -tls-key-file options to enable HTTPS.
$ ./wavedb \
-access-keychain /path/to/keychain \
-tls-cert-file /path/to/cert-file \
-tls-key-file /path/to/key-file

Configuration

$ ./wavedb -help
Usage of ./wavedb:
-access-key-id string
default API access key ID (default "access_key_id")
-access-key-secret string
default API access key secret (default "access_key_secret")
-access-keychain string
path to file containing API access keys (default ".wave-keychain")
-benchmark int
run benchmarks for the given number of iterations
-dir string
path to directory containing database (.db) files (default ".")
-listen string
listen on this address (default ":10100")
-tls-cert-file string
path to certificate file (TLS only)
-tls-key-file string
path to private key file (TLS only)
-verbose
enable verbose logging
-version
print version and exit

Acknowledgements

WaveDB is based on SQLite and bvinc/go-sqlite-lite, built with Go. The Python client uses HTTPX under the hood. Thank you to the authors and contributors.

WaveDB can be used from any language or platform that has libraries for HTTP and JSON. The reference implementation of the client is ~75 lines of code if you're interested in porting it to other languages.