Connecting an app to a database

Six chapters ago, Samwell disappeared at every restart. Today, the loop closes: we connect Fastify to a real database and give the Watch its promised permanent register.

🔌 How they communicate

First an overview applicable to any stack. A database is usually a separate server; the backend connects through a driver, a library opening connections, sending SQL queries and translating results into your language's objects. A request's full flow becomes:

browser ──HTTP──▶ backend ──SQL──▶ database
browser ◀──JSON── backend ◀──righe── database

The frontend never talks directly to the database in this architecture: it passes through the backend, which validates, checks permissions and chooses queries. Now chapter 10 becomes fully clear: the backend guards data too.

🗃️ Our database: SQLite

For our first connection, choose SQLite, perfect for learning: no separate server to install or configure, because the entire database lives in one file alongside your code. It is no toy: used across phones everywhere, it speaks last chapter's SQL.

npm install better-sqlite3

Open server.js and retire the in-memory array:

1import Fastify from 'fastify';
2import cors from '@fastify/cors';
3import Database from 'better-sqlite3';
4
5const app = Fastify();
6await app.register(cors);
7
8// the register now lives in a file: guardia.db
9const db = new Database('guardia.db');
10
11// create the table if missing (SQL from chapter 17!)
12db.exec(`
13  CREATE TABLE IF NOT EXISTS guardiani (
14    id INTEGER PRIMARY KEY AUTOINCREMENT,
15    nome TEXT NOT NULL,
16    ruolo TEXT NOT NULL DEFAULT 'recluta'
17  )
18`);
19
20// Read: the list, straight from the database
21app.get('/guardiani', () => {
22  return db.prepare('SELECT * FROM guardiani').all();
23});
24
25// Create: persistent recruitment
26app.post('/guardiani', (request, reply) => {
27  const { nome } = request.body;
28
29  if (typeof nome !== 'string' || !nome.trim() || nome.length > 100) {
30    return reply.code(400).send({ errore: 'Invalid name' });
31  }
32
33  const result = db
34    .prepare('INSERT INTO guardiani (nome) VALUES (?)')
35    .run(nome.trim());
36
37  return reply.code(201).send({
38    id: result.lastInsertRowid,
39    nome: nome.trim(),
40    ruolo: 'recluta',
41  });
42});
43
44await app.listen({ port: 3000 });
45console.log('The permanent register is listening at http://localhost:3000');

These are last chapter's SELECT and INSERT, delivered by the driver. Now the moment awaited for six chapters: start the server, recruit Samwell through the form or curl, stop it and restart. Read the list:

Samwell is still there. 🎉

Data now lives in guardia.db, surviving restarts, crashes and updates. Promise kept: the Watch has a register remembering its names.

💉 The question mark that saves you

Before celebrating too much, return to INSERT and inspect ?:

db.prepare('INSERT INTO guardiani (nome) VALUES (?)').run(nome.trim());

This is a parameterized query, the promised SQL injection defense. SQL and data travel separately: the database receives the query structure (with ? placeholders) and the values, treated only as data. Last chapter's malicious ' OR '1'='1 would be saved literally as a recruit's bizarre name, never becoming part of the command.

One habit provides this defense: never concatenate input into queries; always use parameters. Every driver and language has its syntax. From now on, a query built with + should set off an alarm.

🧙 ORMs: talking to databases through objects

Writing SQL manually is fine, and knowing how is essential. On large projects, endlessly translating rows into objects and objects into queries becomes repetitive work. ORMs (Object-Relational Mapping) map tables to language objects and generate SQL for you.

A popular Node option is Prisma. Describe the schema:

1model Guardiano {
2  id    Int    @id @default(autoincrement())
3  nome  String
4  ruolo String @default("recluta")
5}

Then query in typed JavaScript without visible SQL:

1// Read
2const guardiani = await prisma.guardiano.findMany({
3  where: { ruolo: 'recluta' },
4  orderBy: { nome: 'asc' },
5});
6
7// Create
8await prisma.guardiano.create({
9  data: { nome: 'Samwell Tarly' },
10});

Recognize where and orderBy? Last chapter's SELECT in different clothes. Prisma's generated queries are parameterized by default, helping prevent injection by design.

Driver and handwritten SQL, or ORM? The honest answer: it depends. ORMs offer productivity, types and safe defaults; direct SQL offers full control without hidden machinery. Real projects often use both: ORM for daily work, SQL for complex queries. (Fastify plus Prisma is actually my working stack; you will see it in this site's projects.)

✅ Conclusion

Look at the complete architecture you traversed: frontend collecting data and sending HTTP, backend validating, protecting and deciding, database storing persistently. A complete web app, sharing the anatomy of Gmail, Amazon or business software. Scale changes; the skeleton does not.

We studied each piece separately. In the next section, the last of this arc, we follow the whole cycle continuously: frontend-backend communication and the flow from click to updated pixel. All that remains is joining the dots.