SQLite
The sq SQLite driver implements connectivity for
the SQLite database. It makes use of the backing
mattn/sqlite3 library.
The driver implements all optional sq driver features.
Add source
Use sq add to add a source. The location argument is simply the
filepath to the SQLite DB file. For example:
# Relative path
$ sq add ./sakila.db
# Absolute path
$ sq add /Users/neilotoole/sakila.db
sq usually can detect that a file is a SQLite datafile, but in the event
it doesn’t, you can explicitly specify the driver type:
$ sq add --driver=sqlite3 ./sakila.db
Use the connection string form with prefix sqlite3:// to specify
connection parameters:
# Read/write with a shared cache.
$ sq add 'sqlite3://sakila.db?cache=shared&mode=rw'
# Read-only — any write fails at the SQLite layer.
$ sq add 'sqlite3://sakila.db?mode=ro'
# Treat the file as immutable so SQLite skips locking. Useful for
# inspecting a live database while another process holds it open —
# for example, a Firefox cookies DB while the browser is running.
$ sq add 'sqlite3://cookies.sqlite?immutable=1' --handle @cookies
The full set of supported parameters can be found in the mattn/sqlite3
docs.
Create new SQLite DB
You can use sq to create a new, empty, SQLite DB file.
$ sq add --driver sqlite3 hello.db
@hello sqlite3 hello.db
Extensions
The SQLite driver has several extensions baked in.
sq’s SQLite extension support is in early access. There is no special handling
in sq’s query language for any of the particular extension features (e.g. JSON). This may
change over time: open an issue
if you have a suggestion, or encounter unexpected or undesirable behavior.
You may find it necessary to use native sql mode to access some extension features.
| Extension | Details |
|---|---|
vtable | Virtual Table 👉 sq inspect will show the virtual table’s type as virtual. |
fts5 | Full Text Search 5 |
json | JSON |
math_functions | Math Functions |
introspect | Additional PRAGMA statements: function_list, module_list, pragma_list |
stat4 | Additional statistics to assist query planning |
Inspect field provenance
sq inspect populates the fields below from SQLite pragmas, built-in
functions, and the database file itself.
Source-level fields
| Field | Source |
|---|---|
name | DB file basename |
schema | first row of pragma_database_list (typically main) |
catalog | hardcoded default — SQLite has no catalog concept |
user | not populated — SQLite has no user model |
db_product | "SQLite3 v" + db_version |
db_version | sqlite_version() |
size | filesystem size of the DB file (os.Stat) |
Per-table fields
| Field | Source |
|---|---|
row_count | live SELECT COUNT(*) FROM "tbl" |
size | not reported — SQLite does not expose per-table storage |