migrate

Overview ¶

Package cydb provides automatic table creation based on Go struct definitions.

This package implements a GORM-like AutoMigrate functionality that can automatically create database tables based on struct field definitions and cydb tags. When tables already exist, it will add any missing columns to match the struct definition.

The package supports multiple database types (MySQL, PostgreSQL, SQLite, Oracle) through a dialect-based architecture. Each database type has its own implementation for SQL generation, field type mapping, and constraint handling.

Supported databases (all implement MigrationDialect interface): - MySQL - PostgreSQL - SQLite - Oracle

Usage:

Define your model structs with cydb tags for single-column constraints:

type User struct {
	ID        int       `cydb:"column:id;type:int;primary_key;auto_increment"`
	Name      string    `cydb:"column:name;type:varchar;size:100;not_null"`
	Email     string    `cydb:"column:email;type:varchar;size:255;unique;not_null"`
	Age       int       `cydb:"column:age;type:int;default:0"`
	CreatedAt time.Time `cydb:"column:created_at;type:timestamp;default_current_time"`
	UpdatedAt time.Time `cydb:"column:updated_at;type:timestamp;update_current_time"`
}

// For standard types, use type:bigint instead of type:int64
// PostgreSQL will automatically use BIGSERIAL for auto_increment with bigint
type Product struct {
	ID    int64  `cydb:"column:id;type:bigint;primary_key;auto_increment"`
	Name  string `cydb:"column:name;type:varchar;not_null"` // size defaults to 255
}

type Order struct {
	ID       int  `cydb:"column:id;primary_key"`
	UserID   int  `cydb:"column:user_id;type:int;not_null"`
	OrgID    int  `cydb:"column:org_id;type:int;not_null"`
	Amount   float64 `cydb:"column:amount;type:decimal;precision:10;scale:2;not_null"`
	Status   string  `cydb:"column:status;type:varchar;size:50;default:pending"`
}

// Implement TableNameProvider interface for custom table name
func (Order) TableName() string {
	return "orders"
}

// Implement CompositeForeignKeyProvider interface for composite foreign keys
func (Order) CompositeForeignKeys() []cydb.CompositeForeignKey {
	return []cydb.CompositeForeignKey{
		{
			Name:              "fk_order_user_org",
			Columns:           []string{"user_id", "org_id"},
			ReferencedTable:   "user_orgs",
			ReferencedColumns: []string{"user_id", "org_id"},
			OnDelete:          "CASCADE",
			OnUpdate:          "CASCADE",
		},
	}
}

// Implement CompositeIndexProvider interface for composite indexes
func (Order) CompositeIndexes() []cydb.CompositeIndex {
	return []cydb.CompositeIndex{
		{Name: "idx_user_org", Columns: []string{"user_id", "org_id"}, Unique: false},
	}
}

// Implement FieldMappingProvider interface for handling column renames
func (Order) FieldMappings() []cydb.FieldMapping {
	return []cydb.FieldMapping{
		{OldColumnName: "user_id", NewColumnName: "uid"},
		{OldColumnName: "org_id", NewColumnName: "organization_id"},
	}
}

Then use AutoMigrate to create tables or add missing columns:

db := &DBCli{...} // your database client
err := db.AutoMigrate(&User{}, &Order{})
if err != nil {
	log.Fatal(err)
}

AutoMigrate will: - Create tables that don't exist with all columns from the struct - For existing tables:

• Rename columns according to field mappings
• Add missing columns
• Drop unused columns (if WithDropColumns(true) is specified, considers field mappings to avoid dropping renamed columns)
• Sync primary keys, indexes, and constraints (unless WithoutSync(true) is specified)

Note: WithoutSync(true) only skips constraint synchronization. Column deletion (if enabled) and field mappings are always applied regardless of WithoutSync setting.

AutoMigrate Options:

All options are optional and only affect the current migration call.

Customize type defaults (applied to types without explicit size/precision in struct tags):

// Set default varchar size to 100 (default: 255)
db.AutoMigrate(&User{}, WithVarcharSize(100))

// Set default char size (default: 1)
db.AutoMigrate(&User{}, WithCharSize(10))

// Set default decimal precision and scale (default: 10, 2)
db.AutoMigrate(&Order{},
	WithDecimalPrecision(15),
	WithDecimalScale(4),
)

