DoltHub and DoltLab provide multiple APIs for executing Dolt SQL, fetching database data as CSV files, or automating your workflows for your database.

API tokens can be used to authenticate calls to the SQL API over Basic Authentication. This is useful for executing SQL queries against private databases or executing write queries.

First, create an API token in your settings on DoltHub. Copy the token right away, as you won't be able to see it again.

Now you can use this token in the header when executing a query against a private database.

owner, repo, branch = "dolthub", "private-db", "main"
query = """SELECT * FROM testtable"""
res = requests.get(
    "https://www.dolthub.com/api/v1alpha1/{}/{}/{}".format(owner, repo, branch),
    params={"q": query},
    headers={ "authorization": "token [TOKEN YOU COPIED]" },
)
res.json()

Please note: You must include a ref name (branch, tag, commit hash, etc) when making authenticated calls to the SQL API using a token. Unauthenticated API requests do not require this. They use the default branch (main or master).

DoltHub is a place to share Dolt databases. We host public data for free! DoltHub adds a modern, secure, always on database management web GUI to the Dolt ecosystem. Edit your database on the web, have another person review it via a pull request, and have the production database pull it to deploy.

What is DoltHub

DoltHub is GitHub for Dolt. DoltHub acts as a Dolt remote you can clone, push, pull and fetch from. DoltHub adds permissions, pull requests, issues, and forks to the Dolt ecosystem. Additionally, DoltHub has a modern SQL workbench built in so you can explore and change databases on the web.

Getting Started

DoltHub has many uses. We recommend getting started by .

This documentation will walk you through discovering data on DoltHub, cloning a copy locally, making a change on a fork, and submitting a pull request to the original database.

DoltHub API

DoltHub has you can script against. The documentation covers:

2. - Used to make read or write SQL queries to a DoltHub database

3. - Used to download CSV format files of DoltHub tables

Guides

We added a feature to . This allows you to set up an API to receive and transform files that are uploaded to your databases on DoltHub. The transformed files are sent back to DoltHub for import to a DoltHub database.

We added the workspaces concept to DoltHub as a staging area for changes made from the web. Learn what workspaces are and how to use them most effectively.

DoltHub and DoltLab support continuous integration (CI) testing which allow you to validate changes before you commit them on your primary branch. Learn how to configure and run CI on your DoltHub and DoltLab databases.

What are workspaces?

Workspaces are a concept we invented for editing data on DoltHub. There is not a GitHub equivalent. Essentially workspaces are a special type of ref, similar to or . They are meant to simulate a "staging area" on DoltHub, similar to in Dolt.

Workspaces are shareable and temporary. Make any change to your database without affecting the state of your database. When you're satisfied with your changes you can commit them to a branch or create a pull request. If you're not satisfied with your changes, you can delete the workspace without any repercussions.

DoltHub provides a CSV API for fetching table data as CSVs. You can request a CSV for an individual table or a zip of all table CSVs at a specified commit or branch.

Find data on DoltHub

Our on DoltHub lets you find public databases created by users in our community. You can easily share these databases and adopt them for your own use cases.

Workflows are yaml files stored in a Dolt database that specify one or more CI Jobs and identify when those Job(s) should run.

name

String. The case-insensitive name of the workflow, must be unique. Required.

DoltHub/DoltLab Continuous Integration (CI)

DoltHub and DoltLab support continuous integration (CI) testing which allow you to validate changes before you commit them on your primary branch.

Continuous integration (CI) testing originated as a software development best practice where automated tests run against incoming code changes pushed by software developers who are collaborating on a code repository.

If a developer pushes changes that fail to pass the automated tests, the proposed changes are rejected. This practice ensures that only valid, high quality changes get committed on the primary build branch, resulting in fewer bugs, and higher developer productivity.

