# 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.

Source: https://www.revquix.com/docs/projects/sql-projects
Last verified: 2026-09-03

---

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.

> **Warning** - SQLite 3.36 predates RIGHT JOIN and FULL OUTER JOIN
>
> Those arrived in SQLite 3.39. On 3.36 they are a syntax error. Rewrite a `RIGHT JOIN` as a
> `LEFT JOIN` with the tables swapped, and a `FULL OUTER JOIN` as two `LEFT JOIN`s combined with
> `UNION`.

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.

> **Note** - Two dot-commands worth putting at the top of every file
>
> `.headers on` and `.mode column` turn unlabelled pipe-separated output into a readable table. They
> are shell directives rather than SQL, so they only work here - which is exactly why they are worth
> knowing here.

```sql
.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.

> **Warning** - A file containing only a SELECT will fail
>
> There are no tables until your file creates them. The schema is part of what you write - `CREATE`
> and `INSERT` above the query, every time. That is a feature rather than a limitation: anyone opening
> your project gets exactly your result, because the data travels with the query.

## 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](https://www.revquix.com/docs/projects/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](https://www.revquix.com/docs/projects/running-your-code) - Which file Run executes.

- [Project limits](https://www.revquix.com/docs/projects/project-limits) - Sizes and counts.