Using sqlx in Go to Query Dolt

January 24, 2025

12 min read

DoltDB is an open-source SQL database that also includes all the features of Git – Git gives you distributed version control for files, Dolt gives you distributed version control for relational tables. For example, you can create a branch of a database for your application's configuration, work on changes in isolation, test and rebase them along the way, then send your changes to a teammate to review in a Pull Request on DoltHub. Check out Dolt+Scorewarrior: Version Controlled Game Configuration for a concrete example of what this flow looks like.

Dolt doesn't contain any source code from MySQL, but we've implemented Dolt to be compatible with MySQL – any tool or library that works with MySQL should work with Dolt. (btw... if you're looking for a Postgres-compatible version controlled database, we're also making steady progress building our Doltgres product.) Every few weeks, we test out a different tool or library that works with MySQL and see how well it works with Dolt. This week, we're looking at low-level SQL access in Go. You can check out some of the previous applications, libraries, and tools we've tested with Dolt in our Dolt tested apps guide. There are links to blog posts, as well as sample projects on GitHub that you can use as a starting point for new apps.

Querying SQL with Go

There are a LOT of options for querying a SQL database from a Golang application. In this post, we're going to be using the database/sql package from the Golang standard library as well as the excellent third-party sqlx package that extends it with some handy functionality.

If you're looking for a higher-level, ORM-style option, you can check out GORM. We published a blog post showing how to use GORM to access Dolt databases, and you can also find the sample project on GitHub.

If you just want to dive right into the sample code for this project, go ahead and clone the dolthub/dolt-go-sqlx-sample repository on GitHub. You can follow the instructions in the project's README to get the code running.

`database/sql` and `sqlx`

The database/sql package in Go's standard library is a flexible, low-level interface for interacting with SQL databases. Introduced in Go 1.0, it provides a consistent API to perform database operations such as querying, executing statements, and managing transactions, across over 50 different database engines. Since Dolt is compatible with MySQL, we'll be using the MySQL driver that implements the database/sql functionality to connect to a Dolt sql-server.

database/sql reflects Go's philosophy of simplicity and efficiency. It is minimalistic, offering only the essential tools to manage database connections and execute SQL statements. The implementation for specific database engines are provided as third-party drivers, which implement the database/sql/driver interface. The database/sql package prioritizes performance and resource management, including built-in connection pooling and prepared statement support, and it provides an idiomatic Go interface that fits nicely into existing Go code.

There's a lot to like about the database/sql package, but it is a pretty low-level interface for interacting with a SQL database. In our sample application, we're also going to be using the excellent third-party sqlx package. The sqlx package extends the database/sql package with some really handy functionality, such as marshalling rows into structs, slices, and maps, as we'll see later in this post.

Demo: Accessing a Dolt Database from Go

Let's jump in and start using the sqlx package to query a Dolt database. We'll start by getting a Dolt sql-server up and running for us to connect to, then we'll create a simple Golang application that connects to that server and runs some queries, including using some of Dolt's version control features.

Starting a Dolt SQL Server

If you don't have Dolt installed yet, head over to the Dolt installation docs and follow the instructions to install Dolt for your platform.

Once Dolt is installed, create a directory for your new Dolt database, initialize it, load in some sample data, then start up the server:

# This directory name will be used for the database name, so make sure you use "doltdb"
# here, since we'll use that database name in future commands.
mkdir doltdb && cd doltdb

# Just like with git, the "init" subcommand will initialize a directory – in this 
# case creating a new Dolt database in the current directory. Bonus points if you 
# can figure out what the --fun flag does ;-) 
dolt init --fun

# Download the sample gopher data and load it into our new database
wget https://raw.githubusercontent.com/dolthub/dolt-go-sqlx-sample/refs/heads/main/sample-data.sql
dolt sql < sample-data.sql

# Start up a SQL server that we can connect to. 
dolt sql-server --loglevel DEBUG

The dolt sql-server command above starts a new Dolt sql-server that listens on port 3306 by default. The terminal output will show you logs from the server.

Starting a Golang Application

