Databases and Data Storage

After this lesson, you will be able to: Understand how relational databases work, write basic SQL queries, and connect a database to a Node.js backend.

Databases are how your backend remembers things between requests. This lesson covers the relational model (rows, tables, primary keys), the four core SQL operations, and how to connect PostgreSQL to a Node.js app.

Prerequisites:Introduction to Backend Concepts

Relational databases in plain English

A relational database is a collection of tables. Each table has columns (the fields, like name and email) and rows (the records). Each row has a unique primary key, usually called id. Tables relate to each other with foreign keys: a posts table can have a userId that references the id in users.

Diagram coming soon!

Two database tables side by side, users (id, name, email) on the left, posts (id, userId, title, body) on the right, with an arrow connecting posts.userId to users.id

The four SQL operations you'll use most

Create, Read, Update, Delete, "CRUD".

sql

-- Create
INSERT INTO users (name, email) VALUES ('Alex', '[email protected]');

-- Read
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;

-- Update
UPDATE users SET name = 'Alex Chen' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

Joining tables

Joins combine related rows from different tables.

sql

-- Get every post with its author's name
SELECT posts.title, users.name AS author
FROM posts
JOIN users ON posts.user_id = users.id;

Connecting from Node.js with the pg library

The pg package is the most common PostgreSQL driver for Node.

tsx

// In the in-browser editor shell: npm install pg
const { Pool } = require("pg");
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

app.get("/api/users", async (req, res) => {
  const result = await pool.query("SELECT id, name FROM users");
  res.json(result.rows);
});

app.post("/api/users", async (req, res) => {
  const { name, email } = req.body;
  // $1 and $2 are parameters, safe from SQL injection
  await pool.query("INSERT INTO users (name, email) VALUES ($1, $2)", [name, email]);
  res.status(201).json({ ok: true });
});

Try it: a SQL playground

Use the free SQL sandbox at sqliteonline.com to run real queries, no install required.

1
Go to sqliteonline.com and pick PostgreSQL
2
In the editor, run: CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT, grade INT);
3
Insert three rows with INSERT INTO
4
Run SELECT * FROM students to see them
5
Update one row's grade
6
Delete one row, then re-run SELECT to confirm

Quick Check

What is a primary key?

What does it guarantee about a row in a table?

←Introduction to Backend Concepts

Back to Web Development

Full-Stack Project→