Dolt is a MySQL compatible database with Git-style version control. This means that you can branch, merge, and diff your data just like you would with code. These are powerful features, but they can be confusing to users who aren't used to working with databases that have branches. Also, there are things you need to consider when writing code that takes advantage of a database that has branches. This post will explain how to connect to a Dolt database with branches, and how to use them.

Basics of Connecting

Dolt is MySQL compatible, and you should be able to connect to it the way you connect to any MySQL database. This includes the MySQL command line client, and any MySQL compatible client libraries. Some examples include:

MySQL Command Line Client

mysql -h '127.0.0.1' -u 'user' -p 'password' -d 'dbname'

In Python

import mysql.connector

cnx = mysql.connector.connect(user='user', password='password', host='127.0.0.1', database='dbname')
# Do stuff with the database

In Go

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err = sql.Open("mysql", "user:password@tcp(127.0.0.1)/dbname")
	if err != nil {
		return nil, fmt.Errorf("error opening database: %w", err)
    }
	defer db.Close()
	
	// Do stuff with the database
}

What Branch are you On?

Now that you've connected to a Dolt database, the question is what branch are you on? When the Dolt server process starts it finds the databases in the directory it was started in, and the default branch for every connection will be the branch that the server was on when it started.

Knowing what branch you are on requires knowledge about the server, and how it was configured. However you can query the database to find out what branch you are on using the active_branch() function.

MySQL [cocktails]> SELECT active_branch();
+-----------------+
| active_branch() |
+-----------------+
| main            |
+-----------------+
1 row in set (0.012 sec)

Connecting to a Different Branch

Dolt allows you to specify a branch any time you specify a database name by using a / to delimit the database from the branch name. You can also specify the branch when you connect to the database.

MySQL Command Line Client

mysql -h '127.0.0.1' -u 'user' -p 'password' -d 'dbname/branch'

In Python

import mysql.connector

cnx = mysql.connector.connect(user='user', password='password', host='127.0.0.1', database='dbname/branch')
# Do stuff with the database

In Go

Unfortunately the most popular MySQL driver for Go, go-sql-driver/mysql, does not support specifying the branch in the connection string. However, you can connect using a Config which supports the / character in database names.

import (
	"database/sql"
	"fmt"
	"github.com/go-sql-driver/mysql"
)

func main() {
	cfg := mysql.NewConfig()
	cfg.User = "user"
	cfg.Passwd = "pass"
	cfg.Addr = "127.0.0.1:3306"
	cfg.DBName = "dbname/branch"

	connector, err := mysql.NewConnector(cfg)
	if err != nil {
		return nil, fmt.Errorf("error creating connector: %w", err)
	}

	db := sql.OpenDB(connector)
	defer db.Close()
	
	// Do stuff with the database
}

If you need branch support via DSN, the Dolt team has created a fork of go-sql-driver/mysql that supports url encoded slashes in DSNs (A PR was submitted back to the go-sql-driver/mysql, unfortunately they are not interested in accepting it as it is not a backwards compatible change). You can find the fork here. An example DSN with a branch would look like this:

"user:password@tcp(127.0.0.1)/dbname%2Fbranch"

Changing Branches and Disconnects

When working with branches, there are times when you will want to change branches from the one you connected to initially. Maybe you want to create a new branch using

call dolt_checkout('-b', 'new_branch');

and make changes to your new branch. Or maybe you connected and made changes to your feature branch and are ready to switch back to main to merge those changes in. You can change branches by calling dolt_checkout(). However, once you are on a branch that isn't the one you initially connected to, you need to be aware that if you are disconnected from the database, and reconnect, you will be back on the branch you initially connected to. This is an unfortunate limitation of the MySQL protocol. There is no mechanism to resume a disconnected session, so if you are disconnected the server has no idea that your resumed connection has any relationship to the previous connection.

The danger here is that you could be making writes against a branch, get disconnected. Reconnect and continue your writes, but against an unintended branch. In order to handle this, we recommend putting your code within a transaction, and using the dolt_checkout() function within the transaction. This will ensure that all the statements within your transaction occur against your desired branch, and if you are disconnected your changes will be rolled back.

BEGIN;
call dolt_checkout(branch);
-- Do stuff with the database
COMMIT;

    import mysql.connector

    # the mysql.connector will create a transaction for you
    cnx = mysql.connector.connect(user='user', password='password', host='127.0.0.1', database='dbname/initial_branch')
    
    # Do stuff with the database on the initial branch
    
    # You must commit or rollback any changes before changing branches. Here I will commit the initial transa
    cnx.commit()
    
    # create a new transaction which we will use to make changes on the new branch
    cnx.start_transaction(isolation_level='SERIALIZABLE')
    cursor = cnx.cursor()
    cursor.execute("call dolt_checkout('new_branch')")   
    
    # Do stuff with the database
    
    cnx.commit()

	ctx := context.Background()
	// See the go example above for how to create the db
	
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("error beginning transaction: %w", err)
	}
	
	_, err = tx.ExecContext(ctx, "call dolt_checkout(branch)")
	if err != nil {
		return fmt.Errorf("error changing branches: %w", err)
	}
	
	// Do stuff with the database
	err = tx.Commit()
	if err != nil {
		return fmt.Errorf("error committing transaction: %w", err)
	}

Conclusion

Now that you have an understanding of how to connect to and work with different branches in Dolt, you are ready to start building applications on top of Dolt. We hope this guide has been helpful in getting you started. If you have any questions or comments, please reach out to us on Discord.