To start, we'll create a new directory for our Golang application, then use go mod init to create a go.mod file, and add the MySQL driver for database.sql to our project as well as the sqlx package:

mkdir dolt-go-sqlx-sample && cd dolt-go-sqlx-sample
go mod init github.com/dolthub/dolt-go-sqlx-sample
go get -u github.com/go-sql-driver/mysql
go get -u github.com/jmoiron/sqlx

Next, we'll create a simple main.go file that connects to our Dolt sql-server:

package main

import (
	"fmt"
	"log"

	// Import the MySQL driver for database/sql
	// Note that since we don't use the driver directly, we need to use the blank identifier
	// to avoid compilation errors
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

func main() {
	// STEP 1: Connect to the database
	dsn := "root@tcp(127.0.0.1:3306)/doltdb"
	db := sqlx.MustOpen("mysql", dsn)
	defer db.Close()

	// Test the connection
	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}

	fmt.Println("Connected to Dolt database!")
}

In the code above, we're using the sqlx.MustOpen() function to open a new connection to our Dolt database. This is a handy extension of the sql.Open function that simply panics if we can't create the DB instance. Don't forget to close out your DB instance, when you're done with it! Here, we simply use a defer to schedule the db.Close() call to run when the main function exits. Opening a datasource doesn't necessarily mean that a connection is established (different driver implementations may behave differently, and the sql.database contract doesn't require them to actually validate a connection), so it's important to call db.Ping() to test the connection. If that call to db.Ping() succeeds, then we've got a valid connection to our Dolt database, and we're ready to query it.

When you run this code, you should see the message "Connected to Dolt database!" printed to the console. If you see an error message instead, double-check that your Dolt sql-server is running and that you've got the correct connection details in your dsn string.

Querying the Database

Now that we've got a connection to the Dolt database, let's extend our code to run a simple query. We want to query the Gophers table to grab the name and habitat of each gopher.

Add this code to the end of the main method we created in the last section:

	// STEP 2: Run a simple SELECT query and display the results
	rows, err := db.Queryx("SELECT name, habitat FROM Gophers")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var name, habitat string
		if err := rows.Scan(&name, &habitat); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("Name: %s, Habitat: %s\n", name, habitat)
	}

We're using the Queryx() method on our sqlx.DB instance in this example. This method will return a sqlx.Rows instance, whereas the Query() method would return a sql.Rows instance. In this case, we could use either implementation, but sqlx.Rows provides some additional functionality to help scan in row results into data structures in our application, which we'll see later in this sample.

After we get a rows instance, while it has more results (i.e. rows.Next() == true) we can use the rows.Scan() method to take values from the results and load them into data structures in our application. Here, we're simply passing in pointers to two string variables and then printing them out.

When you run this code, you should see the names and habitats of three gophers displayed:

Connected to Dolt database!
Name: Gary, Habitat: Mountain
Name: Gina, Habitat: Forest
Name: George, Habitat: Meadow

Modify Data

Next, let's see how we can modify data in our database and then create a Dolt commit to save those changes to the commit graph. Go ahead and add the following code to the end of your main function:

	// STEP 3: Change a value in the database and create a Dolt commit
	stmt, err := db.Preparex("UPDATE Gophers SET weight = ? WHERE name = ?")
	if err != nil {
		log.Fatal(err)
	}
    defer stmt.Close()
    stmt.MustExec(4.20, "Gary")
	stmt.MustExec(2.85, "Gina")
	stmt.MustExec(3.45, "George")
    db.MustExec("CALL dolt_commit('--skip-empty', '-am', 'Update gopher weights');")
	fmt.Println("Updated gopher weights")

In this new section, we're using the db.Preparex() method to create a prepared statement, that we can then execute multiple times with different arguments to update our gopher weights. We again see the same pattern with sqlx where database/sql provides a db.Prepare() method that returns a sql.Stmt instance, and sqlx extends that to also provide a db.Preparex() method that returns a sqlx.Stmt instance with all the same methods from sql.Stmt, plus some handy additional methods like stmt.MustExec(). sqlx provides several nice extensions for working with prepared statements beyond what database/sql provides, including the ability to create named prepared statements and to bind parameters by name from struct fields and map keys.

