SQL, charts and backups

The SQL editor runs what you write against the database open in Database, Postgres or SQLite. A script runs as one transaction, anything that can’t be taken back is asked about before it runs, and a query you name is saved as a file in your project. The same view exports a whole database to a file, loads one from a file, and deletes one.

Whatever SQL changes is changed in the database itself. There is no ⌘Z for it and nothing to publish. To change tables through a dialog that also records a migration, see Changing tables.

The SQL editor

Open Database in the top bar, then SQL editor in the list on the left.

• –

  The editor completes the real tables and columns of the open database. Once a statement names a table (from, join, update, into), that table’s columns are offered wherever a name can go. Tab accepts.

• –

  Run (⌘↵) runs what’s selected, or everything when nothing is. While it runs the button is Stop, which cancels the statement in the database.

• –

  Explain asks how the database would run the first statement, without running it (explain in Postgres, explain query plan in SQLite).

• –

  Drag the line between the editor and the result to resize them.

• –

  ⌘Z and ⇧⌘Z undo and redo what you typed in the editor, not what a statement did.

Each statement gives its own result. With several, a row of tabs names them (“1 · SELECT”, “2 · UPDATE”) and the last one that returned rows is shown. A statement with no rows says what it did: “3 rows changed”, or “done”.

Under a result: the number of rows, how long it took, and Table / Chart. Values are shown as the database prints them. In Postgres nothing is converted on the way, so a bigint, a numeric or a timestamp reads exactly as it is stored.

When a statement fails, the error is shown in the database’s own words and the cursor goes to the place it points at.

What you type in the unnamed editor is kept between sessions, so it’s a safe place to try things.

How a script runs

midcode splits the script into statements and runs them in order, in one transaction: all of it, or none. If the third statement fails, what the first two changed is undone, and the result says “Nothing was kept: what the statements before it changed was undone with it.”

Two kinds of script are not wrapped in a transaction:

• –

  one that has its own (BEGIN, COMMIT, ROLLBACK, SAVEPOINT…): it keeps to those,

• –

  one with a statement a database refuses inside a transaction: VACUUM, PRAGMA, ATTACH, REINDEX, CREATE DATABASE, anything CONCURRENTLY.

When one of those fails, the result says “The statements before it stay as they ran.”

On a read-only connection the script runs under the engine’s own read-only mode (see Read-only and Allow changes). A statement that changes something is refused by the database, and midcode says “This connection is read-only. Turn on “Allow changes” first.”

Two limits apply to every statement:

What’s asked first

On a connection that allows changes, midcode looks at each statement before running the script. These are asked about, all in one dialog:

The dialog is titled “Run this?” (or “Run these?”), quotes each statement, ends with “There’s no undo for it.”, and runs the script only if you click Run. Nothing runs while it’s open. A DELETE inside a WITH is caught too; an INSERT … ON CONFLICT DO UPDATE is not asked about.

Saved queries

Save with a name (⌘S) asks for a name and writes the query to your project:

select date_trunc('day', created_at) as day, count(*) as signups
from users
where created_at > now() - interval '7 days'
group by 1
order by 1;

The file is the SQL as you wrote it, nothing added. Its name is the name you gave, without the characters a file name can’t have, up to 80 characters. Saved queries are listed under SQL on the left; ⌘S in one saves it again, and a dot marks unsaved changes. Delete this query, on a query’s row, sends its file to the Trash.

Because they are files in the project, queries travel with the repository and your agent can read them. Commit .midcode/queries/ if you want to share them. See What midcode adds to your project.

Results as CSV

A result with rows has two buttons at the right of its footer: Copy as CSV and Export as CSV, which asks where to save. The first line is the column names. NULL is an empty field, and a value with a comma, a quote or a line break is quoted. It holds the rows on screen, so at most 1,000.

Charts

Click Chart under a result to draw it instead of listing it.

• –

  Value is the number that’s plotted; it lists the result’s numeric columns.

• –

  Along is the column the values are set against. Any column works.

• –

  Drawn as is Bars or Line. midcode starts with a line when the column along is a date, or a number with more than 12 rows, and with bars otherwise.

select date_trunc('month', created_at)::date as month, count(*) as orders
from orders
group by 1
order by 1;

Run this and switch to Chart: orders by month, as a line. Hover a point or a bar for its value. With more points than fit, the chart scrolls sideways. A result with no numeric column says “Nothing to chart: this result has no column of numbers.”

A chart is one measure against one column: one series, one axis. It’s a way to read a result, and isn’t saved with the query.

Export a database

The ⋯ beside the connection has Export…. It writes the whole database to a file you choose:

• –

  SQL script: a .sql file that makes the tables again and fills them. Untick With the rows for the tables alone.

• –

  Database file (SQLite only): a copy of the file itself, made with SQLite’s VACUUM INTO, so it is consistent even if your site is writing at that moment.

