sql migration schema generate from models

SQLize

github action

Generate MySQL/PostgreSQL Migration from golang struct and existing sql

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/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: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 uppercase default, using option sql_builder.WithSqlLowercase() for sql lowercase
  • support generate comment, using option sql_builder.WithCommentGenerate()
  • primary key: sql:"primary_key"
  • auto increment: sql:"auto_increment"
  • index on a single column: sql:"index" or sql:"index:col_name", index name will be idx_col_name
  • index on a single column (custom name): sql:"index:idx_name"
  • composite index (can not custom): sql:"index:col1,col2", index name will be idx_col1_col2
  • index type: sql:"index_type:btree"
  • unique: sql:"unique"
  • 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 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
	User string
}

newMigration.FromObjects(sample{})

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

GitHub's Online Schema Migrations for MySQL

GitHub's Online Schema Migrations for MySQL

gh-ost GitHub's online schema migration for MySQL gh-ost is a triggerless online schema migration solution for MySQL. It is testable and provides paus

Jan 4, 2023

Manage Schema for KubeDB managed Databases

schema-manager Manage Schema for KubeDB managed Databases Installation To install KubeDB, please follow the guide here. Using KubeDB Want to learn how

Feb 19, 2022

Web-based, zero-config, dependency-free database schema change and version control tool for teams

Web-based, zero-config, dependency-free database schema change and version control tool for teams

Live Demo β€’ Install β€’ Help β€’ Development β€’ Design Doc Bytebase is a web-based, zero-config, dependency-free database schema change and version control

Jan 1, 2023

Go package for sharding databases ( Supports every ORM or raw SQL )

Go package for sharding databases ( Supports every ORM or raw SQL )

Octillery Octillery is a Go package for sharding databases. It can use with every OR Mapping library ( xorm , gorp , gorm , dbr ...) implementing data

Dec 16, 2022

Prep finds all SQL statements in a Go package and instruments db connection with prepared statements

Prep Prep finds all SQL statements in a Go package and instruments db connection with prepared statements. It allows you to benefit from the prepared

Dec 10, 2022

🐳 A most popular sql audit platform for mysql

🐳 A most popular sql audit platform for mysql

🐳 A most popular sql audit platform for mysql

Jan 6, 2023

sqlx is a library which provides a set of extensions on go's standard database/sql library

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

Jan 7, 2023

A tool to run queries in defined frequency and expose the count as prometheus metrics. Supports MongoDB and SQL

A tool to run queries in defined frequency and expose the count as prometheus metrics. Supports MongoDB and SQL

query2metric A tool to run db queries in defined frequency and expose the count as prometheus metrics. Why ? Product metrics play an important role in

Jul 1, 2022

Dumpling is a fast, easy-to-use tool written by Go for dumping data from the database(MySQL, TiDB...) to local/cloud(S3, GCP...) in multifarious formats(SQL, CSV...).

πŸ₯Ÿ Dumpling Dumpling is a tool and a Go library for creating SQL dump from a MySQL-compatible database. It is intended to replace mysqldump and mydump

Nov 9, 2022
Comments
  • Postgres SQL output incorrect.

    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.

  • Error on postgresql

    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!

  • Detect modify enum type

    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`;
    
  • correct get index using map instead of find in slice

    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.

Goose database migration tool - fork of https://bitbucket.org/liamstask/goose

goose Goose is a database migration tool. Manage your database schema by creating incremental SQL changes or Go functions. Goals of this fork github.c

Dec 30, 2022
It's a Go console utility for migration from MSSQL to MySQL engine.

A tool for migration the databases to MySQL It's a Go console utility for migration from MSSQL to MySQL engine. The databases should have prepopulated

Jan 4, 2022
Migration tool for ksqlDB, which uses the ksqldb-go client.
Migration tool for ksqlDB, which uses the ksqldb-go client.

ksqldb-migrate Migration tool for ksqlDB, which uses the ksqldb-go client.

Nov 15, 2022
write APIs using direct SQL queries with no hassle, let's rethink about SQL

SQLer SQL-er is a tiny portable server enables you to write APIs using SQL query to be executed when anyone hits it, also it enables you to define val

Jan 7, 2023
Parses a file and associate SQL queries to a map. Useful for separating SQL from code logic

goyesql This package is based on nleof/goyesql but is not compatible with it any more. This package introduces support for arbitrary tag types and cha

Oct 20, 2021
Go-sql-reader - Go utility to read the externalised sql with predefined tags

go-sql-reader go utility to read the externalised sql with predefined tags Usage

Jan 25, 2022
pggen - generate type safe Go methods from Postgres SQL queries

pggen - generate type safe Go methods from Postgres SQL queries pggen is a tool that generates Go code to provide a typesafe wrapper around Postgres q

Jan 3, 2023
auto generate sql from gorm model struct

gorm2sql: auto generate sql from gorm model struct A Swiss Army Knife helps you generate sql from gorm model struct. Installation go get github.com/li

Dec 22, 2022
xlsx2mysql: An tool of helping your fastly generate SQL from Excel.

xlsx2mysql An tool of helping your fastly generate SQL from Excel δΈ­ζ–‡ζ–‡ζ‘£ Origin In order to convert Excel to MySQL and I made a tool to implement.But Wh

Nov 13, 2021
ddl-maker generate ddl (SQL file) from Go struct.
ddl-maker generate ddl (SQL file) from Go struct.

[ζ—₯本θͺž] What is ddl-maker ddl-maker generate ddl (SQL file) from golang struct. It's only supported MySQL only now. The original code is kayac/ddl-maker

Jun 16, 2022