Designing a Pull Request Workflow in React
Dolt is Git for data. One of the most useful Git features is the ability to create pull requests, see their diff, and merge them from the browser.
Both DoltHub (GitHub for Dolt) and the SQL workbench on Hosted Dolt (AWS RDS for Dolt) have pull request workflows, which give the same level of control, collaboration, and review that Git gives you for your code but for your tables, schema, and data.
This blog will show you how to implement a basic pull request workflow in React for your Dolt database. You can also use these patterns and Dolt SQL queries to build a pull request workflow in any language.
What makes up a pull request?
Our pull requests have two main pages: the pull request details and the diff.
The pull details page includes:
- Pull request metadata (title, description, state, to and from branches)
- List of commits between branches
- User comments
- Log of changes
- Actions (merge, close, update)
The diff page includes:
- Summary of tables that have changed and how
- Stats for how many rows and cells changed total and for each table
- Row diffs for each table that show what rows and cells changed
- Schema diffs for each table that show how the schema changed
Basic Implementation
Pull requests include a lot of information about how your database changed between two branches. While this blog won't cover all the features listed above, it will go through how to implement the basic features you need to get started on a pull request workflow in React.
You can follow along with the source code for this example here and learn more about the React + GraphQL + Dolt (RGD) stack, pronounced "rugged", in this blog.
Initial set up
Dolt has version control functionality available via SQL system tables, system variables, functions, and stored procedures. This makes it easy to access version control information we need to display pull requests, such as commits and diffs.
However since pull requests are a web-only feature, there are not built-in Dolt SQL features for storing certain pull request specific metadata. You'll need a place to store this information. Our example code uses a flat file store, but you can also create a table directly in your Dolt database or store it elsewhere.
This is the schema we use for our pull requests table:
CREATE TABLE pulls (
id int NOT NULL,
database_name varchar(32) NOT NULL,
creator_name varchar(36) NOT NULL,
title varchar(256) NOT NULL,
description varchar(2048),
state int, -- Open, Closed, Merged
from_branch varchar(256) NOT NULL, -- Feature branch
to_branch varchar(256) NOT NULL, -- Base branch, commonly "main"
created_at timestamp,
updated_at timestamp,
PRIMARY KEY (id, database_name)
);Creating a pull request
In order to create a pull request, we need a React form with inputs for the branches, title, and description. When the form is submitted it will create the pull request and route to the pull details page.
export default function NewPullForm() {
const router = useRouter();
const [createPull, createRes] = useCreatePullMutation();
const [state, setState] = useSetState({
toBranchName: "",
fromBranchName: "",
title: "",
description: "",
});
const disabled = !state.toBranchName || !state.fromBranchName || !state.title;
const onSubmit = async (e: SyntheticEvent) => {
e.preventDefault();
try {
const { data } = await createPull({
variables: state,
});
if (!data) return;
await router.push(`/pulls/[pullId]`, `/pulls/${data.createPull.pullId}`);
} catch (_) {
// displayed by createRes.error
}
};
return (
<form onSubmit={onSubmit} className="form">
<label htmlFor="base-branch">Base branch</label>
<input
type="text"
id="base-branch"
name="base-branch"
value={state.toBranchName}
onChange={(e) => setState({ toBranchName: e.target.value })}
/>
<label htmlFor="from-branch">From branch</label>
<input
type="text"
id="from-branch"
name="from-branch"
value={state.fromBranchName}
onChange={(e) => setState({ fromBranchName: e.target.value })}
/>
<label htmlFor="title">Title</label>
<input
type="text"
id="title"
name="title"
value={state.title}
onChange={(e) => setState({ title: e.target.value })}
/>
<label htmlFor="description">Description</label>
<textarea
rows={4}
id="description"
name="description"
value={state.description}
onChange={(e) => setState({ description: e.target.value })}
/>
<button type="submit" disabled={disabled}>
Create pull request
</button>
{/* Handle loading and errors here */}
</form>
);
}You'll get a form that looks like this:
Displaying pull request metadata
Once the pull request has been created, we can fetch and display the pull request details from wherever we decided to store pull requests.
type Props = {
pullId: number;
};
export default function PullDetails(props: Props) {
const res = useGetPullQuery({ variables: { pullId: props.pullId } });
// Handle loading and error states
const { pull } = res.data;
return (
<div>
<h2>{res.data.pull.title}</h2>
<div>{pull.state}</div>
<div>
<span>{pull.creatorName}</span> wants to merge commits into{" "}
<Link>{pull.toBranchName}</Link> from <Link>{pull.fromBranchName}</Link>
</div>
<p>{pull.description}</p>
</div>
);
}Listing commits
Now we want the pull details page to display a list of commits that have been added since the from branch was checked out from the to branch.
To get these commits, you need to use two dot
logs. Two dot log lists commit
logs for revision A, while excluding commits from revision B.
We can use the dolt_log table
function
to display commits for our pull request.
mysql> SELECT * FROM DOLT_LOG('main..pr-test') LIMIT 3;
+----------------------------------+-----------+--------------------+-------------------------+--------------------------------------------------+
| commit_hash | committer | email | date | message |
+----------------------------------+-----------+--------------------+-------------------------+--------------------------------------------------+
| 7j99c6cb2u0ou2obgeqf3g2hvoq4207h | taylor | taylor@dolthub.com | 2023-08-16 23:26:21.609 | Changes to tablename2 from pr-test |
| t4fhbg5v85biolmdnumgfrqvug555io0 | taylor | taylor@dolthub.com | 2023-02-28 21:28:53.04 | Changes to renamedtable from pr-test |
| 0ibfqnae97q0oqoeke1d8u0g3cfi59ts | taylor | taylor@dolthub.com | 2023-02-28 21:28:37.272 | Changes to newtable -> renamedtable from pr-test |
+----------------------------------+-----------+--------------------+-------------------------+--------------------------------------------------+And then display the list in React on the pull details page:
type Props = {
pullId: number;
};
export default function CommitList(props: Props) {
const res = usePullCommitListQuery({ variables: props.params });
// Handle loading and error states
if (!res.data.commits.length) {
return <div>No commits found.</div>;
}
return (
<ul>
{res.data.commits.map((commit) => {
return (
<li key={commit.commitHash}>
{commit.message} ({commit.commitHash.slice(0, 7)})
</li>
);
})}
</ul>
);
}Now the pull details page gives a better overview of the proposed changes.
If you'd like to add pull comments and activity logs this is also the best page to do so.
Diff summary
Moving on to the pull request diff page. This page has a lot of information, but we're going to focus on the diff summary and row diffs.
Pull requests use three dot
diffs,
which show changes between revisions A and revision B starting at the last common
commit. That's not a typo - pull requests use two dot logs but three dot diffs. It's
confusing.
We want to show a list of tables that have changed, and if they were added, dropped,
modified, or renamed. We can use the DIFF_SUMMARY table
function
to display this list of tables.
mysql> SELECT * FROM DOLT_DIFF_SUMMARY('main...pr-test');
+-----------------+---------------+-----------+-------------+---------------+
| from_table_name | to_table_name | diff_type | data_change | schema_change |
+-----------------+---------------+-----------+-------------+---------------+
| taylor | | dropped | 1 | 1 |
| | keyless | added | 1 | 1 |
| newtable | renamedtable | renamed | 1 | 1 |
| tablename2 | tablename2 | modified | 1 | 0 |
| | tablename4 | added | 0 | 1 |
+-----------------+---------------+-----------+-------------+---------------+
5 rows in set (0.04 sec)And render the diff summaries in React.
type Props = {
params: { fromBranchName: string; toBranchName: string };
activeTable?: string;
};
export default function DiffSummary(props: Props) {
const res = useDiffSummariesQuery({ variables: props.params });
// Handle loading and error states
const activeTableName =
props.activeTable ?? res.data.diffSummaries[0].tableName;
return (
<table>
<thead>
<tr>
<th />
<th>Table</th>
<th>Data changes</th>
<th>Schema changes</th>
</tr>
</thead>
<tbody>
{res.data.diffSummaries.map((ds) => (
<tr key={props.ds.tableName}>
<td>
<StatIcon tableType={props.ds.tableType} />
</td>
<td>
<TableName {...props} />
</td>
<td>{String(props.ds.hasDataChanges)}</td>
<td>{String(props.ds.hasSchemaChanges)}</td>
</tr>
))}
</tbody>
</table>
);
}
Row diffs
Now when we click on a table link from the diff summary, we want to see the rows and cells that will be changed by this pull request for the selected table.
Similar to DOLT_DIFF_SUMMARY, we can use the DOLT_DIFF table
function
to get the row diffs for the pull request. We can again use the three dot diff between branches.
mysql> select from_pk, to_pk, from_col1, to_col1, from_commit, to_commit, diff_type from dolt_diff('main...pr-test', 'tablename2');
+-----------+-----------+------------------------------+-----------------------+----------------------------------+-----------+-----------+
| from_pk | to_pk | from_col1 | to_col1 | from_commit | to_commit | diff_type |
+-----------+-----------+------------------------------+-----------------------+----------------------------------+-----------+-----------+
| 1 | 1 | string | string that is edited | ngb5eitc0b9d807g58uqr7bi4prut9um | pr-test | modified |
| NULL | 416838400 | NULL | another row | ngb5eitc0b9d807g58uqr7bi4prut9um | pr-test | added |
| 521975598 | NULL | SSID driver https Amazon pdf | NULL | ngb5eitc0b9d807g58uqr7bi4prut9um | pr-test | removed |
+-----------+-----------+------------------------------+-----------------------+----------------------------------+-----------+-----------+
3 rows in set (0.05 sec)You can see this table has one added, one dropped, and one modified row. This UI, where
the cell changes for each column are displayed next to each other, can be hard to read on
the web. So instead we use a union of the table columns from both branches to map the
from_x and to_x diff columns. This results in one row for added and removed rows and
two rows for modified rows.
The above diff ends up looking like this:
const rowDiffs = [
// Modified row
{
deleted: { columnValues: ["1", "string"] },
added: { columnValues: ["1", "string that is edited"] },
},
// Added row
{
deleted: undefined,
added: { columnValues: ["416838400", "another row"] },
},
// Removed row
{
deleted: { columnValues: ["521975598", "SSID driver https Amazon pdf"] },
added: undefined,
},
];This is how we set up our diff table:
type Props = {
params: { fromBranchName: string; toBranchName: string; tableName: string };
};
export default function DiffTable(props: Props) {
const res = useRowDiffsQuery({ variables: props.params });
// Handle loading and error states
if (!res.data?.rowDiffs.list.length) {
return <div>No data changes found</div>;
}
const { rowDiffs } = res.data;
return (
<table>
<thead>
<tr>
<th />
{rowDiffs.columns.map((c) => (
<th key={c.name}>{c.name}</th>
))}
</tr>
</thead>
<tbody>
{rowDiffs.list.map((rd) => (
<Row rowDiff={rd} cols={rowDiffs.columns} />
))}
</tbody>
</table>
);
}And our individual row:
type Props = {
rowDiff: RowDiffFragment;
cols: ColumnFragment[];
};
export default function Row(props: Props) {
const { added, deleted } = props.rowDiff;
const deletedRow = !!deleted && (
<tr>
<td>-</td>
{deleted.columnValues.map((c, i) => {
return <td>{c.displayValue}</td>;
})}
</tr>
);
const addedRow = !!added && (
<tr>
<td>+</td>
{added.columnValues.map((c, i) => {
return <td>{c.displayValue}</td>;
})}
</tr>
);
return (
<>
{deletedRow}
{addedRow}
</>
);
}We can add some row and cell colors to make the diff more readable, and then the basic implementation of a pull request for your database is complete!
Advanced Features
There are some advanced features you can add to further improve your pull request workflow. These include:
- Updating the pull request
- Merging the pull request
- View merge conflicts for data and schema
- View diff stats (number of changed rows and cells) for whole diff and each table
- View schema diffs for each table
- Storing pre-merge commits to preserve diffs after the pull request is merged
Look out for a future blog that dives deeper into these features. If you want it sooner rather than later, let us know!
Conclusion
Adding a pull request workflow to your Dolt database adds an extra level of collaboration and human review to your data. You can easily utilize built-in SQL version control features to build a simple web UI in React.
Come talk to us about your use case for pull requests in Discord.