LogoLogo
DoltHubBlogDiscordGitHubDolt
  • Introduction
    • What Is Dolt?
    • Installation
      • Linux
      • Windows
      • Mac
      • Build from Source
      • Application Server
      • Docker
      • Upgrading
    • Getting Started
      • Version Controlled Database
      • Git For Data
      • Versioned MySQL Replica
    • Use Cases
      • Data Sharing
      • Data and Model Quality Control
      • Manual Data Curation
      • Version Control for your Application
      • Versioned MySQL Replica
      • Audit
      • Configuration Management
      • Offline First
  • Concepts
    • Dolt
      • Git
        • Commits
        • Log
        • Diff
        • Branch
        • Merge
        • Conflicts
        • Remotes
        • Working Set
      • SQL
        • Databases
        • Schema
        • Tables
        • Primary Keys
        • Types
        • Indexes
        • Views
        • Constraints
        • Triggers
        • Procedures
        • Users/Grants
        • Transactions
        • System Variables
      • RDBMS
        • Server
        • Backups
        • Replication
    • DoltHub/DoltLab
      • Permissions
      • Pull Requests
      • Issues
      • Forks
  • SQL Reference
    • Running the Server
      • Configuration
      • Access Management
      • Branch Permissions
      • Backups
      • Garbage Collection
      • Metrics
      • Replication
      • Troubleshooting
    • Version Control Features
      • Using Branches
      • Merges
      • Querying History
      • Using Remotes
      • Procedures
      • Functions
      • System Tables
      • System Variables
      • Saved Queries
    • SQL Language Support
      • Data Description
      • Expressions, Functions, Operators
      • Supported Statements
      • MySQL Information Schema
      • Collations and Character Sets
      • System Variables
      • Miscellaneous
    • Supported Clients
      • Programmatic
      • SQL Editors
    • Benchmarks and Metrics
      • Correctness
      • Latency
      • Import
  • CLI Reference
    • Commands
    • Git Comparison
  • Architecture
    • Overview
    • Storage Engine
      • Commit Graph
      • Prolly Trees
      • Block Store
    • SQL
      • Go MySQL Server
      • Vitess
  • Guides
    • Cheat Sheet
    • Contributing
      • dolt
      • go-mysql-server
    • MySQL to Dolt Replication
    • Importing Data
    • Integrations
  • Other
    • FAQ
    • Roadmap
    • Versioning
  • Products
    • Hosted Dolt
      • Getting Started
      • Notable Features
      • SQL Workbench
      • Cloning a Hosted Database
      • Using DoltHub as a Remote
      • Infrastructure
    • DoltHub
      • Data Sharing
      • API
        • Authentication
        • SQL
        • CSV
        • Database
        • Hooks
      • Continuous Integration
        • Getting Started
        • Workflow Reference
      • Transform File Uploads
      • Workspaces
    • DoltLab
    • Dolt Workbench
    • DoltgreSQL
Powered by GitBook
On this page

Was this helpful?

Edit on GitHub
Export as PDF
  1. Products
  2. DoltHub
  3. API

SQL

PreviousAuthenticationNextCSV

Last updated 1 year ago

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.

Reading

Using the default branch

import requests

owner, database = 'dolthub', 'ip-to-country'
res = requests.get('https://dolthub.com/api/v1alpha1/{}/{}'.format(owner, database))
res.json()

This shows our database metadata as a dictionary:

{'query_execution_status': 'Success',
 'query_execution_message': '',
 'repository_owner': 'dolthub',
 'repository_name': 'ip-to-country',
 'commit_ref': 'main',
 'sql_query': 'SHOW TABLES;',
 'schema': [{'columnName': 'Table',
   'columnType': 'longtext'}],
 'rows': [{'Table': 'IPv4ToCountry'}, {'Table': 'IPv6ToCountry'}]}

Specifying a ref

We can now execute a query:

query = '''SELECT * FROM IPv4ToCountry WHERE CountryCode2Letter = "AU"'''
res = requests.get(
  'https://www.dolthub.com/api/v1alpha1/{}/{}/{}'.format(owner, database, branch),
  params={'q': query},
  )
res.json()

This yields the results as JSON, with both schema and data:

