Database

Database is the place in midcode for what your site stores while it runs: users, orders, messages. It finds the database your project already points at, shows its tables as tables, lets you change rows, run SQL, change the tables themselves, and see who signs in and which environment variables the site reads.

Three rules hold everywhere in it:

• –

  Your connection string stays in midcode’s main process. The window you look at only learns where a database is (its host and name, or its file), never how to get in. It isn’t put in reports, and it isn’t given to your agent. See Privacy and security.

• –

  A database that isn’t on this Mac opens read-only until you turn on Allow changes. Read-only is enforced by the database engine, not by midcode reading your SQL.

• –

  What you change is changed in the database, for real. It isn’t a file, so there is no ⌘Z and nothing to publish. That’s why rows wait for Save, and why what can’t be taken back is asked about first.

This page covers finding and connecting a database, the overview, browsing tables and editing rows. The rest is in SQL, charts and backups, Changing tables, Sign-in and users and Environment variables.

Not sure this is what you need? What you write yourself (posts, FAQs, a team list) is the CMS, and publishes with the site. What your visitors create is the database.

Open it

Click Database in the middle of the top bar. The view takes the place of the canvas; Esc goes back.

The left side is one list of places, with no tabs: Overview, Tables (each table), SQL (the SQL editor and your saved queries), Sign-in (People, Ways to sign in, Protected pages) and Site (Environment variables). Above the list are the connection, its engine and address, and the Allow changes switch. The view opens where you left it, and on Overview the first time.

Which databases midcode finds

midcode looks where your project’s own code looks. It reads these files, in this order: .env.local, .env.development.local, .env.development, .env, .env.production.local, .env.production.

${OTHER} references inside an .env file are filled in from the same file. A file only counts as SQLite if it starts with SQLite’s own header.

Each one found is a connection in the Connection menu, named after its variable or its file. With several, midcode opens the one you picked last, else the first one on this Mac.

Engines

Connect one by hand

If the project has no connection string (a Supabase site that only uses its API keys, for example), give midcode one:

1. 1

   In Overview, click Connect one I have. With a database already open, use the + beside the connection menu.

2. 2

   Paste the connection string. It starts with postgres:// or postgresql://. Where do I find it? says where it is in Supabase (Connect → Session pooler), Neon (Dashboard → Connect) and Vercel (Storage → your database → .env.local).

3. 3

   Click Connect. midcode tries the string before keeping it.

Supabase and Neon show the string with [YOUR-PASSWORD] in it: replace that with your database’s password, or midcode tells you the password is missing.

The string is kept encrypted on your Mac, in midcode’s own data, never in your project. It is never shown again: the field is a password field, and the window gets back only the host and database name. Remove connection…, in the ⋯ menu beside the connection, makes midcode forget it; the database isn’t touched.

sslmode in the string is read the way Postgres’ own tools read it: disable uses no TLS, require encrypts without checking the certificate, verify-ca and verify-full check it. Without it, a remote host is tried with TLS first and a local one without. Prisma’s ?schema= picks the schema that opens.

Only Postgres is connected this way. A SQLite file is found when it’s in one of the folders above.

Create a database

In a project with no database, Overview offers Create a database. It makes an empty SQLite file at data/database.db in your project and opens it. Nothing to install or start. Asking again gives you the same file.

It’s right for building and trying things. A file database works on your Mac and on a server with a disk; a site published on Vercel or Netlify needs a hosted database (Supabase, Neon).

Overview

Overview answers “is this connected to my site, and what’s next?”. It lists six things in order, each with a check when it’s there and the next step when it isn’t. Everything it says about your site comes from reading your code, not from running it.

Give it to the site

A connection you typed is known only to midcode: your site’s code doesn’t have it. Give it to the site writes it as DATABASE_URL into the project’s local env file (.env.local in Next.js, Vite, Astro, SvelteKit, Nuxt, Remix and React Router projects, .env otherwise):

DATABASE_URL=postgresql://app:secret@db.example.com:5432/shop

The string goes from the main process to that file and nowhere else. midcode reads the file back the way your site will, and removes the line again if it doesn’t give the same string. It refuses if DATABASE_URL already has a value, and if the file isn’t ignored by git (see Environment variables). From then on the connection is the project’s, and midcode’s own copy is deleted.

What the agent writes

midcode does not write the code that reads a database: it depends on how each project is built. Four buttons put the request in words for your agent instead, with what midcode already knows. Each shows the exact text before anything is sent, with Copy and Send to the agent.

• –

  Connect the site… asks for one module that opens the database (lib/db.ts or wherever the project keeps such things), with the client the project already has, server only.

• –

  Save to a table…, in Overview’s list of forms and in a form’s settings on the canvas, sends where the form is written, its field names, and the table you chose (with its columns) or the name of a new one. With no form yet, add one from Insert.

• –

  Show a table on a page…, here and as Show on a page… in a table’s ⋯ menu, asks you for the page, the shape (Cards, List or Table) and the columns, and sends the table’s columns and types.

• –

  Add sign-in is described in Sign-in and users.

The briefs say where the connection comes from (the variable’s name and the file it’s in, or the SQLite file’s path), never the string itself. What the agent changes from the Chat view is undoable and listed in Publish like any other edit. An agent in the Terminal view writes files by itself: they show in Publish as changed files, outside ⌘Z.

Read-only and Allow changes

Under the connection, midcode says Read-only or Changes allowed.

