sqlite-utils for coding agents

When a coding agent needs to work with project data from the shell, sqlite-utils lets it import files into a SQLite database and query them. SQLite stores data in a local database file. The agent can, for example, count rows in a CSV without sending the file to a hosted database.

sqlite-utils is a command-line tool and Python library for creating and working with SQLite databases. It can import CSV, JSON, and newline-delimited JSON, where each line contains a JSON value, then return SQL query results as JSON, CSV, TSV, or a formatted table. The official documentation describes it as a set of database utilities, rather than a full object-relational mapper, or ORM, which maps database rows to programming-language objects.

Install the command

Install sqlite-utils where the agent runs:

python -m pip install sqlite-utils
sqlite-utils --help

The first command installs the package from Python’s package index. The second displays the available commands and options. See the official command-line documentation for the current syntax.

These commands read and write local files, so they need no account or API key and send neither the CSV nor the query to a service. The agent still needs permission to read the source file and write the database file in its working directory.

Import and query a CSV

Suppose the agent needs to count issues by status in a project export. It can create a sample CSV, import it into a local database, and query the rows:

cat > issues.csv <<'EOF'
number,title,status
14,Fix login redirect,open
15,Update docs,closed
16,Handle empty response,open
EOF

sqlite-utils insert agent-demo.db issues issues.csv --csv

sqlite-utils query agent-demo.db \
  'SELECT status, COUNT(*) AS issue_count
   FROM issues
   GROUP BY status
   ORDER BY status;' \
  --json

The first command creates a local CSV file. The insert command creates or updates agent-demo.db and imports the CSV rows into a table named issues. To preserve an existing database, have the agent write to a separate destination.

The final command runs a SELECT query, which reads the rows, groups them by status, and counts each group. The --json option returns the result as JSON. An agent can pass that output to another command or inspect it in code. For this sample CSV, the result has two rows:

[
  {
    "status": "closed",
    "issue_count": 1
  },
  {
    "status": "open",
    "issue_count": 2
  }
]

The import writes to the database; the query shown here only reads it. Because the example does not use a preview flag, the agent should treat insert as an immediate change to the local database, not as a dry run. The CLI reference documents import options and query output formats.

A coding agent could receive a focused request such as: “Import issues.csv into a new local database, then report the number of issues in each status. Keep the original CSV unchanged, and show the SQL query and JSON results.” That instruction names the input, output, and boundary: the agent should write only the new database and use a query to produce the report.

Keep the database task in scope

Use sqlite-utils for quick imports, data transformations, indexes, and queries. It also provides a Python API for database work inside a Python program; the Python API reference documents connections, queries, and bulk inserts.

For a one-off summary, the agent can use the CLI to show its work as shell commands and SQL. If your application needs to map database rows to objects and handle broader data-access tasks, its ORM or database library may fit better. sqlite-utils focuses on creating and populating databases, not replacing those libraries.

The agent should inspect SQL before running it if the query might change data. Because query accepts SQL, JSON output alone does not make a statement safe. For other shell tools that support coding work, see Awesome CLI.