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
.
$ sq add 'sqlite3://sakila.db?cache=shared&mode=rw'
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 |