SQLite: build a local data store

SQLite is an embedded SQL database: data lives in a single local file. It is useful for prototypes, utilities and applications that do not need a separate database server.

1. Create a database

Open a terminal:

sqlite3 notes.db

At the SQLite prompt, create a table:

CREATE TABLE notes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  completed INTEGER NOT NULL DEFAULT 0
);

2. Insert and read data

INSERT INTO notes (title) VALUES ('Learn Mojara');
SELECT id, title, completed FROM notes;

Expected row: 1|Learn Mojara|0. Exit SQLite using .quit.

3. Update a record

UPDATE notes SET completed = 1 WHERE id = 1;
SELECT * FROM notes WHERE id = 1;

Now completed is 1.

4. Use SQLite in a Mojara application

The SQLite connection belongs in your application's persistence layer, not in each route handler. The route calls a database service to insert or fetch notes, then responds with JSON. Parameterized SQL should be used for user-supplied values:

INSERT INTO notes (title) VALUES (?);
SELECT id, title FROM notes WHERE id = ?;

These ? placeholders are bound to values by the SQLite driver; never concatenate a submitted title into SQL.

5. Transactions

BEGIN;
INSERT INTO notes (title) VALUES ('Second note');
ROLLBACK;

A subsequent SELECT shows the new note was not committed. Replace ROLLBACK with COMMIT to save changes. See Migrations to evolve the schema.