System Tables
Last updated
Was this helpful?
Last updated
Was this helpful?
dolt_branchesdolt_branches contains information about branches known to the database.
Get all the branches.
remote and branch show the remote host and branch that each branch is tracking.
dirty is TRUE when there are uncommitted changed on the branch.
dolt_remote_branchesdolt_remote_branches contains information about branches on remotes you have fetched. It has a similar schema as dolt_branches, but the remote, branch, and dirty columns don't make sense in this context and are not included. Only remote branches are included in this table.
Get all local and remote branches in a single query. Remote branches will have the prefix remotes/<remoteName> in their names.
dolt_docsdolt_docs stores the contents of Dolt docs (LICENSE.md, README.md).
You can modify the contents of these files via SQL, but you are not guaranteed to see these changes reflected in the files on disk.
Gets all docs.
dolt_proceduresdolt_procedures stores each stored procedure that has been created on the database.
The values in this table are implementation details associated with the storage of stored procedures. It is recommended to use built-in SQL statements for examining and modifying stored procedures rather than using this table directly.
When using the standard CREATE PROCEDURE workflow, the name column will always be lowercase. Dolt assumes that name is always lowercase as a result, and manually inserting a stored procedure must also have a lowercase name. Otherwise, it will be invisible to some operations, such as DROP PROCEDURE.
dolt_query_catalogUsing the dolthub/docs_examples from DoltHub as an example, you can create a named query using the CLI, or by directly inserting into the dolt_query_catalog table.
After creating a named query, you can view it in the dolt_query_catalog table:
Then you can use the dolt CLI to execute it:
Last, but not least, if you want to persist that named query, be sure to commit your change to the dolt_query_catalog table.
dolt_remotesdolt_remotes returns the remote subcontents of the repo_state.json, similar to running dolt remote -v from the command line.
dolt_backupsdolt_backups returns the backup contents of the repo_state.json, similar to running dolt backup -v from the command line.
sql SELECT * FROM dolt_backups;
dolt_schemasdolt_schemas stores SQL schema fragments for a dolt database that are versioned alongside the database itself. Certain DDL statements will modify this table and the value of this table in a SQL session will affect what database entities exist in the session.
The values in this table are implementation details associated with the storage of certain schema elements. It is recommended to use built-in SQL statements for examining and modifying schemas, rather than using this table directly.
Currently, all VIEW, TRIGGER and EVENT definitions are stored in the dolt_schemas table. The column type defines whether the fragment is view, trigger or event. The column name is the fragment name as supplied in the CREATE statement. The column fragment stores the CREATE statement of the fragment. The column json is any additional important information such as CreateAt field for the fragment.
The values in this table are partly implementation details associated with the implementation of the underlying database objects.
Then you can view them in dolt_schemas:
dolt_tagsdolt_tags shows information for all active tags in the current database.
Create a tag using dolt_tag() stored procedure.
Get all the tags.
dolt_statisticsdolt_statistics includes currently collected database statistics. This information is stored outside of the commit graph and is not subject to versioning semantics.
dolt_blame_$tablenameThe dolt_blame_$tablename system view has the following columns:
The remaining columns are dependent on the schema of the user table. Every column from the primary key of your table will be included in the dolt_blame_$tablename system table.
Executing a SELECT * query for a dolt_blame_$tablename system view will show you the primary key columns for every row in the underlying user table and the commit metadata for the last commit that modified that row. Note that if a table has any uncommitted changes in the working set, those will not be displayed in the dolt_blame_$tablename system view.
dolt_blame_$tablename is only available for tables with a primary key. Attempting to query dolt_blame_$tablename for a table without a primary key will return an error message.
Consider the following example table city:
To find who set the current values, we can query the dolt_blame_city table:
dolt_commit_ancestorsThe dolt_commit_ancestors table records the ancestors for every commit in the database. Each commit has one or two ancestors, two in the case of a merge commit.
Each commit hash has one or two entries in the table, depending on whether it has one or two parent commits. The root commit of the database has a NULL parent. For merge commits, the merge base will have parent_index 0, and the commit merged will have parent_index 1.
dolt_commitsThe dolt_commits system table shows ALL commits in a Dolt database.
dolt_history_$TABLENAMEFor every user table named $TABLENAME, there is a read-only system table named dolt_history_$TABLENAME that can be queried to find a row's value at every commit in the current branch's history.
Every Dolt history table contains columns for commit_hash, committer, and commit_date, plus every column from the user table's schema at the current checked out branch.
Consider a table named mytable with the following schema:
The schema for dolt_history_mytable would be:
Assume a database with the mytable table above and the following commit graph:
When the feature branch is checked out, the following query returns the results below, showing the row at every ancestor commit reachable from our current branch.
dolt_logThe following query shows the commits reachable from the current checked out head and created by user jennifersp since April, 2022:
dolt_commit_diff_$TABLENAMEFor every user table named $TABLENAME, there is a read-only system table named dolt_commit_diff_$TABLENAME that can be queried to see a diff of the data in the specified table between any two commits in the database. For example, you can use this system table to view the diff between two commits on different branches. The schema of the returned data from this system table is based on the schema of the underlying user table at the currently checked out branch.
You must provide from_commit and to_commit in all queries to this system table in order to specify the to and from points for the diff of your table data. Each returned row describes how a row in the underlying user table has changed from the from_commit ref to the to_commit ref by showing the old and new values.
The remaining columns are dependent on the schema of the user table at the currently checked out branch.
For every column X in your table at the currently checked out branch, there are columns in the result set named from_X and to_X with the same type as X in the current schema. The from_commit and to_commit parameters must both be specified in the query, or an error is returned.
Consider a simple example with a table that has one column:
Based on the table's schema above, the schema of the dolt_commit_diff_$TABLENAME will be:
Now consider the following branch structure:
We can use the above table to represent two types of diffs: a two-point diff and a three-point diff. In a two-point diff we want to see the difference in rows between Point C and Point G.
We can also compute a three-point diff using this table. In a three-point diff we want to see how our feature branch has diverged from our common ancestor E, without including the changes from F and G on main.
dolt_diffThe dolt_diff system table shows which tables in the current database were changed in each commit reachable from the active branch's HEAD. When multiple tables are changed in a single commit, there is one row in the dolt_diff system table for each table, all with the same commit hash. Any staged or unstaged changes in the working set are included with the value WORKING for their commit_hash. After identifying the tables that changed in a commit, the dolt_diff_$TABLENAME system tables can be used to determine the data that changed in each table.
The DOLT_DIFF system table has the following columns:
dolt_diff displays the changes from the current branch HEAD, including any working set changes. If a commit did not make any changes to tables (e.g. an empty commit), it is not included in the dolt_diff results.
From these results, we can see there were four commits to this database in April 2022. Commits 224helo only changed the dolt_schemas table, commit 7jrvg1a changed the dolt_docs table, and commit 5jpgb0f made changes to two tables. We can also see which of these tables had changes made to their schemas vs just data.
To dig deeper into these changes, we can query the dolt_diff_$TABLE system tables specific to each of the changed tables, like this:
dolt_column_diffThe dolt_column_diff system table shows which columns and tables in the current database were changed in each commit reachable from the active branch's HEAD. When multiple columns are changed in a single commit, there is one row in the dolt_column_diff system table for each column, all with the same commit hash. Any staged changes in the working set are included with the value STAGED for their commit_hash. Any unstaged changes in the working set are included with the value WORKING for their commit_hash.
The DOLT_COLUMN_DIFF system table has the following columns
dolt_column_diff displays the changes from the current branch HEAD, including any working set changes. If a commit did not make any changes to tables (e.g. an empty commit), it is not included in the dolt_column_diff results.
If we narrow in on the dolt_schemas table we can count the number of commits that updated each column over the course of all our commits.
From these results, we can see that fields describing the reasons an inmate is being held are being updated far more frequently than the fields holding demographic information about inmates.
dolt_diff_$TABLENAMEFor every user table named $TABLENAME, there is a read-only system table named dolt_diff_$TABLENAME that returns a list of diffs showing how rows have changed over time on the current branch. Each row in the result set represents a row that has changed between two adjacent commits on the current branch – if a row has been updated in 10 commits, then 10 individual rows are returned, showing each of the 10 individual updates.
Every Dolt diff table will have the columns
The remaining columns are dependent on the schema of the user table at the current branch. For every column X in your table at the current branch there will be columns in the result set named from_X and to_X with the same type as X.
For a table named states with the following schema:
The schema for dolt_diff_states would be:
For each row the field diff_type will be one of the values added, modified, or removed. You can filter which rows appear in the result set to one or more of those diff_type values in order to limit which types of changes will be returned.
dolt_conflictsdolt_conflicts is a system table that has a row for every table in the working set that has an unresolved merge conflict.
dolt_conflicts_$TABLENAMEFor each table $TABLENAME in conflict after a merge, there is a corresponding system table named dolt_conflicts_$TABLENAME. The schema of each such table contains three columns for each column in the actual table, representing each row in conflict for each of ours, theirs, and base values.
Consider a table mytable with this schema:
If we attempt a merge that creates conflicts in this table, the dynamically generated table will show the details of the conflict:
The table has a single row for every row that conflicts in the table. Each column is listed three times, once for the base, once for the ours value, and once for the theirs value. Additionally, the our_diff_type and their_diff_type columns indicate whether the row was "added", "modified", or "removed" in the corresponding branch. The dolt_conflict_id column is unique for each row, and can be used to identify the specific conflict when writing automated conflict resolution applications. Finally, the first column, from_root_ish is the ID of the database root at the time of the merge. User code generally ignores this column.
To simplify, you can query for just the columns you are interested in:
To mark conflicts as resolved, delete them from the corresponding table. To effectively keep all our values, I would simply run:
If I wanted to keep all their values, I would first run this statement:
For convenience, you can also modify the our_ columns of the dolt_conflicts_mytable to update the corresponding row in mytable. The above replace statement can be rewritten as:
And of course you can use any combination of ours, theirs and base rows in these statements.
Updates made to the our_ columns are applied to the original table using the primary key (or keyless hash). If the row does not exist, it will be inserted. Updates made to our_ columns will never delete a row, however.
dolt_conflict_id is a unique identifier for the conflict. It is particularly useful when writing software that needs to resolve conflicts automatically.
from_root_ish is the commit hash of the "from branch" of the merge. This hash can be used to identify which merge produced a conflict, since conflicts can accumulate across merges.
dolt_schema_conflictsdolt_schema_conflicts is a system table that has a row for every table in the working set that has an unresolved schema conflict.
dolt_merge_statusThe dolt_merge_status system table tells a user if a merge is active. It has the following schema:
Let's create a simple conflict:
Output of SELECT * from dolt_merge_status;:
dolt_statusdolt_status returns the status of the database session, analogous to running dolt status from the command line.
dolt_workspace_$TABLENAMEThis system table shows you which rows have been changed in your workspace and if they are staged. It is the union of rows changed from HEAD to STAGED, and STAGED to WORKING. Any table listed in dolt_status table will have a non-empty corresponding dolt_workspace_$TABLENAME table. Changes listed are all relative to the HEAD of the current branch.
The schema of the source table is going to affect the schema of the workspace table. The first three column are always the same, then the schema of the source table is used to create "to_" and "from_" columns.
Each row in the dolt_workspace_$TABLENAME corresponds to a single row update in the table.
There are two ways you can alter the state of your workspace using these tables.
The staged column can be toggled for any row. If changing from false to true, the row values will be moved to staging. If there are already staged changes for that row, they will be overwritten. If changing from true to false, the row values will be unstaged. If there are other changes in the workspace for that row, the workspace change will be preserved and the staged change will be dropped.
Any row which has staged = FALSE can be deleted. This will result in reverting the change to the row in the source table.
The dolt_workspace_$TABLENAME tables are generated based on the session state when inspected, so they can not be considered stable on a branch which has multiple editors.
dolt_constraint_violationsThe dolt_constraint_violations system table contains one row for every table that has a constraint violation introduced by a merge. Dolt enforces constraints (such as foreign keys) during normal SQL operations, but it's possible that a merge puts one or more tables in a state where constraints no longer hold. For example, a row deleted in the merge base could be referenced via a foreign key constraint by an added row in the merged commit. Use dolt_constraint_violations to discover such violations.
dolt_constraint_violations_$TABLENAMEFor each table $TABLENAME with a constraint violation after a merge, there is a corresponding system table named dolt_constraint_violations_$TABLENAME. Each row in the table represents a constraint violation that must be resolved via INSERT, UPDATE, or DELETE statements. Resolve each constraint violation before committing the result of the merge that introduced them.
For a hypothetical table a with the following schema:
dolt_constraint_violations_a will have the following schema:
Each row in the table represents a row in the primary table that is in violation of one or more constraint violations. The violation_info field is a JSON payload describing the violation. The payload varies depending on the type of constraint violation recorded.
For foreign key violations:
For unique constraints:
For not null constraints:
For check constraints:
As with dolt_conflicts, delete rows from the corresponding dolt_constraint_violations table to signal to dolt that you have resolved any such violations before committing.
Configuration Tables can be staged and versioned just like user tables. They always exist, even in an empty database.
dolt_ignoredolt_ignore stores a list of "table name patterns", and a boolean flag for each pattern indicating whether tables that match the patterns should not be staged for commit.
This only affects the staging of new tables. Tables that have already been staged or committed are not affected the contents of dolt_ignore, and changes to those tables can still be staged.
The format of patterns is a simplified version of gitignore’s patterns:
An asterisk "*" or ampersand "%" matches any number of characters.
The character "?" matches any one character.
All other characters match exactly.
If a table name matches multiple patterns with different values for ignored, the most specific pattern is chosen (a pattern A is more specific than a pattern B if all names that match A also match pattern B, but not vice versa.) If no pattern is most specific, then attempting to stage that table will result in an error.
Tables that match patterns in dolt_ignore can be force-committed by passing the --force flag to dolt add or CALL dolt_add.
dolt diff won't display ignored tables, and dolt show won't display ignored tables unless the additional --ignored flag is passed.
dolt_rebaseThe action field can take one of the following rebase actions:
pick - apply a commit as is and keep its commit message.
drop - do not apply a commit. The row in the dolt_rebase table can also be deleted to drop a commit from the rebase plan.
reword - apply a commit, and use the updated commit message from the commit_message field in the dolt_rebase table for its commit message. Note that if you edit the commit_message but do not use the reword action, the original commit message will still be used.
squash - apply a commit, but include its changes in the previous commit instead of creating a new commit. The commit message of the previous commit will be altered to include the previous commit message as well as the commit message from the squashed commit. Note that the rebase plan MUST include a pick or reword action in the plan before a squash action.
fixup - apply a commit, but include its changes in the previous commit instead of creating a new commit. The commit message of the previous commit will NOT be changed, and the commit message from the fixup commit will be discarded. Note that the rebase plan MUST include a pick or reword action in the plan before a fixup action.
To squash all commits into a single commit and include the commit messages from all commits, the following query can be used:
To reword a commit with commit hash '123aef456f', be sure to set the action to reword and to update the commit_message field:
To drop the second commit in the rebase plan, you can use the drop action:
Or you can simply delete that row from the dolt_rebase table:
Because the branch information is global to all clients, not just your session, dolt_branches system table is read-only. Branches can be created or deleted with the .
To find the current active branch use .
dolt_branches only contains information about local branches. For branches on a remote you have fetched, see .
The dolt_query_catalog system table stores named queries for your database. Like all data stored in Dolt, these named queries are versioned alongside your data, so after you create, modify, or remove a named query, you'll need to commit that change to save it.
You can use the Dolt CLI to save and execute named queries or you can use the
dolt_query_catalog system table directly to add, modify, or delete named queries. All named queries are displayed in the Queries tab of your database on .
The dolt_remotes table is currently read only. Use the CLI dolt remote functions or to add, update or delete remotes.
The dolt_backups table is currently read-only. Use the to add, update, or delete backups.
procedure can be used to INSERT and DELETE tags on the dolt_tags table.
For every user table that has a primary key, there is a queryable system view named dolt_blame_$tablename which can be queried to see the user and commit responsible for the current value of each row. This is equivalent to the . Tables without primary keys will not have an associated dolt_blame_$tablename.
This is similar, but different from the dolt_log and the dolt log . dolt log shows you commit history for all commit ancestors reachable from the current HEAD of the checked out branch, whereas dolt_commits shows all commits from the entire database, no matter which branch is checked out.
Using the , we can query for the five commits before April 20th, 2022, across all commits in the database (regardless of what is checked out to HEAD) with this query:
The dolt_log system table contains the commit log for all commits reachable from the current HEAD. This is the same data returned by the .
dolt_commit_diff_$TABLENAME is the analogue of the dolt diff CLI command.
It represents the between the two commits provided. The dolt_diff_$TABLENAME system table also exposes diff information, but instead of a two-way diff, it returns a log of individual diffs between all adjacent commits in the history of the current branch. In other words, if a row was changed in 10 separate commits, dolt_diff_$TABLENAME will show 10 separate rows – one for each individual delta. In contrast, dolt_commit_diff_$TABLENAME would show a single row that combines all the individual commit deltas into one diff.
The is an alternative to the dolt_commit_diff_$tablename system table for cases where a table's schema has changed between the to and from commits. Consider the DOLT_DIFF() table function if you need to see the schema from each of those commits, instead of using the schema from the currently checked out branch.
computes the closest ancestor E between main and feature.
There is one special to_commit value WORKING which can be used to see what changes are in the working set that have yet to be committed to HEAD. It is often useful to use to get the commit hash of a branch, or an ancestor commit. The above table requires both from_commit and to_commit to be filled.
Taking the database from as our example, the following query uses the dolt_diff system table to find all commits, and the tables they changed, from the month of April 2022.
Taking the database from as our example, the following query uses the dolt_column_diff system table to find commits and tables where the column name was updated.
Compared to the , the dolt_diff_$TABLENAME system table focuses on how a particular row has evolved over time in the current branch's history. The major differences are that dolt_commit_diff_$TABLENAME requires specifying from_commit and to_commit, works on any commits in the database (not just the current branch), and returns a single combined diff for all changes to a row between those two commits. In the example above where a row is changed 10 times, dolt_commit_diff_$TABLENAME would only return a single row showing the diff, instead of the 10 individual deltas.
A SELECT * query for a diff table will show you every change that has occurred to each row for every commit in this branch's history. Using to_commit or from_commit will limit the data to specific commits. There is one special to_commit value WORKING which can be used to see what changes are in the working set that have yet to be committed to HEAD. It is often useful to use the function to get the commit hash of a branch, or an ancestor commit. For example, to get the differences between the last commit and its parent you could use to_commit=HASHOF("HEAD") and from_commit=HASHOF("HEAD^").
Taking the database from as our example, the following query will retrieve the jails whose total num_inmates_rated_for have changed the most between 2 versions.
Query this table when resolving conflicts in a SQL session. For more information on resolving merge conflicts in SQL, see docs for the tables.
Query this table when resolving schema conflicts in a SQL session. For more information on resolving schema conflicts during merge, see the docs on .
These tables can be modified in order to update what changes are staged for commit.
The staged column will be TRUE when the changes are going to be committed on the next call to . Changes which have staged = FALSE are present in your workspace which means all queries in your session contain them but they will not be recorded in the event that is executed.
dolt_rebase is only present while an interactive rebase is in progress, and only on the branch where the rebase is being executed. For example, when rebasing the feature1 branch, the rebase will be executed on the dolt_rebase_feature1 branch, and the dolt_rebase system table will exist on that branch while the rebase is in-progress. The dolt_rebase system table starts off with the default rebase plan, which is to pick all of the commits identified for the rebase. Users can adjust the rebase plan by updating the dolt_rebase table to change the rebase action, reword a commit message, or even add new rows with additional commits to be applied as part of the rebase. For more details about rebasing, see .