Cloning a Hosted Database
Hosted DoltDB is for running online, production
Dolt databases. Since we launched Hosted last year, we've been
adding new features to offer a streamlined user experience and easy access to Dolt's
powerful database and version control features. Our latest addition is the ability to
fetch from your running Hosted database, which allows you to create
a local copy of your Hosted database and utilize Dolt's command line
How it works
Unlike DoltHub, which is a hosted Dolt
remote, Hosted is an cloud-hosted,
online database that you can connect to with any MySQL client over the internet. When you
make a change on DoltHub it's not automatically reflected in other remotes. You must use
fetch to sync different copies of your database. For Hosted,
changes you make from a connected client are reflected immediately in other clients
without any manual syncing. If you want to know more about the differences between Hosted
and DoltHub, check out this
In some cases you might want to clone your database from Hosted so that you can access Dolt's command line interface. While much of the command line is available in SQL there are some CLI-specific features that can be helpful for debugging your database offline.
In order to clone from Hosted, we needed to add support for exposing the databases on a Hosted sql-server as a read-only remotesapi endpoint. When you run sql-server with this configuration, it will:
- Listen on an indicated port and serve read-only remotesapi traffic on that port
- Use any server-side TLS associated with the sql-server itself as part of that listener
- Use the deployment's user and password in the sql-server config as password authentication credentials for accessing the exposed data
Once you allow the remotesapi to be exposed for your Hosted deployment (available only for
deployments with Web PKI certificates), you can simply set a
environment variable and provide a username to the
dolt clone command. You'll see these
instructions in the Connectivity tab of your deployment page.
When you're using Hosted as your production database, there might be some performance-heavy operations or tests you want to run in isolation so you don't impact production, such as beta testing a considerable schema migration or running a big analytics query. Clone makes it easy to get a local copy of your Hosted database with one command. Do whatever potentially destructive or performance-degrading operations on your laptop while respecting your production database.
In order to enable cloning from your Hosted database, first expose the remotesapi endpoint. This will set the port for a server which can expose the databases in the sql-server over remotesapi. Note that this will only work for deployments that use a Web PKI certificate.
To authenticate against it, you have to set a
DOLT_REMOTE_PASSWORD environment variable
and pass along a
--user flag to the
dolt clone command. You can find these
instructions in the Connectivity tab on your deployment page.
After running the
dolt clone command, you should have a local copy of your Hosted
% dolt clone https://dolthub-us-housing.dbs.hosted.doltdb.com/us-housing-prices -u"username" cloning https://dolthub-us-housing.dbs.hosted.doltdb.com/us-housing-prices % cd us-housing-prices % dolt sql -q "select count(*) from sales" +----------+ | count(*) | +----------+ | 13844603 | +----------+
Now we can run whatever queries or schema migrations we want without affecting production.
If there are updates to the database, easily sync your local copy using
Assuming your password environment variable is still set, you can authenticate these
commands to your remotesapi endpoint in the same way by passing the
% dolt pull --user "[username]" % dolt fetch --user "[username]"
Note that this remotesapi endpoint is currently read-only. Writes to remotesapi, such as
dolt push, are not supported
It is now possible to clone, fetch, and pull from a Hosted database. Next we are working on supporting private remotes from Hosted, which will include cloning a private DoltHub database to your Hosted deployment. Stay tuned!
If you have any feedback on the Hosted product or want to use Hosted Dolt for your database, make a feature request on GitHub or reach out to us on Discord.