SQL Workbench
Getting started with the workbench
It's easy to get started browsing and updating data in your Hosted database with the SQL Workbench for both new and existing deployments.
1. Add the workbench database users
For new deployments, this is as simple as checking the "Create database users for the SQL Workbench" checkbox in the Create Deployment form.
If you create a deployment without checking that box, you can find instructions for adding the users manually in the Workbench tab of your deployment.
2. Accessing the read-only workbench
The workbench is read-only by default. If the hosted-ui-reader
user exists and you have at least one database, you should see the database(s) in the "Available Databases" section of your Workbench tab, as well as the "Launch SQL Workbench" button at the top.
You can then click on either button to browse your database in read-only mode.
3. Enabling writes
All you need to do to enable writes is to check the "Enable writes" checkbox in the Workbench tab of your deployment page and click Update. Assuming the hosted-ui-admin
user has already been added, you will be able to immediately make changes to your data using the workbench.
4. Making a change using the workbench
You can make changes to your data using the workbench using the SQL console or cell buttons. The cell buttons are a beginner-friendly way to make updates.
First, create a new branch. Changes from the workbench are reflected immediately in your database, unlike DoltHub where you need to manually update your local Dolt with any changes using dolt pull
. Creating changes on a branch lets you test a change without affecting main
.
Next, use the "Edit cell value" cell button to change a cell.
This will open a text input where you can make changes. Once you're done, click the check.
This will generate a SQL update query. You don't need to know SQL to make updates using the workbench, but it can help you learn SQL along the way!
You can make as many changes as you want to the new branch before committing. Adding a new row is also simple using the Add a row
button at the bottom of the screen. When you're done making changes create a commit on your branch.
You will now see a commit for this change in the Commit Log for the branch.
5. Creating a pull request
The pull request will look like this.
And you can click "View diff" to see a detailed view of the proposed changes.
Send this pull request to some teammates and they can comment to request changes or approve. Once approved, merge your branch.
You will see the new commit reflected in the Commit Log for the main
branch, as well as in any connected MySQL client.
The Hosted Workbench vs DoltHub
Implementation Notes
Authentication model
In order to implement the database workbench, we needed some API machinery to access the SQL server running on a deployed hosted instance, and we needed the API to authenticate to the SQL server. Before this feature, Dolt only supported username/password authentication.
One way we could have handled this was to deploy the Hosted instances with a pre-configured username and password, which the API could use to access the database. However, these credentials would have been harder to make short lived and access to the database with the credentials would not have necessarily come with progeny information.
Instead, we built an authentication mechanism into Dolt which allows user accounts to authenticate using signed JWTs. In turn, our Hosted API was extended to issue signed JWTs for accessing either the read-only account associated with UI (hosted-ui-reader
) or the admin account (hosted-ui-admin
), which is available to deployment admins when writes are enabled. When someone attempts to connect to the Hosted SQL server, the GraphQL server calls Hosted API to get short-term credentials, and then connects to the Hosted SQL server using them.
The JWT-based authentication scheme has the following properties:
Credentials are non-forgeable.
Credentials are short-lived.
Credential creation and use is auditable – logs can record when they are minted, as a result of which requests, and when they are used.
Users can configure if these credentials are allowed and for which accounts.
How it was built
Comparing DoltHub and Hosted GraphQL servers
Both DoltHub and the Hosted SQL workbench have this dropdown that lists the database branches. I'm going to use it as a simple example to convey the changes that needed to be made to implement the DoltHub UI against the Hosted SQL servers.
The ListBranchNames
query is straightforward. You give it the owner and database names and it returns a list of all the branches in the database as strings. On DoltHub, ListBranchNames
is a GRPC endpoint that communicates with DoltHub API (a Golang service) to get branch names from the Dolt storage layer, also called a ChunkStore
, which is stored in AWS S3. This service first validates the request to ensure the calling user has permission to see the database and that the database exists on DoltHub. It then uses the read-only chunk store interface to query Dolt for the branch data. This looks something like:
For example, to list tables for a branch, we can use an AS OF
clause: SHOW TABLES AS OF 'feature_branch'
. Or for user-run SQL queries we can use a USE
statement to specify the branch before the query is run, like:
Additional Resources
Here are some blogs that go more into depth about the SQL Workbench on Hosted:
Last updated