We’re building Dolt, the world’s first version-controlled SQL database. Most of our customers run Dolt as a server in Docker and connect to it over the network like any other database server. But for programs written in Go, you can also connect to a Dolt database without a separate server process, similar to SQLite. We call this the embedded use case, and it has suddenly become a lot more relevant with Gas Town migrating its agentic memory storage to use Dolt as its backend. Since Gas Town is a Go process, it can use the embedded Dolt driver to communicate with an embedded Dolt database.

So, because Dolt’s embedded driver is about to get a lot more action than it’s used to, we thought this would be a good time to give a tour of how it works. This pattern is possible through the magic of Go’s database/sql/driver package, which lets you define a database connection that any Go program can use to talk to your SQL backend with a single import statement.

In this tour, we’ll look at how Go’s SQL drivers work under the hood and show you how Dolt implements one to provide access to an embedded Dolt database. Let’s take a look.

What’s the driver package?#

Go’s SQL driver package is an abstraction that lets other software libraries vend their own SQL connection logic through a common set of interfaces. Application developers use a common interface to connect to any SQL database (MySQL, Postgres, MariaDB, SQL Server, Dolt, etc.) without worrying about the specifics of the wire protocol for that particular database.

This is best illustrated with an example. Here’s how you connect to MySQL and read some rows.

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	dsn := "user:password@tcp(127.0.0.1:3306)/testdb"

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	rows, err := db.Query("SELECT id, name FROM users")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		if err := rows.Scan(&id, &name); err != nil {
			log.Fatal(err)
		}
		fmt.Println(id, name)
	}
}

Let’s go over this example line by line and see what it does.

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

This line is kind of magic and weird: the _ tells Go that you’re not using any symbols from this package, you’re importing it just for its side-effects. In this case, those side effects are registering a driver called "mysql" with database/sql package.

	dsn := "user:password@tcp(127.0.0.1:3306)/testdb"

	db, err := sql.Open("mysql", dsn)

A DSN is a data source name, almost always resembling a URL but often with some extra bits. Each database vendor has their own format for these DSNs, but they all look pretty similar. You usually embed the user name and password and some other metadata, like which database you want to connect to, in this string.

To open a connection, you just call sql.Open with the name of the driver and its matching DSN. Easy!

	rows, err := db.Query("SELECT id, name FROM users")

Query() on a connection takes a query string and returns the resulting rows.

	for rows.Next() {
		var id int
		var name string
		if err := rows.Scan(&id, &name); err != nil {
			log.Fatal(err)
		}
		fmt.Println(id, name)
	}

rows.Scan() puts the result of the query into normal Go datatypes, like int or string.

There are more complicated access patterns, and we haven’t touched on things like INSERT statements, but those are the basics.

Let’s see how a Driver is implemented by examining the Dolt driver.

The Dolt driver#

Dolt’s embedded database driver is defined very simply.

var _ driver.Driver = (*doltDriver)(nil)

func init() {
	sql.Register(DoltDriverName, &doltDriver{})
}

// doltDriver is a driver.Driver implementation which provides access to a 
// dolt database on the local filesystem
type doltDriver struct {}

The init() function is the special magic that requires you to use the _ import on the database driver of your choice. At program execution time, this code calls sql.Register() to tell the database/sql package there’s a driver.Driver implementation named “dolt”.

Next we need a way to get a connection to the embedded database, which we do with the Open() method. In the sample below, I’ve removed most of the error handling for brevity. You can read the full source here.