After we update our gopher weights, we use the dolt_commit() stored procedure to write these changes to our database's commit graph. Just like with Git, any changes you make start off in a working set, then you can stage them and commit them to the commit graph. You can think of Dolt commits as a higher level commit than a SQL commit, and in fact, any time you create a Dolt commit, a SQL commit is automatically created, too. We use the --skip-empty flag here to make it easier to run this sample code multiple times as you're building it. This flag tells Dolt to skip creating a commit if there are no changes to commit. Otherwise, if you tried to run the sample code a second time, the updated weights would already be committed, so there wouldn't be any changes to commit and you'd get an error when calling dolt_commit().

When we run our full main function now, we should see the following output:

Connected to Dolt database!
Name: Gary, Habitat: Mountain
Name: Gina, Habitat: Forest
Name: George, Habitat: Meadow
Updated gopher weights

Calculating Diffs

Now that we know how to do some basic SQL operations with our Dolt sql-server, let's see how to use some of the version control features. In the next two blocks of code we add to our sample, we're going to use the dolt_diff_summary() and dolt_diff() table functions to calculate what data is different between two branches.

When we imported the sample data into our Dolt sql-server, there were changes commited to the main branch, as well as changes commited on a branch named new-gophers. A common way to use Dolt branches is for a team or individual to create a branch off of the main or production data and work on changes in isolation until they are ready to be reviewed and merged back to the main branch. In the code below, we see how to use Dolt to calculate the differences between two branches and display it to the user.

Go ahead and add this code to the end of your main function:

	// STEP 4: Diff the changes between new-gophers and main
	// use dolt_diff_summary() to get a summary of what tables have changes
	rows, err = db.Queryx("SELECT * from dolt_diff_summary('main', 'new-gophers');")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	fmt.Printf("Tables changed: \n")
	for rows.Next() {
		diffSummaryRow := make(map[string]interface{})
		err = rows.MapScan(diffSummaryRow)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf(" - Table: %s, Data Change: %b, Schema Change: %b, Diff Type: %s\n",
			diffSummaryRow["to_table_name"], diffSummaryRow["data_change"],
			diffSummaryRow["schema_change"], diffSummaryRow["diff_type"])
	}

We start off by using the dolt_diff_summary() table function to calculate which tables have changed between the tip of the main branch and the tip of the new-gophers branch. Each row in this result set shows us information about a table that has changed, including the name of the table (both before and after the change, in case the change was renaming a table), whether data or schema changed (or both), and the type of table change (added, deleted, or modified).

We're also using the rows.MapScan() method here, which is another convenience method added by sqlx. This method scans the row values into a map, which makes it easy to work with results. It's worth noting that SQL string values are stored as []byte internally in the map, so when you print them out, you'll want to explicitly convert them into a string, such as we're doing here by using the %s format specifier in the fmt.Printf() call.

Before we run this code, let's add the next section that uses the dolt_diff() table function to show us the exact data changes in a single table:

	// use dolt_diff() to get the specific changes in the Gophers table
	rows, err = db.Queryx("SELECT * from dolt_diff('main', 'new-gophers', 'gophers');")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	fmt.Printf("Gopher table changes: \n")
	for rows.Next() {
		diffRow := make(map[string]interface{})
		err = rows.MapScan(diffRow)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf(" - Gopher ID: %s \n", diffRow["to_GopherID"])

		switch string(diffRow["diff_type"].([]uint8)) {
		case "added":
			fmt.Printf("   Added (%s, %v, %s, %s, %s, %s) \n",
				diffRow["to_Name"], diffRow["to_Age"], diffRow["to_Gender"], diffRow["to_Color"], diffRow["to_Habitat"], diffRow["to_Weight"])
		case "deleted":
			fmt.Printf("   Deleted (%s, %v, %s, %s, %s, %s) \n",
				diffRow["from_Name"], diffRow["from_Age"], diffRow["from_Gender"], diffRow["from_Color"], diffRow["from_Habitat"], diffRow["from_Weight"])
		case "modified":
			fmt.Printf("   Changed from (%s, %v, %s, %s, %s, %s) to (%s, %v, %s, %s, %s, %s)\n",
				diffRow["from_Name"], diffRow["from_Age"], diffRow["from_Gender"], diffRow["from_Color"], diffRow["from_Habitat"], diffRow["from_Weight"],
				diffRow["to_Name"], diffRow["to_Age"], diffRow["to_Gender"], diffRow["to_Color"], diffRow["to_Habitat"], diffRow["to_Weight"])
		}
	}

