SQLite § io

The shell and C API

This page documents the two principal interfaces to SQLite: the sqlite3 command-line shell and the C API. The shell is the canonical interactive client, useful for ad-hoc queries, schema inspection, import and export, and benchmarking. The C API is the engine’s programmatic interface, the lowest layer through which every application — including the high-level language bindings — reads and writes the database. Understanding both gives a working programmer the full surface.

The sqlite3 shell

The sqlite3 shell is a small, statically linked executable that ships with the SQLite distribution. It is invoked with the database file as an argument:

sqlite3 example.db

The shell opens the database (creating it if absent), enters interactive mode, and accepts SQL statements. A statement ends with a semicolon; the shell executes it and prints the result. Multi-line statements are accumulated until the semicolon is read. The prompts sqlite> and ...> indicate top-level and continuation respectively.

SQLite version 3.46.0 2026-04-15 …
Enter ".help" for usage hints.
sqlite> SELECT count(*) FROM customer;
12
sqlite> CREATE TABLE event (
   ...> id INTEGER PRIMARY KEY,
   ...> payload TEXT
   ...> );
sqlite> .quit

A non-interactive form takes the SQL from stdin or from a file:

echo 'SELECT count(*) FROM customer;' | sqlite3 example.db
sqlite3 example.db < migrations/001-initial.sql
sqlite3 example.db 'SELECT count(*) FROM customer'

The shell’s exit status reflects the result of the last statement; a non-zero exit indicates an error.

Dot commands

Dot commands are shell directives, not SQL. They begin with a period at the start of the line and are processed by the shell rather than the engine. The principal dot commands:

Command	Purpose
`.help`	Show the dot-command reference.
`.tables [pattern]`	List tables (with optional glob pattern).
`.schema [table]`	Show the `CREATE TABLE` statement.
`.indexes [table]`	List indexes.
`.databases`	List attached databases.
`.headers on/off`	Toggle column-header output.
`.mode <mode>`	Set output mode (`column`, `csv`, `tabs`, `json`, `markdown`, `table`, `box`, `quote`, `insert`, `tcl`, `line`, `list`).
`.separator <s>`	Column separator (default `
`.read <file>`	Execute SQL from a file.
`.dump [table]`	Output the schema and data as SQL.
`.import <file> <table>`	Import a CSV or other-formatted file.
`.output <file>`	Redirect output to a file (or `stdout` to revert).
`.timer on/off`	Show wall-clock and CPU time per statement.
`.eqp on/off`	Enable `EXPLAIN QUERY PLAN` for every statement.
`.changes on/off`	Show the count of rows changed by `INSERT`/`UPDATE`/`DELETE`.
`.load <library>`	Load an extension.
`.open <file>`	Switch to a different database file.
`.shell <cmd>`	Run a shell command (and `.system <cmd>`).
`.quit`, `.exit`	Exit the shell.

sqlite> .mode column
sqlite> .headers on
sqlite> SELECT id, name FROM customer LIMIT 3;
id  name
--  ----
1   Ada
2   Donald
3   Grace

sqlite> .mode json
sqlite> SELECT id, name FROM customer LIMIT 1;
[{"id":1,"name":"Ada"}]

sqlite> .timer on
sqlite> SELECT count(*) FROM event;
1234567
Run Time: real 0.045 user 0.034000 sys 0.011000

The .mode markdown and .mode table modes produce nicely formatted output suitable for documentation; .mode csv is the conventional export format. The full set is at sqlite.org/cli.html.

Importing and exporting

The shell is a convenient mechanism for moving data between SQLite and CSV:

# Export
sqlite> .headers on
sqlite> .mode csv
sqlite> .output customers.csv
sqlite> SELECT * FROM customer;
sqlite> .output stdout

# Import
sqlite> .mode csv
sqlite> .import customers.csv customer

The .import command creates the table if it does not exist (using the first row as column names if .headers on) or appends to an existing table.

For a full database export — a self-contained SQL script that recreates the schema and data — the .dump command produces a complete reproduction:

sqlite3 example.db .dump > backup.sql
sqlite3 restored.db < backup.sql

The output is a sequence of CREATE TABLE and INSERT statements wrapped in a transaction; restoring is a matter of feeding the script to a new database. The .dump form is the canonical text-based backup.

For a binary backup — a copy of the database file — VACUUM INTO is the recommended mechanism (see Schemas and ATTACH). It produces a compacted, consistent snapshot that opens identically to the original.

Configuration files and pragmas

The shell reads ~/.sqliterc (or the file pointed to by $SQLITE_HISTORY) at startup and executes its contents. The conventional content sets pleasant defaults:

.mode column
.headers on
.timer on
PRAGMA foreign_keys = ON;

Every connection then opens with the configured pragmas and presentation. The shell also reads command-line flags -bail, -readonly, -batch, -init <file>, and others; the manual page enumerates them.

The C API

The C API is the engine’s programmatic surface. The principal entry points fall into three groups: connection management, statement execution, and result inspection.

Opening a connection

sqlite3 *db;
int rc = sqlite3_open_v2("example.db", &db,
                         SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
                         NULL);
if (rc != SQLITE_OK) {
    fprintf(stderr, "open failed: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return 1;
}

sqlite3_open_v2 takes a flag set: SQLITE_OPEN_READONLY, SQLITE_OPEN_READWRITE, SQLITE_OPEN_CREATE (auto-create), SQLITE_OPEN_URI (interpret the filename as a URI), SQLITE_OPEN_FULLMUTEX (full thread safety), and others. The simpler sqlite3_open defaults to READWRITE | CREATE and is the historical entry point.

sqlite3_close releases the connection. The matching call sqlite3_close_v2 accepts unfinalised statements; it is the recommended form for application code.

Executing a statement

The conventional pattern: prepare, bind, step, finalise.

sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db,
    "INSERT INTO customer (name, email) VALUES (?, ?)",
    -1, &stmt, NULL);
if (rc != SQLITE_OK) { /* … */ }

sqlite3_bind_text(stmt, 1, "Ada Lovelace", -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 2, "ada@example.com", -1, SQLITE_STATIC);

rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) { /* … */ }

sqlite3_finalize(stmt);

The sqlite3_prepare_v2 call compiles the SQL into a reusable virtual-machine program. The sqlite3_bind_* calls supply parameter values. The sqlite3_step call runs the program; for a statement that returns rows (a SELECT), each step returns SQLITE_ROW until the rows are exhausted. The sqlite3_finalize call releases the prepared statement.

The bind functions take a 1-based parameter index and a value:

Function	Parameter type
`sqlite3_bind_int(stmt, i, v)`	32-bit integer
`sqlite3_bind_int64(stmt, i, v)`	64-bit integer
`sqlite3_bind_double(stmt, i, v)`	Double-precision float
`sqlite3_bind_text(stmt, i, s, n, free)`	UTF-8 string
`sqlite3_bind_text16(stmt, i, s, n, free)`	UTF-16 string
`sqlite3_bind_blob(stmt, i, b, n, free)`	BLOB
`sqlite3_bind_null(stmt, i)`	NULL
`sqlite3_bind_zeroblob(stmt, i, n)`	A zero-filled BLOB of n bytes

The free parameter is SQLITE_STATIC if the buffer outlives the statement, SQLITE_TRANSIENT if SQLite should make a private copy, or a function pointer that SQLite calls to release the buffer.

Reading rows

sqlite3_prepare_v2(db, "SELECT id, name FROM customer", -1, &stmt, NULL);
while (sqlite3_step(stmt) == SQLITE_ROW) {
    int  id   = sqlite3_column_int(stmt, 0);
    const unsigned char *name = sqlite3_column_text(stmt, 1);
    printf("%d %s\n", id, name);
}
sqlite3_finalize(stmt);

The column functions take a 0-based index and return the value:

Function	Returns
`sqlite3_column_int`	32-bit integer
`sqlite3_column_int64`	64-bit integer
`sqlite3_column_double`	Double
`sqlite3_column_text`	UTF-8 text (lifetime: until next step or finalize)
`sqlite3_column_blob`	BLOB
`sqlite3_column_bytes`	Length of text or BLOB
`sqlite3_column_type`	One of `SQLITE_INTEGER`, `SQLITE_FLOAT`, `SQLITE_TEXT`, `SQLITE_BLOB`, `SQLITE_NULL`

The buffers returned by sqlite3_column_text and sqlite3_column_blob are owned by the engine and are valid only until the next sqlite3_step or sqlite3_finalize. A caller that needs the value after the statement must copy it.

Convenience: sqlite3_exec

For one-off statements that do not return rows or where the result is processed by a callback, sqlite3_exec is a convenient shortcut:

char *err = NULL;
rc = sqlite3_exec(db,
    "BEGIN; INSERT INTO event (payload) VALUES ('x'); COMMIT;",
    NULL, NULL, &err);
if (rc != SQLITE_OK) {
    fprintf(stderr, "exec failed: %s\n", err);
    sqlite3_free(err);
}

sqlite3_exec accepts multiple semicolon-separated statements and runs each in turn. It is convenient for schema scripts and one-shot maintenance; for application queries that take parameters, the prepare/bind/step pattern is preferred.

Errors

Every API call returns a status code. The principal codes:

Code	Meaning
`SQLITE_OK`	Success.
`SQLITE_ROW`	A row is available (from `sqlite3_step`).
`SQLITE_DONE`	Statement complete (from `sqlite3_step`).
`SQLITE_ERROR`	Generic error; check `sqlite3_errmsg`.
`SQLITE_BUSY`	Lock contention.
`SQLITE_LOCKED`	Self-deadlock (same connection, multiple statements).
`SQLITE_CONSTRAINT`	Constraint violation.
`SQLITE_MISUSE`	API misuse.
`SQLITE_NOTFOUND`	Object (table, index, function) not found.
`SQLITE_IOERR`	I/O error.
`SQLITE_CORRUPT`	Database file is corrupted.

sqlite3_errmsg(db) returns a human-readable error string for the most recent error on the connection.

Language bindings

Every widely used programming language has a binding to the SQLite C API. The bindings layer a high-level interface — typically a connection and cursor abstraction — over the raw API. The principal bindings:

Python (sqlite3, in standard library)

import sqlite3

conn = sqlite3.connect('example.db')
conn.row_factory = sqlite3.Row
conn.execute('PRAGMA foreign_keys = ON')

with conn:                                      # implicit BEGIN/COMMIT
    cur = conn.execute(
        'INSERT INTO customer (name, email) VALUES (?, ?) RETURNING id',
        ('Ada Lovelace', 'ada@example.com'))
    new_id = cur.fetchone()[0]

for row in conn.execute('SELECT id, name FROM customer'):
    print(row['id'], row['name'])

conn.close()

Node.js (better-sqlite3)

const Database = require('better-sqlite3');
const db = new Database('example.db');
db.pragma('foreign_keys = ON');

const insert = db.prepare(
    'INSERT INTO customer (name, email) VALUES (?, ?)');
const result = insert.run('Ada Lovelace', 'ada@example.com');
console.log(result.lastInsertRowid);

const rows = db.prepare('SELECT id, name FROM customer').all();
for (const row of rows) console.log(row.id, row.name);

better-sqlite3 is the recommended Node binding; it is synchronous (a deliberate design choice that matches SQLite’s blocking API) and substantially faster than the asynchronous node-sqlite3.

Go (mattn/go-sqlite3)

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
)

db, _ := sql.Open("sqlite3", "example.db?_foreign_keys=on")
defer db.Close()

result, _ := db.Exec(
    `INSERT INTO customer (name, email) VALUES (?, ?)`,
    "Ada Lovelace", "ada@example.com")
id, _ := result.LastInsertId()

rows, _ := db.Query(`SELECT id, name FROM customer`)
defer rows.Close()
for rows.Next() {
    var id int
    var name string
    rows.Scan(&id, &name)
    fmt.Println(id, name)
}

mattn/go-sqlite3 uses cgo. The pure-Go alternative modernc.org/sqlite is a transpiled C-to-Go port; it is slower but builds without cgo, which is convenient for cross-compilation.

Rust (rusqlite)

use rusqlite::{Connection, params};

let conn = Connection::open("example.db")?;
conn.execute_batch("PRAGMA foreign_keys = ON;")?;

conn.execute(
    "INSERT INTO customer (name, email) VALUES (?1, ?2)",
    params!["Ada Lovelace", "ada@example.com"])?;

let mut stmt = conn.prepare("SELECT id, name FROM customer")?;
let rows = stmt.query_map([], |row| {
    Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
})?;
for row in rows {
    let (id, name) = row?;
    println!("{} {}", id, name);
}

rusqlite is the conventional Rust binding; the alternative sqlx provides a higher-level query-builder surface and async support.

Other bindings

Language	Binding
Java	`sqlite-jdbc` (the canonical JDBC driver)
Ruby	`sqlite3` gem
C# / .NET	`Microsoft.Data.Sqlite` (or `System.Data.SQLite`)
PHP	PDO with the `sqlite` driver
Swift	`sqlite3` (in the standard library since iOS 8)
Kotlin	The Java JDBC driver, or platform-specific bindings on Android
Lua	`lsqlite3` (LuaRocks)

The bindings expose substantially the same surface — connection, prepared statement, parameter binding, row iteration — over the underlying C API. A programmer who has learned SQLite through one binding will recognise the others.

File format

The on-disk database file is a fixed format documented at sqlite.org/fileformat2.html. The file consists of fixed-size pages (default 4096 bytes), the first of which is the header containing the format version, page size, encoding, and pointers to root pages of system tables. Subsequent pages hold table B-trees, index B-trees, free pages, and overflow chains for large rows.

The format has been backward-compatible since version 3.0 (2004): a database file written by SQLite 3.0 opens unchanged in version 3.46, and a database written by 3.46 (using only features compatible with the older version) opens in 3.0. The format is also forward-compatible in a defined sense: a database that uses a newer feature opens in older versions, with the newer feature recognised but not used. This is one of the project’s notable engineering achievements and is the basis of SQLite’s role as an application file format: a database file written today will be readable in twenty years’ time without modification.