Branch a database and merge changes
Use a branch to prepare data changes without applying them to the original database. You can run an import, update records, review the differences, and then merge the changes you want to keep.
This guide uses a database called app with a users table. Substitute your own database, table and column names. The database must already exist in ASQL. If your data is in MySQL or MariaDB, first copy it into ASQL or set up a mirror.
1. Create a branch
Connect to ASQL with your SQL client, then create a branch named profile_update:
CREATE BRANCH profile_update FROM app;
SHOW BRANCHES;
ASQL exposes the branch as a database named app@profile_update. Switch to it before making changes:
USE `app@profile_update`;
An application or import tool can also connect directly to app@profile_update as its database.
A branch is not a frozen snapshot of the entire database. Tables you have not written to still read from the base database and reflect its latest changes. When you first write to a table, ASQL creates a separate working copy and saves that table's starting state for the later comparison.
2. Make your changes
Run ordinary SQL against the branch. For example, if users has id and display_name columns:
UPDATE users SET display_name = 'Alex Morgan' WHERE id = 42;
SELECT id, display_name FROM users WHERE id = 42;
The update changes the branch's copy of users. It does not update app.users in the base database or forward the write to a connected database. Your branch and its changes survive an ASQL restart.
3. Review the differences
Start with a summary, then inspect individual changes:
SHOW BRANCH DIFF profile_update;
SHOW BRANCH DIFF profile_update FOR users LIMIT 100;
SHOW BRANCH CONFLICTS profile_update FOR users;
The diff identifies inserted, updated and deleted rows. It also checks whether the base has changed since the branch first copied each table. Rows are matched by primary key, and updates are compared column by column.
If you changed a user's display name while the base changed their email address, both changes can be kept. If both changed the display name to different values, you must choose which value to keep before merging.
4. Preview the merge
To merge into the original database in this ASQL server, use TO LOCAL. Add DRY RUN to review the planned SQL without applying it:
MERGE BRANCH profile_update TO LOCAL DRY RUN;
Review the planned inserts, updates and deletes. Updates contain only the columns changed in the branch.
If ASQL reports unresolved conflicts, inspect them and specify which side to prefer. For example, to keep the branch's display names but prefer base values for other conflicts in users:
MERGE BRANCH profile_update TO LOCAL
PREFER BRANCH FOR users.display_name
PREFER BASE FOR users
DRY RUN;
A column rule takes precedence over a table rule. You can also use PREFER BRANCH or PREFER BASE without a FOR clause to resolve all conflicts the same way. Choose that only when it matches your intent.
ASQL does not apply a merge while conflicts remain unresolved.
5. Apply the merge
When the preview is correct, run the same command without DRY RUN:
MERGE BRANCH profile_update TO LOCAL
PREFER BRANCH FOR users.display_name
PREFER BASE FOR users;
If there were no conflicts, the simpler command is enough:
MERGE BRANCH profile_update TO LOCAL;
Run the command appropriate to your merge, then check its result. Review any rows reported as Not_merged or tables reported as Not_mergeable. Check the base records before removing the branch.
A completed branch cannot be merged again. Create a new branch for another set of changes.
Merge into MySQL or MariaDB
If ASQL holds a copy or mirror of another database, you can merge directly into that MySQL or MariaDB server instead of the local ASQL base:
MERGE BRANCH profile_update TO '127.0.0.1:3306'
USER 'app_writer' PASSWORD_FILE '/etc/aios/app-writer.pass'
DATABASE 'app'
DRY RUN;
Replace the host, port, account, password-file path and database with your target's details. The account needs permission to write the affected tables. PASSWORD_FILE is a file on the ASQL server, not on your client.
Review the preview and resolve any conflicts, then repeat the command without DRY RUN to apply it. For a mirrored database, the target's changes return to ASQL through replication.
If an inserted ID is already in use
A branch and its target can independently generate the same auto-increment ID. RENUMBER assigns a new ID to the branch's inserted row. Use ALSO to update related references in the branch:
MERGE BRANCH profile_update TO '127.0.0.1:3306'
USER 'app_writer' PASSWORD_FILE '/etc/aios/app-writer.pass'
DATABASE 'app'
RENUMBER users.id ALSO user_settings.user_id
DRY RUN;
Use your actual ID and reference columns. Preview the result before applying it.
If a merge is interrupted
Merges run in batches of 200 statements by default. Each batch is a separate transaction, so a merge is not one transaction covering every changed row. If a batch fails, that batch is rolled back; earlier committed batches remain applied.
Repeat the same merge command to resume the saved plan. You can specify a batch size with BATCH, for example BATCH 50.
Remove the branch
After checking the merge result, switch back to the base and remove the branch:
USE app;
DROP BRANCH profile_update;
You can also drop a branch to discard changes without merging. Dropping it removes its working copies and saved comparison data.
Before you start
- Use tables with primary keys. The merge needs a primary key to match rows.
- Keep schema changes separate. New tables and changes to table definitions are not merged.
- Allow memory for copies. The first write to a table copies the whole table twice: a working copy and its starting state.
SHOW BRANCHESreports their size inMemory_bytes. - Expect live reads until the first write. A table the branch has not written to still reflects the base.
- Branch in ASQL. This does not create a branch inside MySQL or MariaDB; those servers are merge targets.