Dolt's revolutionary technology that marries Git and MySql now allows for CI testing on data and is supported on both DoltHub and DoltLab. In the same way the proposed code changes undergo automated tests to ensure they're valid, proposed data changes on a DoltHub or DoltLab database can also undergo automated tests to assert their validity.

The following sections will introduce you to how CI works with Dolt, DoltHub and DoltLab and help you setup CI testing for your own databases.

CI starts with Dolt

CI configuration for a DoltHub or DoltLab database is stored in the database itself. At the time of this writing, in order to add CI configuration to a DoltHub or DoltLab database, you will need to have a local Dolt client version >= and will have to clone a copy of the the database. In order to configure CI on the database, you will use Dolt's CI CLI commands.

Dolt CI Commands

The primary interface for creating and editing CI configuration in a Dolt database is via the dolt ci CLI command. These commands aim to simplify CI configuration in Dolt, so that users do not need to manually interact with the underlying CI tables directly.

The dolt ci commands as of Dolt v1.45.3 are:

• . This command creates internal database tables used to store continuous integration configuration.

• . This command drops all database tables used to store continuous integration configuration.

• . This command will import a Dolt continuous integration workflow file into the database.

• . This command will export a Dolt continuous integration workflow by name.

The dolt ci init command is the starting point for adding CI to a Dolt database, since it creates the underlying tables Dolt needs to begin storing configuration. To get started adding CI to a Dolt database, follow our .

Workflows

Borrowing from terminology, Dolt CI configuration is also defined as workflows and follows their same general syntax and definitions. Workflows are yaml files that define when CI on a database should run, and what should happen during the course of that run. Though workflows in Dolt are defined and edited as files, Dolt does not store these files directly. Instead, it parses these files and only stores the relevant content of each file in its internal CI tables. These tables are then read by DoltHub and DoltLab to enact the defined CI runs.

More specifically, a workflow file specifies when it should run, by the Events defined in it, and what should happen during the run by the Jobs defined in it.

Events

Events are specific activities that occur in a DoltHub or DoltLab database that trigger a workflow to run. One such event might be the pushing of a branch to the database, known as a push event, or the opening of a pull request on a database, a pull_request event. When these events occur on a database that contains a workflow that specifies it should run on these events, DoltHub and DoltLab run them.

For a complete list of events that trigger workflows, please see the .

Jobs

In addition to Events, A workflow file also contains Jobs. In the context of a workflow, Jobs define what should happen when a workflow runs. This definition consists of a series of programmatic Steps that DoltHub or DoltLab will execute during the course of a workflow run. These workflow Jobs are somewhat related to DoltHub and DoltLab Jobs, although at this time their relationship has no direct impact on the end-user.

These DoltHub/DoltLab Jobs, are the automated asynchronous machinery that allow DoltHub and DoltLab to run long running processes to do programmatic work outside of its main API. These come in different types and depending on their type, do things like merge pull requests, or import a file uploaded by a user into a database. With the addition of CI on DoltHub and DoltLab, a new type of Job was added, a Dolt CI Job. This is the type of DoltHub/DoltLab Job executes a workflow Job as it is defined in the workflow file.

Steps

A workflow Job is made up of a series of Steps. A step, in its current form, is a single Saved Query that will run against the database as the "check", or test, that asserts the database branch's validity. Steps run in the order they're defined and will "pass", or succeed, if the Saved Query they execute completes without error and if the defined expected SQL results match the actual SQL results returned from the Saved Query.

For more information on Steps, please see the .

Saved Query

A is a SQL query that is stored and versioned in a Dolt database. For the purpose of DoltHub and DoltLab CI, this allows users to write a SQL query that will be executed on command at a later time, during a CI run.

For example, take the simple SQL query "show tables;". This can be added to a Dolt database as a saved query using the with the --save flag.

The query is saved under the name "Show tables query", and can be executed at anytime with the -x flag.

In a Dolt CI workflow file, this saved query can be reference by name as a Step during the execution of a workflow Job.

