Add support for database migrations
* Add support for database migrations
Closes
This PR does NOT add any migrations as an example. I have
manually tested that the library works with SQL and Go based
upgrades correctly. Documentation should be sufficient for
devs to add migrations.
* Clarifications
* Linting
ADD
cmd/goose/README.md
+## Database migrations
+
+We use [goose](https://github.com/pressly/goose) to handle database migrations. This allows us to execute
+both SQL deltas (e.g `ALTER TABLE ...`) as well as manipulate data in the database in Go using Go functions.
+
+To run a migration, the `goose` binary in this directory needs to be built:
+```
+$ go build ./cmd/goose
+```
+
+This binary allows Dendrite databases to be upgraded and downgraded. Sample usage for upgrading the roomserver database:
+
+```
+# for sqlite
+$ ./goose -dir roomserver/storage/sqlite3/deltas sqlite3 ./roomserver.db up
+
+# for postgres
+$ ./goose -dir roomserver/storage/postgres/deltas postgres "user=dendrite dbname=dendrite sslmode=disable" up
+```
+
+For a full list of options, including rollbacks, see https://github.com/pressly/goose or use `goose` with no args.
+
+
+### Rationale
+
+Dendrite creates tables on startup using `CREATE TABLE IF NOT EXISTS`, so you might think that we should also
+apply version upgrades on startup as well. This is convenient and doesn't involve an additional binary to run
+which complicates upgrades. However, combining the upgrade mechanism and the server binary makes it difficult
+to handle rollbacks. Firstly, how do you specify you wish to rollback? We would have to add additional flags
+to the main server binary to say "rollback to version X". Secondly, if you roll back the server binary from
+version 5 to version 4, the version 4 binary doesn't know how to rollback the database from version 5 to
+version 4! For these reasons, we prefer to have a separate "upgrade" binary which is run for database upgrades.
+Rather than roll-our-own migration tool, we decided to use [goose](https://github.com/pressly/goose) as it supports
+complex migrations in Go code in addition to just executing SQL deltas. Other alternatives like
+`github.com/golang-migrate/migrate` [do not support](https://github.com/golang-migrate/migrate/issues/15) these
+kinds of complex migrations.
+
+### Adding new deltas
+
+You can add `.sql` or `.go` files manually or you can use goose to create them for you.
+
+If you only want to add a SQL delta then run:
+
+```
+$ ./goose -dir serverkeyapi/storage/sqlite3/deltas sqlite3 ./foo.db create new_col sql
+2020/09/09 14:37:43 Created new file: serverkeyapi/storage/sqlite3/deltas/20200909143743_new_col.sql
+```
+
+In this case, the version number is `20200909143743`. The important thing is that it is always increasing.
+
+Then add up/downgrade SQL commands to the created file which looks like:
+```sql
+-- +goose Up
+-- +goose StatementBegin
+SELECT 'up SQL query';
+-- +goose StatementEnd
+
+-- +goose Down
+-- +goose StatementBegin
+SELECT 'down SQL query';
+-- +goose StatementEnd
+
+```
+You __must__ keep the `+goose` annotations. You'll need to repeat this process for Postgres.
+
+For complex Go migrations:
+
+```
+$ ./goose -dir serverkeyapi/storage/sqlite3/deltas sqlite3 ./foo.db create complex_update go
+2020/09/09 14:40:38 Created new file: serverkeyapi/storage/sqlite3/deltas/20200909144038_complex_update.go
+```
+
+Then modify the created `.go` file which looks like:
+
+```go
+package migrations
+
+import (
+ "database/sql"
+ "fmt"
+
+ "github.com/pressly/goose"
+)
+
+func init() {
+ goose.AddMigration(upComplexUpdate, downComplexUpdate)
+}
+
+func upComplexUpdate(tx *sql.Tx) error {
+ // This code is executed when the migration is applied.
+ return nil
+}
+
+func downComplexUpdate(tx *sql.Tx) error {
+ // This code is executed when the migration is rolled back.
+ return nil
+}
+
+```
+
+You __must__ import the package in `/cmd/goose/main.go` so `func init()` gets called.
+
+
+#### Database limitations
+
+- SQLite3 does NOT support `ALTER TABLE table_name DROP COLUMN` - you would have to rename the column or drop the table
+ entirely and recreate it.

ADD
cmd/goose/main.go
+// This is custom goose binary
+
+package main
+
+import (
+ "flag"
+ "fmt"
+ "log"
+ "os"
+
+ // Example complex Go migration import:
+ // _ "github.com/matrix-org/dendrite/serverkeyapi/storage/postgres/deltas"
+ "github.com/pressly/goose"
+
+ _ "github.com/lib/pq"
+ _ "github.com/mattn/go-sqlite3"
+)
+
+var (
+ flags = flag.NewFlagSet("goose", flag.ExitOnError)
+ dir = flags.String("dir", ".", "directory with migration files")
+)
+
+func main() {
+ err := flags.Parse(os.Args[1:])
+ if err != nil {
+ panic(err.Error())
+ }
+ args := flags.Args()
+
+ if len(args) < 3 {
+ fmt.Println(
+ `Usage: goose [OPTIONS] DRIVER DBSTRING COMMAND
+
+Drivers:
+ postgres
+ sqlite3
+
+Examples:
+ goose -d roomserver/storage/sqlite3/deltas sqlite3 ./roomserver.db status
+ goose -d roomserver/storage/sqlite3/deltas sqlite3 ./roomserver.db up
+
+ goose -d roomserver/storage/postgres/deltas postgres "user=dendrite dbname=dendrite sslmode=disable" status
+
+Options:
+
+ -dir string
+ directory with migration files (default ".")
+ -table string
+ migrations table name (default "goose_db_version")
+ -h print help
+ -v enable verbose mode
+ -version
+ print version
+
+Commands:
+ up Migrate the DB to the most recent version available
+ up-by-one Migrate the DB up by 1
+ up-to VERSION Migrate the DB to a specific VERSION
+ down Roll back the version by 1
+ down-to VERSION Roll back to a specific VERSION
+ redo Re-run the latest migration
+ reset Roll back all migrations
+ status Dump the migration status for the current DB
+ version Print the current version of the database
+ create NAME [sql|go] Creates new migration file with the current timestamp
+ fix Apply sequential ordering to migrations`,
+ )
+ return
+ }
+
+ engine := args[0]
+ if engine != "sqlite3" && engine != "postgres" {
+ fmt.Println("engine must be one of 'sqlite3' or 'postgres'")
+ return
+ }
+ dbstring, command := args[1], args[2]
+
+ db, err := goose.OpenDBWithDriver(engine, dbstring)
+ if err != nil {
+ log.Fatalf("goose: failed to open DB: %v\n", err)
+ }
+
+ defer func() {
+ if err := db.Close(); err != nil {
+ log.Fatalf("goose: failed to close DB: %v\n", err)
+ }
+ }()
+
+ arguments := []string{}
+ if len(args) > 3 {
+ arguments = append(arguments, args[3:]...)
+ }
+
+ if err := goose.Run(command, db, *dir, arguments...); err != nil {
+ log.Fatalf("goose %v: %v", command, err)
+ }
+}

MODIFY
go.mod
go.mod
@@ -29,6 +29,7 @@ require (
github.com/ngrok/sqlmw v0.0.0-20200129213757-d5c93a81bec6
github.com/opentracing/opentracing-go v1.2.0
github.com/pkg/errors v0.9.1
+ github.com/pressly/goose v2.7.0-rc5+incompatible
github.com/prometheus/client_golang v1.7.1
github.com/sirupsen/logrus v1.6.0
github.com/tidwall/gjson v1.6.1

MODIFY
go.sum
go.sum
@@ -725,6 +725,8 @@ github.com/pkg/errors v0.9.1 h1:FEBLx1zS214owpjy7qsBeixbURkuhQAwrK5UwLGTwt4=
github.com/pkg/errors v0.9.1/go.mod h1:bwawxfHBFNV+L2hUp1rHADufV3IMtnDRdf1r5NINEl0=
github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
+github.com/pressly/goose v2.7.0-rc5+incompatible h1:txvo810iG1P/rafOx31LYDlOyikBK8A/8prKP4j066w=
+github.com/pressly/goose v2.7.0-rc5+incompatible/go.mod h1:m+QHWCqxR3k8D9l7qfzuC/djtlfzxr34mozWDYEu1z8=
github.com/prometheus/client_golang v0.8.0/go.mod h1:7SWBe2y4D6OKWSNQJUaRYU/AaXPKyh/dDVn+NZz0KFw=
github.com/prometheus/client_golang v0.9.1/go.mod h1:7SWBe2y4D6OKWSNQJUaRYU/AaXPKyh/dDVn+NZz0KFw=
github.com/prometheus/client_golang v1.0.0/go.mod h1:db9x61etRT2tGnBNRi70OPL5FsnadC4Ky3P0J6CfImo=

Add support for database migrations (#1416)

* Add support for database migrations

Closes #1246

This PR does NOT add any migrations as an example. I have
manually tested that the library works with SQL and Go based
upgrades correctly. Documentation should be sufficient for
devs to add migrations.

* Clarifications

* Linting