Sign Up in JavaScript

So far only the owner and the staff have keys, and they are written into the code. Customers need accounts of their own: they sign up with an email and a password, and the shop keeps each one as a row in a new table, users.

A table is created once with CREATE TABLE IF NOT EXISTS, so the shop can start again and again. A column marked UNIQUE never holds the same value twice. To know whether a value is taken, look for it with a SELECT: .get(...) gives the row, or undefined when nothing matches. Here is the same idea for a made-up coupons table:

db.exec("CREATE TABLE IF NOT EXISTS coupons (id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE)");
const row = db.prepare("SELECT id FROM coupons WHERE code = ?").get("SPRING10");
if (row === undefined) {
  console.log("No such coupon");
}

An email that is already registered gets 409 Conflict: the request clashes with what the server already has. A new user gets 201 Created with their id and email. The answer never sends the password back.

These are the requests your code must answer. /check/users is a route the checker adds to show what the table holds:

POST /register  {"email": "ana@example.com", "password": "secret123"}  →  201  {"email": "ana@example.com", "id": 1}
POST /register  {"email": "ana@example.com", "password": "hello456"}   →  409  {"error": "Email already registered"}
POST /register  {"email": "ben@example.com", "password": "secret123"}  →  201  {"email": "ben@example.com", "id": 2}
GET /check/users  →  200  [{"email": "ana@example.com", "id": 1, "password": "secret123"}, {"email": "ben@example.com", "id": 2, "password": "secret123"}]

Your task: create the users table, then add POST /register. When the email is taken, answer {"error": "Email already registered"} with 409; otherwise save the user and answer 201 with its id and email.

Assignment
Follow the Coding Tutorial to create the users table and the sign-up route.

Hint
Look for the email before the INSERT. node:sqlite stores each statement as it runs, so there is nothing to commit, and result.lastInsertRowid is the id the database gave the new row. A route's function must send an answer: until yours calls res.status(...).json(...), a request to it just waits.