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.

users

id (PK)

name

posts

id (PK)

userId (FK)

title

body

⟶posts.userId references users.id. One user has many posts.

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→