Skip to content
This repository has been archived by the owner on Apr 9, 2023. It is now read-only.
/ ddl-maker Public archive

ddl-maker generate ddl (SQL file) from Go struct.

License

Notifications You must be signed in to change notification settings

nao1215/ddl-maker

Repository files navigation

UnitTest reviewdog codecov Go Reference GitHub Go Report Card
[日本語]

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 and this repository is a fork from it. nao1215/ddl-maker was not actively updated. I wanted to add features, add tests, and improve documentation. However, I wasn't sure if they would be merged. So, I decided to fork it and add our own features.

Support SQL Driver & Golang version

  • MySQL
  • SQLite
  • go version 1.18

How to use

The following sample code uses two files.

  • example.go defining structures for DDL generation
  • create_ddl.go defines an implementation for generating DDL from golang structures.

_example/example.go

package example

import (
	"database/sql"
	"time"

	"github.com/nao1215/ddl-maker/dialect"
	"github.com/nao1215/ddl-maker/dialect/mysql"
)

type User struct {
	Id                  uint64
	Name                string
	CreatedAt           time.Time
	UpdatedAt           time.Time
	Token               string `ddl:"-"`
	DailyNotificationAt string `ddl:"type=time"`
}

func (u *User) Table() string {
	return "player"
}

func (u *User) PrimaryKey() dialect.PrimaryKey {
	return mysql.AddPrimaryKey("id")
}

type Entry struct {
	Id        int32   `ddl:"auto"`
	Title     string  `ddl:"size=100"`
	Public    bool    `ddl:"default=0"`
	Content   *string `ddl:"type=text"`
	CreatedAt time.Time
	UpdatedAt time.Time
}

func (e Entry) PrimaryKey() dialect.PrimaryKey {
	return mysql.AddPrimaryKey("id", "created_at")
}

func (e Entry) Indexes() dialect.Indexes {
	return dialect.Indexes{
		mysql.AddUniqueIndex("created_at_uniq_idx", "created_at"),
		mysql.AddIndex("title_idx", "title"),
		mysql.AddIndex("created_at_idx", "created_at"),
		mysql.AddFullTextIndex("full_text_idx", "content").WithParser("ngram"),
	}
}

type PlayerComment struct {
	Id        int32          `ddl:"auto,size=100" json:"id"`
	PlayerID  int32          `json:"player_id"`
	EntryID   int32          `json:"entry_id"`
	Comment   sql.NullString `json:"comment" ddl:"null,size=99"`
	CreatedAt time.Time      `json:"created_at"`
	updatedAt time.Time
}

func (pc PlayerComment) PrimaryKey() dialect.PrimaryKey {
	return mysql.AddPrimaryKey("id")
}

func (pc PlayerComment) Indexes() dialect.Indexes {
	return dialect.Indexes{
		mysql.AddIndex("player_id_entry_id_idx", "player_id", "entry_id"),
	}
}

func (pc PlayerComment) ForeignKeys() dialect.ForeignKeys {
	return dialect.ForeignKeys{
		mysql.AddForeignKey(
			[]string{"player_id"},
			[]string{"id"},
			"player",
		),
		mysql.AddForeignKey(
			[]string{"entry_id"},
			[]string{"id"},
			"entry",
		),
	}
}

