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	Driver Name
PostgreSQL	lib/pq	`github.com/lib/pq`	`postgres`
MySQL	go-sql-driver/mysql	`github.com/go-sql-driver/mysql`	`mysql`
SQLite	go-sqlite3	`github.com/mattn/go-sqlite3`	`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.

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`	`true`

Always set parseTime=true when using MySQL with Go. Without it, date and time columns are returned as []byte instead of time.Time.

SQLite

Install the SQLite driver:

bash

go get github.com/mattn/go-sqlite3

go-sqlite3 requires CGO to be enabled. Set CGO_ENABLED=1 in your environment. This also means you need a C compiler installed (e.g., gcc).

main.go

package main

import (
    "log"
    "time"

    "github.com/gopackx/go-migration/pkg/database"
    "github.com/gopackx/go-migration/pkg/database/drivers"
    _ "github.com/mattn/go-sqlite3"
)

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

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

    if err := mgr.AddConnection("default", database.ConnectionConfig{
        Driver:          "sqlite3",
        Database:        "myapp.db",
        MaxOpenConns:    1,
        MaxIdleConns:    1,
        ConnMaxLifetime: 0,
        Options:         map[string]string{"cache": "shared", "mode": "rwc"},
    }); 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")
}

The SQLite driver registers itself with database/sql under the name sqlite3. When using the connection Manager directly, register and reference the driver as sqlite3 so the lookup matches. (Config-file validation, by contrast, accepts the value sqlite.)

SQLite Connection Fields

The SQLite driver uses the Database field as the file path (e.g. myapp.db or :memory:). Entries in the Options map are appended as query parameters.

Field	Description	Example
`Database`	Path to the database file	`myapp.db`, `:memory:`
`Options["cache"]`	Shared cache mode	`shared`
`Options["mode"]`	Access mode	`rwc` (read-write-create)

SQLite only supports a single writer at a time. Set MaxOpenConns to 1 to avoid database is locked errors.

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"
    _ "github.com/mattn/go-sqlite3"
)

mgr := database.NewManager()

mgr.RegisterDriver("postgres", drivers.NewPostgresDriver())
mgr.RegisterDriver("mysql", drivers.NewMySQLDriver())
mgr.RegisterDriver("sqlite3", 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:   "sqlite3",
    Database: "cache.db",
    Options:  map[string]string{"cache": "shared", "mode": "rwc"},
})

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