# Connect to a Live Database

> A step-by-step guide to checking your ERD against a real PostgreSQL or MySQL database — from signing in to running the drift check in CI.

On **Pro**, Sqemo can read a real PostgreSQL or MySQL database — to import
its schema into an ERD, or to check whether the database has *drifted* from
the ERD you treat as the source of truth. Everything runs locally (or in
your CI job) through [sqemo-mcp](https://www.npmjs.com/package/sqemo-mcp);
your database credentials are never sent to Sqemo.

This guide walks the whole flow end to end. Follow it top to bottom the
first time.

## What you need

- **Node.js 22+** (`node --version` to check).
- A **Pro** Sqemo account (or a Team workspace member). On the Free plan the
  database commands stop with an upgrade notice before connecting.
- A database to check — or none at all: [see the Docker option below](#no-database-yet-use-docker)
  to spin one up in a couple of minutes.

## Step 1 — Sign in from the terminal

The database commands need you signed in. Sign in once:

```bash
npx sqemo-mcp login
#   1) Google           (opens your browser)
#   2) GitHub           (opens your browser)
#   3) Email + password (right here)
```

Pick 1 or 2 and a browser tab opens; sign in there and the terminal picks it
up. Pick 3 to type an email and password without leaving the terminal.
However you signed up — Google, GitHub, or email — one of these works.

Either way this stores a refresh token in `~/.erdmaker/credentials.json` (your
password is never stored). `npx sqemo-mcp logout` removes it.

### On a machine with no browser

Options 1 and 2 need a browser on the same machine, because the callback
comes back to `127.0.0.1`. Over SSH or in a container, use the password path
directly:

```bash
npx sqemo-mcp login --password
```

For automation, skip `login` entirely and set `SQEMO_EMAIL` / `SQEMO_PASSWORD`
as shown in the [CI section below](#put-it-in-ci-github-actions-step-by-step).

Scripts can also name a provider and skip the menu:
`npx sqemo-mcp login --provider github`.

## Step 2 — Get your ERD as a baseline

Drift is *ERD vs database*, so you need the ERD side. Two ways:

- **Server ERD** (saved to your account): use `--erd <id>`. The id comes
  from the `list_erds` MCP tool, or the app URL when the ERD is open.
- **Local project** (lives only in your browser): export it to a file first.
  In the app: **toolbar → Export → Download project**. You get a
  `<name>.erd.json` file (usually in your Downloads folder). Commit this file
  to your repo if you'll run the check in CI — it becomes your schema's
  source of truth.

## Step 3 — Run the drift check

Point `lint` at your ERD and your database:

```bash
# local project file
npx sqemo-mcp lint "Online Shop Example.erd.json" --db "mysql://user:pass@host:3306/dbname"

# a server ERD instead of a file
npx sqemo-mcp lint --erd <erd-id> --db "postgresql://user:pass@host:5432/dbname"
```

Connection URL formats:

```
PostgreSQL: postgresql://user:pass@host:5432/dbname
MySQL:      mysql://user:pass@host:3306/dbname
```

### Which databases are supported?

The rule of thumb: **a live `--db` connection works with PostgreSQL and MySQL
(including MariaDB) only.** Every other dialect is still supported for
*exporting/importing* SQL, and you can drift-check it through the
[`--schema` dump path](#step-3--run-the-drift-check) — you just can't connect
to it live.

| Database | ERD export/import | Live `--db` | Dump `--schema --dialect` |
|----------|:---:|:---:|:---:|
| PostgreSQL | ✓ | ✓ | ✓ `postgres` |
| MySQL / MariaDB | ✓ | ✓ (`mysql://`) | ✓ `mysql` |
| Oracle | ✓ | — | ✓* `oracle` |
| SQL Server | ✓ | — | ✓* `sqlserver` |
| SQLite | ✓ | — | ✓* `sqlite` |
| H2 | ✓ | — | ✓* `h2` |
| CUBRID | ✓ | — | ✓* `cubrid` |

\* Parser exists but that dialect isn't in the officially tested set, so
results may vary with the dump's exact format.

Why only PostgreSQL and MySQL connect live: both ship **pure-JavaScript
drivers** (nothing to install beyond `npx`) and share the standard
`information_schema`, so one introspection path covers both. The others each
need a different driver and catalog — Oracle wants its Instant Client and its
own data dictionary, SQLite is a file rather than a server, H2 is
Java/JDBC-only, and so on — so for now they go through the dump path.

**MariaDB note:** connect with the `mysql://` scheme (not `mariadb://`, which
isn't recognised) since it speaks the MySQL protocol; use `--dialect mysql`
for dump mode. MariaDB-specific column types may surface as *warnings* (which
pass by default) rather than errors.

Useful options:

- `--db-schema <name>` — schema to read (PostgreSQL defaults to `public`;
  MySQL reads the database in the URL).
- `--ignore "flyway_*"` — skip tables that live in the DB but not the ERD
  (migration-history, audit tables). Repeatable.
- `--strict` — treat type/representation differences (normally warnings) as
  failures too.
- `--schema dump.sql --dialect postgres` — compare against a `pg_dump -s`
  file **instead of** connecting, so no database URL is needed at all.

## Step 4 — Read the result

The command's **exit code** is the answer (`echo $LASTEXITCODE` on
PowerShell, `echo $?` on bash):

| Exit | Meaning |
|------|---------|
| **0** | ERD and database match. |
| **1** | Drift found. Each item is listed, e.g. `[missing_column] CUST.EMAIL`, `[missing_table] ORDER_ITEM`, plus a summary line. |
| **2** | Couldn't compare yet — bad connection, wrong/empty schema, not signed in, Free plan, or a usage error. Passwords in the URL are masked as `:****@` in messages. |

Note that an **empty database** (zero tables in the schema) reports exit **2**,
not "everything is missing" — that almost always means the schema name or
database is pointed at the wrong place, so the tool asks you to check rather
than drowning you in false positives.

## No database yet? Use Docker

You don't need a hosted database to try this. Spin one up, load your ERD's
schema into it, and check:

```bash
# 1) start a throwaway MySQL
docker run -d --name sqemo-my -e MYSQL_ROOT_PASSWORD=pw -e MYSQL_DATABASE=app -p 13306:3306 mysql:8
# wait ~30s for it to initialise

# 2) turn your ERD into SQL and load it
npx sqemo-mcp export "Online Shop Example.erd.json" --format sql --dialect mysql > shop.sql
docker exec -i sqemo-my mysql -uroot -ppw app < shop.sql

# 3) check — matches, so exit 0
npx sqemo-mcp lint "Online Shop Example.erd.json" --db "mysql://root:pw@localhost:13306/app"

# 4) change the DB, then re-check — now exit 1 with the difference
docker exec sqemo-my mysql -uroot -ppw app -e "ALTER TABLE CUST DROP COLUMN EMAIL;"
npx sqemo-mcp lint "Online Shop Example.erd.json" --db "mysql://root:pw@localhost:13306/app"

# 5) clean up
docker rm -f sqemo-my
```

Swap `mysql:8` / the `mysql://` URL for `postgres:16` /
`postgresql://postgres:pw@localhost:15432/app` (port `-p 15432:5432`) to try
PostgreSQL. Tip: match `--dialect` to the database — a schema exported for the
wrong engine may use types the other one doesn't have.

## Where do the database credentials live?

Sqemo never stores them — you supply the URL at run time. Keep it out of the
ERD file and out of version control:

- **Local, occasional runs:** an environment variable or a `.env` file that's
  in `.gitignore`. Avoid typing the password straight on the command line
  (it lands in your shell history).
- **MCP client (agent):** put it in the client's server config as an `env`
  value (e.g. Claude Desktop's `claude_desktop_config.json`), which stays on
  your machine.
- **CI:** your platform's secret store — GitHub Actions Secrets, GitLab CI
  variables, etc. Never in the workflow file.

**Strongly recommended:** give the tool a **read-only database account**.
It only queries `information_schema` / the catalog, so a read-only account
loses nothing — and if the secret ever leaks, the blast radius is limited to
schema structure, not your data.

## Put it in CI (GitHub Actions, step by step)

The point of drift checking is catching it automatically — every push, every
pull request — instead of remembering to run it by hand. Here's the whole
setup from scratch, no prior GitHub Actions experience assumed.

### 1. Commit your ERD to the repo

Export the project (**toolbar → Export → Download project**) and put the
`.erd.json` in your repository, e.g. `erd/app.erd.json`, then commit and push
it. This file is now your schema's source of truth — changes to it go through
pull-request review like any code.

### 2. Add the secrets

Secrets are encrypted values GitHub injects into the job; they never appear in
your code or logs. In your repository on GitHub:

**Settings → Secrets and variables → Actions → New repository secret.** Add
three:

| Name | Value |
|------|-------|
| `DATABASE_URL` | your connection string, e.g. `mysql://user:pass@host:3306/db` (a **read-only** account is best) |
| `SQEMO_EMAIL` | your Sqemo Pro account email |
| `SQEMO_PASSWORD` | that account's password ([no password yet?](#signed-up-with-google-or-github-set-one-password-for-ci)) |

#### Signed up with Google or GitHub? Set one password for CI

Interactive sign-in can use your browser, but a CI job has no browser — so it
needs an email and password. If you only ever clicked *Continue with Google* /
*GitHub*, your account has no password yet. Add one once; this does **not**
remove social login, it just adds a second way in:

1. Open **[app.sqemo.com](https://app.sqemo.com)** and open the sign-in dialog.
2. Click **Forgot your password? / Reset**.
3. Enter **the same email** your Google/GitHub account uses and send the link.
4. Open the link from your inbox and set a password.
5. Use that email and password as the two secrets above. You can still sign in
   with Google/GitHub everywhere else, including `npx sqemo-mcp login`.

### 3. Add the workflow file

Create **`.github/workflows/schema-drift.yml`** in your repo with this content:

```yaml
name: Schema drift
on:
  push:
    paths: ["erd/**"]          # runs when the ERD changes
  pull_request:                # and on every PR, so drift blocks the merge
jobs:
  drift:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: "22"
      - run: npx sqemo-mcp lint erd/app.erd.json --db "$DATABASE_URL" --ignore "flyway_*"
        env:
          DATABASE_URL:   ${{ secrets.DATABASE_URL }}
          SQEMO_EMAIL:    ${{ secrets.SQEMO_EMAIL }}
          SQEMO_PASSWORD: ${{ secrets.SQEMO_PASSWORD }}
```

- `${{ secrets.NAME }}` pulls in the values you added in Step 2 — never paste
  the real credentials into this file.
- `on:` decides when it runs. As written: whenever the ERD changes on a push,
  and on every pull request. Change `paths` or add `branches` to taste.
- `SQEMO_EMAIL` / `SQEMO_PASSWORD` sign in just for that job — no `login` step
  or stored token needed.

### 4. Push, and it runs

Commit the workflow file and push. From now on GitHub runs the check
automatically on the events you listed — **you don't trigger it manually.**
Push a change under `erd/`, or open a PR, and the job starts on its own.

### Optional: no database credentials in CI

If you'd rather not give CI a live database URL, have a job **inside your
network** run `pg_dump -s` and compare against the dump file instead. No
`DATABASE_URL`, and the `--db` becomes `--schema`:

```yaml
      - run: npx sqemo-mcp lint erd/app.erd.json --schema schema.sql --dialect postgres
        env:
          SQEMO_EMAIL:    ${{ secrets.SQEMO_EMAIL }}
          SQEMO_PASSWORD: ${{ secrets.SQEMO_PASSWORD }}
```

### Optional: check against a server ERD instead of the file

`--erd <id>` uses an ERD saved in your Sqemo account as the baseline, so you
skip committing a file. The CI account must have read access to it.

```yaml
      - run: npx sqemo-mcp lint --erd <erd-id> --db "$DATABASE_URL"
```

Trade-off worth knowing: a committed `.erd.json` is **pinned and reviewed** —
it changes only through a PR, and old commits re-check against their own
baseline. A server ERD is the **current, mutable** standard, so if someone
edits it in the app, the CI result can change with no code change. For
reproducible PR gating the committed file is the safer default; use `--erd`
when your team deliberately treats the server ERD as the single live standard.

## Where do the results show up?

In **GitHub**, not in Sqemo. Sqemo does not collect or display your CI runs —
the drift check is a command that exits `0` (pass) or `1` (drift), and GitHub
reports that like any other check:

- On a **pull request**, it appears as a **status check** — green tick or red
  ✗ next to the PR, with drift able to block the merge if you make the check
  required (Settings → Branches → branch protection).
- In the **Actions** tab, each run's full log lists exactly what drifted
  (`[missing_column] CUST.EMAIL`, …).
- GitHub emails you (per your notification settings) when a run you're
  involved in fails.

So the loop is: push → GitHub runs it → you see red/green on the PR and the
details in the Actions log. There's nothing to check back inside Sqemo.

## Security summary

- Read-only: only `information_schema` / the catalog is queried; the
  connection is opened read-only. The tool never writes to your database.
- Your database URL is used by the local (or CI) process only and is
  **never sent to Sqemo servers**.
- Prefer a read-only DB account, and the `--schema` dump path when you'd
  rather not hand credentials to CI at all.

---
Source: https://sqemo.com/docs/live-database/ · Updated 2026-07-30