modernc.org/sqlite

sqlite

Package sqlite is a cgo-free port of SQLite. Although you could see mattn's driver (github.com/mattn/go-sqlite3) in go.mod file, we import it for tests only.

SQLite is an in-process implementation of a self-contained, serverless, zero-configuration, transactional SQL database engine.

Thanks

This project is sponsored by Schleibinger Geräte Teubert u. Greim GmbH by allowing one of the maintainers to work on it also in office hours.

Installation

$ go get modernc.org/sqlite

Documentation

pkg.go.dev/modernc.org/sqlite

Builders

modern-c.appspot.com/-/builder/?importpath=modernc.org%2fsqlite

Speedtest1

Numbers for the pure Go version were produced by

 ~/src/modernc.org/sqlite/speedtest1$ go build && ./speedtest1

Numbers for the pure C version were produced by

 ~/src/modernc.org/sqlite/testdata/sqlite-src-3410200/test$ gcc speedtest1.c ../../sqlite-amalgamation-3410200/sqlite3.c -lpthread -ldl && ./a.out

The results are from Go version 1.20.4 and GCC version 10.2.1 on a Linux/amd64 machine, CPU: AMD Ryzen 9 3900X 12-Core Processor × 24, 128GB RAM. Shown are the best of 3 runs.

 Go											C

 -- Speedtest1 for SQLite 3.41.2 2023-03-22 11:56:21 0d1fc92f94cb6b76bffe3ec34d69	-- Speedtest1 for SQLite 3.41.2 2023-03-22 11:56:21 0d1fc92f94cb6b76bffe3ec34d69
  100 - 50000 INSERTs into table with no index......................    0.071s            100 - 50000 INSERTs into table with no index......................    0.077s
  110 - 50000 ordered INSERTS with one index/PK.....................    0.114s            110 - 50000 ordered INSERTS with one index/PK.....................    0.082s
  120 - 50000 unordered INSERTS with one index/PK...................    0.137s            120 - 50000 unordered INSERTS with one index/PK...................    0.099s
  130 - 25 SELECTS, numeric BETWEEN, unindexed......................    0.083s            130 - 25 SELECTS, numeric BETWEEN, unindexed......................    0.091s
  140 - 10 SELECTS, LIKE, unindexed.................................    0.210s            140 - 10 SELECTS, LIKE, unindexed.................................    0.120s
  142 - 10 SELECTS w/ORDER BY, unindexed............................    0.276s            142 - 10 SELECTS w/ORDER BY, unindexed............................    0.182s
  145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..................    0.183s            145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..................    0.099s
  150 - CREATE INDEX five times.....................................    0.172s            150 - CREATE INDEX five times.....................................    0.127s
  160 - 10000 SELECTS, numeric BETWEEN, indexed.....................    0.080s            160 - 10000 SELECTS, numeric BETWEEN, indexed.....................    0.078s
  161 - 10000 SELECTS, numeric BETWEEN, PK..........................    0.080s            161 - 10000 SELECTS, numeric BETWEEN, PK..........................    0.078s
  170 - 10000 SELECTS, text BETWEEN, indexed........................    0.187s            170 - 10000 SELECTS, text BETWEEN, indexed........................    0.169s
  180 - 50000 INSERTS with three indexes............................    0.196s            180 - 50000 INSERTS with three indexes............................    0.154s
  190 - DELETE and REFILL one table.................................    0.200s            190 - DELETE and REFILL one table.................................    0.155s
  200 - VACUUM......................................................    0.180s            200 - VACUUM......................................................    0.142s
  210 - ALTER TABLE ADD COLUMN, and query...........................    0.004s            210 - ALTER TABLE ADD COLUMN, and query...........................    0.005s
  230 - 10000 UPDATES, numeric BETWEEN, indexed.....................    0.093s            230 - 10000 UPDATES, numeric BETWEEN, indexed.....................    0.080s
  240 - 50000 UPDATES of individual rows............................    0.153s            240 - 50000 UPDATES of individual rows............................    0.137s
  250 - One big UPDATE of the whole 50000-row table.................    0.024s            250 - One big UPDATE of the whole 50000-row table.................    0.019s
  260 - Query added column after filling............................    0.004s            260 - Query added column after filling............................    0.005s
  270 - 10000 DELETEs, numeric BETWEEN, indexed.....................    0.278s            270 - 10000 DELETEs, numeric BETWEEN, indexed.....................    0.263s
  280 - 50000 DELETEs of individual rows............................    0.188s            280 - 50000 DELETEs of individual rows............................    0.180s
  290 - Refill two 50000-row tables using REPLACE...................    0.411s            290 - Refill two 50000-row tables using REPLACE...................    0.359s
  300 - Refill a 50000-row table using (b&1)==(a&1).................    0.175s            300 - Refill a 50000-row table using (b&1)==(a&1).................    0.151s
  310 - 10000 four-ways joins.......................................    0.427s            310 - 10000 four-ways joins.......................................    0.365s
  320 - subquery in result set......................................    0.440s            320 - subquery in result set......................................    0.521s
  400 - 70000 REPLACE ops on an IPK.................................    0.125s            400 - 70000 REPLACE ops on an IPK.................................    0.106s
  410 - 70000 SELECTS on an IPK.....................................    0.081s            410 - 70000 SELECTS on an IPK.....................................    0.078s
  500 - 70000 REPLACE on TEXT PK....................................    0.174s            500 - 70000 REPLACE on TEXT PK....................................    0.116s
  510 - 70000 SELECTS on a TEXT PK..................................    0.153s            510 - 70000 SELECTS on a TEXT PK..................................    0.117s
  520 - 70000 SELECT DISTINCT.......................................    0.083s            520 - 70000 SELECT DISTINCT.......................................    0.067s
  980 - PRAGMA integrity_check......................................    0.436s            980 - PRAGMA integrity_check......................................    0.377s
  990 - ANALYZE.....................................................    0.107s            990 - ANALYZE.....................................................    0.038s
        TOTAL.......................................................    5.525s                  TOTAL.......................................................    4.637s

This particular test executes 16.1% faster in the C version.

Troubleshooting

• Q: How can I write to a database concurrently without getting the database is locked error (or SQLITE_BUSY)?
  • A: You can't. The C sqlite implementation does not allow concurrent writes, and this libary does not modify that behaviour. You can, however, use DB.SetMaxOpenConns(1) so that only 1 connection is ever used by the DB, allowing concurrent access to DB without making the writes concurrent. More information on issues #65 and #106.