sqlite-utils

Create a database

$ sqlite-utils create-database [path/to/database.db]

$ sqlite-utils create-table [path/to/database.db] [table_name] [id integer name text height float photo blob --pk id]

$ sqlite-utils tables [path/to/database.db]

$ [echo '[ {"id": 1, "name": "Linus Torvalds"}, {"id": 2, "name": "Steve Wozniak"}, {"id": 3, "name": "Tony Hoare"} ]'] | sqlite-utils upsert [path/to/database.db] [table_name] - [--pk id]

$ sqlite-utils rows [path/to/database.db] [table_name]

$ sqlite-utils query [path/to/database.db] "[delete from table_name where name = 'Tony Hoare']"

$ sqlite-utils drop-table [path/to/database.db] [table_name]

$ sqlite-utils [[-h|--help]]

sqlite-utils [options] database [arguments...]

attach
    Attach a database to the current connection using an alias.

autocomplete
    Output shell autocomplete code.

backup
    Backup a database to a file.

calc
    Calculate a value.

close-vulnerability
    Close a vulnerability by adding a record to the close_vulnerabilities table.

convert
    Convert values in a column to a different type.

create-database
    Create an empty SQLite database file.

create-index
    Create an index on a table.

create-table
    Create a table in the database.

delete
    Delete a row from a table.

delete-column
    Delete a column from a table.

disable-wal
    Disable Write-Ahead Logging (WAL) mode.

docs
    Open the sqlite-utils documentation in your browser.

dump
    Dump the database as SQL.

enable-wal
    Enable Write-Ahead Logging (WAL) mode.

explain-query-plan
    Explain the query plan for a SQL query.

extract
    Extract columns into a new table, creating a foreign key relationship.

fts4-tokenize
    Tokenize a column using the FTS4 tokenizer.

fts4-setup
    Set up a table for FTS4 full-text search.

fts5-tokenize
    Tokenize a column using the FTS5 tokenizer.

fts5-setup
    Set up a table for FTS5 full-text search.

index-foreign-keys
    Create indexes on all foreign key columns.

indexes
    Show indexes on a table.

insert
    Insert rows into a table.

inspect
    Inspect the database schema.

json-cols
    Create virtual JSON columns.

keys
    List the primary keys for a table.

optimize
    Optimize the database file.

output
    Output the results of a query to a file.

package
    Convert a database to an extension.

query
    Execute a SQL query.

rename-column
    Rename a column in a table.

rewrite
    Rewrite a table using the optimal data types.

rows
    Show rows from a table.

schema
    Show the schema for a table.

serve
    Serve the database as a website.

shell
    Open a SQLite shell connected to the database.

tables
    List the tables in the database.

vacuum
    Vacuum the database to reclaim space.

version
    Show the sqlite-utils version.

views
    List the views in the database.

sqlite-utils is a Python command-line tool and library for manipulating SQLite databases. It provides a set of commands for creating, inspecting, and modifying SQLite databases from the command line.

It is designed to be easy to use and to simplify common database tasks, such as importing data from CSV or JSON files, creating indexes, and running SQL queries. It works on top of the standard `sqlite3` library but improves the user experience by providing tools to make the interactions with SQLite databases easier. The utility also offers a Python API to interact with databases within Python scripts.

sqlite-utils focuses on providing a streamlined workflow for common database operations, making it a valuable tool for developers and data enthusiasts who need to work with SQLite databases frequently.

Requires Python to be installed and `sqlite-utils` package to be installed using pip.

sqlite-utils provides excellent support for importing data from various formats, including CSV and JSON. The `insert` command can be used with `--csv` or `--json-cols` options to efficiently import data into your SQLite database. If the table doesn't exist, it can automatically create the table with the schema derived from the input data.

sqlite-utils was created by Simon Willison to simplify working with SQLite databases. It has evolved over time to include many helpful utilities for common database tasks. The project has a large and active community which contributes to the project

sqlite3(1)