Every database engine implements SQL slightly differently, and if you’re coming to SQLite from another system, or coming to databases for the first time altogether, it helps enormously to understand the shape of SQLite’s syntax before diving into individual commands. This article isn’t about any one command in particular — it’s about the underlying grammar, conventions, and quirks that apply across everything you’ll write in SQLite. Once these fundamentals click, every specific command becomes much easier to pick up.
Statements End with a Semicolon
Every SQL statement in SQLite should end with a semicolon. In the interactive sqlite3 shell, this actually matters functionally — SQLite won’t execute your statement until it sees that semicolon, since it assumes you might still be typing a multi-line command.
SELECT * FROM users;
If you forget the semicolon in the interactive shell, you’ll see the prompt change from sqlite> to ...>, indicating SQLite is still waiting for you to finish the statement. This trips up beginners constantly — you type a command, hit enter, and nothing happens, because SQLite is patiently waiting for that final semicolon.
sqlite> SELECT * FROM users
...> ;
Both of those lines together form one complete statement.
Case Sensitivity: Keywords vs Identifiers vs String Values
This is one of the most common sources of confusion for people newer to SQL generally. SQL keywords (SELECT, FROM, WHERE, INSERT, and so on) are case-insensitive in SQLite. You can write them in uppercase, lowercase, or a mix, and SQLite treats them identically:
select * from users where age > 18;
SELECT * FROM users WHERE age > 18;
SeLeCt * FrOm users WhErE age > 18;
All three of these run identically. That said, the near-universal convention — one I’d strongly encourage you to follow — is to write keywords in uppercase and everything else (table names, column names) in lowercase. It makes queries dramatically easier to scan visually, especially once they get longer and more complex.
Identifiers (table names, column names) are technically case-insensitive for ASCII characters in SQLite by default too, though this can get confusing since the case you use when creating a table is preserved and displayed in things like .schema, even though comparisons against it are case-insensitive.
String literal values, however, are absolutely case-sensitive:
SELECT * FROM users WHERE name = 'Ahmed';
SELECT * FROM users WHERE name = 'ahmed';
These are two different queries with potentially different results, since 'Ahmed' and 'ahmed' are different string values, even though the SQL keywords surrounding them wouldn’t care about case at all.
Quoting: Single Quotes, Double Quotes, and Brackets
SQLite has specific rules about quoting that are worth understanding clearly, because getting it wrong produces confusing errors.
Single quotes are for string literals. This is the standard, correct way to write a text value:
SELECT * FROM users WHERE city = 'Lahore';
Double quotes are technically for identifiers — table names, column names — particularly ones that contain spaces, special characters, or happen to clash with a reserved keyword:
CREATE TABLE "order" (
id INTEGER PRIMARY KEY,
"customer name" TEXT
);
Here, "order" is quoted because ORDER is a reserved SQL keyword, and "customer name" is quoted because it contains a space. Without the quotes, both would cause a syntax error.
Here’s the part that catches people off guard: for historical compatibility reasons, SQLite will actually accept double quotes around what looks like a string literal, if there’s no matching identifier, silently treating it as a string instead. This is considered a misfeature even by SQLite’s own documentation, and relying on it is a bad habit worth avoiding entirely. Always use single quotes for string values, and reserve double quotes strictly for identifiers that genuinely need quoting.
SQLite also supports square brackets and backticks as alternative identifier quoting styles, inherited from compatibility with SQL Server and MySQL respectively:
SELECT [customer name] FROM users;
SELECT `customer name` FROM users;
I wouldn’t recommend leaning on these unless you’re working with tooling that specifically expects them — double quotes are the standard SQL way to quote identifiers, and it’s the style most portable across other database engines if you ever need to migrate away from SQLite.
Comments
SQLite supports two comment styles, both borrowed from common programming language conventions:
-- This is a single-line comment
SELECT * FROM users; -- comment after a statement
/* This is a
multi-line comment */
SELECT * FROM orders;
Double-dash comments run to the end of the line; /* */ block comments can span multiple lines. I use single-line comments constantly when writing longer scripts, to explain why a particular query or migration step exists, since that context is easy to forget months later.
Whitespace and Formatting Freedom
SQLite doesn’t care about whitespace, line breaks, or indentation beyond what’s needed to separate tokens. This means:
SELECT first_name, last_name FROM employees WHERE department = 'Sales' ORDER BY last_name;
and
SELECT
first_name,
last_name
FROM
employees
WHERE
department = 'Sales'
ORDER BY
last_name;
are functionally identical. The second form is obviously more readable once queries grow beyond a single simple line, and I’d strongly encourage adopting a consistent multi-line formatting style for anything beyond the most trivial queries. Future you, debugging a complex query at 11pm, will thank present you for the readable formatting.
Parameter Placeholders
When writing SQL from within application code rather than typing it directly, you should almost always use parameterized queries instead of manually concatenating values into your SQL strings. SQLite supports several placeholder styles:
SELECT * FROM users WHERE id = ?;
SELECT * FROM users WHERE id = ?1;
SELECT * FROM users WHERE id = :id;
SELECT * FROM users WHERE id = @id;
The plain ? style is positional — values are supplied in order. The named styles (:id, @id) let you bind values by name instead, which becomes valuable in longer queries with many parameters, where positional matching becomes error-prone.
In Python, for example:
cursor.execute('SELECT * FROM users WHERE id = ?', (5,))
cursor.execute('SELECT * FROM users WHERE id = :id', {'id': 5})
I can’t stress enough how important this is, beyond just convenience: parameterized queries are your primary defense against SQL injection. Never build SQL strings by directly interpolating user input into the query text.
Expressions and Operators
SQLite supports the standard set of SQL operators you’d expect: arithmetic (+, -, *, /, %), comparison (=, != or <>, <, >, <=, >=), logical (AND, OR, NOT), and a handful of SQLite-specific conveniences worth knowing about.
|| is the string concatenation operator:
SELECT first_name || ' ' || last_name AS full_name FROM users;
IS and IS NOT handle NULL comparisons correctly, unlike = and !=, as covered in more depth in the data types article in this series:
SELECT * FROM users WHERE phone IS NOT NULL;
GLOB is a case-sensitive pattern matcher using Unix-style wildcards (* and ?), distinct from the more commonly used LIKE:
SELECT * FROM users WHERE name GLOB 'A*';
Multiple Statements in One Go
You can execute several SQL statements together, separated by semicolons, particularly when running a script file rather than typing interactively:
CREATE TABLE a (id INTEGER);
CREATE TABLE b (id INTEGER);
INSERT INTO a VALUES (1);
INSERT INTO b VALUES (2);
When running these from a .sql file via sqlite3 mydb.db < script.sql, SQLite processes each statement in order. Just be aware: when calling SQLite programmatically from most language bindings, a single execute call often only accepts one statement at a time, and you’ll need a script-execution method (often called executescript() in Python, for instance) specifically to run multiple statements at once.
Dot-Commands Are Not SQL
It’s worth explicitly calling this out, since it confuses beginners regularly: commands that begin with a dot, like .tables, .schema, .dump, .mode, and .headers, are not part of the SQL language at all. They’re special commands understood only by the sqlite3 command-line shell itself. If you try to use .tables from within an application’s SQL execution call (say, from Python’s sqlite3 module), it will fail, because your Python code is sending that string directly to SQLite’s SQL parser, which has no idea what to do with a dot-command. Dot-commands only work when typed directly into the interactive shell or a script fed into it via the command line.
Common Syntax Mistakes to Avoid
Using double quotes for string values instead of single quotes. It often works by accident due to SQLite’s compatibility fallback, but it’s fragile and non-standard — stick to single quotes for strings.
Forgetting the semicolon and being confused why the interactive shell seems stuck. Look for the ...> continuation prompt as your signal that SQLite is still waiting for more input.
Manually concatenating user input into SQL strings instead of using parameterized queries — this is both a syntax habit worth breaking and a serious security risk.
Trying to run dot-commands from application code. Remember, they’re shell-specific, not SQL.
Inconsistent formatting on long, multi-clause queries, making them hard to debug later. Adopt a consistent style — one clause per line is a good default — early on.
Best Practices Worth Adopting
Write SQL keywords in uppercase and identifiers in lowercase, consistently, across your whole project.
Always use single quotes for string literals and double quotes exclusively for identifiers that genuinely need quoting.
Format multi-clause queries across multiple lines, with consistent indentation, even though SQLite doesn’t require it — your future self and any collaborators will appreciate it.
Always use parameterized queries when working with user-supplied data from application code, never string concatenation.
Keep dot-commands strictly to interactive shell sessions and script files run directly against the sqlite3 CLI tool — never expect them to work from within application code executing SQL through a driver or library.
Wrapping Up
Syntax might seem like the least exciting part of learning any language, SQL included, but getting comfortable with these fundamentals — quoting rules, case sensitivity, comments, parameterization, and the distinction between SQL proper and shell-specific dot-commands — makes everything else you learn about SQLite click into place much faster. These aren’t rules you need to memorize consciously forever; write enough SQLite queries following good conventions, and they become second nature surprisingly quickly.