sqlite3 command does not save database after creation?

Question

I'm migrating from MySQL. I'm having trouble scripting a SQLite database creation. Similar code worked well for MySQL.

It appears the database and tables are created, but then sqlite3 command does not save changes to disk.

$ sqlite3 < testdb.sql
$ ls -Al testdb.db
-rw-r--r-- 1 jwalton jwalton 0 Mar 25 04:02 testdb.db

And:

$ cat testdb.sql

ATTACH DATABASE 'testdb.db' AS 'testdb';
PRAGMA foreign_keys = ON;

-- #####################################################

CREATE TABLE IF NOT EXISTS name_number
(
        nn_id INTEGER PRIMARY KEY AUTOINCREMENT,
        nn_name VARCHAR,
        nn_number VARCHAR
);

CREATE INDEX IF NOT EXISTS number_index ON name_number(nn_number);

-- #####################################################

CREATE TABLE IF NOT EXISTS whitelist
(
        list_id INTEGER PRIMARY KEY AUTOINCREMENT,
        nn_id INT NOT NULL,
        list_date DATETIME,
        fk1_id INTEGER,
        FOREIGN KEY(fk1_id) REFERENCES name_number(nn_id)
);

-- #####################################################

CREATE TABLE IF NOT EXISTS blacklist
(
        list_id INTEGER PRIMARY KEY AUTOINCREMENT,
        nn_id INT NOT NULL,
        list_date DATETIME,
        fk2_id INTEGER,
        FOREIGN KEY(fk2_id) REFERENCES name_number(nn_id)
);

-- #####################################################

INSERT INTO name_number (nn_name, nn_number) VALUES ("Test User", "4105551212");
INSERT INTO whitelist (nn_id, list_date) VALUES (last_insert_rowid(), datetime('now'));

INSERT INTO name_number (nn_name, nn_number) VALUES ("Test User", "8005551212");
INSERT INTO blacklist (nn_id, list_date) VALUES (last_insert_rowid(), datetime('now'));

How do I tell sqlite3 command to save the database after creation?

Answer 1

There are two databases here: the main database, which does not have a file name, and therefore is a temporary in-memory database, and the database named testdb with the file name testdb.db, which gets attached to the main database.

The SQL script does not mention testdb anywhere except in the ATTACH command, so all tables are created in the main database.

You shold drop the ATTACH command, and create the database file directly:

$ sqlite3 testdb.db < testdb.sql

Alternatively, replace the ATTACH with .open testdb.db to make that the main database. (The .open is not an SQL command and must not be terminated with a semicolon.)

The third option is to drop the ATTACH and to save the main database into the specified file using .backup testdb.db afterwards, but that only works in the command line interface, or by explicitly calling the backup API.