During the execution of the example workflow defined above, the "Show tables query" will be executed whenever the "example job" runs. This will result in the SQL query "show tables;" running against the database.

Saved queries are currently the primary method for defining tests or checks on a Dolt database.

For more information on Saved Query Steps, please see the .

What is a hook?

Hooks allow you to build or set up integrations that subscribe to certain events on DoltHub, such as pushing data. You give DoltHub the URL of an HTTP endpoint you've set up to accept POST requests, and then when certain events happen your endpoint will receive a payload.

DoltHub provides an API for accessing Dolt databases via web requests. A Dolt database can be attached to a DoltHub remote and pushed. At that point DoltHub provides an API against which users can execute Dolt SQL with results returned as JSON.

DoltHub and DoltLab >= v0.8.1 allow users to optionally transform files before they're imported into a database. To do this, users can provide a Transform Url at the time they're uploading a file.

If a Transform Url is provided, DoltHub/DoltLab will send an http POST request containing the uploaded file contents. In the http response to this request, the service receiving the request can return new file contents that will be imported into the database in place of the original file contents.

Additionally, on the Database Settings page of DoltHub/DoltLab, users can provide Transform Url along with request headers, which, will automatically be used to transform all file uploads across a database.

Pull requests are automatically created against a database after successful file uploads and imports.

DoltHub provides a database API for fetching and creating data on your database. You can create a database, create a pull request, create a pull request comment, and merge a pull request through these APIs.

Create database

Here's an example of how to create a new database called museum-collections under the organization dolthub using an .

Creating a database requires authentication, so you must include this authorization header in your request. See the section for more details.

Create pull request

Here is an example of opening a pull request on the museum-collections database with data from the Los Angeles County Museum of Art. This data was added to the lacma branch on a fork database, whose owner is liuliu, we would like to eventually merge lacma branch into the main branch using an .

Include this header in your request.

Get pull request details

This API allows you to retrieve the details of a specific pull request in the museum-collections database. In this example, we will retrieve the details of pull request #1.

Include this header in your request.

Update a pull request

This API allows you to update a pull request by providing the fields you want to update in the request body. You can update the title, description, and state (only closing a pull request is supported).

Here's an example of how to update pull request #1 on the museum-collections database. In this example, we will set a new title, description, and close the pull request.

List pull requests

Here is an example of listing pull requests for the museum-collections database using an . The response of pull request list is paginated, so you need to use the next page token included in the response to retrieve the following pages of pull requests.

Include this header in your request.

Create a pull request comment

Here is an example of adding a pull request comment using an .

Include this header in your request.

Merge pull request

Here is an example of merging a pull request #66 on a database museum-collections using an . Note that the merge operation is asynchronous and creates an operation that can be polled to get the result.

To poll the operation and check its status, you can use the operationName in the returned response of the merge request to query the API. Once the operation is complete, the response will contain a job_id field indicating the job that's running the merge, as well as other information such as the database_owner, database_name, and pull_id.

Keep in mind that the time it takes for the merge operation to complete can vary depending on the size of the pull request and the complexity of the changes being merged.

Include this header in your request with the API token you created.

Then use GET to poll the operation to check if the merge operation is done.

Upload a file

Here is an example of uploading a file lacma.csv to create a table lacma on a database museum-collections using an . Note that the file import operation is asynchronous and creates an operation that can be polled to get the result.

To poll the operation and check its status, you can use the operationName in the returned response of the file upload post to query the API. Once the operation is complete, the response will contain a job_id field indicating the job that's running the file import as well as the id of the pull request that's created when the import job is completed.

Keep in mind that the time it takes for the import operation to complete can vary depending on the size of the file and the complexity of the changes being applied to the database. The file size limit is 100 MB.

Include this header in your request with the API token you created.

To upload the file, include two fields in the request body, file and params, the file should be type of Blob, and params should be a JSON object.

Then use GET to poll the operation to check if the import operation is done.

