Tutorial – Setting up the database

Goal for this step 🏁: Have a working SQLite database with a database schema for Dossier.

The quickest way to get started with a database is to use SQLite. Let's go with better-sqlite3 as our library to talk to SQLite. In order to use it with Dossier, we use the library @dossierhq/better-sqlite3. And since we want to log to the console, we use @dossierhq/core.

npm install better-sqlite3 @dossierhq/better-sqlite3 @dossierhq/core
npm install -D @types/better-sqlite3

Now we can initialize the database in backend/server.ts:

import { createBetterSqlite3Adapter } from '@dossierhq/better-sqlite3';
import { notOk, ok, type Logger } from '@dossierhq/core';
import BetterSqlite, { type Database } from 'better-sqlite3';

async function initializeDatabase(logger: Logger) {
  let database: Database;
  try {
    database = new BetterSqlite('data/database.sqlite');
  } catch (error) {
    return notOk.GenericUnexpectedException({ logger }, error);
  }

  return await createBetterSqlite3Adapter({ logger }, database, {
    migrate: true,
    fts: { version: 'fts5' },
    journalMode: 'wal',
  });
}

export async function initialize(logger: Logger) {
  const databaseResult = await initializeDatabase(logger);
  if (databaseResult.isError()) return databaseResult;

  return ok(undefined);
}

const logger = createConsoleLogger(console);
(await initialize()).throwIfError();

And use it from backend/main.ts:

import { createConsoleLogger } from '@dossierhq/core';
import { initialize } from './server.js';

const logger = createConsoleLogger(console);
(await initialize(logger)).throwIfError();

When saving the file the backend should restart since we use nodemon, or start the backend and frontend with npm start . We get a log like this the first time the server restart:

[be] info: Starting migration of database schema to version=1...
[be] info: Migrated database schema to version=1
[be] info: Starting migration of database schema to version=2...
[be] info: Migrated database schema to version=2
[be] info: Starting migration of database schema to version=3...
[be] info: Migrated database schema to version=3
[be] info: Starting migration of database schema to version=4...
[be] info: Migrated database schema to version=4
[be] info: Starting migration of database schema to version=5...
[be] info: Migrated database schema to version=5
[be] info: Starting migration of database schema to version=6...
[be] info: Migrated database schema to version=6
[be] Listening on http://localhost:3000

In the file data/database.sqlite we have the actual database with the latest database schema, configured with Write-Ahead Logging (WAL) (which means you might see temporary files data/database.sqlite-wal and data/database.sqlite-shm as well) for write performance, and FTS5 as the full-text search module.