Changing tables

In Database you can change the tables themselves: make a new one, add a column, rename or delete either. Every change goes through the same two steps. First you say what you want, in words (a name, the columns, their types). Then midcode shows the exact SQL it will run and the file where the change will be kept in your project. Nothing runs before you click Apply.

Where the change ends up depends on how your project defines its tables:

The connection has to allow changes: see Read-only and Allow changes. A change to the tables has no ⌘Z.

What you can change

SQLite can’t alter a column that exists, so those two aren’t offered there.

Make a change

A new table. Click the + beside Tables in the list (or New table in Overview). Give it a name, choose what each row’s id is, and list its columns:

• –

  Each row’s id: “A number that counts up” (1, 2, 3…) or “A random id” (a UUID). The column is always called id.

• –

  Each column has a name, a type, a default and Required. The form starts with one empty column and a created_at that fills itself with the moment the row is made. Rows left without a name are ignored.

A column. Open the table, then ⋯ → Add column…, or Add column in the Structure tab. Besides name, type and default, a new column can be Required and No two rows the same (unique). If the table already has rows, a required column needs a default: midcode says so.

Everything else. In Structure, the ⋯ at the end of a column’s row has Rename…, Make it required or Let it be empty, Change the default… and Delete column…. The table’s own ⋯ has Rename table… and Delete table…. A primary key column can be renamed, not deleted or altered.

Then Continue shows the review: the SQL, the file it will be kept in, and, on a database that isn’t on this Mac, “the change is real as soon as it’s applied”. Back returns to the form. Apply runs it. A change with nothing to fill in (required or optional, a deletion) goes straight to the review. A deletion says what is lost (“Its rows go with it, and there’s no undo.”) and its button is a red Delete.

Column types

The dialog offers types in words. Each engine gets its own:

A link (“→ orders”) is offered for every table in the same schema that has a one-column primary key. It makes a foreign key to that key.

Defaults are asked the way the type is answered: Today or Now for dates, Yes or No, or a value you type, which midcode checks (a number has to be a number, JSON has to parse) and quotes. Table and column names are always written in quotes, so a table called Order or a column called user is fine. A name can be up to 63 characters.

The SQL it runs

A table called messages, with the id that counts up, on Postgres:

create table "public"."messages" (
  "id" bigint generated by default as identity primary key,
  "name" text not null,
  "email" text not null,
  "body" text,
  "created_at" timestamptz not null default now()
);

The same on SQLite:

create table "messages" (
  "id" integer primary key,
  "name" text not null,
  "email" text not null,
  "body" text,
  "created_at" datetime not null default current_timestamp
);

With “A random id”, the key is "id" uuid primary key default gen_random_uuid() on Postgres and "id" text primary key default (lower(hex(randomblob(16)))) on SQLite.

The other changes are one statement each:

alter table "public"."messages" add column "read" boolean not null default false;
alter table "public"."orders" add column "customer_id" bigint not null references "public"."customers" ("id");
alter table "public"."messages" rename column "body" to "text";
alter table "public"."messages" alter column "body" set not null;
alter table "public"."messages" drop column "body";
alter table "public"."messages" rename to "inbox";
drop table "public"."messages";

On SQLite a unique column added to an existing table is two statements, because SQLite takes unique only when a table is made:

alter table "messages" add column "slug" text;
create unique index "messages_slug_key" on "messages" ("slug");

The statements of one change run as one transaction.

The migration file

After the SQL runs, midcode writes it into your project, so the code says what the database is and another copy of it can be brought to the same state.

-- Create the table “messages” with an “id” key (a number that counts up) and the columns “name” (text, required), “email” (text, required), “body” (text, optional), “created_at” (datetime, required, default now)

create table "public"."messages" (
  "id" bigint generated by default as identity primary key,
  "name" text not null,
  "email" text not null,
  "body" text,
  "created_at" timestamptz not null default now()
);

Where it goes:

The name follows the files already there. If they are numbered (0004_add_orders.sql), the new one is the next number with the same width (0005_create_messages.sql). Otherwise it’s the time, in UTC, as Supabase’s CLI and most tools write it: 20261005183000_create_messages.sql. The second part says what the change is: create_messages, add_messages_read, rename_messages_body_to_text, drop_messages.

In a Supabase project, if the database has Supabase’s own supabase_migrations.schema_migrations table, midcode adds the migration to it in the same transaction, so a later supabase db push doesn’t run it again.

The file shows in Publish, as “Database:” and its name. It is not in ⌘Z on purpose: taking the file back would not take the change out of the database. midcode never overwrites a migration file that exists.

Projects with Prisma