The results returned from the dolt_diff() table function are more detailed than the results from dolt_diff_summary(), since they contain the exact data changes, so they take a little more code to process and display.

Just like before, we're running a query, then using rows.MapScan() from sqlx to scan each row's values into a map. We print out the primary key for each changed gopher, and then depending on the diff_type, we display the data differently. You can read more details about the result schema for dolt_diff() in the Dolt documentation, but the main idea is that we'll have all the columns from the current version of the table listed twice in the result set: once with the from_ prefix, indicating the values before the change, and a second time with the to_ prefix indicating the values of each field after the change.

In the code above, we look at what type of diff we have, and print it out differently depending on whether it's a new row being added (where all the from_ fields are nil), a row being deleted (where all the to_ fields are nil), or if it's a row being modified (where the from_ and to_ fields show us exactly how a row changed).

When you run this code, you should see the following output:

Connected to Dolt database!
Name: Gary, Habitat: Mountain
Name: Gina, Habitat: Forest
Name: George, Habitat: Meadow
Updated gopher weights
Tables changed: 
 - Table: Diets, Data Change: 1, Schema Change: 0, Diff Type: modified
 - Table: Gophers, Data Change: 1, Schema Change: 0, Diff Type: modified
 - Table: HealthRecords, Data Change: 1, Schema Change: 0, Diff Type: modified
Gopher table changes: 
 - Gopher ID: 2c3b8abf-e85e-4f26-8245-d3a3438f6dee 
   Added (Gordon, 1, Male, White, Urban Park, 2.30) 
 - Gopher ID: 74dcb430-9ca6-4aef-bdde-ba5b8a504fab 
   Changed from (Gary, 3, Male, Black, Mountain, 4.20) to (Gary, 3, Male, Black, Mountain, 4.10)
 - Gopher ID: 853866d6-dea6-4fdb-bfb9-b5230288f49d 
   Changed from (Gina, 2, Female, Gray, Forest, 2.85) to (Gina, 2, Female, Gray, Forest, 2.80)
 - Gopher ID: a52fc9e2-4df5-4c2d-b546-825d64717215 
   Added (Grace, 5, Female, Golden, Prairie, 3.90) 
 - Gopher ID: beccaea9-0f27-44c0-8ea5-f0cdd371a09e 
   Changed from (George, 4, Male, Brown, Meadow, 3.45) to (George, 4, Male, Brown, Meadow, 3.50)

Next Steps

There's a lot more we could do in this sample application. If you want a challenge, try extending this sample code to merge the new-gophers branch into the main branch. You can call the dolt_merge() stored procedure to perform the merge.

You could also try exploring other version control features in Dolt, such as querying the dolt_log system table. This system table is the analogue of git log and shows you all the reachable commits from your current checked out branch.

Summary

There are several ways to query SQL databases from a Golang application. We covered using the sqlx library, which extends the database/sql package from the Golang standard library. There's a lot more included in sqlx than we were able to show here. If you're curious to go deeper, check out the library's excellent documentation in the Illustrated Guide to SQLX.

If you want a higher-level approach, you can consider an ORM like GORM, which also works with Dolt. There are a variety of other interesting database access tools for Golang, too. If you're using one that you think we should test with Dolt, let us know!

You can find the full source code for this sample in the dolthub/dolt-go-sqlx-sample repository on GitHub.

If you want to talk about Golang, or database tools and libraries, come join us on the DoltHub Discord server! We're always happy to help people get started with Dolt and discuss how Dolt can help you manage your most important data.

Blog