River’s supported SQLite for over a year, but until now, Pro features were exclusive to Postgres.
Today, River Pro brings its full slate of features to SQLite:
- Workflows
- Concurrency limits
- Job batching
- Sequences (guaranteed ordering by sequence key)
- Active job rescue
- Encrypted and ephemeral jobs
Go and Ruby both support these features on either database (with Java, Rust, and TypeScript coming soon). Future Pro features will ship for every supported language × database all at once.
Our driver system already made multi-database support trivial in theory. The hard part was porting Pro’s SQL. River Pro has 220+ queries across its database drivers, with the longest running to ~130 lines of gnarly SQL (that’s gnarly looking, but efficiently running). Despite their superficial resemblance, Postgres and SQLite SQL dialects differ enough to make porting all those a painful project.
LLMs were the major unlock here. They helped us work through those thousands of lines of SQL while simultaneously expanding our driver test suite, giving us confidence that both implementations were really doing the same thing.
Why SQLite
We’re as committed to Postgres as ever, but SQLite opens up some interesting possibilities:
A database is a file, so there’s no separate database server to provision or manage. That makes SQLite a natural fit for local apps and embedded devices.
At edges, apps can read and write a local database without no network round trips.
Utilities like Litestream continuously stream changes to object storage, providing inexpensive backups and a way to recover if the host goes away suddenly.
We’ve got a lot of ideas around the use of River + SQLite + workflows for lightweight containers that hibernate between bursts of work. e.g. Agent sandboxes.
Workflow performance
Workflows are a headline feature of River Pro. We already had a benchmark suite for them, so we reran it for the SQLite support. In it, we measure throughput of thousands of workflows made up of ten tasks a piece (where a “task” is defined as a job that takes part in a workflow), all competing with each other to make progress.
On my two-year-old commodity M4 MBP, it ran 1,400-2,100 jobs a second:
| Workflows | Workers | Workflows/s | Jobs/s | Total time |
|---|---|---|---|---|
| 1,000 | 10 | 167.5 | 1,675 | 6.0 s |
| 1,000 | 100 | 198.5 | 1,985 | 5.1 s |
| 5,000 | 10 | 161.6 | 1,616 | 31.0 s |
| 5,000 | 100 | 205.8 | 2,058 | 24.3 s |
| 20,000 | 10 | 140.8 | 1,408 | 2m 22s |
| 20,000 | 100 | 162.1 | 1,621 | 2m 3s |
Unsurprisingly, SQLite is most not River’s fastest database engine. Postgres outperforms it by 2-4x on the same workloads:
| Workflows | Workers | SQLite workflows/s | Postgres workflows/s | SQLite / Postgres |
|---|---|---|---|---|
| 1,000 | 10 | 167.5 | 423.6 | 39.5% |
| 1,000 | 100 | 198.5 | 607.7 | 32.7% |
| 5,000 | 10 | 161.6 | 445.0 | 36.3% |
| 5,000 | 100 | 205.8 | 473.1 | 43.5% |
| 20,000 | 10 | 140.8 | 500.0 | 28.2% |
| 20,000 | 100 | 162.1 | 560.9 | 28.9% |
Recall that SQLite is more like a file with a really smart driver than an online, daemonized server like Postgres. Infamously, it facilitates only one write operation at a time.
So we’re pretty happy with these initial results. Postgres performed considerably better, but this write-heavy benchmark is practically anti-optimized for SQLite – you’d have to spend time to design one that you’d expect to perform worse. And yet, SQLite still managed to process thousands of tasks per second. Certainly enough for the most real-world applications.
Getting started with Pro + SQLite
First, set up your River Pro credentials if you haven’t already. Then update to the latest version of River Pro and add the Pro SQLite driver:
go get riverqueue.com/riverpro@latestgo get riverqueue.com/riverpro/driver/riverprosqlite@latestgo install riverqueue.com/riverpro/cmd/riverpro@latestRun both the main and Pro migration lines against your SQLite database:
export DATABASE_URL="sqlite://./river.sqlite3"riverpro migrate-up --database-url "$DATABASE_URL"riverpro migrate-up --database-url "$DATABASE_URL" --line proDepending on where your program is running, it might also be easier to use the migration framework’s Go API to bootstrap a database from inside a program.
Next, initialize River Pro with riverprosqlite instead of a Postgres driver, and make sure a SQLite package like modernc.org/sqlite is imported (LibSQL and Turso are also supported):
import ( "database/sql"
_ "modernc.org/sqlite"
"github.com/riverqueue/river" "riverqueue.com/riverpro" "riverqueue.com/riverpro/driver/riverprosqlite")
dbPool, err := sql.Open("sqlite", "file:./river.sqlite3?_pragma=journal_mode(WAL)")if err != nil { panic(err)}defer dbPool.Close()
dbPool.SetMaxOpenConns(1)
riverClient, err := riverpro.NewClient(riverprosqlite.New(dbPool), &riverpro.Config{ Config: river.Config{ Queues: map[string]river.QueueConfig{ river.QueueDefault: {MaxWorkers: 100}, }, Workers: workers, },})if err != nil { panic(err)}If you haven’t tried River Pro yet, try it today. We’d love to know how it works out for you.