If the project has prisma/schema.prisma, the change is made the way Prisma means it to be made.

1. 1

   midcode edits prisma/schema.prisma: only the lines that change, lined up with their neighbours.

2. 2

   It asks the project’s own Prisma CLI for the SQL between the schema as it was and as it is now (prisma migrate diff, from file to file). No database is asked anything.

3. 3

   The review shows that SQL and, under “And in prisma/schema.prisma”, the lines that go and the lines that come.

4. 4

   Apply runs the SQL in one transaction, on the connection that’s open. If the project has a prisma/migrations folder, midcode also writes prisma/migrations/<time>_<name>/migration.sql and, in the same transaction, adds it to _prisma_migrations with the checksum Prisma checks. To Prisma it’s a migration like any other, already applied.

5. 5

   prisma generate runs, so your code knows the new shape.

Adding an optional text column subtitle to Post:

 model Post {   id        Int      @id @default(autoincrement())   title     String   createdAt DateTime @default(now())+  subtitle  String? }

-- AlterTable
ALTER TABLE "Post" ADD COLUMN     "subtitle" TEXT;

The SQL is Prisma’s own, in Prisma’s own style. midcode never runs prisma db push, prisma migrate dev or prisma migrate deploy: those compare the whole database with the whole schema, or apply every pending migration, not this one change.

Three things are worth knowing:

• –

  Renaming a column adds @map("new_name") to the field and runs midcode’s own rename column, because Prisma would write it as a column dropped and another added. Your code keeps calling the field what it did.

• –

  A link to another table is three lines: the column, the relation, and the other model’s side of it. Deleting that column, or that table, takes all three.

• –

  Without a prisma/migrations folder (a project that uses db push), only the schema and the database change.

The files are written before the SQL runs and put back as they were if the database refuses it. If the client can’t be generated, midcode says: “Run: npx prisma generate”.

What Prisma changes are handed to the agent

When a change isn’t one clean edit of the schema, midcode doesn’t make it. The review says why and shows the text for your agent instead. That happens for:

• –

  renaming a table (Prisma names its key and indexes after it),

• –

  renaming or deleting a column that is the table’s key, part of a key or an index, or that another table links to,

• –

  renaming a column that has a unique index, or one that is a link,

• –

  making a link column required or optional,

• –

  a table or a column that isn’t in the Prisma schema (it was made another way),

• –

  a second link between the same two models, or a table that links to itself (both need named relations),

• –

  deleting a table another model still points at,

• –

  a schema split in several files, or anywhere other than prisma/schema.prisma,

• –

  a project with Prisma migrations on SQLite,

• –

  a database whose migration history isn’t where the project’s is: a migration not applied yet (midcode names it and says to run npx prisma migrate deploy first), one that failed halfway, or no _prisma_migrations table at all,

• –

  a change for which Prisma would drop something you didn’t ask to drop,

• –

  Prisma not installed in the project yet.

Projects with another tool

midcode recognises these by their files:

In these projects the tool’s files are the truth. SQL run behind its back would leave the tool and the database disagreeing, so midcode refuses to run the change even if asked. The review step shows the brief, with Copy and Send to the agent:

Change this project's database: Add the column “subtitle” (text, optional) to the table “posts”.

The project defines its tables with Drizzle, so don't run SQL against the database directly: Drizzle and the database would stop agreeing.
Make the change in the Drizzle schema (the file drizzle.config points at), then run: npx drizzle-kit generate && npx drizzle-kit migrate (or npx drizzle-kit push, if the project has no migrations folder)

For reference, the change as SQL (Postgres):
alter table "public"."posts" add column "subtitle" text;

One exception: the database midcode created for you (data/database.db) is always changed directly, whatever else the project has.

Changing rows, and running your own SQL in the SQL editor, work the same in every project. Only changes made through these dialogs are held back.

Limits

• –

  Changing tables is in beta and not in the released version yet.

• –

  Not possible yet from these dialogs: changing a column’s type, creating or dropping indexes, a key of more than one column. Use the SQL editor or your agent.

• –

  SQLite can’t change whether an existing column is required, or its default, and can’t add a column that defaults to the current time to a table that exists.

• –

  Drizzle is recognised and handed to the agent; midcode doesn’t edit a Drizzle schema.

• –

  Rows you changed in a table and hadn’t saved are discarded when that table’s columns change.

• –

  If the migration file can’t be written after the SQL ran, the database still has the change: midcode says “Done.” without a file.

• –

  The Prisma path was checked with Prisma 5.22 and 7.6 (migrate status, migrate diff against the database and migrate dev all agree afterwards). Other versions follow the same commands and weren’t each tried.