Connecting a database

An in-memory array is cleared every time the server restarts. To keep posts and comments, you need a database. The course uses SQLite — the whole database lives in one file and needs no separate server — through the better-sqlite3 package. Database queries are written in SQL.

Installing

npm install better-sqlite3

The package contains a compiled part, so installation can take a minute.

The database plugin

Every route needs the database connection, so it is written as a plugin with fastify-plugin. Create src/plugins/db.js:

// src/plugins/db.js
import fp from 'fastify-plugin';
import Database from 'better-sqlite3';
 
async function dbPlugin(app) {
  const db = new Database(app.config.DB_FILE);
  db.pragma('foreign_keys = ON');
 
  db.exec(`
    CREATE TABLE IF NOT EXISTS posts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      body TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS comments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
      author TEXT NOT NULL,
      text TEXT NOT NULL
    );
  `);
 
  app.decorate('db', db);
  app.addHook('onClose', async () => db.close());
}
 
export default fp(dbPlugin);

The plugin opens the file from the DB_FILE setting, creates the tables if they do not exist yet, adds app.db and closes the database when the server stops. ON DELETE CASCADE removes comments together with their post.

Registering it

In src/app.js replace the store plugin with db. It must come after @fastify/env, because it reads app.config:

// src/app.js
import dbPlugin from './plugins/db.js';
 
app.register(fastifyEnv, { schema: envSchema });
app.register(dbPlugin);
app.register(postRoutes);
app.register(commentRoutes);

After npm run dev a blog.db file appears in the project root. Add it to .gitignore.

The first query

better-sqlite3 methods are synchronous: prepare() prepares a query, and all(), get() and run() execute it.

// src/routes/posts.js
app.get('/posts', async () => {
  return app.db.prepare('SELECT id, title FROM posts ORDER BY id DESC').all();
});

Open http://localhost:3000/posts — while the database is empty you see []. Posts start to appear in the next lesson, when the POST route writes them to the database.

Common mistake

Never put client data straight into the query text — this opens the way to SQL injection, where an attacker slips in their own SQL:

// src/routes/posts.js
// Wrong:
app.db.prepare(`SELECT * FROM posts WHERE id = ${request.params.id}`).get();

Put ? in the query and pass the values separately: prepare('SELECT * FROM posts WHERE id = ?').get(id).