The sqlite3 command-line shell has a .dump command that writes a
database out as SQL, in UTF-8. The result is loaded back by feeding it to
sqlite3, or with .read inside the shell.
The file
The whole dump is one transaction. Each table’s CREATE TABLE is the text
stored in the database itself, so it reads as it was first written, and each row is an
INSERT of its own. Indexes and triggers follow all the tables.
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE fruit(id integer primary key autoincrement, name text);
INSERT INTO fruit VALUES(1,'apple');
INSERT INTO fruit VALUES(2,'it''s');
CREATE TABLE IF NOT EXISTS sqlite_sequence(name,seq);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('fruit',2);
CREATE INDEX fruit_name on fruit(name);
COMMIT;Because there is no DELIMITER command, a CREATE TRIGGER ends at
the semicolon after the END of its body. See
Ending statements.
A table with AUTOINCREMENT has a row in the hidden table
sqlite_sequence, and the dump puts that back as shown. Newlines and other
control characters in text are written with SQLite’s unistr() or
char() functions rather than as raw bytes, depending on the shell’s
version.
Options
The current sqlite3 shell lists these options to .dump:
| Option | Effect |
|---|---|
--data-only | Only the INSERT statements. |
--newlines | Newlines in text are written as they are. |
--nosys | System tables such as sqlite_stat1 are left out. |
--preserve-rowids | Rowid values are included. |
Arguments after the options are LIKE patterns for the tables, indexes,
triggers or views to dump.
What only SQLite does
SQLite takes the quoting of other databases, so "name",
[name] and `name` all work. A column needs no type. Table
options are limited to WITHOUT ROWID and STRICT. The binary
literal is X'…'.
The database’s own file format is described at SQLite database.