# Migrating to Unbase from Neon, Supabase, and other databases

This guide walks through replacing a Postgres-style database (Neon, Supabase,
RDS, Railway, PlanetScale, Turso, …) with an Unbase **project**.

An Unbase project is a **zero-config SQLite database over HTTP**. There's no
connection string, no driver, no pool, no server to size — you `POST` SQL to a
URL with a bearer token and get JSON rows back. That difference is most of the
migration.

Before you start, be clear on what Unbase replaces:

| You're replacing… | Unbase covers it? |
| --- | --- |
| Tables, SQL queries, transactions, migrations | ✅ Yes |
| A connection string + driver (`pg`, `@neondatabase/serverless`, Prisma, Drizzle over HTTP) | ✅ Yes — swap it for `fetch` (or the MCP server) |
| Postgres-specific SQL (types, `SERIAL`, extensions, `pgvector`, stored procedures) | ⚠️ Translate to SQLite (see [SQL dialect](#4-translate-the-schema-postgres--sqlite)) |
| Supabase **Auth** (GoTrue) — email/password, magic link, JWT sessions | ✅ Yes — every project has a built-in [Auth service](#3-replace-supabase-auth-with-unbase-auth) |
| Supabase **Storage** (S3), **Realtime**, **Edge Functions**, Row-Level Security | ❌ No — Unbase is the database + auth, not the whole platform. Keep or replace those separately. |

If your app is "Postgres tables + queries" (optionally plus Supabase-style
sign-in), this is a clean swap. If it leans heavily on Supabase's
storage/realtime/edge functions, Unbase replaces the **database and auth**
halves; you'll keep the rest (or move it elsewhere).

---

## 1. Get an Unbase project

Grab one however you like — all three give you a `url`, a `token` (the secret
key), and an `anonKey`:

- **Landing page:** open [unbase.dev](https://unbase.dev), click *Create a
  project*, and copy the `UNBASE_DB_URL` / `UNBASE_DB_KEY` pair.
- **API:**

  ```bash
  curl -X POST https://api.unbase.dev/v1/projects
  # => { "projectId": "unbase_...", "url": "https://api.unbase.dev/v1/projects/unbase_...",
  #      "token": "unbase_....<sig>", "anonKey": "unbase_....pk.<sig>" }
  ```

- **Dashboard (multiple projects):** log in at
  [unbase.dev/login](https://unbase.dev/login) and create a named project —
  rename it anytime from the project header, and find its Auth service
  credentials (anon key + JWT secret) under **Settings → Auth**.

The `token` is your project's **secret key** — the whole data-plane credential,
with full read/write access and no separate API-key step. Keep it in server-side
env, never in client code. (The `anonKey` is a separate, publishable key that
only unlocks the project's Auth endpoints — see §3.) Anonymous projects expire
after 7 days; [claim one with your email](./quickstart.md#4-dont-lose-it) (or
create it from a logged-in account) to keep it.

Put the two values in your environment, replacing your old `DATABASE_URL` (the
env var **names** are unchanged — the URL just points at a project path now):

```bash
# .env  — before
DATABASE_URL=postgres://user:pass@ep-cool-name.us-east-2.aws.neon.tech/neondb

# .env  — after
UNBASE_DB_URL=https://api.unbase.dev/v1/projects/unbase_xxxxxxxxxxxx
UNBASE_DB_KEY=unbase_xxxxxxxxxxxx.<signature>
```

---

## 2. Swap the client for one `query()` helper

Every driver call becomes a `POST` to `${UNBASE_DB_URL}/query` with
`{ sql, params }`. Positional `?` placeholders are passed in `params` and the
response is `{ rows, rowsRead, rowsWritten }`.

Drop this helper in and delete the driver/pool:

```js
const URL = process.env.UNBASE_DB_URL;
const KEY = process.env.UNBASE_DB_KEY;

export async function query(sql, params = []) {
  const res = await fetch(`${URL}/query`, {
    method: "POST",
    headers: {
      authorization: `Bearer ${KEY}`,
      "content-type": "application/json",
    },
    body: JSON.stringify({ sql, params }),
  });
  if (!res.ok) {
    const { error } = await res.json().catch(() => ({}));
    throw new Error(error?.message ?? `Unbase query failed (${res.status})`);
  }
  const { rows } = await res.json();
  return rows;
}
```

### Neon (`@neondatabase/serverless`)

```js
// before
import { neon } from "@neondatabase/serverless";
const sql = neon(process.env.DATABASE_URL);
const users = await sql`SELECT * FROM users WHERE id = ${id}`;

// after
const users = await query("SELECT * FROM users WHERE id = ?", [id]);
```

Neon's tagged-template already uses parameters — move the interpolated values
into the `params` array and switch `$1`/tagged holes to `?`.

### node-postgres (`pg`) / `postgres.js`

```js
// before
const { rows } = await pool.query("SELECT * FROM users WHERE email = $1", [email]);

// after
const rows = await query("SELECT * FROM users WHERE email = ?", [email]);
```

Postgres uses `$1, $2, …`; Unbase (SQLite) uses `?` in positional order. There's
no pool to create or `end()` — HTTP is the transport.

### Supabase client

Supabase's query builder maps to plain SQL:

```js
// before
const { data, error } = await supabase
  .from("posts")
  .select("id, title")
  .eq("author_id", authorId)
  .order("created_at", { ascending: false })
  .limit(10);

// after
const data = await query(
  "SELECT id, title FROM posts WHERE author_id = ? ORDER BY created_at DESC LIMIT 10",
  [authorId]
);
```

`.insert()` / `.update()` / `.delete()` become the corresponding SQL. If you
relied on `.select()` returning the affected row, use SQLite's `RETURNING`:

```js
const [post] = await query(
  "INSERT INTO posts (author_id, title) VALUES (?, ?) RETURNING *",
  [authorId, title]
);
```

> **Supabase Storage / Realtime / Edge Functions / RLS have no Unbase
> equivalent** — keep Supabase (or another provider) for those, or replace them
> yourself. **Auth *does* now have one** (see §3). Note there is no per-row
> policy engine: the secret key is a single full-access credential, so
> table-level authorization stays in *your* server — treat the secret key like
> any private Postgres password you'd never expose to the browser.

### ORMs (Prisma, Drizzle, Kysely)

Unbase speaks SQL over HTTP, not the Postgres wire protocol, so Postgres ORM
drivers won't connect directly. Options:

- **Drizzle / Kysely:** use their **SQLite** dialect for query building and route
  the generated SQL through the `query()` helper (a thin custom driver). You keep
  the type-safe builder; only execution changes.
- **Prisma:** point the schema at `provider = "sqlite"` for local codegen, but run
  statements through the helper — Prisma has no built-in Unbase adapter.
- **Simplest:** for most apps, the `query()` helper plus plain SQL is less code
  than an ORM and maps 1:1 to what you already write.

---

## 3. Replace Supabase Auth with Unbase Auth

If you were using Supabase Auth (GoTrue) for sign-up/sign-in, every Unbase
project ships an equivalent, Supabase-style **Auth service** at
`${UNBASE_DB_URL}/auth`. It authenticates the end users of *your* app and hands
them JWT sessions — no separate service to run.

Two keys, two audiences:

- The **anon key** (`unbase_....pk.<sig>`, the `anonKey` from §1) is publishable —
  ship it in your browser/mobile client. Send it in an `apikey` header to reach
  the Auth endpoints. It cannot read or write your tables.
- The **secret key** (your `UNBASE_DB_KEY`) stays server-side and additionally
  unlocks the admin Auth endpoints.

Client-side, the shape mirrors `supabase.auth`:

```js
// before (Supabase)
const { data } = await supabase.auth.signInWithPassword({ email, password });

// after (Unbase Auth) — anon key goes in the apikey header
const res = await fetch(`${UNBASE_DB_URL}/auth/signin`, {
  method: "POST",
  headers: { "content-type": "application/json", apikey: UNBASE_ANON_KEY },
  body: JSON.stringify({ email, password }),
});
const session = await res.json();
// { accessToken, tokenType: "bearer", expiresIn, expiresAt, refreshToken, user }
```

- `POST .../auth/signup { email, password }` — register (password min 8 chars), returns a Session.
- `POST .../auth/signin { email, password }` — returns a Session.
- `POST .../auth/magiclink { email, redirectTo? }` — emails a passwordless link; your app then calls `POST .../auth/verify { token }` to get a Session (the user is created on first verify).
- `POST .../auth/token { refreshToken }` — rotate an expired access token (single-use refresh).
- `POST .../auth/logout { refreshToken }` — sign out.
- `POST .../auth/user` — with `Authorization: Bearer <accessToken>` + `apikey`, returns `{ user }`.

The `accessToken` is a standard **HS256 JWT signed with your project's own JWT
secret**, so you verify end-user tokens directly in your backend — the same
place you'd have verified a Supabase JWT. Claims are
`{ sub: userId, iss: projectId, role: "authenticated", email, iat, exp }`. Fetch
the signing secret (admin, **secret key only**) from `GET .../auth/settings`, and
list users with `GET .../auth/users`. Row-Level Security has no equivalent —
enforce per-user access in your own server using the verified `sub` claim.

---

## 4. Translate the schema (Postgres → SQLite)

Unbase is SQLite. Most DDL is identical; a handful of Postgres-isms need a
translation. Common ones:

| Postgres | SQLite / Unbase |
| --- | --- |
| `SERIAL` / `BIGSERIAL PRIMARY KEY` | `INTEGER PRIMARY KEY` (auto-increments) |
| `uuid` + `gen_random_uuid()` | `TEXT` with a UUID you generate in app code |
| `BOOLEAN` (`true`/`false`) | `INTEGER` (`1`/`0`) |
| `TIMESTAMPTZ` / `now()` | `TEXT` ISO-8601, or `INTEGER` epoch; default `CURRENT_TIMESTAMP` |
| `JSONB` | `TEXT` (store `JSON.stringify`; query with `json_extract(col, '$.k')`) |
| `TEXT[]` / arrays | a JSON `TEXT` column, or a child table |
| `NUMERIC(10,2)` | `REAL` (or integer cents to avoid float rounding) |
| `ENUM` types | `TEXT` + a `CHECK (col IN ('a','b'))` constraint |
| `ILIKE` | `LIKE` (SQLite `LIKE` is case-insensitive for ASCII) |
| Extensions (`pgvector`, `postgis`, `pg_trgm`) | Not available — keep those workloads on Postgres |

Example:

```sql
-- Postgres
CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT UNIQUE NOT NULL,
  is_admin   BOOLEAN DEFAULT false,
  metadata   JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Unbase (SQLite)
CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  email      TEXT UNIQUE NOT NULL,
  is_admin   INTEGER DEFAULT 0,
  metadata   TEXT,                       -- JSON string
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
```

Run migrations by sending the whole script to `/query` — multiple
`;`-separated statements execute together:

```bash
curl -X POST "$UNBASE_DB_URL/query" \
  -H "Authorization: Bearer $UNBASE_DB_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql": "CREATE TABLE users (...); CREATE INDEX users_email ON users(email);"}'
```

Or paste them into the **SQL editor** in the dashboard. For a set of statements
that must be **all-or-nothing**, use the batch endpoint (next section).

---

## 5. Move your data

1. **Dump from the source.** From Postgres, export the data you want as rows.
   `pg_dump --data-only --inserts` gets you `INSERT` statements; a CSV export
   works too. (Postgres `INSERT`s often need light editing — `true`/`false` →
   `1`/`0`, `now()` → literal timestamps, `$$`-quoting removed.)

2. **Load in atomic batches.** Send inserts to the transactional batch endpoint
   so a partial failure rolls the whole batch back:

   ```js
   await fetch(`${URL}/batch`, {
     method: "POST",
     headers: { authorization: `Bearer ${KEY}`, "content-type": "application/json" },
     body: JSON.stringify({
       statements: [
         { sql: "INSERT INTO users (email, is_admin) VALUES (?, ?)", params: ["a@x.com", 0] },
         { sql: "INSERT INTO users (email, is_admin) VALUES (?, ?)", params: ["b@x.com", 1] },
       ],
     }),
   });
   ```

   Chunk large tables into batches of a few hundred–thousand rows to stay under
   the request/response size caps, and always use `params` rather than building
   SQL strings.

3. **Verify.** `GET /v1/projects/:id/tables` lists your tables with row counts and
   columns, and `GET /v1/projects/:id/usage` shows storage and monthly usage — a
   quick way to confirm the load landed.

---

## 6. Give your AI agent direct access (optional)

If you're moving to Unbase partly to hand your project to an AI agent (Cursor,
Claude, etc.), add the MCP server instead of wiring HTTP calls yourself:

```json
{
  "mcpServers": {
    "unbase": {
      "command": "npx",
      "args": ["-y", "@unbase-mcp/server"],
      "env": {
        "UNBASE_DB_URL": "https://api.unbase.dev/v1/projects/unbase_xxxxxxxxxxxx",
        "UNBASE_DB_KEY": "unbase_xxxxxxxxxxxx.<signature>"
      }
    }
  }
}
```

The agent gets `query` and `get_usage` tools scoped to that one project. The
`@unbase-mcp/server` package also supports a generic, multi-project mode
(create and address any project by id/token) when you omit those env vars.

---

## 7. Back up and stay portable

Unbase never locks your data in:

- **Export** a full, replayable SQL dump any time:

  ```bash
  curl -H "Authorization: Bearer $UNBASE_DB_KEY" \
    "$UNBASE_DB_URL/export" -o backup.sql
  # restore anywhere: sqlite3 restored.db < backup.sql
  ```

- **Delete** a project outright with `DELETE /v1/projects/:id`.

Because the dump is standard SQL, migrating *off* Unbase later is the same
exercise in reverse.

---

## Gotchas checklist

- **Placeholders:** Postgres `$1` → SQLite `?` (positional order matters).
- **Booleans:** `true`/`false` → `1`/`0`.
- **Auto-increment:** `SERIAL` → `INTEGER PRIMARY KEY` (don't insert the id).
- **Timestamps:** store ISO-8601 `TEXT` or epoch `INTEGER`; default with
  `CURRENT_TIMESTAMP`.
- **JSON:** store as `TEXT`; read with `json_extract(col, '$.path')`.
- **Two keys:** the **secret key** is full-access (keep it server-side); the
  **anon key** is publishable and only reaches the Auth endpoints. Table-level
  authorization is yours to enforce — there's no RLS.
- **No Postgres extensions:** `pgvector`, `postgis`, full-text via `pg_trgm`,
  etc. don't exist in SQLite; keep those workloads elsewhere.
- **Size/rate limits:** responses are capped (~2 MB) and plans meter rows/storage
  — page large reads and chunk large writes.

## See also

- [`quickstart.md`](./quickstart.md) — five-minute intro.
- [`llms.txt`](./llms.txt) — dense API reference for feeding to an agent.
- [`openapi.yaml`](./openapi.yaml) — full OpenAPI spec.