# sqlite3 — The SQLite Command-Line Tool

> Practical guide to sqlite3 — the serverless, file-based SQL database: dot-commands, import/export, backup and JSON from the command line.

Source: https://www.jpkc.com/db/en/cheatsheets/build-languages/sqlite3/

<!-- PROSE:intro -->
sqlite3 is the official command-line tool for SQLite – a serverless, file-based SQL database that stores an entire database in a single file. You need no server, no configuration and no running service: you simply open a `.db` file and get to work. Alongside plain SQL, the tool offers so-called dot-commands like `.tables`, `.schema` and `.mode` that let you inspect the database, control the output format and import or export data. This guide walks you through the commands you reach for in development, testing and embedded use.
<!-- PROSE:intro:end -->

## Open & Create

`sqlite3 <database>` — Open a database (creates if not exists).

```bash
sqlite3 myapp.db
```

`sqlite3 :memory:` — Create an in-memory database.

```bash
sqlite3 :memory:
```

`sqlite3 <database> '<query>'` — Execute a query and exit.

```bash
sqlite3 myapp.db 'SELECT count(*) FROM users;'
```

`sqlite3 <database> < <file>` — Execute SQL from a file.

```bash
sqlite3 myapp.db < schema.sql
```

## Dot-Commands — Info

`.databases` — List attached databases and their file paths.

```bash
.databases
```

`.tables` — List all tables in the current database.

```bash
.tables
```

`.tables <pattern>` — List tables matching a pattern.

```bash
.tables user%
```

`.schema <table>` — Show the CREATE statement for a table.

```bash
.schema users
```

`.schema` — Show CREATE statements for all tables.

```bash
.schema
```

`.indexes <table>` — Show indexes for a table.

```bash
.indexes users
```

## Dot-Commands — Output

`.mode <mode>` — Set output mode (column, csv, json, table, line, etc.).

```bash
.mode table
```

`.headers on` — Show column headers in output.

```bash
.headers on
```

`.output <file>` — Send query output to a file.

```bash
.output results.txt
```

`.output` — Reset output back to stdout.

```bash
.output
```

`.once <file>` — Send next query output to a file (one-time).

```bash
.once export.csv
```

## Import & Export

Import CSV data into a table.

```bash
.mode csv
.import <file> <table>
```

Export a table to CSV.

```bash
.mode csv
.headers on
.output <file>
SELECT * FROM <table>;
```

`.dump` — Export entire database as SQL statements.

```bash
.output backup.sql
.dump
```

`.dump <table>` — Export a specific table as SQL.

```bash
.dump users
```

`.read <file>` — Execute SQL commands from a file.

```bash
.read migrations/001.sql
```

## Backup & Utility

`.backup <file>` — Create a backup of the database.

```bash
.backup /tmp/myapp-backup.db
```

`.clone <file>` — Clone the current database to a new file.

```bash
.clone myapp-copy.db
```

`VACUUM;` — Compact the database file (reclaim free space).

```bash
VACUUM;
```

`PRAGMA integrity_check;` — Check database integrity.

```bash
PRAGMA integrity_check;
```

`PRAGMA table_info(<table>);` — Show column details for a table.

```bash
PRAGMA table_info(users);
```

`.quit` — Exit sqlite3.

```bash
.quit
```

## Common Patterns

`sqlite3 <db> -header -csv '<query>'` — One-liner CSV export.

```bash
sqlite3 myapp.db -header -csv 'SELECT * FROM users' > users.csv
```

`sqlite3 <db> -json '<query>'` — One-liner JSON export.

```bash
sqlite3 myapp.db -json 'SELECT * FROM users'
```

`sqlite3 <db> '.dump' | sqlite3 <newdb>` — Clone a database via dump and restore.

```bash
sqlite3 prod.db '.dump' | sqlite3 dev.db
```

`sqlite3 <db> 'SELECT count(*) FROM <table>;'` — Quick row count from command line.

```bash
sqlite3 myapp.db 'SELECT count(*) FROM users;'
```

<!-- PROSE:outro -->
## Conclusion

sqlite3 packs a complete SQL database into a single file – and bundles the command line right along with it – making it ideal for development, testing, small tools and embedded use. The dot-commands handle the routine work: use `.schema` and `.tables` to get your bearings, `.mode` and `.output` to control export formats, and `.dump` or `.backup` to capture the whole dataset. Be careful with writing commands: a `DROP` or a `DELETE` without a `WHERE` is immediate and irreversible. For consistent backups, use `.backup` rather than copying an open file, and mind the file permissions of the `.db` file – anyone who can read it reads all of your data.

## Further Reading

- [Command Line Shell For SQLite](https://sqlite.org/cli.html) – official documentation for the sqlite3 command-line shell
- [SQLite – home page](https://sqlite.org/) – project site with documentation, downloads and format specification
<!-- PROSE:outro:end -->

## Related Commands

- [artisan](https://www.jpkc.com/db/en/cheatsheets/build-languages/artisan/) – command-line tool of the Laravel framework
- [cargo](https://www.jpkc.com/db/en/cheatsheets/build-languages/cargo/) – package manager and build tool for Rust
- [composer](https://www.jpkc.com/db/en/cheatsheets/build-languages/composer/) – dependency manager for PHP projects