sqlize

README

SQLize

English | 中文

Generate Migration from golang struct and existing sql, support:

• MySQL
• Postgres
• Sqlite
• Sql Server

Features

• Sql parser
• Sql builder from objects
• Generate sql migration from diff between existed sql and objects
• Generate arvo schema (Mysql only)
• Support embedded struct
• Generate migration version - compatible with golang-migrate/migrate
• Tag options - compatible with gorm tag

WARNING: some functions doesn't work on PostgreSQL, let me know of any issues

Getting Started

package main

import (
	"time"
	
	"github.com/Jinboo-Tech/sqlize"
)

type user struct {
	ID          int32  `sql:"primary_key;auto_increment"`
	Alias       string `sql:"type:VARCHAR(64)"`
	Name        string `sql:"type:VARCHAR(64);unique;index_columns:name,age"`
	Age         int
	Bio         string
	IgnoreMe    string     `sql:"-"`
	AcceptTncAt *time.Time `sql:"index:idx_accept_tnc_at"`
	CreatedAt   time.Time  `sql:"default:CURRENT_TIMESTAMP"`
	UpdatedAt   time.Time  `sql:"default:CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;index:idx_updated_at"`
}

func (user) TableName() string {
	return "user"
}

var createStm = `
CREATE TABLE user (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(64),
  age           INT,
  bio           TEXT,
  gender        BOOL,
  accept_tnc_at DATETIME NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX idx_name_age ON user(name, age);
CREATE INDEX idx_updated_at ON user(updated_at);`

func main() {
	n := time.Now()
	newMigration := sqlize.NewSqlize(sqlize.WithSqlTag("sql"), sqlize.WithMigrationFolder(""))
	_ = newMigration.FromObjects(user{AcceptTncAt: &n})

	println(newMigration.StringUp())
	//CREATE TABLE `user` (
	//	`id`            int(11) AUTO_INCREMENT PRIMARY KEY,
	//	`alias`         varchar(64),
	//	`name`          varchar(64),
	//	`age`           int(11),
	//	`bio`           text,
	//	`accept_tnc_at` datetime NULL,
	//	`created_at`    datetime DEFAULT CURRENT_TIMESTAMP(),
	//	`updated_at`    datetime DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
	//);
	//CREATE UNIQUE INDEX `idx_name_age` ON `user`(`name`, `age`);
	//CREATE INDEX `idx_accept_tnc_at` ON `user`(`accept_tnc_at`);
	//CREATE INDEX `idx_updated_at` ON `user`(`updated_at`);

	println(newMigration.StringDown())
	//DROP TABLE IF EXISTS `user`;

	oldMigration := sqlize.NewSqlize(sqlize.WithMigrationFolder(""))
	//_ = oldMigration.FromMigrationFolder()
	_ = oldMigration.FromString(createStm)

	newMigration.Diff(*oldMigration)

	println(newMigration.StringUp())
	//ALTER TABLE `user` ADD COLUMN `alias` varchar(64) AFTER `id`;
	//ALTER TABLE `user` DROP COLUMN `gender`;
	//CREATE INDEX `idx_accept_tnc_at` ON `user`(`accept_tnc_at`);

	println(newMigration.StringDown())
	//ALTER TABLE `user` DROP COLUMN `alias`;
	//ALTER TABLE `user` ADD COLUMN `gender` tinyint(1) AFTER `age`;
	//DROP INDEX `idx_accept_tnc_at` ON `user`;

	println(newMigration.ArvoSchema())
	//...

	_ = newMigration.WriteFiles("demo migration")
}

Convention

• mysql by default, using option sql_builder.WithPostgresql() for postgresql
• Sql syntax uppercase (Eg: "SELECT * FROM user WHERE id = ?") default, using option sql_builder.WithSqlLowercase() for lowercase
• Support generate comment, using option sql_builder.WithCommentGenerate()
• Support automatic addition of s to table names (plural naming convention), using option sql_builder.WithPluralTableName()
• Accept tag convention: snake_case or camelCase, Eg: sql:"primary_key" equalize sql:"primaryKey"
• Primary key for this field: sql:"primary_key"
• Foreign key: sql:"foreign_key:user_id;references:user_id"
• Auto increment: sql:"auto_increment"
• Indexing this field: sql:"index"
• Custom index name: sql:"index:idx_col_name"
• Unique indexing this field: sql:"unique"
• Custome unique index name: sql:"unique:idx_name"
• Composite index (include unique index and primary key): sql:"index_columns:col1,col2"
• Index type: sql:"index_type:btree"
• Set default value: sql:"default:CURRENT_TIMESTAMP"
• Override datatype: sql:"type:VARCHAR(64)"
• Ignore: sql:"-"
• Pointer value must be declare in struct

type sample struct {
	ID        int32 `sql:"primary_key"`
	DeletedAt *time.Time
}

now := time.Now()
newMigration.FromObjects(sample{DeletedAt: &now})

• mysql data type will be changed implicitly:

TINYINT => tinyint(4)
INT     => int(11)
BIGINT  => bigint(20)

• fields belong to embedded struct have the lowest order, except primary key always first
• an embedded struct (sql:"embedded" or sql:"squash") can not be pointer, also support prefix: sql:"embedded_prefix:base_"

