README
¶
libschema/lsmysql - mysql support for libschema
Install:
go get github.com/muir/libschema
DDL Transactions
MySQL and MariaDB do not support transactions around DDL (Data
Definition Language) changes like CREATE TABLE. Such commands
cause the current transaction to switch to autocommit mode.
The consequence of this is that it is not possible for a schema migration tool, like libschema, to track if a migration has been applied or not by tracking the status of a transaction (did it error or succeed?)
When working with MySQL and MariaDB, schema-changing migrations should be done separately from data-changing migrations. Schema-changing transactions that are idempotent are safe and require no special handling.
Schema-changing transactions that are not idempotent need to be guarded with conditionals so that they're skipped if they've already been applied.
Fortunately, IF EXISTS and IF NOT EXISTS clauses can be added
to most of the DDL statements.
Conditionals
The DDL statements missing IF EXISTS and IF NOT EXISTS include:
ALTER TABLE ...
ADD CONSTRAINT
ALTER COLUMN SET SET DEFAULT
ALTER COLUMN SET DROP DEFAULT
ADD FULLTEXT
ADD SPATIAL
ADD PERIOD FOR SYSTEM TIME
ADD {INDEX|KEY} index_name [NOT] INVISIBLE
DROP PRIMARY KEY
RENAME COLUMN
RENAME INDEX
RENAME KEY
DISCARD TABLESPACE
IMPORT TABLESPACE
COALESCE PARTITION
REORGANIZE PARTITION
EXCHANGE PARTITION
REMOVE PARTITIONING
DISABLE KEYS
ENABLE KEYS
To help make these conditional, the lsmysql provides some helper functions to easily check the current database state.
For example:
schema := libschema.NewSchema(ctx, libschema.Options{})
sqlDB, err := sql.Open("mysql", "....")
database, mysql, err := lsmysql.New(logger, "main-db", schema, sqlDB)
database.Migrations("MyLibrary",
lsmysql.Script("createUserTable", `
CREATE TABLE users (
name text,
id bigint,
PRIMARY KEY (id)
) ENGINE=InnoDB`
}),
lsmysql.Script("dropUserPK", `
ALTER TABLE users
DROP PRIMARY KEY`,
libschema.SkipIf(func() (bool, error) {
hasPK, err := mysql.HasPrimaryKey("users")
return !hasPK, err
})),
)
Some notes on MySQL
While most identifiers (table names, etc) can be "quoted", you
cannot use quotes around a schema (database really) name with CREATE SCHEMA.
MySQL does not support schemas. A schema is just a synonym for
DATABASE in the MySQL world. This means that it is easier to put
migrations tracking table in the same schema (database) as the rest
of the tables. It also means that to run migration unit tests, the
DSN for testing has to give access to a user that can create and
drop databases.
Documentation
¶
Overview ¶
Package lsmysql has a libschema.Driver support MySQL
Index ¶
- Variables
- func CheckScript(s string) errordeprecated
- func Computed[T ConnPtr](name string, action func(context.Context, T) error, ...) libschema.Migration
- func Generate(name string, generator func(context.Context, *sql.Tx) string, ...) libschema.Migration
- func Script(name string, sqlText string, opts ...libschema.MigrationOption) libschema.Migration
- func WithoutDatabase(p *MySQL)
- type ConnPtr
- type MySQL
- func (p *MySQL) ColumnDefault(table, column string) (*string, error)
- func (p *MySQL) ColumnIsInPrimaryKey(table string, column string) (bool, error)
- func (p *MySQL) CreateSchemaTableIfNotExists(ctx context.Context, _ *internal.Log, d *libschema.Database) error
- func (m *MySQL) DatabaseName() (string, error)
- func (p *MySQL) DoOneMigration(ctx context.Context, log *internal.Log, d *libschema.Database, ...) (rowsAffected int64, _ error)
- func (p *MySQL) DoesColumnExist(table, column string) (bool, error)
- func (p *MySQL) GetTableConstraint(table, constraintName string) (string, bool, error)
- func (p *MySQL) HasPrimaryKey(table string) (bool, error)
- func (p *MySQL) IsMigrationSupported(d *libschema.Database, _ *internal.Log, migration libschema.Migration) error
- func (p *MySQL) LoadStatus(ctx context.Context, _ *internal.Log, d *libschema.Database) (_ []libschema.MigrationName, err error)
- func (p *MySQL) LockMigrationsTable(ctx context.Context, _ *internal.Log, d *libschema.Database) (finalErr error)
- func (p *MySQL) TableHasIndex(table, indexName string) (bool, error)
- func (p *MySQL) UnlockMigrationsTable(_ *internal.Log) error
- func (m *MySQL) UseDatabase(name string)
- type MySQLOpt
Constants ¶
This section is empty.
Variables ¶
var ( ErrDataAndDDL = libschema.ErrDataAndDDL // Deprecated: use libschema.ErrDataAndDDL ErrNonIdempotentDDL = libschema.ErrNonIdempotentDDL // Deprecated: use libschema.ErrNonIdempotentDDL )
Functions ¶
func CheckScript
deprecated
func Computed ¶
func Computed[T ConnPtr](name string, action func(context.Context, T) error, opts ...libschema.MigrationOption) libschema.Migration
Computed defines a migration that runs arbitrary Go code. The signature of the action callback determines if the migration runs transactionally or if it runs outside a transaction:
func(context.Context, *sql.Tx) error // run transactionlly func(context.Context, *sql.DB) error // run non-transactionally
func Generate ¶
func Generate(name string, generator func(context.Context, *sql.Tx) string, opts ...libschema.MigrationOption) libschema.Migration
Generate registers a callback that returns a migration in a string. To run the migration, libschema automatically chooses transactional (*sql.Tx) or non-transactional (*sql.DB) execution based on mysql rules for statements that cannot run inside a transaction (DML (insert/update) can be in a transaction but DDL (create table, etc) cannot be). If the migration will be run transactionally, it will run in the same transaction as the callback that returned the string. If it runs non-transactionally, the transaction that returned the string will be idle (hanging around) while the migration runs. The choice of transactional vs non-transactional Can be overridden with ForceNonTransactional() or ForceTransactional() options.
func Script ¶
Script defines a literal SQL statement migration. To run the migration, libschema automatically chooses transactional (*sql.Tx) or non-transactional (*sql.DB) execution based on mysql rules for statements that cannot run inside a transaction (DML (insert/update) can be in a transaction but DDL (create table, etc) cannot be). The choice of transactional vs non-transactional Can be overridden with ForceNonTransactional() or ForceTransactional() options.
func WithoutDatabase ¶ added in v0.1.0
func WithoutDatabase(p *MySQL)
WithoutDatabase skips creating a *libschema.Database. Without it, functions for getting and setting the dbNames are required.
Types ¶
type MySQL ¶
type MySQL struct {
// contains filtered or unexported fields
}
MySQL is a libschema.Driver for connecting to MySQL-like databases that have the following characteristics: * CANNOT do DDL commands inside transactions * Support UPSERT using INSERT ... ON DUPLICATE KEY UPDATE * uses /* -- and # for comments * supports advisory locks * has quoting modes (ANSI_QUOTES) * can use ALTER TABLE to modify the primary key of a table
Because mysql DDL commands cause transactions to autocommit, tracking the schema changes in a secondary table (like libschema does) is inherently unsafe. The MySQL driver will record that it is about to attempt a migration and it will record if that attempts succeeds or fails, but if the program terminates mid-transaction, it is beyond the scope of libschema to determine if the transaction succeeded or failed. Such transactions will be retried. For this reason, it is reccomend that DDL commands be written such that they are idempotent.
There are methods the MySQL type that can be used to query the state of the database and thus transform DDL commands that are not idempotent (like CREATE INDEX) into idempotent commands by only running them if they need to be run.
Because Go's database/sql uses connection pooling and the mysql "USE database" command leaks out of transactions, it is strongly recommended that the libschema.Option value of SchemaOverride be set when creating the libschema.Schema object. That SchemaOverride will be propagated into the MySQL object and be used as a default table for all of the functions to interrogate data defintion status.
func New ¶
func New(log *internal.Log, dbName string, schema *libschema.Schema, db *sql.DB, options ...MySQLOpt) (*libschema.Database, *MySQL, error)
New creates a libschema.Database with a mysql driver built in. The dbName parameter specifies the name of the database, but that name is not actaully used anywhere except for logging. To override the database used for the migrations set the SchemaOverride option.
func (*MySQL) ColumnDefault ¶
ColumnDefault returns the default value for a column. If there is no default value, then nil is returned. The table is assumed to be in the current database unless m.UseDatabase() has been called.
func (*MySQL) ColumnIsInPrimaryKey ¶ added in v0.4.0
ColumnIsInPrimaryKey returns true if the column part of the prmary key. The table is assumed to be in the current database unless m.UseDatabase() has been called.
func (*MySQL) CreateSchemaTableIfNotExists ¶
func (p *MySQL) CreateSchemaTableIfNotExists(ctx context.Context, _ *internal.Log, d *libschema.Database) error
CreateSchemaTableIfNotExists creates the migration tracking table for libschema. It is expected to be called by libschema and is not called internally which means that is safe to override in types that embed MySQL.
func (*MySQL) DatabaseName ¶
DatabaseName returns the name of the current database (aka schema for MySQL). A call to UseDatabase() overrides all future calls to DatabaseName(). If the MySQL object was created from a libschema.Schema that had SchemaOverride set then this will return whatever that value was. It is reccomened that UseDatabase() be called to make sure that the right database is returned.
func (*MySQL) DoOneMigration ¶
func (p *MySQL) DoOneMigration(ctx context.Context, log *internal.Log, d *libschema.Database, m libschema.Migration) (rowsAffected int64, _ error)
DoOneMigration applies a single migration. It is expected to be called by libschema and is not called internally which means that is safe to override in types that embed MySQL.
func (*MySQL) DoesColumnExist ¶
DoesColumnExist returns true if the column exists The table is assumed to be in the current database unless m.UseDatabase() has been called.
func (*MySQL) GetTableConstraint ¶
GetTableConstraints returns the type of constraint and if it is enforced. The table is assumed to be in the current database unless m.UseDatabase() has been called.
func (*MySQL) HasPrimaryKey ¶
HasPrimaryKey returns true if the table has a primary key The table is assumed to be in the current database unless m.UseDatabase() has been called.
func (*MySQL) IsMigrationSupported ¶
func (p *MySQL) IsMigrationSupported(d *libschema.Database, _ *internal.Log, migration libschema.Migration) error
IsMigrationSupported checks to see if a migration is well-formed. Absent a code change, this should always return nil.
It is expected to be called by libschema and is not called internally which means that is safe to override in types that embed MySQL.
func (*MySQL) LoadStatus ¶
func (p *MySQL) LoadStatus(ctx context.Context, _ *internal.Log, d *libschema.Database) (_ []libschema.MigrationName, err error)
LoadStatus loads the current status of all migrations from the migration tracking table.
It is expected to be called by libschema and is not called internally which means that is safe to override in types that embed MySQL.
func (*MySQL) LockMigrationsTable ¶
func (p *MySQL) LockMigrationsTable(ctx context.Context, _ *internal.Log, d *libschema.Database) (finalErr error)
LockMigrationsTable locks the migration tracking table for exclusive use by the migrations running now.
It is expected to be called by libschema and is not called internally which means that is safe to override in types that embed MySQL.
In MySQL, locks are _not_ tied to transactions so closing the transaction does not release the lock. We'll use a transaction just to make sure that we're using the same connection. If LockMigrationsTable succeeds, be sure to call UnlockMigrationsTable.
func (*MySQL) TableHasIndex ¶
TableHasIndex returns true if there is an index matching the name given. The table is assumed to be in the current database unless m.UseDatabase() has been called.
func (*MySQL) UnlockMigrationsTable ¶
UnlockMigrationsTable unlocks the migration tracking table.
It is expected to be called by libschema and is not called internally which means that is safe to override in types that embed MySQL.
func (*MySQL) UseDatabase ¶ added in v0.0.2
UseDatabase() overrides the default database for DatabaseName(), ColumnDefault(), HasPrimaryKey(), HasTableIndex(), DoesColumnExist(), and GetTableConstraint(). If name is empty then the override is removed and the database will be queried from the mysql server. Due to connection pooling in Go, that's a bad idea.
type MySQLOpt ¶ added in v0.1.0
type MySQLOpt func(*MySQL)
func WithDialect ¶ added in v0.10.0
func WithDialect(dialect classifysql.Dialect) MySQLOpt
WithDialect is a somewhat internal function -- used by lssinglestore. It overrides the dialect.
func WithTrackingTableQuoter ¶ added in v0.1.0
func WithTrackingTableQuoter(f func(*libschema.Database) (schemaName, tableName, simpleTableName string, err error)) MySQLOpt
WithTrackingTableQuoter is a somewhat internal function -- used by lssinglestore. It replaces the private function that takes apart the name of the tracking table and provides the components.
Source Files