{'query_execution_status': 'RowLimit',
 'query_execution_message': '',
 'repository_owner': 'dolthub',
 'repository_name': 'ip-to-country',
 'commit_ref': 'main',
 'sql_query': 'SELECT * FROM IPv4ToCountry WHERE CountryCode2Letter = "AU"',
 'schema': [{'columnName': 'IPFrom',
   'columnType': 'Int'},
  {'columnName': 'IpTo', 'columnType': 'Int'},
  {'columnName': 'Registry', 'columnType': 'String'},
  {'columnName': 'AssignedDate', 'columnType': 'Int'},
  {'columnName': 'CountryCode2Letter',
   'columnType': 'String'},
  {'columnName': 'CountryCode3Letter',
   'columnType': 'String'},
  {'columnName': 'Country', 'columnType': 'String'}],
 'rows': [{'IPFrom': '16777216',
   'IpTo': '16777471',
   'Registry': 'apnic',
   'AssignedDate': '1313020800',
   'CountryCode2Letter': 'AU',
   'CountryCode3Letter': 'AUS',
   'Country': 'Australia'},
.
.
.
]}

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:

Writing

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.

1. Run query

First, we want to hit the write endpoint with our UPDATE query. This will start an asynchronous operation.

owner, database, from_branch, to_branch = 'dolthub', 'SHAQ', 'main', 'update-player'
query = '''UPDATE player_season_stat_totals SET player_id=714287 WHERE player_id=15404617'''
headers = {
    'authorization': 'token [see Authentication section for token]'
}

write_res = requests.post(
        f'https://www.dolthub.com/api/v1alpha1/{owner}/{database}/write/{from_branch}/{to_branch}',
        params={'q': query},
        headers=headers,
    )
write_json = write_res.json()

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.

{
  "query_execution_status": "Success",
  "query_execution_message": "Operation created. Poll the operation endpoint to see the execution status of this query.",
  "repository_owner": "dolthub",
  "repository_name": "SHAQ",
  "to_branch_name": "update-player",
  "from_branch_name": "main",
  "query": "UPDATE player_season_stat_totals SET player_id=714287 WHERE player_id=15404617",
  "operation_name": "operations/72abb56b-d478-43ae-9a2d-c9602184c7ab"
}

2. Poll operation

operation_name can be used to poll the second endpoint to check if the operation is done.

def get_operation(op_name):
  op_res = requests.get(
      f'https://www.dolthub.com/api/v1alpha1/{owner}/{database}/write',
      params={'operationName': op_name},
      headers=headers,
  )
  return op_res.json()


def poll_operation(op_name):
    done = False
    while done == False:
        poll_res = get_operation(op_name)
        done = poll_res['done']
        if done:
            return poll_res
        else:
            time.sleep(3)


res = poll_operation(write_json['operation_name'])

A done operation will yield a response that includes some query metadata, including from and to commit ids.

{
  "_id": "operations/72abb56b-d478-43ae-9a2d-c9602184c7ab",
  "done": true,
  "res_details": {
    "query_execution_status": "Success",
    "query_execution_message": "Query OK, 2 rows affected. Rows matched: 2  Changed: 2  Warnings: 0.",
    "owner_name": "tbantle",
    "repository_name": "SHAQ",
    "from_commit_id": "518ue176ec13qf563e87uoerkqqepood",
    "to_commit_id": "5sp7i0bov6itma2u04dpk15ui24lrigp"
  }
}

3. View changes

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.

from_commit, to_commit = res['res_details']['from_commit_id'], res['res_details']['to_commit_id']
query = f'''SELECT from_player_id, to_player_id, diff_type FROM `dolt_commit_diff_player_season_stat_totals` WHERE from_commit="{from_commit}" AND to_commit="{to_commit}"'''

diff_res = requests.get(
    f'https://www.dolthub.com/api/v1alpha1/{owner}/{database}/{to_branch}',
    params={'q': query},
    headers=headers,
)
diff_res.json()

We can repeat this process with as many queries as we want. Every query will create a commit on the <from_branch>.

4. Merge changes

Once we're satisfied with our changes, we can merge our branches by hitting the first endpoint with an empty query.

merge_res = requests.post(
        f'https://www.dolthub.com/api/v1alpha1/{owner}/{database}/write/{to_branch}/{from_branch}',
        params=None,
        headers=headers,
    )

merge_json = merge_res.json()
poll_operation(merge_json['operation_name'])

We will use an example DoltHub database, and the Python requests library to explore it in the Python console:

We can use our as an example. These are the steps we'd take to update a player's id in the player stats table.

You can learn more about using the writable SQL API .

dolthub/ip-to-country
SHAQ database
here
SQL API Tab

SQL read queries on the default branch

get
Authorizations
q
qstringOptional

The SQL query to execute.

Example: SHOW TABLES
Path parameters
ownerstringRequired

The name of the database owner.

Example: dolthub
databasestringRequired

The name of the database.

