Three-way Merge in a SQL Database Examples
REFERENCE
18 min read
As promised in Three-way Merge Opus, I've come with a plethora of examples of three-way merge in a SQL database. Dolt is the only SQL database to support three-way merge of both data and schema.
Getting Started
Start with the database we use in the Dolt Getting Started guide.
The database comes with three tables like you would find in a basic HR systems database.
three_way_merge/main> show tables;
+---------------------------+
| Tables_in_three_way_merge |
+---------------------------+
| employees |
| employees_teams |
| teams |
+---------------------------+
3 rows in set (0.00 sec)
three_way_merge/main> show create table employees;
+-----------+------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------+
| employees | CREATE TABLE `employees` ( |
| | `id` int NOT NULL, |
| | `last_name` varchar(255), |
| | `first_name` varchar(255), |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
three_way_merge/main> show create table teams;
+-------+------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------+
| teams | CREATE TABLE `teams` ( |
| | `id` int NOT NULL, |
| | `team_name` varchar(255), |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-------+------------------------------------------------------------------+
1 row in set (0.00 sec)
three_way_merge/main> show create table employees_teams;
+-----------------+-------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+-------------------------------------------------------------------------------------------------+
| employees_teams | CREATE TABLE `employees_teams` ( |
| | `team_id` int NOT NULL, |
| | `employee_id` int NOT NULL, |
| | PRIMARY KEY (`team_id`,`employee_id`), |
| | KEY `employee_id` (`employee_id`), |
| | CONSTRAINT `employees_teams_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`), |
| | CONSTRAINT `employees_teams_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-----------------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)The initial data can be summarized by the following query:
three_way_merge/main> select first_name, last_name, team_name from employees
-> join employees_teams on (employees.id=employees_teams.employee_id)
-> join teams on (teams.id=employees_teams.team_id)
-> where team_name='Engineering';
+------------+-----------+-------------+
| first_name | last_name | team_name |
+------------+-----------+-------------+
| Tim | Sehn | Engineering |
| Brian | Hendriks | Engineering |
| Aaron | Son | Engineering |
+------------+-----------+-------------+
3 rows in set (0.00 sec)Clean Data Merge
Let's add Daylon to the engineering team on main. We'll also change my first name to Timothy on the change-name branch. First we make the branch at the proper commit.
three_way_merge/main> call dolt_branch('change-name');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.03 sec)Then, we execute our desired changes on main and commit.
three_way_merge/main> insert into employees(id, last_name, first_name) values (5, 'Wilkins', 'Daylon');
Empty set (0.01 sec)
three_way_merge/main> insert into employees_teams(team_id, employee_id) values (0, 5);
Empty set (0.01 sec)
three_way_merge/main> call dolt_commit('-a', '-m', 'Added employees')
-> ;
+----------------------------------+
| hash |
+----------------------------------+
| pc9mggcn2mome9kvjil8lq0jpgreb0nq |
+----------------------------------+
1 row in set (0.01 sec)Now, we checkout our branch and change my name to be more formal.
three_way_merge/main> call dolt_checkout('change-name');
+--------+----------------------------------+
| status | message |
+--------+----------------------------------+
| 0 | Switched to branch 'change-name' |
+--------+----------------------------------+
1 row in set (0.00 sec)
three_way_merge/change-name> update employees set first_name='Timothy' where first_name='Tim' ;
Empty set (0.01 sec)
three_way_merge/change-name> select * from dolt_diff_employees where to_commit='WORKING'; \G
*************************** 1. row ***************************
to_id: 0
to_last_name: Sehn
to_first_name: Timothy
to_commit: WORKING
to_commit_date: NULL
from_id: 0
from_last_name: Sehn
from_first_name: Tim
from_commit: 5udorqpkh8koi8djdhpk97ptttnu51ks
from_commit_date: 2024-06-24 22:38:23.797
diff_type: modified
1 row in set (0.00 sec)
three_way_merge/change-name> call dolt_commit('-a', '-m', 'Changed name') ;
+----------------------------------+
| hash |
+----------------------------------+
| 5c41dbta2fl0a4brsb3gltcaegocnc92 |
+----------------------------------+
1 row in set (0.01 sec)Now, we checkout main again and execute the merge. Everything should merge cleanly because I modified different rows as identified by primary key.
three_way_merge/change-name> call dolt_checkout('main');
+--------+---------------------------+
| status | message |
+--------+---------------------------+
| 0 | Switched to branch 'main' |
+--------+---------------------------+
1 row in set (0.00 sec)
three_way_merge/main> call dolt_merge('change-name');
+----------------------------------+--------------+-----------+------------------+
| hash | fast_forward | conflicts | message |
+----------------------------------+--------------+-----------+------------------+
| 45psosm4cqg9rnth1urg4vb0ccl8s717 | 0 | 0 | merge successful |
+----------------------------------+--------------+-----------+------------------+
1 row in set (0.01 sec)
three_way_merge/main> select * from employees;
+----+------------+------------+
| id | last_name | first_name |
+----+------------+------------+
| 0 | Sehn | Timothy |
| 1 | Hendriks | Brian |
| 2 | Son | Aaron |
| 3 | Fitzgerald | Brian |
| 5 | Wilkins | Daylon |
+----+------------+------------+
5 rows in set (0.00 sec)It merged. The result is simple and intuitive.
Clean Schema Merge
Now for a schema merge. Let's add start_date to the employees table on main. Then, in parallel, we'll add end_date to employees on a branch named add-end-date. We'll leave the data as NULL for now. We expect this to merge cleanly because two different columns were added to the same table.
First, let's set up the add-end-date branch.
three_way_merge/main> call dolt_branch('add-end-date');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.04 sec)Then, we'll add the start_date column to employees on main
three_way_merge/main> alter table employees add column start_date date;
three_way_merge/main> select * from dolt_status;
+------------+--------+----------+
| table_name | staged | status |
+------------+--------+----------+
| employees | 0 | modified |
+------------+--------+----------+
1 row in set (0.00 sec)
three_way_merge/main> select * from dolt_schema_diff('HEAD','WORKING') \G;
*************************** 1. row ***************************
from_table_name: employees
to_table_name: employees
from_create_statement: CREATE TABLE `employees` (
`id` int NOT NULL,
`last_name` varchar(255),
`first_name` varchar(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
to_create_statement: CREATE TABLE `employees` (
`id` int NOT NULL,
`last_name` varchar(255),
`first_name` varchar(255),
`start_date` date,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
1 row in set (0.00 sec)Don't forget to commit our changes.
three_way_merge/main> call dolt_commit('-a', '-m', 'Added start_date column') ;
+----------------------------------+
| hash |
+----------------------------------+
| r1pfs9h31lnq1o4q450ai0u4vuhiv9ag |
+----------------------------------+
1 row in set (0.01 sec)Now, we check out our branch add-end-date and add an end_date column and make a commit.
three_way_merge/main> call dolt_checkout('add-end-date');
+--------+-----------------------------------+
| status | message |
+--------+-----------------------------------+
| 0 | Switched to branch 'add-end-date' |
+--------+-----------------------------------+
1 row in set (0.00 sec)
three_way_merge/add-end-date> alter table employees add column end_date date;
three_way_merge/add-end-date> select * from dolt_schema_diff('HEAD','WORKING') \G;
*************************** 1. row ***************************
from_table_name: employees
to_table_name: employees
from_create_statement: CREATE TABLE `employees` (
`id` int NOT NULL,
`last_name` varchar(255),
`first_name` varchar(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
to_create_statement: CREATE TABLE `employees` (
`id` int NOT NULL,
`last_name` varchar(255),
`first_name` varchar(255),
`end_date` date,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
1 row in set (0.00 sec)Finally, we merge both changes together and inspect the result.
three_way_merge/add-end-date> call dolt_checkout('main') ;
+--------+---------------------------+
| status | message |
+--------+---------------------------+
| 0 | Switched to branch 'main' |
+--------+---------------------------+
1 row in set (0.00 sec)
three_way_merge/main> call dolt_merge('add-end-date');
+----------------------------------+--------------+-----------+------------------+
| hash | fast_forward | conflicts | message |
+----------------------------------+--------------+-----------+------------------+
| ul59g5dfb07ca9i3bebg09qrtpah820e | 0 | 0 | merge successful |
+----------------------------------+--------------+-----------+------------------+
1 row in set (0.02 sec)
three_way_merge/main> show create table employees;
+-----------+------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------+
| employees | CREATE TABLE `employees` ( |
| | `id` int NOT NULL, |
| | `last_name` varchar(255), |
| | `first_name` varchar(255), |
| | `start_date` date, |
| | `end_date` date, |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
three_way_merge/main> select * from employees;
+----+------------+------------+------------+----------+
| id | last_name | first_name | start_date | end_date |
+----+------------+------------+------------+----------+
| 0 | Sehn | Timothy | NULL | NULL |
| 1 | Hendriks | Brian | NULL | NULL |
| 2 | Son | Aaron | NULL | NULL |
| 3 | Fitzgerald | Brian | NULL | NULL |
| 5 | Wilkins | Daylon | NULL | NULL |
+----+------------+------------+------------+----------+
5 rows in set (0.00 sec)Again, we get the expected result, an employees table with both added columns, simple and intuitive.
Clean Schema and Data Merge
Now we'll add a check constraint that start_date < end_date if end_date NOT NULL on main. Then we'll fill in the data on the add-start-dates branch.
First, we create the branch so the merge base is correct.
three_way_merge/main> call dolt_branch('add-start-dates');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.03 sec)Now, we add the check constraint on main to make sure start_date is always before end_date if the end_date is not NULL. This must be expressed as a single conditional like end_date is NULL OR start_date < end_date.
three_way_merge/main> alter table employees add constraint check(end_date is NULL OR start_date < end_date);
three_way_merge/main> select * from dolt_schema_diff('HEAD','WORKING') \G;
*************************** 1. row ***************************
from_table_name: employees
to_table_name: employees
from_create_statement: CREATE TABLE `employees` (
`id` int NOT NULL,
`last_name` varchar(255),
`first_name` varchar(255),
`start_date` date,
`end_date` date,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
to_create_statement: CREATE TABLE `employees` (
`id` int NOT NULL,
`last_name` varchar(255),
`first_name` varchar(255),
`start_date` date,
`end_date` date,
PRIMARY KEY (`id`),
CONSTRAINT `employees_chk_8nqigbma` CHECK ((`end_date` IS NULL OR (`start_date` < `end_date`)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
1 row in set (0.00 sec)
three_way_merge/main> call dolt_commit('-a', '-m', 'Added check constraint') ;
+----------------------------------+
| hash |
+----------------------------------+
| q7l32u1dkeitgvob1b574p7oh00748vp |
+----------------------------------+
1 row in set (0.01 sec)Now on add-start-dates I'll add the data. Note, the data will only be subject to the check constraint once the two branches are merged.
three_way_merge/add-start-dates> update employees set start_date='2018-08-04';
Empty set (0.01 sec)
three_way_merge/add-start-dates> update employees set start_date='2021-04-19' where last_name='Fitzgerald';
Empty set (0.01 sec)
three_way_merge/add-start-dates> update employees set start_date='2018-12-19' where last_name='Wilkins';
Empty set (0.01 sec)
three_way_merge/add-start-dates> select * from employees;
+----+------------+------------+------------+----------+
| id | last_name | first_name | start_date | end_date |
+----+------------+------------+------------+----------+
| 0 | Sehn | Timothy | 2018-08-04 | NULL |
| 1 | Hendriks | Brian | 2018-08-04 | NULL |
| 2 | Son | Aaron | 2018-08-04 | NULL |
| 3 | Fitzgerald | Brian | 2021-04-19 | NULL |
| 5 | Wilkins | Daylon | 2018-12-19 | NULL |
+----+------------+------------+------------+----------+
5 rows in set (0.00 sec)
three_way_merge/add-start-dates> call dolt_commit('-a', '-m', 'Added start dates') ;
+----------------------------------+
| hash |
+----------------------------------+
| gr42oc7fc9ih6uudvdu4sn83hb0keqa3 |
+----------------------------------+
1 row in set (0.01 sec)Now, back on main I'll perform the merge. This should all merge together fine.
three_way_merge/add-start-dates> call dolt_checkout('main');
+--------+---------------------------+
| status | message |
+--------+---------------------------+
| 0 | Switched to branch 'main' |
+--------+---------------------------+
1 row in set (0.00 sec)
three_way_merge/main> call dolt_merge('add-start-dates');
+----------------------------------+--------------+-----------+------------------+
| hash | fast_forward | conflicts | message |
+----------------------------------+--------------+-----------+------------------+
| pviakkulr2a9ves02qat5tdtcuaknc4h | 0 | 0 | merge successful |
+----------------------------------+--------------+-----------+------------------+
1 row in set (0.01 sec)
three_way_merge/main> select * from employees;
+----+------------+------------+------------+----------+
| id | last_name | first_name | start_date | end_date |
+----+------------+------------+------------+----------+
| 0 | Sehn | Timothy | 2018-08-04 | NULL |
| 1 | Hendriks | Brian | 2018-08-04 | NULL |
| 2 | Son | Aaron | 2018-08-04 | NULL |
| 3 | Fitzgerald | Brian | 2021-04-19 | NULL |
| 5 | Wilkins | Daylon | 2018-12-19 | NULL |
+----+------------+------------+------------+----------+
5 rows in set (0.00 sec)
three_way_merge/main> show create table employees;
+-----------+---------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` ( |
| | `id` int NOT NULL, |
| | `last_name` varchar(255), |
| | `first_name` varchar(255), |
| | `start_date` date, |
| | `end_date` date, |
| | PRIMARY KEY (`id`), |
| | CONSTRAINT `employees_chk_8nqigbma` CHECK ((`end_date` IS NULL OR (`start_date` < `end_date`))) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)Yet again, we get the expected result, an employees table with the added start dates that fit the new check constraint, simple and intuitive.
Data Conflict
Now, to create a data conflict I am going to lowercase all the names on main while changing my name back to "Tim" from "Timothy" on the less-formal branch.
First, I create the less-formal branch.
three_way_merge/main> call dolt_branch('less-formal');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.03 sec)Then, I lowercase the first_name and last_name columns on main.
three_way_merge/main> update employees set first_name=lower(first_name);
Empty set (0.01 sec)
three_way_merge/main> update employees set last_name=lower(last_name);
Empty set (0.01 sec)
three_way_merge/main> select * from employees;
+----+------------+------------+------------+----------+
| id | last_name | first_name | start_date | end_date |
+----+------------+------------+------------+----------+
| 0 | sehn | timothy | 2018-08-04 | NULL |
| 1 | hendriks | brian | 2018-08-04 | NULL |
| 2 | son | aaron | 2018-08-04 | NULL |
| 3 | fitzgerald | brian | 2021-04-19 | NULL |
| 5 | wilkins | daylon | 2018-12-19 | NULL |
+----+------------+------------+------------+----------+
5 rows in set (0.00 sec)
three_way_merge/main> call dolt_commit('-a', '-m', 'Lowercased all names');
+----------------------------------+
| hash |
+----------------------------------+
| 0asfar95efko8iph7gvajd223t5s7ggj |
+----------------------------------+
1 row in set (0.01 sec)Now, over to the the less-formal branch. I'll even try to follow the new lowercase convention.
three_way_merge/main> call dolt_checkout('less-formal');
+--------+----------------------------------+
| status | message |
+--------+----------------------------------+
| 0 | Switched to branch 'less-formal' |
+--------+----------------------------------+
1 row in set (0.00 sec)
three_way_merge/less-formal> update employees set first_name='tim' where first_name='Timothy';;
Empty set (0.01 sec)
three_way_merge/less-formal> select * from employees;
+----+------------+------------+------------+----------+
| id | last_name | first_name | start_date | end_date |
+----+------------+------------+------------+----------+
| 0 | Sehn | tim | 2018-08-04 | NULL |
| 1 | Hendriks | Brian | 2018-08-04 | NULL |
| 2 | Son | Aaron | 2018-08-04 | NULL |
| 3 | Fitzgerald | Brian | 2021-04-19 | NULL |
| 5 | Wilkins | Daylon | 2018-12-19 | NULL |
+----+------------+------------+------------+----------+
5 rows in set (0.00 sec)
three_way_merge/less-formal> call dolt_commit('-a', '-m', 'Changed back to tim and lowercased as is new convention');
+----------------------------------+
| hash |
+----------------------------------+
| 97f7qeu6pdf88nmnht15c7onqfiuolv9 |
+----------------------------------+
1 row in set (0.01 sec)Now, I'll try to merge it together, expecting a conflict.
three_way_merge/less-formal> call dolt_checkout('main');
+--------+---------------------------+
| status | message |
+--------+---------------------------+
| 0 | Switched to branch 'main' |
+--------+---------------------------+
1 row in set (0.00 sec)
three_way_merge/main> call dolt_merge('less-formal');
Error 1105 (HY000): Merge conflict detected, @autocommit transaction rolled back. @autocommit must be disabled so that merge conflicts can be resolved using the dolt_conflicts and dolt_schema_conflicts tables before manually committing the transaction. Alternatively, to commit transactions with merge conflicts, set @@dolt_allow_commit_conflicts = 1As expected a conflict was detected. But, I have @autocommit on. AUTOCOMMIT is a SQL mode that finishes every SQL statement run with a SQL COMMIT. This commits the SQL transaction. Most databases and clients have AUTOCOMMIT on by default with the notable exception being Python. In Dolt, AUTOCOMMIT forces the transaction initiated by the merge to be rolled back because the dolt_merge() was unable to be transaction committed with conflicts. To resolve the conflict manually, I must turn @autocommit off so the additional conflict resolution steps can happen in the same SQL transaction.
three_way_merge/main> set @@autocommit=0;
three_way_merge/main> call dolt_merge('less-formal');
+------+--------------+-----------+-----------------+
| hash | fast_forward | conflicts | message |
+------+--------------+-----------+-----------------+
| | 0 | 1 | conflicts found |
+------+--------------+-----------+-----------------+
1 row in set (0.00 sec)Now, I can use Dolt's conflict system tables to explore and resolve the conflicts. First, let's see what tables have conflicts and how many using the dolt_conflicts system table.
three_way_merge/main> select * from dolt_conflicts;
+-----------+---------------+
| table | num_conflicts |
+-----------+---------------+
| employees | 1 |
+-----------+---------------+
1 row in set (0.00 sec)As expected, I have a single conflict in the employees table. Now, let's see what row is in conflict using the dolt_conflicts_employees system table.
three_way_merge/main> select * from dolt_conflicts_employees \G;
*************************** 1. row ***************************
from_root_ish: 97f7qeu6pdf88nmnht15c7onqfiuolv9
base_id: 0
base_last_name: Sehn
base_first_name: Timothy
base_start_date: 2018-08-04
base_end_date: NULL
our_id: 0
our_last_name: sehn
our_first_name: timothy
our_start_date: 2018-08-04
our_end_date: NULL
our_diff_type: modified
their_id: 0
their_last_name: Sehn
their_first_name: tim
their_start_date: 2018-08-04
their_end_date: NULL
their_diff_type: modified
dolt_conflict_id: B4HXDRRpL+9vSyeMrxdW9QNow, let's set the first_name to tim and resolve the conflict by deleting the row from dolt_conflicts_employees.
three_way_merge/main> update employees set first_name='tim' where first_name='timothy';
Empty set (0.00 sec)
three_way_merge/main> delete from dolt_conflicts_employees;
Empty set (0.00 sec)
three_way_merge/main> select * from employees;
+----+------------+------------+------------+----------+
| id | last_name | first_name | start_date | end_date |
+----+------------+------------+------------+----------+
| 0 | sehn | tim | 2018-08-04 | NULL |
| 1 | hendriks | brian | 2018-08-04 | NULL |
| 2 | son | aaron | 2018-08-04 | NULL |
| 3 | fitzgerald | brian | 2021-04-19 | NULL |
| 5 | wilkins | daylon | 2018-12-19 | NULL |
+----+------------+------------+------------+----------+
5 rows in set (0.00 sec)And now that I'm satisfied, I will commit the merge.
three_way_merge/main> call dolt_commit('-a', '-m', 'Resolved merge conflict to tim');
+----------------------------------+
| hash |
+----------------------------------+
| fiqs94lugmd79p3bn4cs4g3cc5sj0436 |
+----------------------------------+
1 row in set (0.01 sec)As you can see, Dolt provides powerful data conflict detection and resolution capabilities.
Schema Conflict
I'm going to add a NOT NULL constraint to start_date on main. Then on a start-default branch, I am going to make the default CURDATE() or today.
First, I make the start-default branch.
three_way_merge/main> call dolt_branch('start-time');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.03 sec)Then, I add the NOT NULL constraint.
three_way_merge/main> alter table employees modify start_date date not null;
three_way_merge/main> show create table employees;
+-----------+---------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` ( |
| | `id` int NOT NULL, |
| | `last_name` varchar(255), |
| | `first_name` varchar(255), |
| | `start_date` date NOT NULL, |
| | `end_date` date, |
| | PRIMARY KEY (`id`), |
| | CONSTRAINT `employees_chk_8nqigbma` CHECK ((`end_date` IS NULL OR (`start_date` < `end_date`))) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
three_way_merge/main> call dolt_commit('-a', '-m', 'Added not null constraint to start_date');
+----------------------------------+
| hash |
+----------------------------------+
| b2c2uc6gn39rah7epd28bvla2alaqc6c |
+----------------------------------+
1 row in set (0.01 sec)Now I check out the start-default branch and add the default.
three_way_merge/main> call dolt_checkout('start-default');
+--------+------------------------------------+
| status | message |
+--------+------------------------------------+
| 0 | Switched to branch 'start-default' |
+--------+------------------------------------+
1 row in set (0.00 sec)
three_way_merge/start-default> alter table employees modify start_date date default(curdate());
three_way_merge/start-default> show create table employees;
+-----------+---------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` ( |
| | `id` int NOT NULL, |
| | `last_name` varchar(255), |
| | `first_name` varchar(255), |
| | `start_date` date DEFAULT (curdate()), |
| | `end_date` date, |
| | PRIMARY KEY (`id`), |
| | CONSTRAINT `employees_chk_8nqigbma` CHECK ((`end_date` IS NULL OR (`start_date` < `end_date`))) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
three_way_merge/start-default> call dolt_commit('-a', '-m', 'Added curdate() default to start_date');
+----------------------------------+
| hash |
+----------------------------------+
| 225datd8uj2gg3u5khnruqb2pdn2ljkq |
+----------------------------------+
1 row in set (0.01 sec)Now, the merge.
three_way_merge/start-default> call dolt_checkout('main');
+--------+---------------------------+
| status | message |
+--------+---------------------------+
| 0 | Switched to branch 'main' |
+--------+---------------------------+
1 row in set (0.00 sec)
three_way_merge/main> call dolt_merge('start-default');
+------+--------------+-----------+-----------------+
| hash | fast_forward | conflicts | message |
+------+--------------+-----------+-----------------+
| | 0 | 1 | conflicts found |
+------+--------------+-----------+-----------------+
1 row in set (0.00 sec)As expected, we have a schema conflict. Let's use the Dolt system tables to find out what is in conflict.
three_way_merge/main> select * from dolt_status;
+------------+--------+-----------------+
| table_name | staged | status |
+------------+--------+-----------------+
| employees | 0 | schema conflict |
+------------+--------+-----------------+
1 row in set (0.00 sec)
three_way_merge/main> select * from dolt_schema_conflicts \G;
*************************** 1. row ***************************
table_name: employees
base_schema: CREATE TABLE `employees` (
`id` int NOT NULL,
`last_name` varchar(255),
`first_name` varchar(255),
`start_date` date,
`end_date` date,
PRIMARY KEY (`id`),
CONSTRAINT `employees_chk_8nqigbma` CHECK ((end_date IS NULL OR (start_date < end_date)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
our_schema: CREATE TABLE `employees` (
`id` int NOT NULL,
`last_name` varchar(255),
`first_name` varchar(255),
`start_date` date NOT NULL,
`end_date` date,
PRIMARY KEY (`id`),
CONSTRAINT `employees_chk_8nqigbma` CHECK ((end_date IS NULL OR (start_date < end_date)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
their_schema: CREATE TABLE `employees` (
`id` int NOT NULL,
`last_name` varchar(255),
`first_name` varchar(255),
`start_date` date DEFAULT (curdate()),
`end_date` date,
PRIMARY KEY (`id`),
CONSTRAINT `employees_chk_8nqigbma` CHECK ((end_date IS NULL OR (start_date < end_date)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
description: different column definitions for our column start_date and their column start_date
1 row in set (0.00 sec)As we can see, we added a different constraint on the same column and this generated a conflict. Unfortunately, there is no way to resolve schema conflicts in Dolt right now. It is a known issue and we will run a project to implement it soon.
In this case, I like the NOT NULL constraint on main so I'll just abort the merge and carry on.
three_way_merge/main> call dolt_merge('--abort') ;
+------+--------------+-----------+---------------+
| hash | fast_forward | conflicts | message |
+------+--------------+-----------+---------------+
| | 0 | 0 | merge aborted |
+------+--------------+-----------+---------------+
1 row in set (0.03 sec)Constraint Violation
For our final example, we'll show you a constraint violation. These can happen with check constraints or foreign key constraints. On main, I will move my start date to today. On the fire-tim branch I will make my end_date yesterday. Merging these two changes is fine because the data was changed in two different columns but the merged data now violates the check constraint that start_date must be before end_date.
First, we set up the branch.
three_way_merge/main> call dolt_branch('fire-tim');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.02 sec)Now, I'll make my start date today on main.
three_way_merge/main> call dolt_branch('fire-tim');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.02 sec)
three_way_merge/main> update employees set start_date=curdate() where first_name='tim';
Empty set (0.00 sec)
three_way_merge/main> select * from employees;
+----+------------+------------+------------+----------+
| id | last_name | first_name | start_date | end_date |
+----+------------+------------+------------+----------+
| 0 | sehn | tim | 2024-07-10 | NULL |
| 1 | hendriks | brian | 2018-08-04 | NULL |
| 2 | son | aaron | 2018-08-04 | NULL |
| 3 | fitzgerald | brian | 2021-04-19 | NULL |
| 5 | wilkins | daylon | 2018-12-19 | NULL |
+----+------------+------------+------------+----------+
5 rows in set (0.00 sec)
three_way_merge/main> call dolt_commit('-a', '-m', 'Moved tim start date to today');
+----------------------------------+
| hash |
+----------------------------------+
| d6nvvdrm7mcjj7ijmipuink7hn84pq7n |
+----------------------------------+
1 row in set (0.01 sec)And fire me yesterday on the fire-tim branch.
three_way_merge/main> call dolt_checkout('fire-tim');
+--------+-------------------------------+
| status | message |
+--------+-------------------------------+
| 0 | Switched to branch 'fire-tim' |
+--------+-------------------------------+
1 row in set (0.00 sec)
three_way_merge/fire-tim> update employees set end_date=date_sub(curdate(), interval 1 day) where first_name='tim';
Empty set (0.00 sec)
three_way_merge/fire-tim> select * from employees;
+----+------------+------------+------------+------------+
| id | last_name | first_name | start_date | end_date |
+----+------------+------------+------------+------------+
| 0 | sehn | tim | 2018-08-04 | 2024-07-09 |
| 1 | hendriks | brian | 2018-08-04 | NULL |
| 2 | son | aaron | 2018-08-04 | NULL |
| 3 | fitzgerald | brian | 2021-04-19 | NULL |
| 5 | wilkins | daylon | 2018-12-19 | NULL |
+----+------------+------------+------------+------------+
5 rows in set (0.00 sec)
three_way_merge/fire-tim> call dolt_commit('-a', '-m', 'tim end date yesterday');
+----------------------------------+
| hash |
+----------------------------------+
| t74u1b6fjpqvopri4297v6ttlerbp0u1 |
+----------------------------------+
1 row in set (0.01 sec)And now the merge.
three_way_merge/fire-tim> call dolt_checkout('main');
+--------+---------------------------+
| status | message |
+--------+---------------------------+
| 0 | Switched to branch 'main' |
+--------+---------------------------+
1 row in set (0.00 sec)
three_way_merge/main> call dolt_merge('fire-tim');
+------+--------------+-----------+-----------------+
| hash | fast_forward | conflicts | message |
+------+--------------+-----------+-----------------+
| | 0 | 1 | conflicts found |
+------+--------------+-----------+-----------------+
1 row in set (0.00 sec)As expected we have a conflict in the form of a constraint violation.
three_way_merge/main> select * from dolt_constraint_violations;
+-----------+----------------+
| table | num_violations |
+-----------+----------------+
| employees | 1 |
+-----------+----------------+
1 row in set (0.00 sec)
three_way_merge/main> select * from dolt_constraint_violations_employees;
+----------------------------------+------------------+----+-----------+------------+------------+------------+---------------------------------------------------------------------------------------------------+
| from_root_ish | violation_type | id | last_name | first_name | start_date | end_date | violation_info |
+----------------------------------+------------------+----+-----------+------------+------------+------------+---------------------------------------------------------------------------------------------------+
| t74u1b6fjpqvopri4297v6ttlerbp0u1 | check constraint | 0 | sehn | tim | 2024-07-10 | 2024-07-09 | {"Name": "employees_chk_8nqigbma", "Expression": "(end_date IS NULL OR (start_date < end_date))"} |
+----------------------------------+------------------+----+-----------+------------+------------+------------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)I don't really want to be fired so I'm just going to abort the merge and reset main to my original start date.
three_way_merge/main> call dolt_merge('--abort');
+------+--------------+-----------+---------------+
| hash | fast_forward | conflicts | message |
+------+--------------+-----------+---------------+
| | 0 | 0 | merge aborted |
+------+--------------+-----------+---------------+
1 row in set (0.01 sec)
three_way_merge/main> call dolt_reset('--hard', 'HEAD^');
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0.01 sec)
three_way_merge/main> select * from employees;
+----+------------+------------+------------+----------+
| id | last_name | first_name | start_date | end_date |
+----+------------+------------+------------+----------+
| 0 | sehn | tim | 2018-08-04 | NULL |
| 1 | hendriks | brian | 2018-08-04 | NULL |
| 2 | son | aaron | 2018-08-04 | NULL |
| 3 | fitzgerald | brian | 2021-04-19 | NULL |
| 5 | wilkins | daylon | 2018-12-19 | NULL |
+----+------------+------------+------------+----------+Crisis averted!
Conclusion
As you saw through many detailed examples, Dolt provides powerful and intuitive three-way merge capabilities to a SQL database across schema and data. Give it a try with your database. Curious to learn more? Stop by our Discord and we'd be happy to chat about three-way merge.