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] command [arguments]

Common options:
  `--version`             Show the version and exit.
  `--help`                 Show this message and exit.
Example usage:
  `sqlite-utils insert my_database.db my_table data.json --csv`
  `sqlite-utils my_database.db 'select * from my_table where id = ?' 1`

`insert`
    Insert data into a table from various formats (CSV, JSON).

`upsert`
    Insert or update data into a table based on a primary key.

`update`
    Update records in a table based on criteria.

`delete`
    Delete records from a table.

`create-table`
    Create a new table with specified columns and types.

`drop-table`
    Drop (delete) one or more tables.

`dump`
    Dump the entire database schema and data as SQL.

`schema`
    Display the schema of a database or specific tables.

`tables`
    List all tables in a database.

`rows`
    Display rows from a table, with optional filtering and ordering.

`columns`
    Display columns for a table.

`indexes`
    Display indexes for a table.

`triggers`
    Display triggers for a table.

`optimize`
    Optimize a database (e.g., re-index tables, run `VACUUM`).

`vacuum`
    Run the `VACUUM` command to reclaim space.

`convert`
    Convert columns to a different type or value using a Python function.

`transform`
    Apply various transformations to table data or schema.

`extract`
    Extract specific values or data based on a pattern from a column.

`enable-fts`
    Enable full-text search (FTS) on a table or specific columns.

`disable-fts`
    Disable full-text search (FTS) on a table.

`search`
    Perform full-text search queries against FTS-enabled tables.

`fkeys`
    Display foreign keys in the database.

`add-foreign-key`
    Add a foreign key constraint to a table.

`add-column`
    Add a new column to a table.

`rename-column`
    Rename a column in a table.

`alter-table`
    Apply multiple schema alterations to a table in one go.

`memory`
    Analyze memory usage of the database.

`init-plugin`
    Initialize a new plugin directory for custom commands.

sqlite-utils is a powerful and versatile command-line tool designed to simplify interactions with SQLite databases. Developed by Simon Willison, it acts as a wrapper around the `sqlite3` library, providing a more user-friendly and feature-rich interface for common database operations. It excels at tasks such as importing data from various formats (CSV, JSON), querying databases, exporting data, managing tables, columns, and indexes, enabling full-text search (FTS), and performing data transformations. Its intuitive subcommands and Pythonic approach make it an invaluable tool for developers, data scientists, and anyone working with SQLite, enabling quick prototyping, data exploration, and automated database management scripts. It's particularly popular for its ease of use in preparing data for Datasette, another tool by Willison for exploring data with a web interface.

Python Dependency: `sqlite-utils` requires Python to be installed on the system, as it is a Python package. It's not a standalone executable like some native Linux commands.
Installation: It must be installed via `pip` (e.g., `pip install sqlite-utils`) before it can be used, unlike many core Linux utilities which are pre-installed.
Database File Path: Most subcommands require specifying the path to the SQLite database file as an argument.
Performance for Very Large Datasets: While generally efficient, operations on extremely large databases (hundreds of gigabytes or billions of rows) might still benefit from direct `sqlite3` CLI or more specialized database tools for optimal performance.

`sqlite-utils` is often used in conjunction with Datasette. It helps prepare, load, and manage data in SQLite databases, which can then be easily explored and published using Datasette's web interface. This synergy makes for a powerful data exploration and sharing workflow.

`sqlite-utils` supports plugins, allowing developers to extend its functionality with custom commands or data transformations. This extensibility makes it adaptable to specific data processing needs not covered by its built-in features.

When inserting data from formats like CSV or JSON, `sqlite-utils` intelligently attempts to guess appropriate SQL data types for columns, simplifying the schema creation process. This can be particularly helpful for rapid prototyping and data loading.

`sqlite-utils` was created by Simon Willison, a renowned British software developer and co-creator of the Django web framework. It was first released in 2018 as an open-source project, primarily developed to complement his other popular tool, Datasette, which provides a web interface for exploring and publishing data from SQLite databases. Willison designed `sqlite-utils` to be a highly practical, opinionated tool for common SQLite data wrangling tasks, filling a gap between the low-level `sqlite3` command-line interface and full-fledged database management systems. Its development continues actively on GitHub, with a strong emphasis on user-friendliness, robust data handling, and integration with the Python ecosystem. Its adoption has grown significantly within the data science and development communities for its efficiency in preparing and manipulating data for various analytical and application purposes.

sqlite3(1), jq(1), csvcut(1)