type Base struct {
	ID        int32 `sql:"primary_key"`
	CreatedAt time.Time
}
type sample struct {
	Base `sql:"embedded"`
	User string
}

newMigration.FromObjects(sample{})

/*
CREATE TABLE sample (
 id         int(11) PRIMARY KEY,
 user       text,
 created_at datetime
);
*/

Documentation

Index

• type Sqlize
  • func NewSqlize(opts ...SqlizeOption) *Sqlize
  • func (s Sqlize) ArvoSchema(needTables ...string) []string
  • func (s Sqlize) Diff(old Sqlize)
  • func (s *Sqlize) FromMigrationFolder() error
  • func (s *Sqlize) FromObjects(objs ...interface{}) error
  • func (s *Sqlize) FromString(sql string) error
  • func (s Sqlize) HashValue() int64
  • func (s Sqlize) StringDown() string
  • func (s Sqlize) StringDownWithVersion(ver int64) string
  • func (s Sqlize) StringUp() string
  • func (s Sqlize) StringUpWithVersion(ver int64, dirty bool) string
  • func (s Sqlize) WriteFiles(name string) error
  • func (s Sqlize) WriteFilesVersion(name string, ver int64, dirty bool) error
  • func (s Sqlize) WriteFilesWithVersion(name string, ver int64, dirty bool) error
• type SqlizeOption
  • func WithCommentGenerate() SqlizeOption
  • func WithMigrationFolder(path string) SqlizeOption
  • func WithMigrationSuffix(upSuffix, downSuffix string) SqlizeOption
  • func WithMigrationTable(table string) SqlizeOption
  • func WithMysql() SqlizeOption
  • func WithPluralTableName() SqlizeOption
  • func WithPostgresql() SqlizeOption
  • func WithSqlLowercase() SqlizeOption
  • func WithSqlTag(sqlTag string) SqlizeOption
  • func WithSqlUppercase() SqlizeOption
  • func WithSqlite() SqlizeOption
  • func WithSqlserver() SqlizeOption

Types

type Sqlize

type Sqlize struct {
	// contains filtered or unexported fields
}

Sqlize ...

func NewSqlize

func NewSqlize(opts ...SqlizeOption) *Sqlize

NewSqlize ...

func (Sqlize) ArvoSchema

func (s Sqlize) ArvoSchema(needTables ...string) []string

ArvoSchema export arvo schema, support mysql only

func (Sqlize) Diff

func (s Sqlize) Diff(old Sqlize)

Diff differ between 2 migrations

func (*Sqlize) FromMigrationFolder

func (s *Sqlize) FromMigrationFolder() error

FromMigrationFolder load migration from folder `migrations`

func (*Sqlize) FromObjects

func (s *Sqlize) FromObjects(objs ...interface{}) error

FromObjects load from objects

func (*Sqlize) FromString

func (s *Sqlize) FromString(sql string) error

FromString load migration from sql

func (Sqlize) HashValue

func (s Sqlize) HashValue() int64

HashValue ...

func (Sqlize) StringDown

func (s Sqlize) StringDown() string

StringDown migration down

func (Sqlize) StringDownWithVersion

func (s Sqlize) StringDownWithVersion(ver int64) string

StringDownWithVersion migration down with version

func (Sqlize) StringUp

func (s Sqlize) StringUp() string

StringUp migration up

func (Sqlize) StringUpWithVersion

func (s Sqlize) StringUpWithVersion(ver int64, dirty bool) string

StringUpWithVersion migration up with version

func (Sqlize) WriteFiles

func (s Sqlize) WriteFiles(name string) error

WriteFiles create migration files

func (Sqlize) WriteFilesVersion

func (s Sqlize) WriteFilesVersion(name string, ver int64, dirty bool) error

WriteFilesVersion create migration version only

func (Sqlize) WriteFilesWithVersion

func (s Sqlize) WriteFilesWithVersion(name string, ver int64, dirty bool) error

WriteFilesWithVersion create migration files with version

type SqlizeOption

type SqlizeOption interface {
	// contains filtered or unexported methods
}

SqlizeOption ...

func WithCommentGenerate

func WithCommentGenerate() SqlizeOption

WithCommentGenerate default is off

func WithMigrationFolder

func WithMigrationFolder(path string) SqlizeOption

WithMigrationFolder ...

func WithMigrationSuffix

func WithMigrationSuffix(upSuffix, downSuffix string) SqlizeOption

WithMigrationSuffix ...

func WithMigrationTable

func WithMigrationTable(table string) SqlizeOption

WithMigrationTable default is 'schema_migration'

func WithMysql

func WithMysql() SqlizeOption

WithMysql default

func WithPluralTableName

func WithPluralTableName() SqlizeOption

Table name plus s default

func WithPostgresql

func WithPostgresql() SqlizeOption

WithPostgresql ...

func WithSqlLowercase

func WithSqlLowercase() SqlizeOption

WithSqlLowercase ...

func WithSqlTag

func WithSqlTag(sqlTag string) SqlizeOption

WithSqlTag default is `sql`

func WithSqlUppercase

func WithSqlUppercase() SqlizeOption

WithSqlUppercase default

func WithSqlite

func WithSqlite() SqlizeOption

WithSqlite ...

func WithSqlserver

func WithSqlserver() SqlizeOption

WithSqlserver ...