3、如何使用Golang编写和执行数据库迁移

GolangDB Migration

PostgreSQLSQLiteMySQL/ MariaDBMongoDB

1、安装migrate库

brew

brew install golang-migrate

migrate --help

creategotoupdown

2、创建Golang项目

simplebank

mkdir simpleblank

db/migration

cd simpleblank
mkdir -p db/migration

银行

migrate create -ext sql -dir db/migration -seq init_schema

-ext sql-dir db/migration-seqinit_schema

21updownup/down

updownx.down.sql

3、编辑Golang项目

vscodesimpleblanksqldb/migration/000001_init_schema.up.sql

db/migration/000001_init_schema.down.sqlupdown

DROP TABLE IF EXISTS entries;
DROP TABLE IF EXISTS transfers;
DROP TABLE IF EXISTS accounts;

银行

postgres

docker ps

docker stop

docker stop postgres14

docker psdocker ps -a

docker start postgres14

start容器名称容器的ID

docker execpostgrespostgres alpineubuntu/bin/bash/bin/sh

docker exec -it postgress14 /bin/sh

shelllinuxshellcreatedb--username=rootroot--owner=rootrootsimple_bank

createdb --username=root --owner=root simple_bank

psql

psql simple_bank

\q

dropdb simple_bank

exitdocker execcreatedb

docker exec -it postgres14 createdb --username=root --owner=root simple_bank

psql

docker exec -it postgres14 psql -U root simple_bank

Makefile

Makefilecreatedb银行dropdb

createdb:
	docker exec -it postgres14 createdb --username=root --owner=root simple_bank

dropdb:
	docker exec -it postgres14 dropdb simple_bank
	
.PHONY: createdb, dropdb

make dropdbpostgresMakefileMakefile

postgres:
	docker run --name postgres14 -e POSTGRES_PASSWORD=123456 -e POSTGRES_USER=root -p 5432:5432 -d postgres:14-alpine

createdb:
	docker exec -it postgres14 createdb --username=root --owner=root simple_bank

dropdb:
	docker exec -it postgres14 dropdb simple_bank

.PHONY: postgres, createdb, dropdb

postgres

docker stop postgres14

然后，将容器删除

docker rm postgres14

make postgrespostgresmake createdb

navicatsimple_bank

6、执行第一次迁移

migrate--path db/migration--database="postgresql://root:123456@localhost:5432/simple_bank"-verbosemigrateupmigratemigrate up

migrate --path db/migration --database="postgresql://root:123456@localhost:5432/simple_bank" -verbose up

SSL

error: pq: SSL is not enabled on the server

postgresSSLsslmode=disable

migrate --path db/migration --database="postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose up

navicatsimple_bankschema_migrationsversion1dirty

migrate upmigrate downMakefile

postgres:
	docker run --name postgres14 -e POSTGRES_PASSWORD=123456 -e POSTGRES_USER=root -p 5432:5432 -d postgres:14-alpine

createdb:
	docker exec -it postgres14 createdb --username=root --owner=root simple_bank

dropdb:
	docker exec -it postgres14 dropdb simple_bank

migrateup:
	migrate --path db/migration --database="postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose up

migratedown:
	migrate --path db/migration --database="postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose down

.PHONY: postgres, createdb, dropdb, migrateup, migratedown

make migratedownynavicatschema_migrationsmake migrateup

SQLGolangCRUDGORMdbsqlsqlxsqlc