Search notes:

SQLite: export and import a data base with .dump

The SQL shell command .dump makes it fairly easy to export and import a database.
I try to demonstrate this with the following example.

A schema

First, we need some tables with primary and foreign key and a unique constraint. 
create table num (
  num    integer not null primary key,
  roman  text    not null
);

create table lang (
  id     text not null primary key,
  lng    text not null
);

create table num_lang (
  id_num   integer not null references num,
  id_lang  text    not null references lang,
  txt      text    not null,
  --
  unique(id_num, id_lang)
);

create index ix_num on num(roman);
Github repository about-sqlite, path: /shell/dump/export-import/create-schema.sql

Inserting a few values

The tables need to be filled. 
pragma foreign_keys = on;

insert into num      values (5, 'V'   );
insert into num      values (7, 'VII' );
insert into num      values (4, 'IV'  );
insert into num      values (2, 'II'  );
insert into num      values (9, 'IX'  );

insert into lang     values ('en', 'English');
insert into lang     values ('de', 'German' );
insert into lang     values ('fr', 'French' );

insert into num_lang values (7, 'en', 'seven' );
insert into num_lang values (7, 'de', 'sieben');
insert into num_lang values (9, 'fr', 'neuf'  );
insert into num_lang values (9, 'de', 'neue'  );
insert into num_lang values (2, 'de', 'zwei'  );
Github repository about-sqlite, path: /shell/dump/export-import/insert.sql

Export the database

The entire database is exported with the .dump command 
sqlite3 db .dump > db.dump

Import the database

The database can easily be imported because .dump exported the database as SQL statements: 
sqlite3 db < db.domp

Export file

For completness' sake, the dump file that was produces is: 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE num (
  num    integer not null primary key,
  roman  text    not null
);
INSERT INTO num VALUES(2,'II');
INSERT INTO num VALUES(4,'IV');
INSERT INTO num VALUES(5,'V');
INSERT INTO num VALUES(7,'VII');
INSERT INTO num VALUES(9,'IX');
CREATE TABLE lang (
  id     text not null primary key,
  lng    text not null
);
INSERT INTO lang VALUES('en','English');
INSERT INTO lang VALUES('de','German');
INSERT INTO lang VALUES('fr','French');
CREATE TABLE num_lang (
  id_num   integer not null references num,
  id_lang  text    not null references lang,
  txt      text    not null,
  --
  unique(id_num, id_lang)
);
INSERT INTO num_lang VALUES(7,'en','seven');
INSERT INTO num_lang VALUES(7,'de','sieben');
INSERT INTO num_lang VALUES(9,'fr','neuf');
INSERT INTO num_lang VALUES(9,'de','neue');
INSERT INTO num_lang VALUES(2,'de','zwei');
CREATE INDEX ix_num on num(roman);
COMMIT;

Index