SQL
Last updated
Was this helpful?
Last updated
Was this helpful?
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.
Please make sure to send your requests to https://www.dolthub.com
instead of https://dolthub.com
.
We will use an example DoltHub database, dolthub/ip-to-country and the Python requests
library to explore it in the Python console:
This shows our database metadata as a dictionary:
We can now execute a query:
This yields the results as JSON, with both schema and data:
As a reminder, this API is in Alpha version, and we will continue to flesh this out, as well as improve query performance, over time.
You can also explore our SQL API on DoltHub whenever you execute a query:
Since adding, updating, and deleting data can take a bit of time to execute against larger databases, we made the writable API asynchronous. Therefore we needed to add two HTTP endpoints for executing these kinds of queries using the SQL API.
We can use our SHAQ database as an example. These are the steps we'd take to update a player's id in the player stats table.
First, we want to hit the write endpoint with our UPDATE
query. This will start an asynchronous operation.
Note: for larger queries you can put the query in the request body (json={"query": [your query]}
) instead of using the query parameter.
The main
branch already exists in the database, and providing a <to_branch>
that doesn't exist will create this new branch from the <from_branch>
.
The yielded JSON results include an operation_name
.
operation_name
can be used to poll the second endpoint to check if the operation is done.
A done
operation will yield a response that includes some query metadata, including from and to commit ids.
Since a lot of Dolt's functionality is exposed via SQL, we can use the commit ids to query the dolt_commit_diff_$tablename
table to view the resulting diff from our UPDATE
query.
We can repeat this process with as many queries as we want. Every query will create a commit on the <from_branch>
.
Once we're satisfied with our changes, we can merge our branches by hitting the first endpoint with an empty query.
You can learn more about using the writable SQL API here.
/{owner}//{database}
The SQL query to execute.
SHOW TABLES
The name of the database owner.
dolthub
The name of the database.
ip-to-country
/{owner}//{database}//{ref}
The SQL query to execute.
SELECT * FROM IPv4ToCountry WHERE CountryCode2Letter = 'AU'
The name of the database owner.
dolthub
The name of the database.
ip-to-country
The database ref to execute the query against.
newbranch
Executes SQL write against to_branch (will be created from from_branch if it doesn't exist). If no query is provided, will merge to_branch into from_branch.
/{owner}//{database}/write/{from_branch}//{to_branch}
The name of the database owner.
dolthub
The name of the database.
SHAQ
The base branch.
main
The branch to write to. Will be created from the from_branch if it doesn't exist.
feature
The SQL query to execute. Use the request body instead for larger queries.
UPDATE player_season_stat_totals SET player_id=714287 WHERE player_id=15404617
SQL write query to execute. Can be used in place of the query parameter for larger queries.
UPDATE player_season_stat_totals SET player_id=714287 WHERE player_id=15404617
Poll the operation to check if the SQL write operation is done
/{owner}//{database}/write
The name of the database owner.
dolthub
The name of the database.
SHAQ
The name of the operation
operations/72abb56b-d478-43ae-9a2d-c9602184c7ab