← All Cheat Codes Library / Cheat Codes / SQLite

🪶 SQLite Cheat Codes

SQLite Cheat Codes

SQLite Cheat Codes for single-file databases, WAL mode, pragmas, and FTS5 search. 20 essential cheat codes ready to copy and execute.

🌐 All Engines 🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

# 1 SELECT DQL

Fetch columns from SQLite database.

Syntax:

SELECT col1, col2 FROM tbl;

Working Example:

SELECT id, name FROM notes;

# 2 WHERE DQL

Filter output rows.

Syntax:

SELECT * FROM tbl WHERE col = val;

Working Example:

SELECT * FROM tasks WHERE completed = 1;

# 3 JOIN Joins

Join two tables on condition.

Syntax:

SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk;

Working Example:

SELECT n.title, c.name FROM notes n JOIN categories c ON n.cat_id = c.id;

# 4 GROUP BY Aggregations

Group rows for aggregate stats.

Syntax:

SELECT col, COUNT(*) FROM tbl GROUP BY col;

Working Example:

SELECT category, COUNT(*) FROM tasks GROUP BY category;

# 5 ORDER BY DQL

Sort output rows.

Syntax:

SELECT * FROM tbl ORDER BY col DESC;

Working Example:

SELECT * FROM notes ORDER BY updated_at DESC;

# 6 LIMIT OFFSET Pagination

Paginate SQLite query output.

Syntax:

SELECT * FROM tbl LIMIT count OFFSET skip;

Working Example:

SELECT * FROM logs LIMIT 20 OFFSET 40;

# 7 INSERT INTO DML

Add new row into table.

Syntax:

INSERT INTO tbl (col) VALUES (val);

Working Example:

INSERT INTO notes (title) VALUES ('Meeting Summary');

# 8 UPDATE SET DML

Modify column values in rows.

Syntax:

UPDATE tbl SET col = val WHERE condition;

Working Example:

UPDATE tasks SET completed = 1 WHERE id = 5;

# 9 DELETE FROM DML

Remove records matching filter.

Syntax:

DELETE FROM tbl WHERE condition;

Working Example:

DELETE FROM temp_files WHERE age > 7;

# 10 ON CONFLICT DO UPDATE DML

Execute upsert on unique key collision.

Syntax:

INSERT INTO tbl (id, val) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET val = excluded.val;

Working Example:

INSERT INTO kv (key, val) VALUES ('theme', 'dark') ON CONFLICT (key) DO UPDATE SET val = excluded.val;

# 11 PRAGMA table_info Introspection

Inspect table columns and data types.

Syntax:

PRAGMA table_info('tbl');

Working Example:

PRAGMA table_info('users');

# 12 PRAGMA journal_mode Storage

Enable high-concurrency WAL mode.

Syntax:

PRAGMA journal_mode = WAL;

Working Example:

PRAGMA journal_mode = WAL;

# 13 VACUUM Maintenance

Reclaim unused file disk space.

Syntax:

VACUUM;

Working Example:

VACUUM;

# 14 CREATE VIRTUAL TABLE (FTS5) FTS5

Build full-text search index.

Syntax:

CREATE VIRTUAL TABLE fts_tbl USING fts5(col1, col2);

Working Example:

CREATE VIRTUAL TABLE docs_fts USING fts5(title, body);

# 15 FTS5 MATCH FTS5

Execute full-text search query.

Syntax:

SELECT * FROM fts_tbl WHERE fts_tbl MATCH 'query';

Working Example:

SELECT * FROM docs_fts WHERE docs_fts MATCH 'sqlite OR database';

# 16 json_extract() Functions

Extract JSON field value.

Syntax:

SELECT json_extract(json_col, '$.key') FROM tbl;

Working Example:

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

# 17 ATTACH DATABASE Multi-DB

Attach secondary database file.

Syntax:

ATTACH DATABASE 'file.db' AS alias;

Working Example:

ATTACH DATABASE 'archive.db' AS archive;

# 18 datetime() Functions

Manipulate timestamps and dates.

Syntax:

SELECT datetime('now', 'start of month');

Working Example:

SELECT datetime('now', '-7 days');

# 19 PRAGMA foreign_keys Integrity

Enable foreign key constraint checks.

Syntax:

PRAGMA foreign_keys = ON;

Working Example:

PRAGMA foreign_keys = ON;

# 20 STRICT TABLE DDL

Enforce strict column data types.

Syntax:

CREATE TABLE tbl (id INT PRIMARY KEY) STRICT;

Working Example:

CREATE TABLE accounts (id INT PRIMARY KEY, balance REAL) STRICT;