SQLite CRUD and SELECT Cheatsheet

SELECT * FROM users;

# Read rows from a table.

SELECT id, email, created_at FROM users;

# Return a smaller projection.

SELECT id, title FROM posts WHERE title LIKE '%sqlite%';

# Find rows by pattern.

SELECT id, email, created_at FROM users ORDER BY created_at DESC LIMIT 20;

# Get recent rows first.

SELECT id, email FROM users ORDER BY id LIMIT 20 OFFSET 40;

# Fetch one page of rows.

INSERT INTO users (email, display_name) VALUES ('ava@example.com', 'Ava');

# Add a new record.

INSERT INTO tags (name) VALUES ('sql'), ('sqlite'), ('database');

# Add several records in one statement.

UPDATE users SET timezone = 'America/Los_Angeles' WHERE id = 1;

# Modify one or more records.

DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP;

# Remove records by condition.

INSERT INTO users (email, display_name)
VALUES ('ava@example.com', 'Ava Martin')
ON CONFLICT(email) DO UPDATE SET display_name = excluded.display_name;

# Insert or update on a uniqueness collision.

INSERT INTO tags (name) VALUES ('sqlite') ON CONFLICT(name) DO NOTHING;

# Keep the existing row when a unique key collides.