---
title: "Database Branching"
---

# Database Branching


Database branching in HeliosDB Nano works like Git branches for your data. Create isolated copies of your database for development, testing, or experimentation without duplicating storage.

One place the Git analogy breaks down: **merge has no conflict detection**. See
[Merging Branches](#merging-branches) before you plan a workflow around it.

## Overview

Branches are lightweight, copy-on-write database snapshots that share unchanged data with their parent. This makes creating branches nearly instantaneous regardless of database size.

## Creating Branches

### From Current State

```sql
-- Create a branch from the current state of main
CREATE DATABASE BRANCH dev FROM main;

-- Or use AS OF NOW explicitly
CREATE DATABASE BRANCH dev FROM main AS OF NOW;
```

### From a Point in Time

```sql
-- Branch from a specific timestamp
CREATE DATABASE BRANCH hotfix FROM main
AS OF TIMESTAMP '2025-01-15 00:00:00';

-- Branch from a specific transaction
CREATE DATABASE BRANCH feature FROM main
AS OF TRANSACTION 12345;
```

## Switching Branches

### SQL Syntax

```sql
USE BRANCH dev;
```

### REPL Command

```bash
\use dev
```

### Check Current Branch

```bash
\show branch
```

## Listing Branches

### REPL

```bash
\branches
```

### SQL

```sql
SELECT * FROM pg_database_branches();
```

Output includes:
- `name` - Branch name
- `parent` - Parent branch name
- `created_at` - Creation timestamp
- `commit_count` - Number of commits
- `is_current` - Whether this is the active branch

## Merging Branches

`MERGE DATABASE BRANCH` does move rows — it is not a no-op. But it is
**last-writer-wins and performs no conflict detection**: rows changed on both
branches are overwritten by the merge, and no conflict is reported. Treat it as
"apply the source branch's rows onto the target", not as a three-way merge.

### Basic Merge

```sql
MERGE DATABASE BRANCH dev INTO main;
```

### Options

| Option | Status |
|--------|--------|
| `delete_branch_after = true` | Supported — drops the source branch after a successful merge |
| `conflict_resolution = 'fail' \| 'target_wins' \| 'branch_wins'` | **Not implemented** — the statement returns an error |

```sql
-- Returns an error, by design:
MERGE DATABASE BRANCH dev INTO main
WITH (conflict_resolution = 'branch_wins');
```

```
MERGE BRANCH conflict_resolution is not implemented: merges are
last-writer-wins and conflicts are never detected. Remove the option to
merge with those semantics.
```

The option is parsed but the storage engine never detects a conflict or applies
a strategy, so accepting it would be a silent no-op — you would ask for
`target_wins`, get `branch_wins`, and be told the merge completed with zero
conflicts. It now fails loudly instead of lying, and will stay that way until
conflict detection exists.

### Safe pattern for anything conflict-sensitive

Branch, validate, **discard**, then re-apply the validated SQL to `main`:

```sql
CREATE DATABASE BRANCH trial FROM main;
USE BRANCH trial;
-- experiment, inspect, verify
USE BRANCH main;
DROP DATABASE BRANCH trial;
-- then run the statements you actually validated against main
```

## Dropping Branches

```sql
DROP DATABASE BRANCH dev;

-- With IF EXISTS to avoid errors
DROP DATABASE BRANCH IF EXISTS dev;
```

## Use Cases

### Development Environment

```sql
-- Create dev branch
CREATE DATABASE BRANCH dev FROM main;
USE BRANCH dev;

-- Make experimental changes
ALTER TABLE users ADD COLUMN preferences JSONB;
INSERT INTO users (name, preferences) VALUES ('Test', '{}');

-- If changes work, merge back (last-writer-wins: rows changed on both
-- branches are overwritten without a conflict report)
USE BRANCH main;
MERGE DATABASE BRANCH dev INTO main;
```

### Feature Testing

```sql
-- Create branch for testing a feature
CREATE DATABASE BRANCH feature_x FROM main;
USE BRANCH feature_x;

-- Run integration tests with modified data
DELETE FROM users WHERE test_account = true;
-- Tests run here...

-- Discard when done (no merge needed)
USE BRANCH main;
DROP DATABASE BRANCH feature_x;
```

### Point-in-Time Recovery

```sql
-- Create branch from before the incident
CREATE DATABASE BRANCH recovery FROM main
AS OF TIMESTAMP '2025-01-15 00:00:00';

-- Verify data is correct
USE BRANCH recovery;
SELECT COUNT(*) FROM orders;

-- If good, make it the new main
-- (requires careful planning in production)
```

## How It Works

1. **Copy-on-Write**: Branches share data pages with their parent. Only modified pages are copied.
2. **Snapshot Isolation**: Each branch has its own transaction log and snapshot.
3. **Minimal Overhead**: Creating a branch is O(1) regardless of data size.

## Best Practices

1. **Name branches descriptively**: `feature_auth_refactor`, `bugfix_order_calc`
2. **Keep branches short-lived**: Merge or delete when done
3. **Test merges on a copy first**: Create a test branch to verify merge results
4. **Use timestamps for recovery branches**: Makes it clear what state you're restoring

## Limitations

- **No conflict detection on merge.** `MERGE DATABASE BRANCH` is
  last-writer-wins; `conflict_resolution=` is not implemented and returns an
  error (see above).
- **Whole-database scope.** A branch covers the whole database, not an
  individual table or schema.
- **Not a security boundary.** Branching is storage-level copy-on-write
  isolation. It does not sandbox anything outside the database — an agent
  working on a branch can still make external side effects — and it is not an
  access-control mechanism.
- Circular branch relationships are not allowed.
- Branch names must be unique.

## Related

- [Time-Travel Queries](https://www.heliosdb.com/docs/nano/features/time-travel/) - Query historical data without branches
- [REPL Commands](https://www.heliosdb.com/docs/nano/user-guide/repl-commands/) - `\branches`, `\use` commands