Go SQLite3

This repo contains an implementation of a read-only SQLite3 client written in Go. It can be used as a drop-in, API-compatible, replacement for mattn/go-sqlite3.

The goal of this project is to provide an optimized Go-native implementation of the SQLite client for a specific subset of read-only SQLite behavior. It aims to parallel the high-level architecture of the real C-based SQLite3 client.

It is strongly recommended that you do not use this library in production.

Compatibility

This library supports GOOS of [darwin, linux] and GOARCH of [amd64].

This library supports the following SQLite features. The list of unsupported features is not complete. Keep in mind this repo aims to minimize complexity by supporting only a specific subset of functionality, so it is unlikely to support any of the unsupported features, unless otherwise noted.

Namespace	Feature	Support
SQL	SELECT *	✅
SQL	SELECT <column> [, <column>]*	✅
SQL	SELECT ROWID	✅
SQL	`CAST( AS BLOB	...)`
SQL	FROM <tableName>	✅
SQL	FROM pragma_table_info(?)	✅
SQL	`WHERE [AND	OR ]*`
SQL	`ORDER BY [ASC	DESC]`
SQL	LIMIT <n>	✅
SQL	WITHOUT ROWID tables	❌
SQL	ATTACH/DETACH	❌
SQL	Pragmas	❌ pragma_table_info, but no others
SQL	JOIN (any kind)	❌
Journaling	WAL	✅ Yes, except for checkpointing and recovery
Journaling	Legacy (Rollback)	❌
DB Types	File	✅
DB Types	:memory:	❌ (PRs welcome!)
DB Types	Temporary	❌
Indexes	Primary Key	✅
Collation	Binary	✅
Text Encoding	UTF-8	✅
Text Encoding	UTF-16	❌
SQLite	Handlers	❌
...	...	...

Using the client for any unsupported features will lead to undefined behavior.

Architecture

For a high-level overview of the real SQLite3 architecture, see the technical design docs. This implementation was also inspired by SQLite Database System Design and Implementation (2015).

Query Patterns

GetRowByKey

GetRowByKey is the same as GetRowsByKeyPrefix, except that it includes all of the keys in the PK and therefore will only return 0 or 1 results. Therefore, GetRowsByKeyPrefix is a superset of GetRowByKey.

SELECT*FROM $ldbTableName
WHERE
$pkCol1 = ?
AND $pkCol2 = ?
-- ...

Here is an example bytecode plan:

sqlite> explain select*from flagon2___gates where family=CAST("colin"AS BLOB) and name=CAST("gate"as BLOB);
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------0 Init 028000 Start at 281 OpenRead 0156301400 root=1563 iDb=0; flagon2___gates
2 OpenRead 115640 k(3,,,) 02 root=1564 iDb=0; sqlite_autoindex_flagon2___gates_1
3 String8 010 colin 00 r[1]='colin'4 Cast 165000 affinity(r[1])
5 IsNull 127000 if r[1]==NULL goto 276 String8 020 gate 00 r[2]='gate'7 Cast 265000 affinity(r[2])
8 IsNull 227000 if r[2]==NULL goto 279 SeekGE 1271200 key=r[1..2]
10 IdxGT 1271200 key=r[1..2]
11 DeferredSeek 10000 Move 0 to 1.rowid if needed
12 Column 10300 r[3]=flagon2___gates.family13 Column 11400 r[4]=flagon2___gates.name14 Column 02500 r[5]=flagon2___gates.description15 Column 03600 r[6]=flagon2___gates.id_type16 Column 04700 r[7]=flagon2___gates.tier_list_id17 Column 05800 r[8]=flagon2___gates.rollout18 Column 06900 r[9]=flagon2___gates.salt19 Column 071000 r[10]=flagon2___gates.open20 Column 081100 r[11]=flagon2___gates.archived21 Column 091200 r[12]=flagon2___gates.archived_at22 Column 0101300 r[13]=flagon2___gates.user_id23 Column 0111400 r[14]=flagon2___gates.user_type24 Column 0121500 r[15]=flagon2___gates.created_at25 Column 0131600 r[16]=flagon2___gates.updated_at26 ResultRow 314000 output=r[3..16]
27 Halt 0000028 Transaction 0090934001 usesStmtJournal=029 Goto 01000

GetRowsByKeyPrefix

SELECT*FROM $ldbTableName

SELECT*FROM $ldbTableName
WHERE
$pkCol1 = ?
AND $pkCol2 = ?
-- ...

Here's an example bytecode plan:

sqlite> explain select*from flagon2___gates where family=CAST("colin"AS BLOB);
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------0 Init 026000 Start at 261 OpenRead 0156301400 root=1563 iDb=0; flagon2___gates
2 OpenRead 115640 k(3,,,) 02 root=1564 iDb=0; sqlite_autoindex_flagon2___gates_1
3 String8 010 colin 00 r[1]='colin'4 Cast 165000 affinity(r[1])
5 IsNull 125000 if r[1]==NULL goto 256 SeekGE 1251100 key=r[1]
7 IdxGT 1251100 key=r[1]
8 DeferredSeek 10000 Move 0 to 1.rowid if needed
9 Column 10200 r[2]=flagon2___gates.family10 Column 11300 r[3]=flagon2___gates.name11 Column 02400 r[4]=flagon2___gates.description12 Column 03500 r[5]=flagon2___gates.id_type13 Column 04600 r[6]=flagon2___gates.tier_list_id14 Column 05700 r[7]=flagon2___gates.rollout15 Column 06800 r[8]=flagon2___gates.salt16 Column 07900 r[9]=flagon2___gates.open17 Column 081000 r[10]=flagon2___gates.archived18 Column 091100 r[11]=flagon2___gates.archived_at19 Column 0101200 r[12]=flagon2___gates.user_id20 Column 0111300 r[13]=flagon2___gates.user_type21 Column 0121400 r[14]=flagon2___gates.created_at22 Column 0131500 r[15]=flagon2___gates.updated_at23 ResultRow 214000 output=r[2..15]
24 Next 1700025 Halt 0000026 Transaction 0090934001 usesStmtJournal=027 Goto 01000

GetLedgerLatency

SELECTtimestampFROM _ldb_last_update
-- "ledger"WHERE name=?

FetchSeqFromDB

SELECT seq
FROM _ldb_seq
WHERE id =1

Ping

SELECT seq
FROM _ldb_seq
-- 1WHERE id = ?

getPrimaryKey

SELECT name, type
-- ldbTable parameterFROM pragma_table_info(?)
WHERE pk >0ORDER BY pk ASC

SELECT*FROM $ldbTable LIMIT1

TestUtils (EnsureLdbInitialized, etc.)

The TestUtils require that we support creating tables and writing to them.

In this case, let's just re-implement the testing libraries to use an in-memory LDB of some kind.

Alternatively, we could implement an exclusive writer in this package that assumes it is the only reader/writer to the DB and where ACID compliance (f.e. WALing) isn't necessary. This would allow us to make a number of simplifying assumptions.

Edge Cases

• PKs being dropped and re-created. Seems like there is some logic to handle execution errors and refresh the PK cache.