Databases with SQLite

Introduction

Data stored in a JavaScript array vanishes when the server restarts. A database keeps it. SQLite is a small, reliable database stored in a single file, ideal for learning and for many real applications. Recent Node versions include a SQLite module, but it still prints an experimental warning, so for large production systems many teams use a package such as better-sqlite3 or a full database server. The ideas are the same. If you took our SQL courses, the queries will be familiar.

Open a database and create a table

":memory:" makes a temporary database for experiments. Give a file name, such as "app.db", to keep data between runs. This module is synchronous, which keeps simple code simple but blocks while it works, so keep queries quick.

Insert and query

Real output:

[
  [Object: null prototype] { id: 1, title: 'Learn Node', done: 0 },
  [Object: null prototype] { id: 2, title: 'Build an API', done: 0 }
]
[Object: null prototype] { id: 2, title: 'Build an API', done: 0 }

• prepare compiles a statement. run executes it, all returns every row and get returns the first row.
• The ? is a placeholder, and you pass the value separately.

SQL injection

Never build SQL by joining strings with user input:

A value such as ' OR '1'='1 changes the meaning of the query. Always use placeholders, as above. Validate input too, but placeholders are the real defence.

Fitting it into an API

A typical GET /todos/:id handler with a database:

Prepare statements once, outside the handler, and reuse them.

Good habits

• Design the schema with constraints such as NOT NULL, UNIQUE and foreign keys.
• Use transactions for changes that must succeed or fail together.
• Keep the database file out of Git and out of public folders, and back it up.
• Store migrations (scripts that change the schema over time) in the project.
• For many users writing at once, consider a server database such as PostgreSQL.
• Convert types carefully. SQLite stores booleans as 0 and 1.

Try it yourself

Replace the array in your to-do API with a SQLite table. Implement list, get, create and delete with prepared statements, and test that data survives a server restart when you use a file database.

Summary

• SQLite stores data in one file, and Node can use it directly, though the built-in module is still experimental.
• Use prepared statements with placeholders, never string-built SQL.
• Prepare statements once, design the schema carefully and back up the database.