---
name: bnna-database-copy
description: Copy a database from a source DSN to a bnna-managed target. Use when a team member needs a copy of production data for testing migrations, destructive queries, or development.
user-invocable: false
---

# Database Copy

Copy a database (schema + data) from a read-only source DSN into a bnna-managed
target database. Supports both PostgreSQL and MariaDB.

## When to Use

- A team member has a read-only DSN for a production (or staging) database
- They need a full copy for testing migrations, destructive queries, or development
- The target is a bnna-managed database (new or existing)

## Prerequisites

- `BNNA_HOST` and `BNNA_TOKEN` (token determines which databases are visible)
- A read-only DSN for the source database
- Network access from the machine running the command to both source and target

## Commands

### Operator CLI (bnadm)

```sh
# Copy into a new database (auto-creates target in the specified bunch)
bnadm databases copy --bunch <bunch> --prefix <name> '<source-dsn>'

# Copy into an existing database (by name, requires --target-dsn)
bnadm databases copy --target-dsn '<target-dsn>' '<source-dsn>' <target-ref>

# Dry run (show plan without executing)
bnadm databases copy --dry-run --bunch <bunch> --prefix <name> '<source-dsn>'
```

### Tenant CLI (bnna)

```sh
# Copy into a new database in a bunch
bnna databases copy --bunch <bunch> --prefix <name> '<source-dsn>'

# Copy into an existing database
bnna databases copy --target-dsn '<target-dsn>' '<source-dsn>' <target-ref>
```

## Target Name Resolution

Databases follow the `<bunch_slug>__<app>_<rnd>` naming convention (e.g. `aj_dev__todos_a1b2c3`).

The `target-ref` argument is matched in priority order:

1. **Exact**: `aj_dev__todos_a1b2c3`
2. **Suffix**: `todos_a1b2c3` (part after `__`)
3. **App-only**: `todos` (suffix with trailing `_<hex6>` stripped)

Must be unambiguous (exactly one match). If ambiguous, the error lists candidates.

## What Gets Copied

| Object | PostgreSQL | MariaDB |
|--------|-----------|---------|
| Tables (structure + data) | yes | yes |
| Primary keys, unique constraints | yes | yes |
| Indexes | yes | yes (via SHOW CREATE TABLE) |
| Foreign key constraints | yes (deferred) | yes (FK_CHECKS=0) |
| Views | yes | yes (DEFINER stripped) |
| Functions/procedures | yes | yes (DEFINER stripped) |
| Triggers | yes | yes (DEFINER stripped) |
| Sequences | yes (reset to max) | n/a (AUTO_INCREMENT) |
| Enums | yes | n/a |
| Ownership transfer | yes (ALTER OWNER) | n/a (GRANT via creation) |

## What Does NOT Get Copied

- Extensions (must be pre-installed on target)
- Roles/users (target role is the database owner)
- pg_hba rules or access control
- Tablespaces
- Custom types other than enums
- Materialized views (not yet supported)

## Engine Detection

Engine is inferred from the source DSN scheme:

- `postgres://` or `postgresql://` -> PostgreSQL
- `mysql://` or `mariadb://` -> MariaDB
- Override with `--type <engine>`

Cross-engine copy (PG -> MariaDB or vice versa) is not supported.

## Data Transfer

- **PostgreSQL**: Uses the COPY protocol via pgx for efficient streaming.
  Source and target are piped through `io.Pipe` per table — no intermediate files.
- **MariaDB**: Batched INSERT (1000 rows per batch). Reads via `SELECT *`,
  writes via parameterized INSERT.

## Implementation

Core logic: `internal/bndb/`

| File | Purpose |
|------|---------|
| `dsn.go` | Engine detection, MariaDB URL-to-DSN conversion |
| `resolve.go` | Target name resolution (fuzzy matching) |
| `copy.go` | Orchestrator, types (`CopyOpts`, `CopyResult`) |
| `copy_postgres.go` | PG schema extraction + COPY data transfer |
| `copy_mariadb.go` | MariaDB SHOW CREATE + batch INSERT |

## Flags Reference

| Flag | Description |
|------|-------------|
| `--bunch <name>` | Bunch to create the new database in (required with --prefix) |
| `--prefix <name>` | Create a new target database with this prefix |
| `--daemon <id>` | Specific daemon for new target (auto-selects if omitted) |
| `--target-dsn <dsn>` | Explicit target DSN (for existing databases) |
| `--type <engine>` | Override engine detection (postgresql or mariadb) |
| `--dry-run` | Show plan without executing |
| `--verbose` | Show per-table progress |