A Postgres export is one schema; with several, the dialog asks which. The file is written beside its destination as <name>.part and renamed when it’s complete, so a failed export never replaces a good backup.

What a Postgres script contains

Everything is read in one read-only snapshot, and every name is written with its schema. In this order: enum types, sequences, functions, tables, their rows, where each sequence left off, primary keys and other constraints, indexes, views and materialized views, triggers, row-level security and its policies. A function that takes or returns a table’s row is written after the tables.

-- db.example.com:5432/shop, schema "public" · Postgres · exported by midcode on 2026-10-05
-- What makes the tables, then their rows.
-- Not in this file: roles and what they may do, extensions, comments.
-- Load it in one transaction (midcode’s Import does; psql: --single-transaction), into a database that doesn’t have these tables yet.

SET LOCAL check_function_bodies = false;
CREATE SCHEMA IF NOT EXISTS "public";
CREATE TABLE "public"."orders" (
  "id" bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  "email" text NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL
);
INSERT INTO "public"."orders" ("id", "email", "created_at") VALUES
  ('1', 'ana@example.com', '2026-09-30 14:02:11.52+00');
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('"public"."orders"', 'id'), 1, true);
ALTER TABLE ONLY "public"."orders" ADD CONSTRAINT "orders_pkey" PRIMARY KEY (id);

Not in the file:

• –

  roles and grants,

• –

  extensions (the ones the database has are named in a comment; the database you load into needs the ones it uses),

• –

  comments on tables and columns,

• –

  partitioned and foreign tables (named in a comment),

• –

  anything that belongs to an extension,

• –

  other schemas: each schema is its own export.

The file’s first lines say the first three, so whoever loads it knows.

What a SQLite script contains

Each table’s CREATE TABLE exactly as SQLite keeps it, an INSERT per row with every value written by SQLite’s own quote(), where each AUTOINCREMENT left off, then indexes, views and triggers. It is wrapped the way sqlite3 .dump wraps it:

-- database.db · SQLite · exported by midcode on 2026-10-05
-- What makes the tables, then their rows.
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "messages" (
  "id" integer primary key,
  "name" text not null,
  "body" text
);
INSERT INTO "messages" ("id", "name", "body") VALUES (1, 'Ana', 'Hello');
COMMIT;

Generated columns are left out of the INSERTs, and a virtual table is written without its shadow tables.

Import a SQL file

Import a SQL file…, in the same menu, loads a .sql script into the open database. The connection has to allow changes.

1. 1

   Choose the file. midcode reads it and counts its statements. Nothing has run yet.

2. 2

   A dialog says what’s in it: how many statements of each kind (“CREATE × 4, INSERT × 120”) and how many of them delete something that’s there now.

3. 3

   Load it runs the file.

The file runs as one transaction: all of it or none. A script with its own BEGIN and COMMIT keeps to those, and one with a statement that can’t run inside a transaction runs statement by statement. If a statement fails, midcode shows its line, the statement and the error, and says whether anything was kept: “It wasn’t loaded: nothing was changed”, or “It stopped partway”.

A script exported by midcode expects a database that doesn’t have those tables yet. A .db file is not a script: put it in your project’s data folder and midcode finds it.

Delete a database

Delete database… only deletes a database that is a file inside your project.

• –

  The dialog says what goes: how many tables and rows, and which variable points at the file, so you know your site fails wherever it reads it.

• –

  You type the file’s name to confirm. Pasting is blocked, so it can’t be done by a slip.

• –

  The file goes to the Trash, with SQLite’s -wal, -shm and -journal files. It can be put back until the Trash is emptied.

• –

  Export a copy first opens the export dialog from there.

A database on a server isn’t deleted from midcode. The dialog says where to delete it (Supabase, Neon, or the tool that runs it on your Mac) and, for a connection you typed, offers Remove connection…, which only makes midcode forget the string. A SQLite file outside your project isn’t deleted either: delete it in Finder.

Limits

• –

  SQL in midcode is in beta and not in the released version yet.

• –

  What’s asked first is found by scanning the statement, not by parsing it. A WHERE anywhere in the statement, in a subquery too, counts as having one. Don’t lean on the dialog: read what you run.

• –

  One statement may return 1,000 rows and take 30 seconds. An export or an import may take 10 minutes. An import file may be 150 MB; load a bigger one with psql or sqlite3.

• –

  Explain covers the first statement only.

• –

  Charts are one series, bars or a line. No stacked or grouped series, no second axis, no export as an image.

• –

  A saved query can’t be renamed from the editor: rename its file.

• –

  Tried: a Postgres 17 database with an enum, serial and identity columns, a generated column, foreign keys, a check, a partial index, a view, functions (one returning rows of a table), a trigger and policies, exported, imported into another database and compared equal, with new ids continuing where they were. The same with SQLite, against sqlite3 .dump.

• –

  Not tried: a real Supabase project (its auth and storage schemas, its roles), partitioned tables (they are skipped), large databases.