Here is an example of uploading a CSV file to create a table through this api endpoint in Javascript, you can reference the documentation for additional information.:

And an example of polling the job status in Javascript:

Create a branch

Here's an example of how to create a new branch in database museum-collections under the organization dolthub using an .

Creating a branch requires authentication, so you must include this authorization header in your request. See the section for more details.

List branches

Here's an example of how to list branches in the database museum-collections under the organization dolthub using an .

Listing branches requires authentication, so you must include this authorization header in your request. See the section for more details.

Create a tag

Here's an example of how to create a new tag in the database museum-collections under the organization dolthub using an .

Creating a tag requires authentication, so you must include this authorization header in your request. See the section for more details.

List tags

Here's an example of how to list tags in the database museum-collections under the organization dolthub using an .

Listing tags requires authentication, so you must include this authorization header in your request. See the section for more details.

Create a release

Here's an example of how to create a new release in the database museum-collections under the organization dolthub using an .

Creating a release requires authentication, so you must include this authorization header in your request. See the section for more details.

{% swagger src="../../../.gitbook/assets/dolthub-api/createRelease.json" path="/{owner}/{database}/releases" method="post" % }

List releases

Here's an example of how to list releases in the database museum-collections under the organization dolthub using an .

Listing releases requires authentication, so you must include this authorization header in your request. See the section for more details.

List operations

DoltHub provides support for asynchronous operations, including merging, SQL writes, and file importing. When you execute one of these operations from the API, you will get an operation name that you can poll using another endpoint to check the operation status and other information.

This API endpoint lets you monitor the status of all the operations you started in one place without needing to poll the endpoints for singular operations. These operations have error and metadata fields which contain useful information for troubleshooting and debugging.

For example, if you have executed a few SQL write queries using that , you can list those operations using the operationType query parameter to filter for SqlWrite operations. The metadata will show the query executed, database and branch that the query ran on, as well as any syntax or other errors you may have encountered.

Here's an example of how to list SqlWrite operations initiated by user liuliu using an .

Listing operations requires authentication, so you must include this authorization header in your request. See the section for more details.

List jobs

DoltHub performs certain asynchronous operations through job execution, including merging, importing, SQL reading, and migrating. When these operations are initiated via the API, you receive an operation name that includes the job ID.

This API endpoint lets you monitor the status of jobs started in a specific database.

Here is an example of how to list all the jobs on a database museum-collections using an .

Listing jobs requires authentication, so you must include this authorization header in your request. See the section for more details.

CI for DoltHub and DoltLab requires Dolt v1.45.3. This release contains the dolt ci command you will use to configure CI to run.

To start, let's ensure we have the correct Dolt version installed locally.

Now, let's clone a database that's hosted on DoltHub that we want to run CI tests on.

I've created the fork, dolthub/options, of the popular DoltHub databasepost-no-preference/options, and cloned a copy of my fork locally.

The first step required to enable a Dolt database to run CI is to run the dolt ci init command. This command will create Dolt's internal CI tables and also writes a new commit to the branch.

After CI initialization, we need to create the workflow file that will define our CI tests.

Defining a Workflow

Modeled after GitHub Action's , Dolt CI is configured with a yaml file that gets imported into the database.

Let's create an example workflow.yaml file now.

The above workflow.yaml definition should look pretty familiar to GitHub Action's users.

It defines a new Dolt workflow named "my first DoltHub workflow" in the top-level name field, specifies when this workflow should run in the on field, and what should happen when the workflow runs, defined in the jobs field. Each of these top-level fields is required.

A Workflow's name must be unique and is case-insensitive.

The on field defines when the workflow should run, or rather, what should trigger the workflow to run. The above workflow is configured to run whenever a push to this database's master branch occurs.

jobs defines the work to be performed when the workflow is run. Each workflow Job must have a unique name and at least one step defined in the steps field. Currently, these are where Dolt CI differs the most from GitHub Actions.

