Lesson 7 of 8 · 5 min

Changing data and transactions

INSERT, UPDATE and DELETE without accidents, upserts, transactions and the ACID guarantees, and how to keep SQL injection out of your application.

On this page
  1. INSERT
  2. UPDATE
  3. DELETE
  4. Upsert: insert or update
  5. Transactions
  6. ACID
  7. SQL injection
  8. Check yourself

Reading data is safe. Changing it is where mistakes cost something, so this lesson pairs each command with the habit that keeps it safe.

INSERT

INSERT INTO customers (name, email, city)
VALUES ('Sara Khan', 'sara@example.com', 'Hyderabad');

Always list the columns. If someone adds a column later, an insert without the list breaks or, worse, puts values in the wrong place.

Insert several rows at once:

INSERT INTO products (name, category, price) VALUES
  ('Desk lamp', 'Accessories', 1299.00),
  ('Ring light', 'Video', 2199.00);

PostgreSQL and SQLite can hand back the new row, which saves a second query:

INSERT INTO orders (customer_id, ordered_at)
VALUES (5, CURRENT_DATE)
RETURNING id;

UPDATE

UPDATE orders
SET status = 'shipped'
WHERE id = 102;

An UPDATE without WHERE changes every row in the table. Two habits prevent that:

  1. Write the WHERE clause first.
  2. Run it as a SELECT before turning it into an UPDATE:
SELECT * FROM products WHERE category = 'Accessories';     -- check: 3 rows
UPDATE products SET price = price * 1.05 WHERE category = 'Accessories';

DELETE

DELETE FROM orders WHERE status = 'cancelled';

The same warning applies: no WHERE, no data. Many applications never delete important rows at all; they set a flag such as deleted_at instead, so mistakes can be undone and history is kept.

If other tables point at a row, the foreign key stops you deleting it, which is usually what you want. You can choose the behaviour when you create the key:

order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE

CASCADE deletes the order's items along with the order. Use it only where the child rows are meaningless without the parent.

Upsert: insert or update

"Insert this row, or update it if it already exists":

-- PostgreSQL and SQLite
INSERT INTO customers (email, name, city)
VALUES ('asha@example.com', 'Asha Rao', 'Mysuru')
ON CONFLICT (email) DO UPDATE SET city = excluded.city;

-- MySQL
INSERT INTO customers (email, name, city)
VALUES ('asha@example.com', 'Asha Rao', 'Mysuru')
ON DUPLICATE KEY UPDATE city = VALUES(city);

Transactions

Placing an order means creating the order and its items together. If the second step fails, you don't want half an order. A transaction groups statements so they all succeed or none do:

BEGIN;

INSERT INTO orders (id, customer_id, ordered_at) VALUES (105, 3, CURRENT_DATE);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (105, 2, 1);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (105, 4, 2);

COMMIT;

If anything goes wrong before COMMIT, run ROLLBACK and the database returns to exactly how it was.

ACID

Transactions in a relational database give four guarantees:

  • Atomicity: all of the transaction happens, or none of it.
  • Consistency: constraints hold before and after; a transaction can't leave broken data.
  • Isolation: transactions running at the same time don't see each other's half-finished work.
  • Durability: once COMMIT returns, the data survives a crash or power cut.

SQL injection

When your application builds SQL from user input, never paste the input into the query string:

// Dangerous: an email of  x' OR '1'='1  returns every customer
db.query(`SELECT * FROM customers WHERE email = '${email}'`)

Use parameters instead. The database receives the value separately and can never mistake it for SQL:

// Safe (node-postgres)
db.query("SELECT * FROM customers WHERE email = $1", [email])

Every database library supports parameters (? in MySQL and SQLite, $1 in PostgreSQL). ORMs such as Prisma, Sequelize and Drizzle use them for you.

Check yourself

  1. Write an UPDATE that marks order 103 as shipped. What SELECT would you run first?
  2. Raise the price of every Video product by 10%, inside a transaction. Check the result, then ROLLBACK. Are the prices back?
  3. Why is ON DELETE CASCADE a good fit for order_items but a bad one for orders.customer_id?

More from Databases and SQL