Store application data

This guide uses a small work queue stored as an ordinary SQL table. Run the SQL in a connected SQL client or the SQL editor.

Create and populate a table

CREATE TABLE work_items (
  id BIGINT PRIMARY KEY,
  title TEXT NOT NULL,
  status TEXT NOT NULL
);
INSERT INTO work_items VALUES
  (1, 'Review request', 'pending'),
  (2, 'Send response', 'pending');

Read only the records you need

SELECT id, title
FROM work_items
WHERE status = 'pending'
ORDER BY id;

Use bound parameters for values supplied by users. The placeholder syntax depends on the client; see your language guide.

Commit related changes together

Use one connection for the transaction:

BEGIN;
UPDATE work_items SET status = 'completed' WHERE id = 1;
UPDATE work_items SET status = 'running' WHERE id = 2;
COMMIT;

If a statement fails, roll back before reusing the connection. Retry from BEGIN when a transaction fails with a retryable conflict.

Check the result

SELECT id, status FROM work_items ORDER BY id;

The statuses are completed for record 1 and running for record 2.

Next steps

Transactions · Data types · Indexes and queries