SQLite
Online SQL Compiler
main.sql is fed to the sqlite3 shell from SQLite 3.46, statement by statement, against a database that lives only for that run. Each SELECT prints its rows to the console; the script itself is the input, so there’s nothing to type while it runs.
Try it aboveHow SQLite runs here
- Versions
- SQLite 3.46
- Run
sqlite3 < main.sql- Limits per run
- 256 MB memory, 15 s of CPU time on half a core, 2 MB of output
- Time
- Up to 10 minutes a run (30 on a paid plan); waits 2 minutes for input (5 on a paid plan)
- Sandbox
- Each run gets its own Linux sandbox with no network access. Text files your program writes next to your code open as new tabs.
- Good to know
- Each run starts from an empty in-memory database.
A script with two tables
Create the tables, fill them and query them in one file:
CREATE TABLE students ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, class TEXT ); CREATE TABLE scores ( student_id INTEGER REFERENCES students(id), subject TEXT, score INTEGER ); INSERT INTO students (name, class) VALUES ('Ada', 'A'), ('Linus', 'B'), ('Grace', 'A'); INSERT INTO scores VALUES (1, 'math', 95), (1, 'art', 80), (2, 'math', 70), (2, 'art', 88), (3, 'math', 91); .headers on SELECT s.name, COUNT(*) AS tests, ROUND(AVG(sc.score), 1) AS average FROM students s JOIN scores sc ON sc.student_id = s.id GROUP BY s.id ORDER BY average DESC;
$ sqlite3 < main.sql name|tests|average Grace|1|91.0 Ada|2|87.5 Linus|2|79.0
By default the shell prints rows with | between columns. .headers on adds the column names, and the dot commands below change the layout.
Shell commands for output and tables
Lines that start with a dot are commands for the sqlite3 shell, not SQL. Each sits at the start of its own line with no semicolon:
- .headers on prints column names above the rows.
- .mode box, .mode table or .mode markdown draw aligned tables; .mode column lines up columns without borders; .mode csv and .mode json print rows in those formats.
- .tables lists the tables, and .schema students prints that table’s CREATE statement.
- .import --csv people.csv people loads an uploaded CSV file into a table. Without the table first, the CSV’s first row becomes the column names.
SQLite is not MySQL
Tutorials often use MySQL or PostgreSQL. These are the differences that trip people up:
- Auto-increment: write id INTEGER PRIMARY KEY, which numbers rows by itself. MySQL’s INTEGER PRIMARY KEY AUTO_INCREMENT stops with near "AUTO_INCREMENT": syntax error, and the INT AUTO_INCREMENT PRIMARY KEY spelling is accepted but leaves every id NULL.
- No SHOW TABLES, DESCRIBE or TRUNCATE: each is a syntax error. Use .tables, .schema name or PRAGMA table_info(name), and DELETE FROM name.
- Dates: there’s no NOW(). Use datetime('now') or CURRENT_TIMESTAMP, and date('now', '-7 days') for date arithmetic.
- Types are loose: a column declared INTEGER stores the text 'ten' without complaint, and VARCHAR(5) doesn’t limit the length. Add STRICT after the closing parenthesis of CREATE TABLE to reject wrong types.
- 7 / 2 is 3, because dividing two integers truncates. Write 7 / 2.0 or CAST(x AS REAL).
- Foreign keys aren’t enforced until the script runs PRAGMA foreign_keys = ON;.
What isn’t available
- Data that lasts. Every run starts empty, so main.sql must create and fill its tables each time.
- Typing input while it runs: the whole file is the input.
- Server features: stored procedures, users and GRANT, and connecting to a MySQL or PostgreSQL server.
- MySQL and PostgreSQL functions without a SQLite equivalent, such as DATE_FORMAT(); SQLite uses strftime() instead.
Errors you’re likely to see
The shell reports each error with the line it was found near, and goes on with the next statement:
- “no such table: user”: the table is misspelled, or its CREATE TABLE comes later in the file or failed.
- “no such column: nmae”: a misspelled column, or a column from a table that isn’t in the FROM or JOIN.
- “near "SHOW": syntax error”: MySQL syntax. Every “near … syntax error” points at the word where SQLite stopped understanding.
- “no such function: NOW”: use datetime('now').
- “UNIQUE constraint failed: users.email”: an INSERT repeats a value in a UNIQUE or PRIMARY KEY column.
- “NOT NULL constraint failed: products.name”: an INSERT left out a column declared NOT NULL.
After a failed run, Fix with AI explains the error and proposes a change you review before it applies.
Window functions and modern SQL
SQLite 3.46 has window functions. Added to the end of the script above, this ranks students within their class by math score:
SELECT s.class, s.name, sc.score, RANK() OVER (PARTITION BY s.class ORDER BY sc.score DESC) AS class_rank FROM students s JOIN scores sc ON sc.student_id = s.id WHERE sc.subject = 'math' ORDER BY s.class, class_rank;
class|name|score|class_rank A|Ada|95|1 A|Grace|91|2 B|Linus|70|1
- WITH and WITH RECURSIVE for common table expressions.
- INSERT … ON CONFLICT (col) DO UPDATE for upserts, and RETURNING to see the rows a statement changed.
- JSON functions: json_extract(), and the -> and ->> operators.
Questions
Is this MySQL?
No, it’s SQLite 3.46. Most SELECT, JOIN and GROUP BY queries are the same, but table definitions, dates and admin commands differ, as listed above.
Does my data stay between runs?
No. Each run starts with an empty database, so keep the CREATE TABLE and INSERT statements in main.sql.
How do I show column names?
Put .headers on before the query, or .mode box to draw a table with a header row.
How do I list the tables?
Use .tables, or SELECT name FROM sqlite_master WHERE type = 'table';.
How do I make an AUTO_INCREMENT id?
Declare the column as id INTEGER PRIMARY KEY. SQLite fills it with the next number when an INSERT leaves it out.
Can I load a CSV file?
Yes. Upload it, then use .import --csv data.csv tablename in main.sql.