Database Grammars

go-migration uses grammars to translate its fluent schema builder API into database-specific SQL. Each grammar knows how to map column types, modifiers, and operations to the correct syntax for its target database engine.

Three grammars are supported out of the box:

• PostgreSQL (default)
• MySQL
• SQLite

Selecting a Grammar

When using the CLI (app.Run()), the grammar is auto-resolved from the driver field of the default connection in your migration.json config file. No manual grammar selection is needed:

For manual setup, pass a grammar to migrator.New() using the migrator.WithGrammar() option. The grammar drives the schema builder, Fresh(), the migration lock, and — since v1.2.0 — the tracking table: its DDL and bind placeholders come from the grammar too. Without WithGrammar, only the tracker falls back to PostgreSQL syntax, so always pass a grammar, and in particular for MySQL and SQLite.

package main

import (
    "database/sql"
    "log"

    _ "github.com/lib/pq"

    "github.com/gopackx/go-migration/pkg/migrator"
    "github.com/gopackx/go-migration/pkg/schema/grammars"
)

func main() {
    db, err := sql.Open("postgres", "postgres://user:password@localhost:5432/mydb?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    m := migrator.New(db, migrator.WithGrammar(&grammars.PostgresGrammar{}))

    // Register migrations and run...
}

package main

import (
    "database/sql"
    "log"

    _ "github.com/go-sql-driver/mysql"

    "github.com/gopackx/go-migration/pkg/migrator"
    "github.com/gopackx/go-migration/pkg/schema/grammars"
)

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/mydb")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    m := migrator.New(db, migrator.WithGrammar(&grammars.MySQLGrammar{}))

    // Register migrations and run...
}

package main

import (
    "database/sql"
    "log"

    _ "modernc.org/sqlite" // pure Go, registers as "sqlite"

    "github.com/gopackx/go-migration/pkg/migrator"
    "github.com/gopackx/go-migration/pkg/schema/grammars"
)

func main() {
    db, err := sql.Open("sqlite", "./app.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    m := migrator.New(db, migrator.WithGrammar(&grammars.SQLiteGrammar{}))

    // Register migrations and run...
}

Type Mapping Table

The table below shows how each go-migration column type maps to native SQL types across all three grammars.

Key Differences Between Grammars

PostgreSQL

• Uses BIGSERIAL for auto-incrementing IDs
• Has native UUID and JSONB types
• Uses BYTEA for binary data
• BOOLEAN is a true boolean type
• Timestamps use TIMESTAMPTZ (timestamp with time zone)

MySQL

• Uses BIGINT UNSIGNED AUTO_INCREMENT for IDs
• UUIDs are stored as CHAR(36)
• Has native JSON type (MySQL 5.7+)
• Booleans use TINYINT(1) (0 or 1)

SQLite

• Uses INTEGER PRIMARY KEY AUTOINCREMENT for IDs
• Most types map to SQLite's core affinities (INTEGER, TEXT, REAL, BLOB)
• Strings, dates, and timestamps are all stored as TEXT (the String length is ignored)
• No native UUID or JSON types — stored as TEXT
• No native boolean — stored as integer (0 or 1)

Behaviour per Database

See Transactions, Fresh, and Concurrency and Locking for details.

Identifier Quoting

Table, column, index, and tracking-table names are always quoted, with the delimiter escaped by doubling it: "a""b" on PostgreSQL and SQLite, `a``b` on MySQL. Names compared as values in the HasTable/HasColumn catalog queries are single-quoted with ' doubled (and, on MySQL, \ doubled). Any name the database accepts can therefore be used, including ones containing quote characters.

Because names are quoted, they are case-sensitive on PostgreSQL. In schema-builder names (Create, Alter, HasTable, ...) a dot is part of the name. The tracking table name (migration_table / WithTableName) is the exception: it may be schema-qualified, and each dot-separated part is quoted separately — app.migrations becomes "app"."migrations" on PostgreSQL (schema app), `app`.`migrations` on MySQL (database app), and "app"."migrations" on SQLite (attached database app). The schema must already exist.

Custom Grammars

To support another database, or to change the generated SQL, implement the schema.Grammar interface and pass your type to migrator.WithGrammar. As of v1.2.0 the interface is:

type Grammar interface {
    CompileCreate(blueprint *Blueprint) ([]string, error)
    CompileAlter(blueprint *Blueprint) ([]string, error)
    CompileDrop(table string) string
    CompileDropIfExists(table string) string
    CompileRename(from, to string) string
    CompileHasTable(table string) string
    CompileHasTableIn(schema, table string) string
    CompileHasColumn(table, column string) string
    CompileListTables() string
    CompileDropAllTables(tables []string) []string
    CompileTrackingTable(table string) string
    QuoteIdentifier(name string) string
    Placeholder(index int) string
    CompileTryLock(key string) string
    CompileUnlock(key string) string
    CompileColumnType(col ColumnDefinition) (string, error)
}

Upgrading a Custom Grammar to v1.2.0

The built-in grammars already implement everything below. If you implement schema.Grammar yourself, the interface changed in v1.2.0:

Changed signatures

• CompileCreate(bp) ([]string, error) now returns a slice — the CREATE TABLE statement followed by any separate CREATE INDEX statements. Each element is executed individually, because many drivers (pgx stdlib, go-sql-driver/mysql) reject multi-statement Exec. It was previously CompileCreate(bp) (string, error).
• CompileDropAllTables(tables []string) []string receives the table list produced by CompileListTables() and returns the statements to run, in order, on one connection. It was previously CompileDropAllTables() string. Grammars that can drop everything without the list (like PostgreSQL's DROP SCHEMA ... CASCADE) may ignore it.

New methods

Return "" from both CompileTryLock and CompileUnlock if the database needs no migration lock, as SQLite does — the migrator then skips locking. The lock must belong to the database session, not to a transaction, so that it can be held across several migration transactions and is released by the server when the connection closes.

For reference, the built-in lock queries are:

-- PostgreSQL
SELECT pg_try_advisory_lock(hashtext($1))
SELECT pg_advisory_unlock(hashtext($1))

-- MySQL
SELECT GET_LOCK(CONCAT(DATABASE(), ':', ?), 0)
SELECT RELEASE_LOCK(CONCAT(DATABASE(), ':', ?))

What's Next?

• Seeders — populate your database with test data
• Column Types — full reference of all schema builder column methods
• Connections — manage multiple database connections