Databases and Authentication

Choosing a database

• SQL databases (MySQL, PostgreSQL) store data in related tables. They are a strong default for most apps.
• NoSQL databases (such as MongoDB) store flexible documents and suit changing data shapes.

Designing tables

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL
);

CREATE TABLE todos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  done BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

One user has many to-dos. This is a one-to-many relationship.

Querying

SELECT t.title, u.email
FROM todos t
JOIN users u ON u.id = t.user_id
WHERE t.done = FALSE
ORDER BY t.id DESC;

Authentication basics

Authentication proves who the user is. Authorization decides what they may do.

1. Register: hash the password with a slow, salted algorithm such as bcrypt or Argon2. Never store the plain password.
2. Login: compare the submitted password with the stored hash.
3. Keep the user logged in: with a server session cookie or a signed token (JWT).
4. Protect routes: check the session or token on every request.

const bcrypt = require("bcrypt");

const hash = await bcrypt.hash(password, 12);          // at registration
const ok = await bcrypt.compare(password, user.hash);  // at login

Security checklist

• Use prepared statements to prevent SQL injection.
• Always use HTTPS.
• Set cookies as HttpOnly, Secure and SameSite.
• Limit login attempts.
• Make sure users can only access their own data. Check user_id on every query.

Practice

Design the tables for a blog with users, posts and comments, and write a query that lists each post with its comment count.