// Set default varbinary size (default: 255)
db.AutoMigrate(&Document{}, WithVarbinarySize(50000))

Control constraint synchronization when table exists:

// Skip syncing constraints (only add missing columns)
// This also skips GetTableInfo call, improving performance for large tables
db.AutoMigrate(&User{}, WithoutSync(true))

Control column deletion:

// Drop columns that exist in database but not in struct (default: false, safe mode)
// Considers field mappings to avoid dropping renamed columns
// If a column fails to drop, continues with other columns (some databases may not support dropping)
db.AutoMigrate(&User{}, WithDropColumns(true))

Combine multiple options:

db.AutoMigrate(&User{},
	WithVarcharSize(100),
	WithDecimalPrecision(12),
	WithoutSync(true),
	WithDropColumns(true),
)

Standard Type System:

The package supports database-agnostic standard types that are automatically converted to database-specific types. This allows you to write portable code that works across all supported databases.

Available standard types: - String: varchar, text, char, longtext - Integer: tinyint, smallint, int, bigint - Unsigned: utinyint, usmallint, uint, ubigint - Float: float, double, decimal - Time: date, time, datetime, timestamp - Other: bool, varbinary, blob, json, jsonb

Parameters with defaults (can be omitted): - varchar: size defaults to 255 - char: size defaults to 1 - varbinary: size defaults to 255 - decimal: precision defaults to 10, scale defaults to 2

Example with standard types:

type User struct {
	ID       int64   `cydb:"column:id;type:bigint;primary_key;auto_increment"`
	Username string  `cydb:"column:username;type:varchar;size:50"`
	Bio      string  `cydb:"column:bio;type:text"`
	Price    float64 `cydb:"column:price;type:decimal;precision:10;scale:2"`
	Birthday time.Time `cydb:"column:birthday;type:date"`
	Active   bool    `cydb:"column:active;type:bool"`
	Email    string  `cydb:"column:email;type:varchar"` // size defaults to 255
}

Limitations: - Does not modify existing column definitions (type, constraints, etc.) - Does not delete columns by default (use WithDropColumns(true) to enable) - Does not modify or delete existing indexes (only syncs new ones) - Column renames must be defined in FieldMappings before the first migration

Supported cydb tag options:

Column definition:

• column:name - specify column name (required)
• type:datatype - specify standard type or SQL data type (optional) If omitted, type is inferred from Go field type by the database dialect Examples: varchar, text, int, bigint, decimal, date, bool, etc. See Standard Type System section for complete list

Size/Precision (optional, have built-in defaults that can be overridden with WithXX options): - size:length - specify size for varchar, char, varbinary types (built-in defaults: 255, 1, 255) - precision:n - specify precision for decimal type (built-in default: 10) - scale:n - specify scale for decimal type (built-in default: 2)

Constraints:

• primary_key - mark field as primary key
• auto_increment - mark field as auto-increment (MySQL: AUTO_INCREMENT, PostgreSQL: SERIAL/BIGSERIAL, SQLite: AUTOINCREMENT, Oracle: SEQUENCE+TRIGGER)
• unique - add unique constraint
• not_null - add NOT NULL constraint
• index - create an index on the field
• foreign_key:table:column - add foreign key constraint (single column)

Default values: - default:value - set default value (without quotes, e.g., default:0, default:pending) - default_current_time - set DEFAULT CURRENT_TIMESTAMP (for creation time) - update_current_time - set to update to current timestamp on every update

Foreign key actions (used with foreign_key): - on_delete:action - specify ON DELETE action (CASCADE, SET NULL, RESTRICT, NO ACTION) - on_update:action - specify ON UPDATE action (CASCADE, SET NULL, RESTRICT, NO ACTION)

For composite foreign keys, composite indexes, and field mappings, implement the corresponding interfaces: - TableNameProvider - provide custom table name - CompositeForeignKeyProvider - define multi-column foreign key constraints - CompositeIndexProvider - define multi-column indexes - FieldMappingProvider - define column name mappings for renames

Important Notes:

• Foreign key constraints are database-specific and may have different syntax across database types. The implementation handles these differences through dialects.
• When using WithDropColumns(true), ensure FieldMappings are correct before running migration to avoid accidentally deleting renamed columns.
• WithoutSync(true) skips constraint synchronization but still applies field mappings and column deletion (if enabled). Use it for performance optimization when you know constraints are already in sync.