Example: ip-to-country
Responses
200
Success
application/json
400
Bad request. The request was invalid or could not be processed.
application/json
get
GET /api/v1alpha1/{owner}/{database} HTTP/1.1
Host: www.dolthub.com
authorization: YOUR_API_KEY
Accept: */*
{
  "query_execution_status": "Success",
  "query_execution_message": "",
  "repository_owner": "dolthub",
  "repository_name": "ip-to-country",
  "commit_ref": "main",
  "sql_query:": "SHOW TABLES",
  "schema": [
    {
      "columnName": "Tables_in_dolt",
      "columnType": "longtext"
    }
  ],
  "rows": [
    {
      "[columnName]": "IPv4ToCountry"
    }
  ]
}

SQL read queries on a specified ref

get
Authorizations
q
qstringOptional

The SQL query to execute.

Example: SELECT * FROM IPv4ToCountry WHERE CountryCode2Letter = 'AU'
Path parameters
ownerstringRequired

The name of the database owner.

Example: dolthub
databasestringRequired

The name of the database.

Example: ip-to-country
refstringRequired

The database ref to execute the query against.

Example: newbranch
Responses
200
Success
application/json
400
Bad request. The request was invalid or could not be processed.
application/json
get
GET /api/v1alpha1/{owner}/{database}/{ref} HTTP/1.1
Host: www.dolthub.com
authorization: YOUR_API_KEY
Accept: */*
{
  "query_execution_status": "Success",
  "query_execution_message": "",
  "repository_owner": "dolthub",
  "repository_name": "ip-to-country",
  "commit_ref": "newbranch",
  "sql_query:": "SELECT * FROM IPv4ToCountry WHERE CountryCode2Letter = 'AU'",
  "schema": [
    {
      "columnName": "IPFrom",
      "columnType": "Int"
    }
  ],
  "rows": [
    {
      "[columnName]": "16777216"
    }
  ]
}

Check write query operation status

get

Poll the operation to check if the SQL write operation is done

Authorizations
Path parameters
ownerstringRequired

The name of the database owner.

Example: dolthub
databasestringRequired

The name of the database.

Example: SHAQ
Query parameters
operationNamestringRequired

The name of the operation

Example: operations/72abb56b-d478-43ae-9a2d-c9602184c7ab
Responses
200
Success
application/json
400
Bad request. The request was invalid or could not be processed.
application/json
get
GET /api/v1alpha1/{owner}/{database}/write HTTP/1.1
Host: www.dolthub.com
authorization: YOUR_API_KEY
Accept: */*
{
  "_id": "operations/72abb56b-d478-43ae-9a2d-c9602184c7ab",
  "done": true,
  "res_details": {
    "query_execution_status": "Success",
    "query_execution_message": "Query OK, 2 rows affected. Rows matched: 2  Changed: 2  Warnings: 0.",
    "owner_name": "dolthub",
    "repository_name": "SHAQ",
    "from_commit_id": "518ue176ec13qf563e87uoerkqqepood",
    "to_commit_id": "5sp7i0bov6itma2u04dpk15ui24lrigp"
  }
}
  • Reading
  • Using the default branch
  • GETSQL read queries on the default branch
  • Specifying a ref
  • GETSQL read queries on a specified ref
  • Writing
  • 1. Run query
  • POSTSQL write query and merge branches
  • 2. Poll operation
  • GETCheck write query operation status
  • 3. View changes
  • 4. Merge changes

SQL write query and merge branches

post

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.

Authorizations
Path parameters
ownerstringRequired

The name of the database owner.

Example: dolthub
databasestringRequired

The name of the database.

Example: SHAQ
from_branchstringRequired

The base branch.

Example: main
to_branchstringRequired

The branch to write to. Will be created from the from_branch if it doesn't exist.

Example: feature
Query parameters
qstringOptional

The SQL query to execute. Use the request body instead for larger queries.

Example: UPDATE player_season_stat_totals SET player_id=714287 WHERE player_id=15404617
Body
querystringOptional

SQL write query to execute. Can be used in place of the query parameter for larger queries.

Example: UPDATE player_season_stat_totals SET player_id=714287 WHERE player_id=15404617
Responses
200
Success
application/json
400
Bad request. The request was invalid or could not be processed.
application/json
post
POST /api/v1alpha1/{owner}/{database}/write/{from_branch}/{to_branch} HTTP/1.1
Host: www.dolthub.com
authorization: YOUR_API_KEY
Content-Type: application/json
Accept: */*
Content-Length: 90

{
  "query": "UPDATE player_season_stat_totals SET player_id=714287 WHERE player_id=15404617"
}
{
  "query_execution_status": "Success",
  "query_execution_message": "Operation created. Poll the operation endpoint to see the execution status of this query.",
  "repository_owner": "dolthub",
  "repository_name": "SHAQ",
  "from_branch_name": "main",
  "to_branch_name": "main",
  "query:": "UPDATE player_season_stat_totals SET player_id=714287 WHERE player_id=15404617",
  "operation_name:": "operations/72abb56b-d478-43ae-9a2d-c9602184c7ab"
}