Bipul Follow

20 Sep 2019

SQLite Database Schema Migration Using Golang

SQLite is a nifty choice for client side database applications. It is serverless, works with easily accessible cross-platform database files, and does not need any installation. However, occasionally, there may arise the need to modify the database schema. For instance, one of the tables might need a new column. Such a modification must not, in any manner, tamper with or corrupt any data that already exists on the database. This is where SQLite has a slight disadvantage, discussed in detail later in this post.

The Go programming language has libraries for creation and migration of SQLite databases. This post covers the basics of SQLite DB creation and migration using Go, and provides guidelines on how to update the DB schema without loss or corruption of data. This is based on one of our applications, where we encountered this problem of schema migration.

Note: If you are aware of how to create and setup migration for databases using Go, please skip the Let Us GO! section below.

Migration Scripts And Versioning

A good way of shipping out a client side database application is to equip it with the mechanism to create the DB itself, when launched for the first time. This can be done by embedding a set of scripts containing SQL commands to do the same. We refer to these files as migration scripts. The naming of these files is done in a specific format: <version>_<description>.<up/down>.sql. This conveys the order in which these must be executed.

The version is the numerical value used to determine the schema version of the database.
The description denotes the changes made by the script. This is ignored in the migration process.
The up/down suffix determines whether the script will be used to upgrade or downgrade the database.

If the database for the application does not exist (as would be the case for the first launch), the migration code shall run the up scripts to create it. The schema version of the database shall be set to the latest version specified among the scripts. For subsequent launches, the migration code searches for any scripts that have a higher version, and executes the SQL statements in those scripts. For instance, if the DB schema version is 6, only the scripts with version 7 or higher, if available, shall be used to update the schema.

Let Us GO!

This post shall utilize the following Go libraries and packages for creation and migration of the DB:

database/sql: Generic interface around SQL databases.
go-sqlite3: Database driver for SQLite. This shall be used in conjunction with database/sql.
golang-migrate: Used for database migrations.
go-bindata: Used for embedding the migration scripts into the application.

The process to equip migration into the application is threefold:

Convert the migration scripts into embeddable binary form
Create the database in the application
Run the relevant migration scripts on the database

A. Using go-bindata

The following command shall generate the binary data:

go-bindata -o <path-to-datafile>.go -prefix "<migration-scripts-dir>" -pkg <package-name> <migration-scripts-dir>

This shall convert the scripts into binary form and store this information in the specifed Go file. The -pkg specifies the package for the generated Go file. The path to this file, and the migration scripts directory, should be in the directory of the specified package.

B. Creating The Database

The database/sql package, along with the go-sqlite3 driver, can be used to create the DB. The following code snippet performs this task:

import (
	"database/sql"

	_ "github.com/mattn/go-sqlite3"
	"github.com/pkg/errors"
)

func NewDB(dbPath string) (*sql.DB, error) {
	sqliteDb, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, errors.Wrap(err, "failed to open sqlite DB")
	}

	return sqliteDb, nil
}

C. Running the Migration Scripts

The generated Go data file contains the migration scripts listed as Assets. We shall use this in the migration function, as follows:

import (
	"fmt"

	"github.com/golang-migrate/migrate"
	"github.com/golang-migrate/migrate/database/sqlite3"
	bindata "github.com/golang-migrate/migrate/source/go_bindata"
)

func RunMigrateScripts(db *sql.DB) error {
	driver, err := sqlite3.WithInstance(db, &sqlite3.Config{})
	if err != nil {
		return fmt.Errorf("creating sqlite3 db driver failed %s", err)
	}

	res := bindata.Resource(AssetNames(),
		func(name string) ([]byte, error) {
			return Asset(name)
		})

	d, err := bindata.WithInstance(res)
	m, err := migrate.NewWithInstance("go-bindata", d, "sqlite3", driver)
	if err != nil {
		return fmt.Errorf("initializing db migration failed %s", err)
	}

	err = m.Up()
	if err != nil && err != migrate.ErrNoChange {
		return fmt.Errorf("migrating database failed %s", err)
	}

	return nil
}

The m.Up() call executes the up scripts, whichever necessary. For downgrading the DB, this can be replaced by m.Down().

D. Putting It All Together

Assuming that we have a db package for the DB related code, the final product looks something like this:

package db

import (
	"database/sql"
	"fmt"

	"github.com/golang-migrate/migrate"
	"github.com/golang-migrate/migrate/database/sqlite3"
	bindata "github.com/golang-migrate/migrate/source/go_bindata"
	_ "github.com/mattn/go-sqlite3"
	"github.com/pkg/errors"
)

func NewDB(dbPath string) (*sql.DB, error) {
	sqliteDb, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, errors.Wrap(err, "failed to open sqlite DB")
	}

	return sqliteDb, nil
}

func RunMigrateScripts(db *sql.DB) error {
	driver, err := sqlite3.WithInstance(db, &sqlite3.Config{})
	if err != nil {
		return fmt.Errorf("creating sqlite3 db driver failed %s", err)
	}

	res := bindata.Resource(AssetNames(),
		func(name string) ([]byte, error) {
			return Asset(name)
		})

	d, err := bindata.WithInstance(res)
	m, err := migrate.NewWithInstance("go-bindata", d, "sqlite3", driver)
	if err != nil {
		return fmt.Errorf("initializing db migration failed %s", err)
	}

	err = m.Up()
	if err != nil && err != migrate.ErrNoChange {
		return fmt.Errorf("migrating database failed %s", err)
	}

	return nil
}

The final step is using these two in the main application.

package main

import (
	"log"

	"<db-package-import-path>"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	sqliteDb, err := db.NewDB("<path-to-db-file>")
	if err != nil {
		log.Fatal(err)
	}

	defer sqliteDb.Close()

	err = db.RunMigrateScripts(sqliteDb.DB)
	if err != nil {
		log.Fatal(err)
	}

	log.Info("successfully migrated DB..")

	// rest of application main
}

Guidelines for SQLite DB Schema Migration Scripts

As mentioned earlier, SQLite has a slight disadvantage compared to other database systems. On most DB systems, the ALTER TABLE statement can be used quite effectively to change the table structure in the database. However, for SQLite databases, this statement can only perform two operations:

Change the name of a table
Add columns to a table

Other operations, such as renaming or removing a column, or changing the data type of a column, cannot be accomplished with the ALTER TABLE statement. For these operations, a four-step process needs to be followed:

Rename the existing table.
Create the new table with the same name that the table originally had.
Populate this newly created table with information from the old table.
Drop the old table.

The following is a template for the the up migration scripts following this process:

ALTER TABLE <table_name> RENAME TO _<table_name>_old;

CREATE TABLE <table_name>
(
	column1 datatype [NULL | NOT NULL],
	column1 datatype [NULL | NOT NULL],
	...
);

INSERT INTO <table_name> (column1, column2, ...)
	SELECT column1, column2, ...
	FROM _<table_name>_old;

DROP TABLE _<table_name>_old

The corresponding down migration scripts shall do the inverse.

Conclusion

This post provides a detailed process for writing a client side Go application with a database, without requiring to install any DB system on the host machine. It also provides a safe, albeit slightly indirect, way of migrating the database schema without worrying about any loss or malformation of data on it.