DailyRNG
Login
Advanced

CRUD with SQL

CRUD means Create, Read, Update, Delete, the four things every app does with data. Learn the SQL statement for each and how to pass user input safely so it cannot corrupt your tables.

What you will see

CRUD with SQL Action SQL HTTP Create INSERT POST Read SELECT GET Update UPDATE PUT Delete DELETE DELETE

About 2 min of reading

Try It

Map HTTP verbs to SQL statements

1

Show how each HTTP verb maps to a SQL action.

action_map = [ ("Create", "INSERT", "POST"), ("Read", "SELECT", "GET"), ("Update", "UPDATE", "PUT"), ("Delete", "DELETE", "DELETE"), ] print() print("CRUD with SQL") print("%-8s %-8s %-8s" % ("Action", "SQL", "HTTP")) for action, sql, method in action_map: print("%-8s %-8s %-8s" % (action, sql, method))

Before you run it, say out loud what it will print. Then open the answer.

What it prints
CRUD with SQL Action SQL HTTP Create INSERT POST Read SELECT GET Update UPDATE PUT Delete DELETE DELETE

Data Flow

A request carries input values bound to a SQL placeholder, SQLite executes the statement, and reports how many rows changed.
1 App receives user input
↓
2 Build an INSERT/UPDATE/DELETE with ? placeholders
↓
3 Bind the user values to the slots
↓
4 SQLite executes the statement
↓
5 Rows affected are reported via this.changes
↓
6 Response reflects the successful change
Why this works
The four CRUD operations

Every feature maps to one of four SQL actions:

ActionSQLHTTP method
CreateINSERTPOST
ReadSELECTGET
UpdateUPDATEPUT
DeleteDELETEDELETE

Learning to move between the table language (SQL) and the web language (HTTP verbs) is the skill that turns a static site into a data app. The right HTTP method pairs with the right SQL statement, and that pairing is the spine of any full-stack app.

Examples

Insert a row

Use INSERT to add a new record. Always bind values with ? placeholders, never paste user input into the SQL string.

db.run(
  'INSERT INTO users (email) VALUES (?)',
  'ada@example.com',
  function (err) {
    console.log('New row id:', this.lastID);
  }
);

The ? is a slot that SQLite fills safely. It is escaped so a value like ' OR '1'='1 cannot hijack your query, this defeats the most common web attack, SQL injection.

Update and delete

UPDATE changes matching rows; DELETE removes them. Both always use a WHERE clause, deleting the whole table is one forgotten word away otherwise.

db.run('UPDATE users SET email = ? WHERE id = ?', ['new@example.com', 1]);
db.run('DELETE FROM users WHERE id = ?', [1]);

After each run, this.changes tells you how many rows were affected. It is how you know the write worked.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Databases with SQLite
Previous Lesson
Databases with SQLite
Next Lesson
Middleware and request flow
Middleware and request flow →