# Changing tables

> Create, rename and delete tables and columns from midcode, see the exact SQL before it runs, and where the change is recorded in your project, Prisma included.

- Page: https://midcode.app/docs/data/schema
- From the midcode docs. Every page as Markdown: https://midcode.app/llms.txt
- Status: This ships with the next release of midcode. The version you can download today (1.1.2) doesn't have it yet.
- Beta: midcode labels this beta.

In [Database](https://midcode.app/docs/data/database.md) 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 project | What midcode does |
| --- | --- |
| Has no tool for its tables | Runs the SQL and writes it as a migration file |
| Uses Prisma | Edits `prisma/schema.prisma`, runs the SQL Prisma writes for that edit, and records it as a Prisma migration |
| Uses Drizzle, or Laravel, Django or Rails migrations | Doesn't run it: hands the change to your agent with what to do in that tool |

The connection has to allow changes: see [Read-only and Allow changes](https://midcode.app/docs/data/database.md). A change to the tables has no `⌘Z`.

## What you can change

| Change | Postgres | SQLite |
| --- | --- | --- |
| Create a table | Yes | Yes |
| Rename or delete a table | Yes | Yes |
| Add, rename or delete a column | Yes | Yes |
| Make a column required or optional | Yes | No |
| Change a column's default | Yes | No |

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:

| In the dialog | Postgres | SQLite |
| --- | --- | --- |
| Text | `text` | `text` |
| Number | `integer` | `integer` |
| Decimal number | `numeric` | `real` |
| Yes / No | `boolean` | `boolean` |
| Date | `date` | `date` |
| Date and time | `timestamptz` | `datetime` |
| JSON | `jsonb` | `json` |
| → another table | The type of that table's key, with `references` | The same |

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:

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

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

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

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

```sql title="db/migrations/20261005183000_create_messages.sql"
-- 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 project has | The folder |
| --- | --- |
| `supabase/migrations` or `supabase/config.toml` | `supabase/migrations/` |
| A SQLite database | `migrations/` beside the file: `data/database.db` → `data/migrations/` |
| `.sql` files in `migrations/`, `db/migrations/`, `sql/migrations/` or `database/migrations/` | That folder |
| None of these | `db/migrations/` |

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](https://midcode.app/docs/publish/publish.md), 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. midcode edits `prisma/schema.prisma`: only the lines that change, lined up with their neighbours.
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. The review shows that SQL and, under "And in `prisma/schema.prisma`", the lines that go and the lines that come.
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. `prisma generate` runs, so your code knows the new shape.

Adding an optional text column `subtitle` to `Post`:

```diff title="prisma/schema.prisma"
 model Post {
   id        Int      @id @default(autoincrement())
   title     String
   createdAt DateTime @default(now())
+  subtitle  String?
 }
```

```sql title="prisma/migrations/20261005183000_add_post_subtitle/migration.sql"
-- 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:

| Tool | Recognised by | What the brief tells the agent to run |
| --- | --- | --- |
| Drizzle | `drizzle.config.ts` (`.js`, `.mjs`) | Change the Drizzle schema, then `npx drizzle-kit generate && npx drizzle-kit migrate` |
| Laravel | `artisan` | `php artisan make:migration …`, write the change, `php artisan migrate` |
| Django | `manage.py` | Change `models.py`, then `makemigrations` and `migrate` |
| Rails | `bin/rails` | `bin/rails generate migration …`, write the change, `bin/rails db:migrate` |

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**:

```text
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](https://midcode.app/docs/data/sql.md), 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](https://midcode.app/docs/data/sql.md) 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.