SQL projects

SQL runs on SQLite 3.36, your file piped into the shell. The dialect you get, why there is no Input tab, and the one kind that accepts nothing but .sql files.

View as .md

A SQL project runs your statements and prints the results. It uses the same sandbox as Java or Python - it is not a separate kind of machine - but almost everything about the file is different, and those differences catch people out.

The engine is SQLite 3.36#

Not Postgres, not MySQL. That is the single most useful thing to know, because dialects diverge more than people expect and a query copied from a Postgres tutorial may simply not run.

What you do get on 3.36:

  • Window functions - ROW_NUMBER(), RANK(), LAG(), OVER (PARTITION BY ...).
  • Common table expressions, including recursive ones - WITH RECURSIVE.
  • RETURNING on insert, update and delete.
  • ALTER TABLE ... DROP COLUMN.

What SQLite does not have at any version: stored procedures, user-defined functions from SQL, SHOW TABLES (use SELECT name FROM sqlite_master WHERE type='table'), and real type enforcement - columns have type affinity, so a TEXT value can land in an INTEGER column without complaint.

Your file is piped into the shell#

The run is literally your file piped into the sqlite3 command line. Two consequences follow, and the second is genuinely useful.

  1. There is no Input tab. Standard input is already carrying your query, so there is nothing left to feed a program with. Every other server kind accepts stdin; this one cannot.
  2. Dot-commands work, because the CLI shell is what reads your file.
.headers on
.mode column

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, total REAL);
INSERT INTO orders (customer, total) VALUES ('Asha', 1200.0), ('Ravi', 450.5);

SELECT customer, total FROM orders ORDER BY total DESC;

The database is in memory and gone afterwards#

No database file is opened, so SQLite creates a transient in-memory one. Every run starts empty.

Only .sql files#

SQL is the strictest kind in the product. Every other kind accepts txt, md and a linked Canvas board alongside its source; SQL accepts nothing but .sql.

So a SQL project cannot hold a README, a data file or a diagram. If you want notes, put them in SQL comments. It also has no folders - the file list is flat, and the entry file is main.sql.

Limits#

The general ceilings apply - project limits - with one of its own: SQL runs get 64 MiB of memory, the smallest allowance of any runtime here.

That is deliberate rather than mean. SQLite is by a wide margin the cheapest runtime in the set, measured at roughly 7.5 MB and 67 ms of CPU on a live container - about an eighteenth of Java's memory. If you are hitting 64 MiB you are generating a very large result set, and the fix is a LIMIT rather than more memory.

Running your code

Which file Run executes.

Project limits

Sizes and counts.

Was this page helpful?

Last checked against the product on . Behaviour changes are listed in the changelog.