Next, we need to make sure that our database is configured. We're using this SQL table definition for the schema (save this as models/schema-sqlite3.sql):

CREATE TABLE IF NOT EXISTS notes (
    notekey VARCHAR(255),
    title VARCHAR(255),
    body TEXT
);

How do we initialize this schema before writing some code? One way is to ensure that the sqlite3 package is installed through your operating system package management system, such as using apt-get on Ubuntu/Debian, and MacPorts on macOS. Once it's installed, you can run the following command:

$ sqlite3 chap07.sqlite3 
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
sqlite> CREATE TABLE IF NOT EXISTS notes (
   ...> notekey VARCHAR(255),
   ...> title VARCHAR(255),
   ...> body TEXT
   ...> );
sqlite> .schema notes
CREATE TABLE notes (
    notekey VARCHAR(255),
    title VARCHAR(255),
    body TEXT
);
sqlite> ^D
$ ls -l chap07.sqlite3 
-rwx------ 1 david staff 8192 Jan 14 20:40 chap07.sqlite3  

While we can do that, the Twelve Factor application model says we must automate any administrative processes in this way. To that end, we should instead write a little script to run an SQL operation on SQLite3 and use that to initialize the database.

Fortunately, the sqlite3 command offers us a way to do this. Add the following to the scripts section of package.json:

"sqlite3-setup": "sqlite3 chap07.sqlite3 --init models/schema-sqlite3.sql", 

Run the setup script:

$ npm run sqlite3-setup
    
> notes@0.0.0 sqlite3-setup /Users/david/chap07/notes
> sqlite3 chap07.sqlite3 --init models/schema-sqlite3.sql 
    
-- Loading resources from models/schema-sqlite3.sql
    
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
sqlite> .schema notes
CREATE TABLE notes (
    notekey VARCHAR(255),
    title   VARCHAR(255),
    body    TEXT
);
sqlite> ^D

We could have written a small Node.js script to do this, and it's easy to do so. However, by using the tools provided by the package, we have less code to maintain in our own project.