CLI reference#
This page lists the --help for every sqlite-utils CLI sub-command.
query#
See Running SQL queries.
Usage: sqlite-utils query [OPTIONS] PATH SQL
Execute SQL query and return the results as JSON
Example:
sqlite-utils data.db \
"select * from chickens where age > :age" \
-p age 1
Options:
--attach <TEXT FILE>... Additional databases to attach - specify alias and
filepath
--nl Output newline-delimited JSON
--arrays Output rows as arrays instead of objects
--csv Output CSV
--tsv Output TSV
--no-headers Omit CSV headers
-t, --table Output as a formatted table
--fmt TEXT Table format - one of asciidoc, double_grid,
double_outline, fancy_grid, fancy_outline, github,
grid, heavy_grid, heavy_outline, html, jira,
latex, latex_booktabs, latex_longtable, latex_raw,
mediawiki, mixed_grid, mixed_outline, moinmoin,
orgtbl, outline, pipe, plain, presto, pretty,
psql, rounded_grid, rounded_outline, rst, simple,
simple_grid, simple_outline, textile, tsv,
unsafehtml, youtrack
--json-cols Detect JSON cols and output them as JSON, not
escaped strings
-r, --raw Raw output, first column of first row
--raw-lines Raw output, first column of each row
-p, --param <TEXT TEXT>... Named :parameters for SQL query
--functions TEXT Python code defining one or more custom SQL
functions
--load-extension TEXT Path to SQLite extension, with optional
:entrypoint
-h, --help Show this message and exit.
memory#
See Querying data directly using an in-memory database.
Usage: sqlite-utils memory [OPTIONS] [PATHS]... SQL
Execute SQL query against an in-memory database, optionally populated by
imported data
To import data from CSV, TSV or JSON files pass them on the command-line:
sqlite-utils memory one.csv two.json \
"select * from one join two on one.two_id = two.id"
For data piped into the tool from standard input, use "-" or "stdin":
cat animals.csv | sqlite-utils memory - \
"select * from stdin where species = 'dog'"
The format of the data will be automatically detected. You can specify the
format explicitly using :json, :csv, :tsv or :nl (for newline-delimited JSON)
- for example:
cat animals.csv | sqlite-utils memory stdin:csv places.dat:nl \
"select * from stdin where place_id in (select id from places)"
Use --schema to view the SQL schema of any imported files:
sqlite-utils memory animals.csv --schema
Options:
--functions TEXT Python code defining one or more custom SQL
functions
--attach <TEXT FILE>... Additional databases to attach - specify alias and
filepath
--flatten Flatten nested JSON objects, so {"foo": {"bar":
1}} becomes {"foo_bar": 1}
--nl Output newline-delimited JSON
--arrays Output rows as arrays instead of objects
--csv Output CSV
--tsv Output TSV
--no-headers Omit CSV headers
-t, --table Output as a formatted table
--fmt TEXT Table format - one of asciidoc, double_grid,
double_outline, fancy_grid, fancy_outline, github,
grid, heavy_grid, heavy_outline, html, jira,
latex, latex_booktabs, latex_longtable, latex_raw,
mediawiki, mixed_grid, mixed_outline, moinmoin,
orgtbl, outline, pipe, plain, presto, pretty,
psql, rounded_grid, rounded_outline, rst, simple,
simple_grid, simple_outline, textile, tsv,
unsafehtml, youtrack
--json-cols Detect JSON cols and output them as JSON, not
escaped strings
-r, --raw Raw output, first column of first row
--raw-lines Raw output, first column of each row
-p, --param <TEXT TEXT>... Named :parameters for SQL query
--encoding TEXT Character encoding for CSV input, defaults to
utf-8
-n, --no-detect-types Treat all CSV/TSV columns as TEXT
--schema Show SQL schema for in-memory database
--dump Dump SQL for in-memory database
--save FILE Save in-memory database to this file
--analyze Analyze resulting tables and output results
--load-extension TEXT Path to SQLite extension, with optional
:entrypoint
-h, --help Show this message and exit.
insert#
See Inserting JSON data, Inserting CSV or TSV data, Inserting unstructured data with --lines and --text, Applying conversions while inserting data.
Usage: sqlite-utils insert [OPTIONS] PATH TABLE FILE
Insert records from FILE into a table, creating the table if it does not
already exist.
Example:
echo '{"name": "Lila"}' | sqlite-utils insert data.db chickens -
By default the input is expected to be a JSON object or array of objects.
- Use --nl for newline-delimited JSON objects
- Use --csv or --tsv for comma-separated or tab-separated input
- Use --lines to write each incoming line to a column called "line"
- Use --text to write the entire input to a column called "text"
You can also use --convert to pass a fragment of Python code that will be used
to convert each input.
Your Python code will be passed a "row" variable representing the imported
row, and can return a modified row.
This example uses just the name, latitude and longitude columns from a CSV
file, converting name to upper case and latitude and longitude to floating
point numbers:
sqlite-utils insert plants.db plants plants.csv --csv --convert '
return {
"name": row["name"].upper(),
"latitude": float(row["latitude"]),
"longitude": float(row["longitude"]),
}'
If you are using --lines your code will be passed a "line" variable, and for
--text a "text" variable.
When using --text your function can return an iterator of rows to insert. This
example inserts one record per word in the input:
echo 'A bunch of words' | sqlite-utils insert words.db words - \
--text --convert '({"word": w} for w in text.split())'
Options:
--pk TEXT Columns to use as the primary key, e.g. id
--flatten Flatten nested JSON objects, so {"a": {"b": 1}}
becomes {"a_b": 1}
--nl Expect newline-delimited JSON
-c, --csv Expect CSV input
--tsv Expect TSV input
--empty-null Treat empty strings as NULL
--lines Treat each line as a single value called 'line'
--text Treat input as a single value called 'text'
--convert TEXT Python code to convert each item
--import TEXT Python modules to import
--delimiter TEXT Delimiter to use for CSV files
--quotechar TEXT Quote character to use for CSV/TSV
--sniff Detect delimiter and quote character
--no-headers CSV file has no header row
--encoding TEXT Character encoding for input, defaults to utf-8
--batch-size INTEGER Commit every X records
--stop-after INTEGER Stop after X records
--alter Alter existing table to add any missing columns
--not-null TEXT Columns that should be created as NOT NULL
--default <TEXT TEXT>... Default value that should be set for a column
-d, --detect-types Detect types for columns in CSV/TSV data
--analyze Run ANALYZE at the end of this operation
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
--silent Do not show progress bar
--ignore Ignore records if pk already exists
--replace Replace records if pk already exists
--truncate Truncate table before inserting records, if table
already exists
-h, --help Show this message and exit.
upsert#
See Upserting data.
Usage: sqlite-utils upsert [OPTIONS] PATH TABLE FILE
Upsert records based on their primary key. Works like 'insert' but if an
incoming record has a primary key that matches an existing record the existing
record will be updated.
Example:
echo '[
{"id": 1, "name": "Lila"},
{"id": 2, "name": "Suna"}
]' | sqlite-utils upsert data.db chickens - --pk id
Options:
--pk TEXT Columns to use as the primary key, e.g. id
[required]
--flatten Flatten nested JSON objects, so {"a": {"b": 1}}
becomes {"a_b": 1}
--nl Expect newline-delimited JSON
-c, --csv Expect CSV input
--tsv Expect TSV input
--empty-null Treat empty strings as NULL
--lines Treat each line as a single value called 'line'
--text Treat input as a single value called 'text'
--convert TEXT Python code to convert each item
--import TEXT Python modules to import
--delimiter TEXT Delimiter to use for CSV files
--quotechar TEXT Quote character to use for CSV/TSV
--sniff Detect delimiter and quote character
--no-headers CSV file has no header row
--encoding TEXT Character encoding for input, defaults to utf-8
--batch-size INTEGER Commit every X records
--stop-after INTEGER Stop after X records
--alter Alter existing table to add any missing columns
--not-null TEXT Columns that should be created as NOT NULL
--default <TEXT TEXT>... Default value that should be set for a column
-d, --detect-types Detect types for columns in CSV/TSV data
--analyze Run ANALYZE at the end of this operation
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
--silent Do not show progress bar
-h, --help Show this message and exit.
bulk#
Usage: sqlite-utils bulk [OPTIONS] PATH SQL FILE
Execute parameterized SQL against the provided list of documents.
Example:
echo '[
{"id": 1, "name": "Lila2"},
{"id": 2, "name": "Suna2"}
]' | sqlite-utils bulk data.db '
update chickens set name = :name where id = :id
' -
Options:
--batch-size INTEGER Commit every X records
--functions TEXT Python code defining one or more custom SQL functions
--flatten Flatten nested JSON objects, so {"a": {"b": 1}} becomes
{"a_b": 1}
--nl Expect newline-delimited JSON
-c, --csv Expect CSV input
--tsv Expect TSV input
--empty-null Treat empty strings as NULL
--lines Treat each line as a single value called 'line'
--text Treat input as a single value called 'text'
--convert TEXT Python code to convert each item
--import TEXT Python modules to import
--delimiter TEXT Delimiter to use for CSV files
--quotechar TEXT Quote character to use for CSV/TSV
--sniff Detect delimiter and quote character
--no-headers CSV file has no header row
--encoding TEXT Character encoding for input, defaults to utf-8
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
search#
See Executing searches.
Usage: sqlite-utils search [OPTIONS] PATH DBTABLE Q
Execute a full-text search against this table
Example:
sqlite-utils search data.db chickens lila
Options:
-o, --order TEXT Order by ('column' or 'column desc')
-c, --column TEXT Columns to return
--limit INTEGER Number of rows to return - defaults to everything
--sql Show SQL query that would be run
--quote Apply FTS quoting rules to search term
--nl Output newline-delimited JSON
--arrays Output rows as arrays instead of objects
--csv Output CSV
--tsv Output TSV
--no-headers Omit CSV headers
-t, --table Output as a formatted table
--fmt TEXT Table format - one of asciidoc, double_grid,
double_outline, fancy_grid, fancy_outline, github,
grid, heavy_grid, heavy_outline, html, jira, latex,
latex_booktabs, latex_longtable, latex_raw, mediawiki,
mixed_grid, mixed_outline, moinmoin, orgtbl, outline,
pipe, plain, presto, pretty, psql, rounded_grid,
rounded_outline, rst, simple, simple_grid,
simple_outline, textile, tsv, unsafehtml, youtrack
--json-cols Detect JSON cols and output them as JSON, not escaped
strings
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
transform#
See Transforming tables.
Usage: sqlite-utils transform [OPTIONS] PATH TABLE
Transform a table beyond the capabilities of ALTER TABLE
Example:
sqlite-utils transform mydb.db mytable \
--drop column1 \
--rename column2 column_renamed
Options:
--type <TEXT CHOICE>... Change column type to INTEGER, TEXT, FLOAT or BLOB
--drop TEXT Drop this column
--rename <TEXT TEXT>... Rename this column to X
-o, --column-order TEXT Reorder columns
--not-null TEXT Set this column to NOT NULL
--not-null-false TEXT Remove NOT NULL from this column
--pk TEXT Make this column the primary key
--pk-none Remove primary key (convert to rowid table)
--default <TEXT TEXT>... Set default value for this column
--default-none TEXT Remove default from this column
--drop-foreign-key TEXT Drop foreign key constraint for this column
--sql Output SQL without executing it
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
extract#
See Extracting columns into a separate table.
Usage: sqlite-utils extract [OPTIONS] PATH TABLE COLUMNS...
Extract one or more columns into a separate table
Example:
sqlite-utils extract trees.db Street_Trees species
Options:
--table TEXT Name of the other table to extract columns to
--fk-column TEXT Name of the foreign key column to add to the table
--rename <TEXT TEXT>... Rename this column in extracted table
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
schema#
See Showing the schema.
Usage: sqlite-utils schema [OPTIONS] PATH [TABLES]...
Show full schema for this database or for specified tables
Example:
sqlite-utils schema trees.db
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
insert-files#
See Inserting data from files.
Usage: sqlite-utils insert-files [OPTIONS] PATH TABLE FILE_OR_DIR...
Insert one or more files using BLOB columns in the specified table
Example:
sqlite-utils insert-files pics.db images *.gif \
-c name:name \
-c content:content \
-c content_hash:sha256 \
-c created:ctime_iso \
-c modified:mtime_iso \
-c size:size \
--pk name
Options:
-c, --column TEXT Column definitions for the table
--pk TEXT Column to use as primary key
--alter Alter table to add missing columns
--replace Replace files with matching primary key
--upsert Upsert files with matching primary key
--name TEXT File name to use
--text Store file content as TEXT, not BLOB
--encoding TEXT Character encoding for input, defaults to utf-8
-s, --silent Don't show a progress bar
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
analyze-tables#
See Analyzing tables.
Usage: sqlite-utils analyze-tables [OPTIONS] PATH [TABLES]...
Analyze the columns in one or more tables
Example:
sqlite-utils analyze-tables data.db trees
Options:
-c, --column TEXT Specific columns to analyze
--save Save results to _analyze_tables table
--common-limit INTEGER How many common values
--no-most Skip most common values
--no-least Skip least common values
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
convert#
See Converting data in columns.
Usage: sqlite-utils convert [OPTIONS] DB_PATH TABLE COLUMNS... CODE
Convert columns using Python code you supply. For example:
sqlite-utils convert my.db mytable mycolumn \
'"\n".join(textwrap.wrap(value, 10))' \
--import=textwrap
"value" is a variable with the column value to be converted.
Use "-" for CODE to read Python code from standard input.
The following common operations are available as recipe functions:
r.jsonsplit(value, delimiter=',', type=<class 'str'>)
Convert a string like a,b,c into a JSON array ["a", "b", "c"]
r.parsedate(value, dayfirst=False, yearfirst=False, errors=None)
Parse a date and convert it to ISO date format: yyyy-mm-dd
- dayfirst=True: treat xx as the day in xx/yy/zz
- yearfirst=True: treat xx as the year in xx/yy/zz
- errors=r.IGNORE to ignore values that cannot be parsed
- errors=r.SET_NULL to set values that cannot be parsed to null
r.parsedatetime(value, dayfirst=False, yearfirst=False, errors=None)
Parse a datetime and convert it to ISO datetime format: yyyy-mm-ddTHH:MM:SS
- dayfirst=True: treat xx as the day in xx/yy/zz
- yearfirst=True: treat xx as the year in xx/yy/zz
- errors=r.IGNORE to ignore values that cannot be parsed
- errors=r.SET_NULL to set values that cannot be parsed to null
You can use these recipes like so:
sqlite-utils convert my.db mytable mycolumn \
'r.jsonsplit(value, delimiter=":")'
Options:
--import TEXT Python modules to import
--dry-run Show results of running this against first 10
rows
--multi Populate columns for keys in returned
dictionary
--where TEXT Optional where clause
-p, --param <TEXT TEXT>... Named :parameters for where clause
--output TEXT Optional separate column to populate with the
output
--output-type [integer|float|blob|text]
Column type to use for the output column
--drop Drop original column afterwards
--no-skip-false Don't skip falsey values
-s, --silent Don't show a progress bar
-h, --help Show this message and exit.
tables#
See Listing tables.
Usage: sqlite-utils tables [OPTIONS] PATH
List the tables in the database
Example:
sqlite-utils tables trees.db
Options:
--fts4 Just show FTS4 enabled tables
--fts5 Just show FTS5 enabled tables
--counts Include row counts per table
--nl Output newline-delimited JSON
--arrays Output rows as arrays instead of objects
--csv Output CSV
--tsv Output TSV
--no-headers Omit CSV headers
-t, --table Output as a formatted table
--fmt TEXT Table format - one of asciidoc, double_grid,
double_outline, fancy_grid, fancy_outline, github,
grid, heavy_grid, heavy_outline, html, jira, latex,
latex_booktabs, latex_longtable, latex_raw, mediawiki,
mixed_grid, mixed_outline, moinmoin, orgtbl, outline,
pipe, plain, presto, pretty, psql, rounded_grid,
rounded_outline, rst, simple, simple_grid,
simple_outline, textile, tsv, unsafehtml, youtrack
--json-cols Detect JSON cols and output them as JSON, not escaped
strings
--columns Include list of columns for each table
--schema Include schema for each table
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
views#
See Listing views.
Usage: sqlite-utils views [OPTIONS] PATH
List the views in the database
Example:
sqlite-utils views trees.db
Options:
--counts Include row counts per view
--nl Output newline-delimited JSON
--arrays Output rows as arrays instead of objects
--csv Output CSV
--tsv Output TSV
--no-headers Omit CSV headers
-t, --table Output as a formatted table
--fmt TEXT Table format - one of asciidoc, double_grid,
double_outline, fancy_grid, fancy_outline, github,
grid, heavy_grid, heavy_outline, html, jira, latex,
latex_booktabs, latex_longtable, latex_raw, mediawiki,
mixed_grid, mixed_outline, moinmoin, orgtbl, outline,
pipe, plain, presto, pretty, psql, rounded_grid,
rounded_outline, rst, simple, simple_grid,
simple_outline, textile, tsv, unsafehtml, youtrack
--json-cols Detect JSON cols and output them as JSON, not escaped
strings
--columns Include list of columns for each view
--schema Include schema for each view
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
rows#
See Returning all rows in a table.
Usage: sqlite-utils rows [OPTIONS] PATH DBTABLE
Output all rows in the specified table
Example:
sqlite-utils rows trees.db Trees
Options:
-c, --column TEXT Columns to return
--where TEXT Optional where clause
-o, --order TEXT Order by ('column' or 'column desc')
-p, --param <TEXT TEXT>... Named :parameters for where clause
--limit INTEGER Number of rows to return - defaults to everything
--offset INTEGER SQL offset to use
--nl Output newline-delimited JSON
--arrays Output rows as arrays instead of objects
--csv Output CSV
--tsv Output TSV
--no-headers Omit CSV headers
-t, --table Output as a formatted table
--fmt TEXT Table format - one of asciidoc, double_grid,
double_outline, fancy_grid, fancy_outline, github,
grid, heavy_grid, heavy_outline, html, jira,
latex, latex_booktabs, latex_longtable, latex_raw,
mediawiki, mixed_grid, mixed_outline, moinmoin,
orgtbl, outline, pipe, plain, presto, pretty,
psql, rounded_grid, rounded_outline, rst, simple,
simple_grid, simple_outline, textile, tsv,
unsafehtml, youtrack
--json-cols Detect JSON cols and output them as JSON, not
escaped strings
--load-extension TEXT Path to SQLite extension, with optional
:entrypoint
-h, --help Show this message and exit.
triggers#
See Listing triggers.
Usage: sqlite-utils triggers [OPTIONS] PATH [TABLES]...
Show triggers configured in this database
Example:
sqlite-utils triggers trees.db
Options:
--nl Output newline-delimited JSON
--arrays Output rows as arrays instead of objects
--csv Output CSV
--tsv Output TSV
--no-headers Omit CSV headers
-t, --table Output as a formatted table
--fmt TEXT Table format - one of asciidoc, double_grid,
double_outline, fancy_grid, fancy_outline, github,
grid, heavy_grid, heavy_outline, html, jira, latex,
latex_booktabs, latex_longtable, latex_raw, mediawiki,
mixed_grid, mixed_outline, moinmoin, orgtbl, outline,
pipe, plain, presto, pretty, psql, rounded_grid,
rounded_outline, rst, simple, simple_grid,
simple_outline, textile, tsv, unsafehtml, youtrack
--json-cols Detect JSON cols and output them as JSON, not escaped
strings
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
indexes#
See Listing indexes.
Usage: sqlite-utils indexes [OPTIONS] PATH [TABLES]...
Show indexes for the whole database or specific tables
Example:
sqlite-utils indexes trees.db Trees
Options:
--aux Include auxiliary columns
--nl Output newline-delimited JSON
--arrays Output rows as arrays instead of objects
--csv Output CSV
--tsv Output TSV
--no-headers Omit CSV headers
-t, --table Output as a formatted table
--fmt TEXT Table format - one of asciidoc, double_grid,
double_outline, fancy_grid, fancy_outline, github,
grid, heavy_grid, heavy_outline, html, jira, latex,
latex_booktabs, latex_longtable, latex_raw, mediawiki,
mixed_grid, mixed_outline, moinmoin, orgtbl, outline,
pipe, plain, presto, pretty, psql, rounded_grid,
rounded_outline, rst, simple, simple_grid,
simple_outline, textile, tsv, unsafehtml, youtrack
--json-cols Detect JSON cols and output them as JSON, not escaped
strings
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
create-database#
See Creating an empty database.
Usage: sqlite-utils create-database [OPTIONS] PATH
Create a new empty database file
Example:
sqlite-utils create-database trees.db
Options:
--enable-wal Enable WAL mode on the created database
--init-spatialite Enable SpatiaLite on the created database
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
create-table#
See Creating tables.
Usage: sqlite-utils create-table [OPTIONS] PATH TABLE COLUMNS...
Add a table with the specified columns. Columns should be specified using
name, type pairs, for example:
sqlite-utils create-table my.db people \
id integer \
name text \
height float \
photo blob --pk id
Valid column types are text, integer, float and blob.
Options:
--pk TEXT Column to use as primary key
--not-null TEXT Columns that should be created as NOT NULL
--default <TEXT TEXT>... Default value that should be set for a column
--fk <TEXT TEXT TEXT>... Column, other table, other column to set as a
foreign key
--ignore If table already exists, do nothing
--replace If table already exists, replace it
--transform If table already exists, try to transform the schema
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
create-index#
See Creating indexes.
Usage: sqlite-utils create-index [OPTIONS] PATH TABLE COLUMN...
Add an index to the specified table for the specified columns
Example:
sqlite-utils create-index chickens.db chickens name
To create an index in descending order:
sqlite-utils create-index chickens.db chickens -- -name
Options:
--name TEXT Explicit name for the new index
--unique Make this a unique index
--if-not-exists, --ignore Ignore if index already exists
--analyze Run ANALYZE after creating the index
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
enable-fts#
See Configuring full-text search.
Usage: sqlite-utils enable-fts [OPTIONS] PATH TABLE COLUMN...
Enable full-text search for specific table and columns"
Example:
sqlite-utils enable-fts chickens.db chickens name
Options:
--fts4 Use FTS4
--fts5 Use FTS5
--tokenize TEXT Tokenizer to use, e.g. porter
--create-triggers Create triggers to update the FTS tables when the
parent table changes.
--replace Replace existing FTS configuration if it exists
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
populate-fts#
Usage: sqlite-utils populate-fts [OPTIONS] PATH TABLE COLUMN...
Re-populate full-text search for specific table and columns
Example:
sqlite-utils populate-fts chickens.db chickens name
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
rebuild-fts#
Usage: sqlite-utils rebuild-fts [OPTIONS] PATH [TABLES]...
Rebuild all or specific full-text search tables
Example:
sqlite-utils rebuild-fts chickens.db chickens
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
disable-fts#
Usage: sqlite-utils disable-fts [OPTIONS] PATH TABLE
Disable full-text search for specific table
Example:
sqlite-utils disable-fts chickens.db chickens
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
tui#
See Experimental TUI.
Usage: sqlite-utils tui [OPTIONS]
Open Textual TUI.
Options:
-h, --help Show this message and exit.
optimize#
See Optimize.
Usage: sqlite-utils optimize [OPTIONS] PATH [TABLES]...
Optimize all full-text search tables and then run VACUUM - should shrink the
database file
Example:
sqlite-utils optimize chickens.db
Options:
--no-vacuum Don't run VACUUM
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
analyze#
See Optimizing index usage with ANALYZE.
Usage: sqlite-utils analyze [OPTIONS] PATH [NAMES]...
Run ANALYZE against the whole database, or against specific named indexes and
tables
Example:
sqlite-utils analyze chickens.db
Options:
-h, --help Show this message and exit.
vacuum#
See Vacuum.
Usage: sqlite-utils vacuum [OPTIONS] PATH
Run VACUUM against the database
Example:
sqlite-utils vacuum chickens.db
Options:
-h, --help Show this message and exit.
dump#
See Dumping the database to SQL.
Usage: sqlite-utils dump [OPTIONS] PATH
Output a SQL dump of the schema and full contents of the database
Example:
sqlite-utils dump chickens.db
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
add-column#
See Adding columns.
Usage: sqlite-utils add-column [OPTIONS] PATH TABLE COL_NAME
[[integer|float|blob|text|INTEGER|FLOAT|BLOB|TEXT]]
Add a column to the specified table
Example:
sqlite-utils add-column chickens.db chickens weight float
Options:
--fk TEXT Table to reference as a foreign key
--fk-col TEXT Referenced column on that foreign key table - if
omitted will automatically use the primary key
--not-null-default TEXT Add NOT NULL DEFAULT 'TEXT' constraint
--ignore If column already exists, do nothing
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
add-foreign-key#
See Adding foreign key constraints.
Usage: sqlite-utils add-foreign-key [OPTIONS] PATH TABLE COLUMN [OTHER_TABLE]
[OTHER_COLUMN]
Add a new foreign key constraint to an existing table
Example:
sqlite-utils add-foreign-key my.db books author_id authors id
WARNING: Could corrupt your database! Back up your database file first.
Options:
--ignore If foreign key already exists, do nothing
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
add-foreign-keys#
See Adding multiple foreign keys at once.
Usage: sqlite-utils add-foreign-keys [OPTIONS] PATH [FOREIGN_KEY]...
Add multiple new foreign key constraints to a database
Example:
sqlite-utils add-foreign-keys my.db \
books author_id authors id \
authors country_id countries id
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
index-foreign-keys#
See Adding indexes for all foreign keys.
Usage: sqlite-utils index-foreign-keys [OPTIONS] PATH
Ensure every foreign key column has an index on it
Example:
sqlite-utils index-foreign-keys chickens.db
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
enable-wal#
See WAL mode.
Usage: sqlite-utils enable-wal [OPTIONS] PATH...
Enable WAL for database files
Example:
sqlite-utils enable-wal chickens.db
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
disable-wal#
Usage: sqlite-utils disable-wal [OPTIONS] PATH...
Disable WAL for database files
Example:
sqlite-utils disable-wal chickens.db
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
enable-counts#
Usage: sqlite-utils enable-counts [OPTIONS] PATH [TABLES]...
Configure triggers to update a _counts table with row counts
Example:
sqlite-utils enable-counts chickens.db
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
reset-counts#
Usage: sqlite-utils reset-counts [OPTIONS] PATH
Reset calculated counts in the _counts table
Example:
sqlite-utils reset-counts chickens.db
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
duplicate#
See Duplicating tables.
Usage: sqlite-utils duplicate [OPTIONS] PATH TABLE NEW_TABLE
Create a duplicate of this table, copying across the schema and all row data.
Options:
--ignore If table does not exist, do nothing
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
rename-table#
See Renaming a table.
Usage: sqlite-utils rename-table [OPTIONS] PATH TABLE NEW_NAME
Rename this table.
Options:
--ignore If table does not exist, do nothing
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
drop-table#
See Dropping tables.
Usage: sqlite-utils drop-table [OPTIONS] PATH TABLE
Drop the specified table
Example:
sqlite-utils drop-table chickens.db chickens
Options:
--ignore If table does not exist, do nothing
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
create-view#
See Creating views.
Usage: sqlite-utils create-view [OPTIONS] PATH VIEW SELECT
Create a view for the provided SELECT query
Example:
sqlite-utils create-view chickens.db heavy_chickens \
'select * from chickens where weight > 3'
Options:
--ignore If view already exists, do nothing
--replace If view already exists, replace it
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
drop-view#
See Dropping views.
Usage: sqlite-utils drop-view [OPTIONS] PATH VIEW
Drop the specified view
Example:
sqlite-utils drop-view chickens.db heavy_chickens
Options:
--ignore If view does not exist, do nothing
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
install#
See Installing packages.
Usage: sqlite-utils install [OPTIONS] [PACKAGES]...
Install packages from PyPI into the same environment as sqlite-utils
Options:
-U, --upgrade Upgrade packages to latest version
-e, --editable DIRECTORY Install a project in editable mode from this path
-h, --help Show this message and exit.
uninstall#
Usage: sqlite-utils uninstall [OPTIONS] PACKAGES...
Uninstall Python packages from the sqlite-utils environment
Options:
-y, --yes Don't ask for confirmation
-h, --help Show this message and exit.
add-geometry-column#
See SpatiaLite helpers.
Usage: sqlite-utils add-geometry-column [OPTIONS] DB_PATH TABLE COLUMN_NAME
Add a SpatiaLite geometry column to an existing table. Requires SpatiaLite
extension.
By default, this command will try to load the SpatiaLite extension from usual
paths. To load it from a specific path, use --load-extension.
Options:
-t, --type [POINT|LINESTRING|POLYGON|MULTIPOINT|MULTILINESTRING|MULTIPOLYGON|GEOMETRYCOLLECTION|GEOMETRY]
Specify a geometry type for this column.
[default: GEOMETRY]
--srid INTEGER Spatial Reference ID. See
https://spatialreference.org for details on
specific projections. [default: 4326]
--dimensions TEXT Coordinate dimensions. Use XYZ for three-
dimensional geometries.
--not-null Add a NOT NULL constraint.
--load-extension TEXT Path to SQLite extension, with optional
:entrypoint
-h, --help Show this message and exit.
create-spatial-index#
Usage: sqlite-utils create-spatial-index [OPTIONS] DB_PATH TABLE COLUMN_NAME
Create a spatial index on a SpatiaLite geometry column. The table and geometry
column must already exist before trying to add a spatial index.
By default, this command will try to load the SpatiaLite extension from usual
paths. To load it from a specific path, use --load-extension.
Options:
--load-extension TEXT Path to SQLite extension, with optional :entrypoint
-h, --help Show this message and exit.
plugins#
Usage: sqlite-utils plugins [OPTIONS]
List installed plugins
Options:
-h, --help Show this message and exit.