SQLite 3 cheatsheet

SQLite 3: Everyday SQL

5 common SQLite 3 tasks.

Create a table

Create a table with an auto-assigned integer key and a default timestamp.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Add a column to an existing table.

ALTER TABLE users ADD COLUMN name TEXT;

Insert a row, doing nothing if the email already exists.

INSERT INTO users (email) VALUES ('[email protected]') ON CONFLICT(email) DO NOTHING;

Extract a field from a JSON text column.

SELECT json_extract(data, '$.name') FROM events;

Return the current UTC date and time.

SELECT datetime('now');