func (d *doltDriver) Open(dataSource string) (driver.Conn, error) {
	ctx := context.Background()
	var fs filesys.Filesys = filesys.LocalFS

	ds, err := ParseDataSource(dataSource)

    exists, isDir := fs.Exists(ds.Directory)
	fs, err = fs.WithWorkingDir(ds.Directory)

    name := ds.Params[CommitNameParam]
	email := ds.Params[CommitEmailParam]

	cfg := config.NewMapConfig(map[string]string{
		config.UserNameKey:  name[0],
		config.UserEmailKey: email[0],
	})

	mrEnv, err := LoadMultiEnvFromDir(ctx, cfg, fs, ds.Directory, "0.40.17")

    seCfg := &engine.SqlEngineConfig{
		IsReadOnly: false,
		ServerUser: "root",
		Autocommit: true,
	}

	se, err := engine.NewSqlEngine(ctx, mrEnv, seCfg)
	gmsCtx, err := se.NewLocalContext(ctx)
	if database, ok := ds.Params[DatabaseParam]; ok && len(database) == 1 {
		gmsCtx.SetCurrentDatabase(database[0])
	}

	return &DoltConn{
		DataSource: ds,
		se:         se,
		gmsCtx:     gmsCtx,
	}, nil
}

Dolt’s DSN format is basically a file URL with some extra query params. It looks something like this:

file:///User/brian/driver/example/path?commitname=Billy%20Bob&commitemail=bb@gmail.com&database=dbname

We parse this URL and extract the relevant query params out of it, then use those to create our internal SQL engine representation, which is what Dolt uses to execute queries internally.

This gives us a DoltConn with an engine it can use to execute queries. Let’s look at that next.

Dolt’s driver.Connection#

Unlike the driver itself, the DoltConn type has some state. But it’s still very simple. Its main job is to pass information down the line, from a call to Prepare to a driver.Stmt type.

type DoltConn struct {
	se         *engine.SqlEngine
	gmsCtx     *gms.Context
	DataSource *DoltDataSource
}

var _ driver.Conn = (*DoltConn)(nil)

// Prepare packages up |query| as a *doltStmt so it can be executed. If multistatements mode
// has been enabled, then a *doltMultiStmt will be returned, capable of executing multiple statements.
func (d *DoltConn) Prepare(query string) (driver.Stmt, error) {
	// Reuse the same ctx instance, but update the QueryTime to the current time.
	// Statements are executed serially on a connection, so it's safe to reuse
	// the same ctx instance and update the time.
	d.gmsCtx.SetQueryTime(time.Now())

	if d.DataSource.ParamIsTrue(MultiStatementsParam) {
		return d.prepareMultiStatement(query)
	} else {
		return d.prepareSingleStatement(query)
	}
}

// prepareSingleStatement creates a doltStmt from |query|.
func (d *DoltConn) prepareSingleStatement(query string) (*doltStmt, error) {
	return &doltStmt{
		query:  query,
		se:     d.se,
		gmsCtx: d.gmsCtx,
	}, nil
}

The driver.Stmt impelementation is where real work begins to happen, in the Query() method.

// Query executes a query that may return rows, such as a SELECT
func (stmt *doltStmt) Query(args []driver.Value) (driver.Rows, error) {
	var sch gms.Schema
	var rowIter gms.RowIter
	var err error

	if len(args) != 0 {
		sch, rowIter, err = stmt.execWithArgs(args)
	} else {
		sch, rowIter, _, err = stmt.se.Query(stmt.gmsCtx, stmt.query)
	}
	if err != nil {
		return nil, translateError(err)
	}

	// Wrap the result iterator in a peekableRowIter and call Peek() to read the first row from the result iterator.
	// This is necessary for insert operations, since the insert happens inside the result iterator logic.
	peekIter := peekableRowIter{iter: rowIter}
	row, _ := peekIter.Peek(stmt.gmsCtx)

	return &doltRows{
		sch:              sch,
		rowIter:          &peekIter,
		gmsCtx:           stmt.gmsCtx,
		isQueryResultSet: isQueryResultSet(row),
	}, nil
}

func (stmt *doltStmt) execWithArgs(args []driver.Value) (gms.Schema, gms.RowIter, error) {
	bindings, err := argsToBindings(args)
	if err != nil {
		return nil, nil, err
	}

	sch, itr, _, err := stmt.se.GetUnderlyingEngine().QueryWithBindings(stmt.gmsCtx, stmt.query, nil, bindings, nil)
	if err != nil {
		return nil, nil, err
	}
	return sch, itr, nil
}

