An SQLite database

An in-memory array is wiped on every server restart. To keep posts, you need a database. SQLite stores the whole database in one file and needs no separate server, and the better-sqlite3 package gives simple access to it from Node.js. Queries are written in SQL.

Installation

npm install better-sqlite3

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

Connection and tables

Create the file src/db.js. It opens the database and creates the tables if they do not exist yet:

// src/db.js
import Database from 'better-sqlite3';
 
const db = new Database('blog.db');
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
  );
`);
 
export default db;

On the first start a blog.db file appears in the project root. The post_id column links a comment to a post, and ON DELETE CASCADE deletes the comments together with the post.

A model

All queries to the tables are collected in a model. Create src/models/post.js:

// src/models/post.js
import db from '../db.js';
 
export function findAll() {
  return db.prepare('SELECT * FROM posts ORDER BY id DESC').all();
}
 
export function findById(id) {
  return db.prepare('SELECT * FROM posts WHERE id = ?').get(id);
}
 
export function create({ title, body }) {
  const result = db.prepare('INSERT INTO posts (title, body) VALUES (?, ?)').run(title, body);
  return Number(result.lastInsertRowid);
}
 
export function findComments(postId) {
  return db.prepare('SELECT * FROM comments WHERE post_id = ? ORDER BY id').all(postId);
}
 
export function addComment(postId, { author, text }) {
  db.prepare('INSERT INTO comments (post_id, author, text) VALUES (?, ?, ?)').run(postId, author, text);
}

prepare() prepares a query, and its methods run it: all() returns an array of rows, get() one row or undefined, run() information about the change. The ? marks are replaced by the arguments in order.

Using it in the controller

The controller now takes data from the model instead of the array:

// src/controllers/postsController.js
import * as Post from '../models/post.js';
 
export function index(req, res) {
  res.render('posts/index', { title: 'All posts', posts: Post.findAll() });
}
 
export function show(req, res) {
  const post = Post.findById(Number(req.params.id));
 
  if (!post) {
    return res.status(404).render('404', { title: 'Not found' });
  }
 
  res.render('posts/show', { title: post.title, post, comments: Post.findComments(post.id) });
}
 
export function storeComment(req, res) {
  Post.addComment(Number(req.params.id), req.body);
  res.redirect(`/posts/${req.params.id}`);
}

The comments route is router.post('/:id/comments', postsController.storeComment), and the form on the post page sends the author and text fields to /posts/<%= post.id %>/comments. Create a couple of posts and restart the server — they stay in place.

Common mistake

Never put visitor data into SQL with a template string:

// src/models/post.js
// Wrong: the value from the address goes straight into SQL.
db.prepare(`SELECT * FROM posts WHERE id = ${id}`).get();

Instead of a number, someone can put a piece of SQL into the address and read or delete other people's data — this is SQL injection. Always use ? marks and pass values as arguments: better-sqlite3 inserts them safely.

Thanks for reading!