• –

  A database on this Mac starts with changes allowed. “On this Mac” means a SQLite file, or a Postgres host that is localhost, 127.0.0.1, ::1, a *.localhost name, host.docker.internal or a Unix socket.

• –

  Any other database starts read-only. Turning on Allow changes asks first: “This database isn’t on this Mac: it may be the one your site runs on. What you change here is changed for real, with no undo and nothing to publish.”

• –

  The choice lasts until you close midcode. Next time, a remote database is read-only again.

• –

  Without a license or a running trial, nothing can be changed, as with every other edit in midcode.

Read-only is the engine’s own. On Postgres every statement runs inside a READ ONLY transaction, so Postgres refuses an UPDATE, and also a function that writes. A SQLite file is opened read-only. Statements that would leave that transaction (COMMIT, BEGIN, SET TRANSACTION…) are refused before they run.

Browse a table

Click a table in the list. A view is listed too, with its own icon. The number beside each table is how many rows it has: counted in SQLite, Postgres’ own estimate in Postgres.

• –

  Rows shows 200 rows at a time, with the arrows for the previous and next 200. The count reads like “1–200 of 12,480”; a “≈” means counting took too long and the number is the engine’s estimate.

• –

  Click a column’s header to sort: ascending, descending, then back. Sorting and filtering are done by the database, over the whole table.

• –

  Filter adds a condition on a column: =, ≠, >, ≥, <, ≤, contains, is null, is not null. Several filters all have to hold. “contains” ignores case.

• –

  A value that points at another table has an arrow: it opens that table on the row it points at.

• –

  A long value, or JSON, is read whole in the strip under the grid when you select its cell. ⌘C copies the selected cell, and the arrow keys move between cells. Every key is in Keyboard shortcuts.

• –

  Structure lists the columns: type, whether empty is allowed, default, and keys. A foreign key is a link to its table.

• –

  Open as SQL puts a select * of the table, with limit 100, in the SQL editor, to go on from there.

With several schemas in a Postgres database, a Schema menu chooses whose tables are listed. Past 8 tables there is a search field.

Edit rows

Rows can be changed in a table (not a view), on a connection that allows changes.

• –

  Double-click a cell, press Enter, or start typing. A column that only takes some values (a boolean, an enum) opens a list. A long value or JSON is edited in the strip below, with Set to NULL, Cancel and Done.

• –

  New row adds a row at the top. A cell you leave alone shows “default” and takes the table’s default.

• –

  Click a row’s number to pick it (⇧ for a range), then Duplicate or Delete (⌫). A duplicate leaves out the key the database gives by itself.

• –

  Right-click a cell for Copy, Set to NULL, Use the default, Put back what it was, Duplicate row, Delete row and, on a row marked for deletion, Keep this row.

Nothing touches the database while you do this. Changes are staged: changed cells are tinted, new rows green, deleted rows red, and the table shows a dot in the list. A bar at the bottom says how many changes are unsaved, with Discard and Save (⌘S). Staged changes are kept per table while midcode is open, also if you leave the view.

What Save does

Save sends every change of that table as one transaction: deletes, then updates, then new rows. One statement per change:

update "public"."orders" set "status" = $1
where "id" = $2 and "status" is not distinct from $3

• –

  A row is found by its whole primary key.

• –

  An update also checks that each cell it changes still holds what the grid showed ($3 above).

• –

  Each statement must change exactly one row.

If any of that fails, it’s a conflict: someone else changed or removed the row since you read it. The whole transaction is rolled back, nothing is saved, and midcode says “A row isn’t what it was when you read it”. Read the table again and redo the change.

On a database that isn’t on this Mac, Save asks once more before it runs: “Once saved, there’s no undo.”

Tables you can’t fully edit

A lock beside the table’s name says which of these applies.

With your agent

The agent doesn’t see your database. With the crosshair of the agent’s prompt on, a click hands over exactly what you clicked: a table in the list gives its name, columns, types and keys (never its rows); a row in the grid gives that row’s values. Nothing else leaves the view.

Limits

• –

  Database is in beta and not in the released version yet.

• –

  Postgres and SQLite only. MySQL, Turso and MongoDB are recognised and not opened.

• –

  Table pages are 200 rows. Counting a table stops after 4 seconds and falls back to the estimate. A SQLite file over 200 MB isn’t counted table by table when it opens.

• –

  A statement may take 30 seconds; then it’s stopped.

• –

  Row changes have no undo, and conflicts are all or nothing: one conflicting row holds back the whole Save.

• –

  midcode doesn’t add data/database.db to .gitignore. A SQLite file inside your project is a file git sees, so Publish lists it when it changes. Decide whether it belongs in the repository.

• –

  Tried with Postgres 17 and SQLite, against databases made for testing. Not tried: a real Supabase project (its auth and storage schemas, its roles), partitioned tables, large databases.

Troubleshooting

“This connection is read-only. Turn on “Allow changes” first.” The database isn’t on this Mac, or changes were turned off. Turn the switch on, above the list.

“Nothing answers at that address (connection refused).” The database isn’t running, or the port is wrong. For a local Postgres, start it; for a hosted one, check the string.

A table of the site is missing. It may be in another schema: check the Schema menu. Read the tables again, beside the switch, reloads the list after something changed outside midcode.

The site and midcode show different data. They may point at different databases. Your site reads the env file of the mode it runs in; midcode lists every connection it finds in all of them and opens one. Check the name and the file of the connection in the menu.

More in Troubleshooting.