In GitHub Actions, a workflow step, or action step, can be the running of an arbitrary binary or snippet of code that is executed as part of a Job. For Dolt CI though, at least in its current form, a job step can only execute a Saved Query, which must be identified by name in the saved_query_name field.

A in Dolt, is an arbitrary SQL query stored in the database for execution at a later time. By specifying the name of the saved query in the workflow.yaml file, we are configuring CI to execute the "show tables" saved query against the master branch, whenever a push to master occurs.

Additionally, each "step" optionally allows an expected_rows or expected_columns field to be defined, which can be used to assert the number of rows or columns in the resulting output of the saved query.

For this simple example, we will assert that the number of rows returned from our "show tables" saved query will be equal to (==), 2.

Let's save this file, and store our new workflow in the database.

To do this, we simply use the dolt ci import <file> command.

Like the dolt ci init command earlier, this command also automatically creates a new commit. At this point the file we created workflow.yaml is no longer needed, as the configuration has been persisted in the Dolt database.

If we ever need this file again, we can simply list the defined workflows in the database with the dolt ci ls command, then export the workflow back to yaml with the dolt ci export <workflow name> command.

The final step we need to perform on our local database is to define the saved query named "show tables".

Defining a saved query

A saved query can be added by using the with the --save option. And, as the name of our saved query suggests, we'll save the query "SHOW TABLES;".

Above we can see the expected output of the "show tables" saved query. The results have two rows, one row for each table in the database.

After creating the saved query, we can see the creation of the internal dolt_query_catalog table which stores saved queries. We now need to add and commit this new table to our database.

With the workflow and saved query defined on the database, we can push our updates to DoltHub.

Workflow Jobs on DoltHub

Once we push the master branch, DoltHub will run our workflow for the first time. It does this by creating a new Dolt CI Job, in the same way it creates Jobs for pull request merges.

Dolt CI Jobs are visible on from the "Jobs" tab.

Here you can see the workflow job we defined in our yaml file "validate tables" is now running. Notice that for the time being, all Dolt CI Jobs will say they were created by the database owner, regardless of the person responsible for the push event. This will be fixed at a later date.

If the Dolt CI Job is successful, meaning the saved query succeeded and the expected number of rows were found, the Job will have status "Succeeded". If the Job failed, the status would be "Failed" and we would see a relevant failure message on the Job's detail page.

To demonstrate what this looks like, let's update our workflow.yaml file to include another step. This time, let's add a step for a saved query that does not exist in the database.

You can see that we've now added a step called "assert table option_chain exists" to our workflow, which uses a saved query "option_chain exists". Let's import these new changes into our local database, but skip saving the saved query "option_chain exists".

Now with our workflow configuration updated and pushed to DoltHub, we can go check the status of our new Workflow Job.

As expected, the Workflow Job that ran on push now failed. And if we click "View details" we can see that it's because we did not add the saved query to the database.

Ok, so let's fix the step we just added by adding the saved query "option_chain exists" to the database and pushing the changes.

And after that latest Workflow Job completes, we can see that our "validate tables" Job is passing again!

Getting fancier

Let's update our workflow.yaml once more, but get a little fancier with it.

Above, we've done quite a bit more than before. With this updated workflow we now have added additional job definitions that will check each tables' schema, and ensure each table has data.

After importing this new workflow.yaml into our local database, we create all of the saved queries we've referenced above in the file. The queries for each are shown in the dolt_query_catalog table below.

After we push these new changes to DoltHub, they'll be live.

Now, let's say that hypothetically, a new committer, unaware of the updated workflow we've defined, comes along and decides to delete all data from the option_chain table and pushes this change to master.

Our updated workflow defined for this database will kick-off each of the Workflow Jobs, as seen in the image below.

But, we'd expect the "check data" Workflow Job to fail. And, it does.

If we click "View details", we can see the failure resulted from expecting > 0 rows, but got 0.