Database

Summary: StartER relies on a native SQLite database for persistent storage. This page details its configuration, usage, and best practices for extending it to suit your needs.

Why SQLite?

The choice of SQLite for StartER is based on several criteria that simplify development:

• a "Zero-Config" approach: since Node.js 22.5.0, SQLite is integrated directly via the node:sqlite module and there is no longer a need to install an external database server or heavy dependencies.
• the entire database resides in a simple local file.
• by using an in-memory instance (:memory:) for API tests, StartER guarantees perfect isolation and near-instant execution.

Note

Up to Node.js v25.7.0, the node:sqlite module is marked as experimental. A warning is displayed in the terminal at server startup: it is perfectly normal and does not prevent the framework from working correctly.

Implementation in StartER

The "Zero-Config" approach

Unlike traditional databases (like MySQL or PostgreSQL), SQLite requires no network configuration. Therefore, you will not find any username, password, or server address in the .env file.

The database instance

The src/database/index.ts file configures and exposes the database access:

import path from "node:path";
import { DatabaseSync } from "node:sqlite";

const dbPath = path.join(import.meta.dirname, "../../data/sqlite/database.sqlite");

// Open the SQLite database file in synchronous mode
const database = new DatabaseSync(dbPath);

// Log the database availability at startup
console.info(`Using database ${path.normalize(dbPath)}`);

export default database;

By default, data is stored in data/sqlite/database.sqlite. This folder is ignored by Git (.gitignore) to avoid publishing your development data.

The synchronous SQLite API

The node:sqlite module offers a synchronous API. Since SQLite reads and writes directly to a local file on your machine, there is no network latency. Node.js therefore does not need to handle these queries asynchronously with async/await.

Here is how to use the database instance:

1. Prepare the query with database.prepare() and ? markers to protect against SQL injections.
2. Execute the query with:
   • .run(...) for insert, update, delete. This method returns an object containing the number of modified rows (changes) and the inserted ID (lastInsertRowid).
   • .get(...) to retrieve a single row (e.g., select ... limit 1).
   • .all(...) to retrieve multiple rows (e.g., select).

Examples:

import database from "../../../database";

// 1. Read multiple rows (synchronous)
const queryAll = database.prepare("select * from item where user_id = ?");
const items = queryAll.all(1); // Returns an array of objects

// 2. Read a single row (synchronous)
const queryOne = database.prepare("select * from user where id = ?");
const user = queryOne.get(42); // Returns an object or undefined

// 3. Insert a row (synchronous)
const insertQuery = database.prepare("insert into item (title, user_id) values (?, ?)");
const result = insertQuery.run("New title", 42);
console.log("New ID:", result.lastInsertRowid);

Build the database

Schema

The database schema is defined in the src/database/schema.sql file.

Here is an example of a minimal schema provided with StartER:

create table user (
  id integer primary key not null,
  email varchar(255) not null unique,
  name varchar(255) not null,
  created_at datetime default current_timestamp,
  deleted_at datetime default null
);

create table magic_link_token (
  user_id integer primary key not null,
  token_hash char(64) not null,
  expires_at datetime not null,
  consumed_at datetime default null,
  foreign key(user_id) references user(id) on delete cascade
);

create table item (
  id integer primary key not null,
  title varchar(255) not null,
  created_at datetime default current_timestamp,
  deleted_at datetime default null,
  user_id integer not null,
  foreign key(user_id) references user(id) on delete cascade
);

This schema defines three tables (user, magic_link_token and item) with their relationships. Using integer primary key allows SQLite to handle auto-incrementation.

Seeder

The sample database content can be defined in the src/database/seeder.sql file.

Here is an example of a minimal seeder provided with StartER:

insert into user(id, email, name)
values
  (1, "jdoe@mail.com", "J. Doe");

insert into item(id, title, user_id)
values
  (1, "Stuff", 1),
  (2, "Doodads", 1);

This seeder inserts some example records into the user and item tables.

Synchronization

To apply the schema to your local SQLite file, use the command:

npm run database:schema:load

To use the seeder:

npm run database:seeder:load

You can do both in one command with:

npm run database:sync

Caution

Warning: These commands are useful during development to easily reset your database, but all the previous data will be lost. They prompt for confirmation before execution, unless the --no-interaction or -n argument is passed (useful for CI/CD environments).

Exploring the database

Since the database is a simple file (data/sqlite/database.sqlite), you can easily explore it with dedicated tools, without needing to run a server or external container.

We recommend:

• If you use VS Code: the SQLite Viewer extension allows you to open and view the .sqlite file directly in your editor.
• Desktop application: the free software DB Browser for SQLite for viewing, editing, and executing SQL queries on your file.

Best practices and use cases

We recommend including the following fields in each table, where relevant:

• id: unique identifier (via integer primary key)
• created_at: creation date and time
• deleted_at: deletion date and time (for soft delete)

These conventions allow to ensure the consistency and traceability of your data throughout the application.

Use foreign keys with referential integrity constraints to maintain data consistency:

foreign key(user_id) references user(id) on delete cascade

See also

• The Repository pattern
• Creating your first resource
• Many-to-many and transactions