Experimenting with SQLite Syntax

One of the things I love most about SQLite is how safe it is to experiment with. There’s no shared server to accidentally break, no other developers whose connections you might disrupt, and no risky production data anywhere nearby unless you deliberately point it there. In this article, I want to share how I actually experiment with SQLite syntax when I’m learning something new or testing an idea, along with some of the more interesting and occasionally quirky syntax behaviors I’ve discovered along the way.

Setting Up a Safe Playground

The fastest way I experiment is with an in-memory database that disappears the moment I close the session:

sqlite3 :memory:

Nothing I do here touches disk, so I can try destructive statements, weird edge cases, or half-formed ideas without any consequences.

Testing Basic Syntax Variations

SQLite is often more forgiving than other SQL engines, and experimenting reveals some interesting flexibility.

Multiple Ways to Quote Identifiers

CREATE TABLE "my table" (id INTEGER);
CREATE TABLE [another table] (id INTEGER);
CREATE TABLE `yet another` (id INTEGER);

I was surprised to learn SQLite accepts double quotes, square brackets, and backticks for identifiers, largely for compatibility with other SQL dialects like SQL Server (brackets) and MySQL (backticks), even though the “correct” SQL standard way is double quotes.

String Literals vs Identifiers

SELECT 'hello';        -- a string literal
SELECT "hello";        -- also works, but ambiguous — could be an identifier

This is actually a subtle trap. In strict SQL, double quotes are reserved for identifiers, and single quotes are for string literals. SQLite is lenient and will interpret a double-quoted string as a literal if no matching identifier exists — but I’ve made this mistake before and had a query silently behave unexpectedly. I now always use single quotes for string literals to avoid the ambiguity entirely.

Experimenting With Type Affinity

Because SQLite uses dynamic typing, I like to test exactly how flexible it really is:

CREATE TABLE affinity_test (
    a INTEGER,
    b TEXT,
    c REAL,
    d BLOB,
    e NUMERIC
);

INSERT INTO affinity_test VALUES (1, 'text', 3.14, x'0102', '5');

SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM affinity_test;

Running typeof() on each column is a great way to actually see how SQLite is storing your values internally, rather than just assuming based on the declared column type.

INSERT INTO affinity_test (a) VALUES ('not a number at all');
SELECT typeof(a) FROM affinity_test WHERE a = 'not a number at all';
-- returns 'text', because it couldn't be converted to INTEGER affinity

Testing STRICT Tables

If you want stricter type enforcement, SQLite lets you opt in explicitly:

CREATE TABLE strict_test (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

INSERT INTO strict_test (id, name) VALUES (1, 42);
-- Error: cannot store INTEGER value in TEXT column name

Experimenting with STRICT tables side by side with regular ones is a great way to genuinely understand the difference between SQLite’s default flexible typing and its opt-in strict mode.

Exploring PRAGMA Statements

PRAGMA statements are a great experimentation playground because they let you inspect and tweak database behavior directly:

PRAGMA table_info(affinity_test);
PRAGMA foreign_key_check;
PRAGMA compile_options;
PRAGMA journal_mode;

PRAGMA compile_options; in particular is genuinely interesting — it tells you exactly which optional features were enabled when your specific SQLite build was compiled, which explains why certain features (like FTS5) might be missing in some environments.

Testing Conflict Resolution Clauses

SQLite supports several conflict resolution strategies that I like to test directly to understand the difference:

CREATE TABLE conflict_test (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

INSERT INTO conflict_test VALUES (1, 'a@example.com');

INSERT OR IGNORE INTO conflict_test VALUES (2, 'a@example.com');
SELECT * FROM conflict_test;
-- second insert is silently skipped, no error

INSERT OR REPLACE INTO conflict_test VALUES (3, 'a@example.com');
SELECT * FROM conflict_test;
-- the conflicting row is deleted and replaced with the new one

Testing INSERT OR IGNORE versus INSERT OR REPLACE versus a plain INSERT side by side made the differences between them click for me far faster than just reading the documentation alone.

Experimenting With JSON Functions

SQLite has built-in JSON support that’s genuinely fun to experiment with:

SELECT json_extract('{"name": "Ahmad", "skills": ["SQL", "Python"]}', '$.name');
-- 'Ahmad'

SELECT json_array_length('{"skills": ["SQL", "Python"]}', '$.skills');
-- 2

SELECT json_each.value
FROM json_each('["SQL", "Python", "Rust"]');

json_each() in particular is a neat trick — it lets you unpack a JSON array directly into rows, which I’ve used for quickly querying JSON blobs stored in text columns without writing separate application-side parsing code.

Testing Date and Time Functions

SELECT date('now');
SELECT datetime('now', 'localtime');
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');
SELECT date('now', '+7 days');
SELECT date('now', 'start of month');

I find date function experimentation particularly worthwhile because SQLite’s date modifiers (like '+7 days' or 'start of month') are genuinely powerful once you learn the syntax, but not always intuitive on the first try.

Testing Generated Columns

CREATE TABLE circle (
    radius REAL,
    area REAL GENERATED ALWAYS AS (3.14159 * radius * radius) STORED
);

INSERT INTO circle (radius) VALUES (5);
SELECT * FROM circle;

Generated columns compute their value automatically from other columns, and experimenting with STORED versus VIRTUAL generated columns is a great way to understand the trade-off between computing a value once and storing it, versus recomputing it on every read.

Testing Common Table Expression Edge Cases

WITH RECURSIVE fib(n, a, b) AS (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n + 1, b, a + b FROM fib WHERE n < 10
)
SELECT a FROM fib;

I often use small, self-contained recursive CTE puzzles like generating a Fibonacci sequence as a way to build real intuition for how recursive CTEs actually execute step by step.

Using EXPLAIN to See What’s Actually Happening

EXPLAIN SELECT * FROM affinity_test WHERE a = 1;

This prints SQLite’s low-level virtual machine bytecode for a query — genuinely more detail than most people need day to day, but fascinating for understanding exactly how SQLite executes a statement internally.

EXPLAIN QUERY PLAN
SELECT * FROM affinity_test WHERE a = 1;

This higher-level version is far more practical for everyday use, showing whether a query is using an index or falling back to a full table scan.

Best Practices for Safe Experimentation

• Always experiment in an in-memory database (:memory:) or a disposable copy of a file, never against production data.
• Use typeof() liberally when experimenting with type affinity — don’t assume based on the declared column type alone.
• Test PRAGMA changes on a scratch database before applying them to a real application, since some, like journal_mode, have persistent effects on the database file itself.
• Use EXPLAIN QUERY PLAN to validate assumptions about performance rather than guessing.
• Keep a personal scratch file of SQL snippets you’ve tested and understood — it becomes an invaluable personal reference over time.

Frequently Asked Questions

Is it safe to experiment with PRAGMA settings? Mostly yes on a scratch database, but some PRAGMA settings, like journal_mode = WAL, persist within the database file itself, so it’s worth testing on a disposable copy first if you’re unsure.

Why does SQLite accept double-quoted strings as literals sometimes? For compatibility reasons — if a double-quoted string doesn’t match any known identifier, SQLite falls back to treating it as a string literal, which can mask bugs. Using single quotes for literals avoids this ambiguity entirely.

What’s the fastest way to understand type affinity? Create a scratch table with several affinity types, insert a variety of values, and run typeof() on each column to see exactly how SQLite stored them.

Are STRICT tables recommended for all new projects? They’re worth strongly considering, especially for teams wanting stronger type guarantees closer to traditional SQL databases, though many existing SQLite codebases still rely on the default flexible typing behavior.

What’s the difference between EXPLAIN and EXPLAIN QUERY PLAN? EXPLAIN shows SQLite’s raw internal bytecode for a statement, while EXPLAIN QUERY PLAN gives a higher-level, human-readable summary of how the query will be executed — the latter is far more useful for everyday performance tuning.

Wrapping Up

Experimenting directly with SQLite syntax, rather than just reading about it, has taught me more about how SQL actually behaves than any tutorial could on its own. Because SQLite is so lightweight and disposable to spin up, there’s really no excuse not to test an idea directly in a scratch database before committing it to real application code. I’d genuinely encourage anyone learning SQL to keep an sqlite3 :memory: session open as a permanent scratchpad — it’s one of the best learning tools available for the language.