SQLite

River mainly targets Postgres, but is equipped with experimental support for SQLite. SQLite databases are files on disk rather than active daemons, making them suitable for embedded environments and other non-server contexts. We're hoping that bringing SQLite to River will widen its potential applications.

SQLite support was introduced in River 0.23.0.

The SQLite driver

SQLite is activated by initializing a client with the riversqlite driver instead of the more common riverpgxv5:

import (
    "database/sql"

    _ "modernc.org/sqlite"

    "github.com/riverqueue/river"
    "github.com/riverqueue/river/riverdriver/riversqlite"
)

dbPool, err := sql.Open("sqlite", "sqlite://river.sqlite3")
if err != nil {
    panic(err)
}
defer dbPool.Close()

dbPool.SetMaxOpenConns(1)

workers := river.NewWorkers()
river.AddWorker(workers, &SortWorker{})

riverClient, err := river.NewClient(riversqlite.New(dbPool), &river.Config{
    Queues: map[string]river.QueueConfig{
        river.QueueDefault: {MaxWorkers: 100},
    },
    Workers:  workers,
})
if err != nil {
    panic(err)
}

See the sqlite example for complete code.

River's SQLite driver uses primitives from Go's built-in database/sql so it should theoretically be able to support a varied selection of SQLite drivers. River tests with modernc.org/sqlite, a modern, pure Go option.

SQLite support is broadly tested internally, but should still be considered experimental, and it may still have a few tweaks made to its schema before being considering finalized. All changes will be noted in the changelog for future versions.

Due to limitations like SQLite's less expressive SQL (for example, mutations in CTEs aren't possible) and rocky SQLite support in sqlc, the SQLite driver isn't as fast as Postgres, benchmarking at about ¼ the speed, but still pushing 10k jobs/sec. We expect to address SQLite's deficiencies over time, and it's reasonably likely that SQLite and Postgres will converge in performance as optimizations for the former are implemented.

Migrating via CLI

Migrations use the normal River CLI. A sqlite:// DSN activates the SQLite driver:

export DATABASE_URL="sqlite://sqlite/river.sqlite3"
river migrate-up --line main --database-url "$DATABASE_URL"

DDL is different than Postgres', so a DSN is needed even for operations that don't enact on a database to tell River which database to produce code for:

river migrate-get --version 6 --up --database-url sqlite:// > river_6.up.sql

Concurrency best practices

An inherent limitation of SQLite is that it can only be opened by one writer at a time. Writers that try to open it while another process has a lock are met with this error:

database is locked (5) (SQLITE_BUSY)

Below are two techniques for avoiding this problem. We recommend the use of both.

WAL journaling

Like most databases, SQLite uses journaling to protect written data in the event of crashes. Its default journaling mode is DELETE, but WAL (write-ahead logging) can be enabled for superior concurrent capability. Connect to a database and set journal_mode:

PRAGMA journal_mode = WAL

Specifically, use of WAL means that some concurrent access becomes possible. Readers do not block writers and a writer does not block readers.

We find in our testing that use of WAL doesn't immediately produce a noticeable effect at lower concurrency levels, but in highly concurrent code its use becomes paramount, with SQLITE_BUSY errors inevitable without it.

Single connection pool

A good way to manage highly concurrent access with a Go connection pool is to configure it to have a maximum of one active connection:

dbPool, err := sql.Open("sqlite", "sqlite://river.sqlite3")
if err != nil {
    panic(err)
}
defer dbPool.Close()

// Set maximum connections to 1 to avoid `SQLITE_BUSY` errors.
dbPool.SetMaxOpenConns(1)

This approach is unconventional by database standards because it means that goroutines will block waiting for database access, but in practice SQLite operations are fast, so it tends to be tolerable even at high throughput.

An alternative is to increase busy_timeout. We find that use of a single active connection is more effective at reducing the incidence of SQLITE_BUSY errors, but your mileage may vary.

However, when setting a maximum of one connection, take care with transactions. An open transaction will monopolize the single available connection as long as it stays open, potentially starving other callers. When using transactions, keep them as short-lived as possible. Ideally they should last only single digit milliseconds.

Benchmark

Here's an informal benchmark of River on SQLite burning through a fixed backlog of one million jobs. On a commodity M4 MacBook Pro, it works about 10k jobs/sec, which is about ¼ the speed of a similar run on Postgres:

$ go run ./cmd/river bench --database-url "sqlite://:memory:" --num-total-jobs 1_000_000
bench: jobs worked [          0 ], inserted [    1000000 ], job/sec [        0.0 ] [0s]
bench: jobs worked [      16007 ], inserted [          0 ], job/sec [     8003.5 ] [2s]
bench: jobs worked [      22009 ], inserted [          0 ], job/sec [    11004.5 ] [2s]
bench: jobs worked [      20019 ], inserted [          0 ], job/sec [    10009.5 ] [2s]
bench: jobs worked [      20005 ], inserted [          0 ], job/sec [    10002.5 ] [2s]
bench: jobs worked [      19490 ], inserted [          0 ], job/sec [     9745.0 ] [2s]
bench: jobs worked [      20011 ], inserted [          0 ], job/sec [    10005.5 ] [2s]
bench: jobs worked [      18521 ], inserted [          0 ], job/sec [     9260.5 ] [2s]
bench: jobs worked [      20017 ], inserted [          0 ], job/sec [    10008.5 ] [2s]
bench: jobs worked [      20004 ], inserted [          0 ], job/sec [    10002.0 ] [2s]
bench: jobs worked [      19502 ], inserted [          0 ], job/sec [     9751.0 ] [2s]
bench: jobs worked [      18520 ], inserted [          0 ], job/sec [     9260.0 ] [2s]
bench: jobs worked [      19504 ], inserted [          0 ], job/sec [     9752.0 ] [2s]
bench: jobs worked [      18511 ], inserted [          0 ], job/sec [     9255.5 ] [2s]
bench: jobs worked [      19752 ], inserted [          0 ], job/sec [     9876.0 ] [2s]
bench: jobs worked [      20262 ], inserted [          0 ], job/sec [    10131.0 ] [2s]
bench: jobs worked [      19520 ], inserted [          0 ], job/sec [     9760.0 ] [2s]
bench: jobs worked [      18503 ], inserted [          0 ], job/sec [     9251.5 ] [2s]
bench: jobs worked [      20004 ], inserted [          0 ], job/sec [    10002.0 ] [2s]
bench: jobs worked [      16007 ], inserted [          0 ], job/sec [     8003.5 ] [2s]
...
bench: total jobs worked [    1000000 ], total jobs inserted [    1000000 ], overall job/sec [     9560.1 ], running 1m44.601231s

The difference in speed has little to do with database performance, and more to do with driver implementation. Due to limitations in sqlc, SQLite's batch operations only work one row at a time, and despite still being quite fast because of the local round trip, it still has a dramatic impact on performance.

Venturing beyond Postgres

SQLite is River's first foray beyond Postgres, and we're interested in hearing from you if you've been able to use it effectively. This will also help us gauge interest in non-Postgres systems, and whether we should implement support for other common RDBMSes like MySQL.