Here you can see that the statement’s real work is nearly all delegated to the SQL engine we created in the initial call to Open(). The rest of its functionality is just to translate between the results that Dolt’s SQL engine provides and what the database/sql interfaces expect. For that, we have the doltRows type.

type doltRows struct {
	sch     gms.Schema
	rowIter gms.RowIter
	gmsCtx  *gms.Context
	columns []string
}

var _ driver.Rows = (*doltRows)(nil)

// Columns returns the names of the columns. The number of columns of the result is inferred from the length of the
// slice. If a particular column name isn't known, an empty string should be returned for that entry.
func (rows *doltRows) Columns() []string {
	if rows.columns == nil {
		rows.columns = make([]string, len(rows.sch))
		for i, col := range rows.sch {
			rows.columns[i] = col.Name
		}
	}

	return rows.columns
}

// Next is called to populate the next row of data into the provided slice.
// The provided slice will be the same size as the Columns() are wide. 
// Next returns io.EOF when there are no more rows.
func (rows *doltRows) Next(dest []driver.Value) error {
	nextRow, err := rows.rowIter.Next(rows.gmsCtx)
	if err != nil {
		if err == io.EOF {
			return io.EOF
		}
		return translateError(err)
	}

	if len(dest) != len(nextRow) {
		return errors.New("mismatch between expected column count and actual column count")
	}

    
	for i := range nextRow {
        // Some types like enums need special handling here, but mostly the types returned
        // by the Dolt SQL engine are already the Go types that the database/sql interfaces require
        if (...) {
            // special type handling code
		} else {
			dest[i] = nextRow[i]
		}
	}

	return nil
}

And that’s it! The job of these interfaces is really to act as a translation layer between the wire protocol the database uses and the types that database/sql expects. In the case of the Dolt embedded driver, there’s no wire protocol: we’re accessing the SQL engine that queries the database on disk directly and using the data structures it returns natively.

Using a driver with Gorm#

A lot of developers prefer to use an ORM tool when interacting with their database, and in the Go world, the most popular ORM library is Gorm. Gorm usually manages your DB connection for you automatically, but in the case of Dolt embedded we want something slightly different: we want it to use its MySQL dialect and logic but connect to an embedded Dolt database connection. This is pretty easy to do.

package main

import (
	"fmt"
	"log"

	"gorm.io/gorm"
    
    _ "github.com/go-sql-driver/mysql"
    _ "github.com/dolthub/driver/embedded"
)

type User struct {
	ID   uint
	Name string
}

func main() {
	dbName := "server_db"
	dsn := fmt.Sprintf("file://%v?commitname=%v&commitemail=%v&database=%v", dir, "Gorm Tester", "gorm@dolthub.com", dbName)
	sqlDB, err := sql.Open("dolt", dsn)

    db, err := gorm.Open(mysql.New(mysql.Config{Conn: sqlDB}), &gorm.Config{SkipDefaultTransaction: true, PrepareStmt: true})
	if err != nil {
		log.Fatal(err)
	}

	var users []User
	if err := db.Find(&users).Error; err != nil {
		log.Fatal(err)
	}

	for _, u := range users {
		fmt.Println(u.ID, u.Name)
	}
}

Note that we need to import both the MySQL driver and the Dolt driver for this to work. But otherwise, it’s a standard Gorm setup.

Conclusion#

Go’s database drivers are a simple way for database application developers to connect to any of the many different SQL databases you can run in production with a common interface. Standardizing these interfaces made it easier for libraries like Gorm to offer support for a larger variety of different database vendors, since the details of the wire protocols and other tricky bits are hidden by the abstraction for most uses. And it’s pretty simple to write your own driver if you have a SQL data source you want other people to connect to.

Want to discuss Go database drivers or learn more about Dolt? Visit us on the DoltHub Discord, where our engineering team hangs out all day. Hope to see you there.