type Bookmark struct {
	Id        int32     `ddl:"size=100" json:"id"`
	UserId    int32     `json:"user_id"`
	EntryId   int32     `json:"entry_id"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

func (b Bookmark) PrimaryKey() dialect.PrimaryKey {
	return mysql.AddPrimaryKey("id")
}

func (b Bookmark) Indexes() dialect.Indexes {
	return dialect.Indexes{
		mysql.AddUniqueIndex("user_id_entry_id", "user_id", "entry_id"),
	}
}

func (b Bookmark) ForeignKeys() dialect.ForeignKeys {
	return dialect.ForeignKeys{
		mysql.AddForeignKey(
			[]string{"player_id"},
			[]string{"id"},
			"player",
		),
		mysql.AddForeignKey(
			[]string{"entry_id"},
			[]string{"id"},
			"entry",
		),
	}
}

_example/create_ddl/create_ddl.go

package main

import (
	"flag"
	"log"

	"github.com/nao1215/ddl-maker"
	ex "github.com/nao1215/ddl-maker/_example"
)

func main() {
	var (
		driver      string
		engine      string
		charset     string
		outFilePath string
	)
	flag.StringVar(&driver, "d", "", "set driver")
	flag.StringVar(&driver, "driver", "", "set driver")
	flag.StringVar(&outFilePath, "o", "./sql/master.sql", "set ddl output file path")
	flag.StringVar(&outFilePath, "outfile", "./sql/master.sql", "set ddl output file path")
	flag.StringVar(&engine, "e", "InnoDB", "set driver engine")
	flag.StringVar(&engine, "engine", "InnoDB", "set driver engine")
	flag.StringVar(&charset, "c", "utf8mb4", "set driver charset")
	flag.StringVar(&charset, "charset", "utf8mb4", "set driver charset")
	flag.Parse()

	if driver == "" {
		log.Println("Please set driver name. -d or -driver")
		return
	}
	if outFilePath == "" {
		log.Println("Please set outFilePath. -o or -outfile")
		return
	}

	conf := ddlmaker.Config{
		DB: ddlmaker.DBConfig{
			Driver:  driver,
			Engine:  engine,
			Charset: charset,
		},
		OutFilePath: outFilePath,
	}

	dm, err := ddlmaker.New(conf)
	if err != nil {
		log.Println(err.Error())
		return
	}

	structs := []interface{}{
		ex.User{},
		ex.Entry{},
		ex.PlayerComment{},
		ex.Bookmark{},
	}

	dm.AddStruct(structs...)

	err = dm.Generate()
	if err != nil {
		log.Println(err.Error())
		return
	}
}

generate ddl

In this example, the DDL is generated as sql/schema.sql.

$ cd _example
$ go run create_ddl/create_ddl.go

sql/schema.sql

SET foreign_key_checks=0;

DROP TABLE IF EXISTS `player`;

CREATE TABLE `player` (
    `id` BIGINT unsigned NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    `daily_notification_at` TIME NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;


DROP TABLE IF EXISTS `entry`;

CREATE TABLE `entry` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(100) NOT NULL,
    `public` TINYINT(1) NOT NULL DEFAULT 0,
    `content` TEXT NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    FULLTEXT `full_text_idx` (`content`) WITH PARSER `ngram`,
    INDEX `created_at_idx` (`created_at`),
    INDEX `title_idx` (`title`),
    UNIQUE `created_at_uniq_idx` (`created_at`),
    PRIMARY KEY (`id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;


DROP TABLE IF EXISTS `player_comment`;

CREATE TABLE `player_comment` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `player_id` INTEGER NOT NULL,
    `entry_id` INTEGER NOT NULL,
    `comment` VARCHAR(99) NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    INDEX `player_id_entry_id_idx` (`player_id`, `entry_id`),
    FOREIGN KEY (`entry_id`) REFERENCES `entry` (`id`),
    FOREIGN KEY (`player_id`) REFERENCES `player` (`id`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;


DROP TABLE IF EXISTS `bookmark`;

CREATE TABLE `bookmark` (
    `id` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `entry_id` INTEGER NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    UNIQUE `user_id_entry_id` (`user_id`, `entry_id`),
    FOREIGN KEY (`entry_id`) REFERENCES `entry` (`id`),
    FOREIGN KEY (`player_id`) REFERENCES `player` (`id`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

SET foreign_key_checks=1;

Type conversion table

Golang Type MySQL SQLite
int8 TINYINT INTEGER
int16 SMALLINT INTEGER
int32 INTGER INTEGER
int64, sql.NullInt64 BIGINT INTEGER
uint8 TINYINT unsigned INTEGER
uint16 SMALLINT unsigned INTEGER
uint32 INTEGER unsigned INTEGER
uint64 BIGINT unsigned INTEGER
float32 FLOAT REAL
float64 FLOAT REAL
[]uint8, sql.RawByte VARBINARY(N) BLOB
float64, sql.NullFloat64 DOUBLDE REAL
string, sql.NullString VARCHAR TEXT
bool, sql.NullBool TINYINT(1) INTEGER
time.Time, mysql.NullTime DATETIME INTEGER
date DATE INTEGER
tinytext TINYTEXT TEXT
text TEXT TEXT
mediumtext MEDIUMTEXT TEXT
longtext LONGTEXT TEXT
tinyblob TINYBLOB BLOB
blob BLOB BLOB
mediumblob MEDIUMBLOB BLOB
longblob LONGBLOB BLOB
json.RawMessage JSON JSON
geometry GEOMETRY Not support

mysql.NullTime is from github.com/go-sql-driver/mysql.

Option using Golang Struct Tag Field's

tag prefix is ddl

TAG Value VALUE
null NULL (DEFAULT NOT NULL)
size=<size> VARCHAR(<size value>)
auto AUTO INCREMENT
type=<type> OVERRIDE struct type.
ex) string `ddl:"text`
- Don't define column

How to Set PrimaryKey

Define struct method called PrimaryKey()

func (b Bookmark) PrimaryKey() dialect.PrimaryKey {
	return mysql.AddPrimaryKey("id")
}

How to Set Index

Define struct method called Indexes()

Index Type Method
Index dialect.Index(index name, columns...)
Unique Index dialect.UniqIndex(index name, columns...)
Full Text Index dialect.FullTextIndex(index name, columns...).WithParser(parser name)
Spatial Index dialect.SpatialIndex(index name, columns...)
func (b Bookmark) Indexes() dialect.Indexes {
	return dialect.Indexes{
		mysql.AddUniqueIndex("user_id_entry_id", "user_id", "entry_id"),
	}
}

How to Set ForeignKey

Define struct method called ForeignKeys()

Referential Actions Option

ReferentialActionsOption Method
ON UPDATE WithUpdateForeignKeyOption(option ForeignKeyOptionType)
ON DELETE WithDeleteForeignKeyOption(option ForeignKeyOptionType)
ForeignKeyOptionType Value
ForeignKeyOptionCascade CASCADE
ForeignKeyOptionSetNull SET NULL
ForeignKeyOptionRestrict RESTRICT
ForeignKeyOptionNoAction NO ACTION
ForeignKeyOptionSetDefault SET DEFAULT
func (pc PlayerComment) ForeignKeys() dialect.ForeignKeys {
	return dialect.ForeignKeys{
		mysql.AddForeignKey(
			[]string{"player_id"},
			[]string{"id"},
			"player",
		),
		mysql.AddForeignKey(
			[]string{"entry_id"},
			[]string{"id"},
			"entry",
		),
	}
}

Contributing

First off, thanks for taking the time to contribute! ❤️ See CONTRIBUTING.md for more information. Contributions are not only related to development. For example, GitHub Star motivates me to develop! Star History Chart

Contact

If you would like to send comments such as "find a bug" or "request for additional features" to the developer, please use one of the following contacts.

LICENSE

The ddl-maker project is licensed under the terms of the Apache License 2.0.