sunary/sqlize

generate SQL migration schema from golang models and the current SQL schema.

SQLize

English | 中文

Generate SQL migration schema from Golang models and the current SQL schema, support:

• MySQL
• Postgres
• Sqlite
• Sql Server

Features

• Sql parser (MySQL, Postgres, Sqlite)
• Sql builder from objects (MySQL, Postgres, Sqlite)
• Generate sql migration from Golang models and the current SQL schema
• Generate arvo schema (Mysql only)
• Support embedded struct
• Generate migration version - compatible with golang-migrate/migrate
• Tag options - compatible with gorm tag (default tag is sql)

Getting Started

package main

import (
	"time"
	
	"github.com/sunary/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 options like 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
);
*/

sqlize's Issues

Enable the option to disable the order of columns when check diff and add a new column.

correct get index using map instead of find in slice

Currently get id in map[string]int is correctness, so I'm finding in slice:

https://github.com/sunary/sqlize/blob/master/element/table.go#L150 and

https://github.com/sunary/sqlize/blob/master/element/table.go#L163

Help me correct this and able get id in map[string]int again.

It seems that sql statements cannot be generated properly when using gorm and the data table relationship is a many-to-many association.

It seems that sql statements cannot be generated properly when using gorm and the data table relationship is a many-to-many association.

A very good tool, hoping to support more databases

A very good tool, hoping to support SQL Server, SQL ite, and more databases

This information comes from Baidu Translate

support postgresql

Postgres SQL output incorrect.

Hi,

Very nice project! I discovered sqlize on a reddit post while I was looking for a Go SQL parser implementation to incorporate into pgdiff.

Anyway, it seems the Postgres SQL output is currently still in-progress but you've done pretty much all the work already so a trivial fix is possible.

Would you be opposed to this patch? It completely fixes the issues for my current use case.

Thanks.

I would like to add the option of automatically adding s to the table name, as well as the support for uppercase identifiers such as primary key/external key

I would like to add the option of automatically adding s to the table name, as well as the support for uppercase (adding a hump to snake conversion, which does not affect the original snake writing) for primary key/external identifiers. Our company is currently working with almost all frameworks based on Gorm, and this library table name will automatically add s. If it is necessary to define table names in each model, this is a relatively large project. Thank you very much

This information comes from Baidu Translate

Error on postgresql

Hi there!

I'm trying to use this package but I am getting this error

/go/pkg/mod/github.com/sunary/[email protected]/sql-parser/postgresql.go:15:18: cannot use sql (type string) as type "github.com/auxten/postgresql-parser/pkg/sql/parser".Statements in argument to w.Walk

I'm getting this issue when I tried to compile a main.go with the following sentence:

newMigration := sqlize.NewSqlize(sqlize.WithSqlTag("sql"), sqlize.WithMigrationFolder("./migrations"), sqlize.WithPostgresql())

Thanks in advance!

Is there any roadmap or todo list of this project?

This project is awesome, I use in my own project, is there any roadmap or TODO list? I want to contribute to this project

Detect modify enum type

before:

Display  string `sql:"type:ENUM('center','full')"`

after:

Display  string `sql:"type:ENUM('top','bottom','center','full')"`

expected sql:

ALTER TABLE `table_name` MODIFY COLUMN `display` enum('top','bottom','center','full') AFTER `previous_column`;