Set up PostgreSQL, MySQL, and SQLite database drivers for use with go-migration.

Drivers

go-migration works with any database driver compatible with Go's database/sql package. This page covers setup for the three supported databases: PostgreSQL, MySQL, and SQLite.

Driver Overview

Database	Driver Package	Import Path	`database/sql` Name	`driver` in config
PostgreSQL	lib/pq	`github.com/lib/pq`	`postgres`	`postgres`
MySQL	go-sql-driver/mysql	`github.com/go-sql-driver/mysql`	`mysql`	`mysql`
SQLite	modernc.org/sqlite (pure Go)	`modernc.org/sqlite`	`sqlite`	`sqlite` (or the alias `sqlite3`)

PostgreSQL

Install the PostgreSQL driver:

bash

go get github.com/lib/pq

main.go

package main

import (
    "log"
    "time"

    "github.com/gopackx/go-migration/pkg/database"
    "github.com/gopackx/go-migration/pkg/database/drivers"
    _ "github.com/lib/pq"
)

func main() {
    mgr := database.NewManager()
    defer mgr.Close()

    mgr.RegisterDriver("postgres", drivers.NewPostgresDriver())

    if err := mgr.AddConnection("default", database.ConnectionConfig{
        Driver:          "postgres",
        Host:            "localhost",
        Port:            5432,
        Database:        "myapp",
        Username:        "user",
        Password:        "password",
        MaxOpenConns:    25,
        MaxIdleConns:    5,
        ConnMaxLifetime: 5 * time.Minute,
        Options:         map[string]string{"sslmode": "disable"},
    }); err != nil {
        log.Fatalf("add connection: %v", err)
    }

    db, err := mgr.Connection("default")
    if err != nil {
        log.Fatalf("connection failed: %v", err)
    }

    log.Println("Connected to PostgreSQL:", db.Stats().OpenConnections, "open")
}

The PostgreSQL driver builds a DSN of the form host=… port=… user=… password=… dbname=… sslmode=… from the config fields. sslmode defaults to disable when omitted; set it (and any other libpq parameter) via the Options map.

Field	Description	Example
`Username`	Database username	`postgres`
`Password`	Database password	`secret`
`Host`	Server hostname	`localhost`
`Port`	Server port	`5432`
`Database`	Database name	`myapp`
`Options["sslmode"]`	SSL mode	`disable`, `require`, `verify-full`

MySQL

Install the MySQL driver:

bash

go get github.com/go-sql-driver/mysql

main.go

package main

import (
    "log"
    "time"

    "github.com/gopackx/go-migration/pkg/database"
    "github.com/gopackx/go-migration/pkg/database/drivers"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    mgr := database.NewManager()
    defer mgr.Close()

    mgr.RegisterDriver("mysql", drivers.NewMySQLDriver())

    if err := mgr.AddConnection("default", database.ConnectionConfig{
        Driver:          "mysql",
        Host:            "localhost",
        Port:            3306,
        Database:        "myapp",
        Username:        "user",
        Password:        "password",
        MaxOpenConns:    25,
        MaxIdleConns:    5,
        ConnMaxLifetime: 5 * time.Minute,
        Options:         map[string]string{"parseTime": "true"},
    }); err != nil {
        log.Fatalf("add connection: %v", err)
    }

    db, err := mgr.Connection("default")
    if err != nil {
        log.Fatalf("connection failed: %v", err)
    }

    log.Println("Connected to MySQL:", db.Stats().OpenConnections, "open")
}

MySQL Connection Fields

The MySQL driver builds a DSN of the form user:password@tcp(host:port)/database?params from the config fields. Any entries in the Options map become query parameters (sorted, so the DSN is deterministic), and parseTime=true is added unless you set parseTime yourself.

Field	Description	Example
`Username`	Database username	`root`
`Password`	Database password	`secret`
`Host`	Server hostname	`localhost`
`Port`	Server port	`3306`
`Database`	Database name	`myapp`
`Options["parseTime"]`	Parse `DATE` and `DATETIME` to `time.Time` (added as `true` by default)	`true`

