Embedding Dolt in your Golang Application

July 25, 2022

5 min read

Dolt is a version controlled database. When run as a server, Dolt is MySQL compatible, so you can connect to it with any MySQL tools or clients. Running Dolt as a server is perfect to back applications, just like you would with MySQL or Postgres.

Dolt is also Git for Data. Use the Dolt command line interface (CLI) without a running server and Dolt works the same as Git. Dolt can work offline when used this way. But what if you want to connect a program to an offline copy of Dolt? You can do this in "Git for Data" mode by automating the command line interface, although this can get cumbersome for complex use cases.

What if you want to embed Dolt in an application? For instance, you want to build a mobile application that needs to work in both online and offline modes. When you open the application, it runs a clone or pull command to synchronize data from a remote. Your application then edits the data offline and when it's ready it commits and push the data back to the remote. Any conflicts are resolved by the client application and there's no need to run or manage a server. You interface with Dolt on the file system much like you would a SQLite database. This is a decentralized application and a great fit for Dolt's model. We call this mode Embedded Dolt, and we're excited about what customers can build with it.

This blog will walk through how to run Dolt in embedded mode in Go using the new Dolt driver.

Golang database/sql driver

The database/sql package is the standard for Golang SQL access. It provides a common interface to relational databases. The implementation of the underlying database access is handled by a driver which must be registered. In order to define the specifics of the database and provide parameters to the driver a datasource name (DSN) string is used.

We have developed a Dolt driver for access to Dolt databases on the local filesystem from within Golang applications. In order to use the Dolt driver you must first import the package "github.com/dolthub/driver". This allows the driver to be registered as an available driver with the database/sql package. Once registered you only need to call the database/sql function Open providing the name of the driver, which in this case is "dolt", and a DSN for your local database.

 package main

import (
	"database/sql"

	_ "github.com/dolthub/driver"
)

func main() {
    db, err := sql.Open("dolt", "file:///path/to/databases?commitname=Billy%20Batson&commitemail=shazam@gmail.com&database=mydb")
    if err != nil {
       // process the error 
    }
    // use the database
}

The Dolt Driver DSN

The Dolt driver DSN is simply a file url and a few parameters. File urls consist of the url scheme file:// followed by a path. In this case it is the path to the directory where your databases are on disk. Calls to CREATE DATABASE and DROP DATABASE will create/destroy a subfolder within this directory. The additional parameters the driver supports currently are:

commitname (Required) - The name of the committer seen in the dolt commit log.
commitemail (Required) - The email of the committer seen in the dolt commit log.
database (optional) - The initial database to connect to. This parameter is optional but recommended and the database can always be changed using the SQL USE statement.

Setting up our Example

For this example I am going to clone the US jails database from DoltHub.

mkdir ~/doltdbs/
cd doltdbs
dolt clone dolthub/us-jails

Querying the Database

Now that we have some data to query let's read it. In my case the DSN will be:

file:///Users/billy/doltdbs?commitname=Billy%20Batson&commitemail=shazam@gmail.com&database=us_jails

One thing to note is that, due to differences in what SQL allows for database names, and what operating systems allow for folder names, the name of the folder which holds your Dolt database may not match with the name you use to access the data in the DSN. Our database resides in the folder "us-jails", but we use the name "us_jails" in our DSN. Any characters that are not legal within a SQL database name are replaced with underscores.

Ok, now that we have our DSN we can connect and query the data. Below is an example that queries all the jails that house men in California and returns them.

type JailInfo struct {
	Id                 string
	County             string
	Name               string
	Zip                string
	NumInmatesRatedFor uint
}

func queryJails(ctx context.Context, db *sql.DB) ([]JailInfo, error) {
    const query = `
SELECT id, county, facility_name, facility_zip, num_inmates_rated_for 
FROM jails
WHERE facility_gender=1 and facility_state='CA';`

	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var jails []JailInfo
	for rows.Next() {
		var jail JailInfo
		err := rows.Scan(&jail.Id, &jail.County, &jail.Name, &jail.Zip, &jail.NumInmatesRatedFor)
		if err != nil {
			return nil, err
		}

		jails = append(jails, jail)
	}

	return jails, nil
}

This code is standard RDBMS code which accesses data via the database/sql package. It queries the database, and then iterates over the returned rows. The rows.Scan method call will put the first column of the row into the first argument passed in, the second column into the second argument, and so on. The order of the columns is determined by your query. For a SELECT * style query the column order is determined by the table schema.

I won't go into further examples of using the standard database/sql interface to access data in Dolt as there are plenty of resources for learning the database/sql package and its use. However, I do want to show off some things that are only possible using Dolt

Let's use the dolt_log table to find out who the contributors to the database are, and the number of commits each committer has made. Below we use the same database/sql interface for reading the dolt_log table which is a system table that provides access to the Dolt commit log.

type Committer struct {
	Email       string
	CommitCount int
}

func getCommitters(ctx context.Context, db *sql.DB) ([]Committer, error) {
	const query = `
SELECT email, COUNT(*) AS num_commits
FROM dolt_log
GROUP BY email 
ORDER BY num_commits DESC;`

	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var committers []Committer
	for rows.Next() {
		var committer Committer
		err = rows.Scan(&committer.Email, &committer.CommitCount)
		if err != nil {
			return nil, err
		}

		committers = append(committers, committer)
	}

	return committers, nil
}

Because Dolt provides full version control functionality via SQL you are able to branch, merge, diff, commit, and anything else you can do with Dolt. Some examples of things you can only do in a Dolt database are:

Try it today

The new Dolt driver supports running Dolt embedded in a Go application. This unlocks the use of Dolt in mobile applications. We're excited to see whether the embedded use case unlocks more interesting Dolt use cases. Come by our Discord if you have any ideas on how to use Dolt embedded.

Blog

Golang database/sql driver

The Dolt Driver DSN

Setting up our Example

Querying the Database

Try it today

Get started with Dolt