Since v1.2.0 the MySQL driver adds parseTime=true to the DSN unless parseTime is already present in Options, so DATETIME/TIMESTAMP columns — including the tracking table's created_at — scan into time.Time. Setting it explicitly, as above, is harmless. If you open the connection yourself with sql.Open("mysql", dsn), include parseTime=true in your DSN.

SQLite

Install the SQLite driver:

bash

go get modernc.org/sqlite

go-migration uses modernc.org/sqlite, a pure-Go SQLite driver. It needs no C compiler, so your binary builds with CGO_ENABLED=0 — the go-migration release binaries are built that way. Before v1.2.0 a CGO-based driver was used.

main.go

package main

import (
    "log"

    "github.com/gopackx/go-migration/pkg/database"
    "github.com/gopackx/go-migration/pkg/database/drivers"
    _ "modernc.org/sqlite"
)

func main() {
    mgr := database.NewManager()
    defer mgr.Close()

    mgr.RegisterDriver("sqlite", drivers.NewSQLiteDriver())

    if err := mgr.AddConnection("default", database.ConnectionConfig{
        Driver:          "sqlite",
        Database:        "myapp.db",
        MaxOpenConns:    1,
        MaxIdleConns:    1,
        Options:         map[string]string{"_pragma": "foreign_keys(1)"},
    }); err != nil {
        log.Fatalf("add connection: %v", err)
    }

    db, err := mgr.Connection("default")
    if err != nil {
        log.Fatalf("connection failed: %v", err)
    }

    log.Println("Connected to SQLite:", db.Stats().OpenConnections, "open")
}

modernc.org/sqlite registers itself with database/sql as sqlite, and drivers.NewSQLiteDriver() opens connections under that name. The name you pass to mgr.RegisterDriver is only the key that ConnectionConfig.Driver is matched against. The CLI registers the SQLite driver under both sqlite and sqlite3, because the config file accepts sqlite3 as an alias; when you use the Manager directly, register it under whichever name your configs use. With sql.Open directly, use sql.Open("sqlite", "app.db?_pragma=foreign_keys(1)").

SQLite Connection Fields

The SQLite driver uses the Database field as the file path (e.g. myapp.db or :memory:). Host, Port, Username, and Password are not used. Entries in the Options map are appended as query parameters and interpreted by modernc.org/sqlite.

Field	Description	Example
`Database`	Path to the database file	`myapp.db`, `:memory:`
`Options["_pragma"]`	A `PRAGMA` run on every new connection	`foreign_keys(1)`, `busy_timeout(5000)`
`Options["_txlock"]`	Transaction locking mode	`immediate`

SQLite only supports a single writer at a time. Set MaxOpenConns to 1 to avoid database is locked errors. This is compatible with the migration lock: SQLite takes no explicit lock (its file lock already serialises writers), so it needs no extra connection.

Registering Multiple Drivers

You can register all three drivers in the same application and use them for different connections:

import (
    "github.com/gopackx/go-migration/pkg/database"
    "github.com/gopackx/go-migration/pkg/database/drivers"
    _ "github.com/lib/pq"
    _ "github.com/go-sql-driver/mysql"
    _ "modernc.org/sqlite"
)

mgr := database.NewManager()

mgr.RegisterDriver("postgres", drivers.NewPostgresDriver())
mgr.RegisterDriver("mysql", drivers.NewMySQLDriver())
mgr.RegisterDriver("sqlite", drivers.NewSQLiteDriver())

// Now you can add connections using any of these drivers
mgr.AddConnection("primary", database.ConnectionConfig{
    Driver:   "postgres",
    Host:     "localhost",
    Port:     5432,
    Database: "myapp",
    Username: "user",
    Password: "pass",
    Options:  map[string]string{"sslmode": "disable"},
})

mgr.AddConnection("cache", database.ConnectionConfig{
    Driver:   "sqlite",
    Database: "cache.db",
    Options:  map[string]string{"_pragma": "foreign_keys(1)"},
})

What's Next?

Connection Manager — manage multiple named connections
Pool Configuration — tune connection pool settings

Drivers

Drivers

Driver Overview

PostgreSQL

PostgreSQL Connection Fields

MySQL

MySQL Connection Fields

SQLite

SQLite Connection Fields

Registering Multiple Drivers

What's Next?

On this page