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

Overview

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.

Issues
  • [BUG] When AUTOINCREMENT is specified, the primary key is not set correctly.

    [BUG] When AUTOINCREMENT is specified, the primary key is not set correctly.

    Describe the bug If the primary key is defined in the "row defining the column" and "at the end of the table creation", SQLite will output an error when creating the table.

    • now
    CREATE TABLE `entry` (
        `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        `player_id` INTEGER NOT NULL,
        `title` TEXT NOT NULL,
        `public` INTEGER NOT NULL DEFAULT 0,
        `content` TEXT NOT NULL,
        `created_at` INTEGER NOT NULL,
        `updated_at` INTEGER NOT NULL,
        FOREIGN KEY (`player_id`) REFERENCES `player` (`id`) ON DELETE CASCADE,
        PRIMARY KEY (`id`)
    );
    
    • want
    CREATE TABLE `entry` (
        `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        `player_id` INTEGER NOT NULL,
        `title` TEXT NOT NULL,
        `public` INTEGER NOT NULL DEFAULT 0,
        `content` TEXT NOT NULL,
        `created_at` INTEGER NOT NULL,
        `updated_at` INTEGER NOT NULL,
        FOREIGN KEY (`player_id`) REFERENCES `player` (`id`) ON DELETE CASCADE,
    );
    

    To Reproduce

    type Entry struct {
    	ID        int32  `ddl:"auto"`
    	PlayerID  int32
    	Title     string  `ddl:"size=100"` // not used tag
    	Public    bool    `ddl:"default=0"`
    	Content   *string `ddl:"type=text"`
    	CreatedAt time.Time
    	UpdatedAt time.Time
    }
    
    func (e Entry) PrimaryKey() dialect.PrimaryKey {
    	return sqlite.AddPrimaryKey("id")
    }
    
    func TestDDLMaker_GenerateForSQLite(t *testing.T) {
    	t.Run("[Normal] generate ddl file for SQLite", func(t *testing.T) {
    		dm, err := New(Config{
    			OutFilePath: "./testdata/sqlite/test.sql",
    			DB: DBConfig{
    				Driver:  "sqlite",
    				Engine:  "",
    				Charset: "",
    			},
    		})
    		if err != nil {
    			t.Fatal("error new maker", err)
    		}
    		defer os.Remove("./testdata/sqlite/test.sql")
    
    		if err = dm.AddStruct(&User{}, &Entry{}); err != nil {
    			t.Fatal("error add struct", err)
    		}
    
    		if err = dm.Generate(); err != nil {
    			t.Fatal(err)
    		}
    
    		got, err := os.ReadFile("./testdata/sqlite/test.sql")
    		if err != nil {
    			t.Fatal(err)
    		}
    
    		want, err := os.ReadFile("./testdata/sqlite/golden.sql")
    		if err != nil {
    			t.Fatal(err)
    		}
    
    		if diff := cmp.Diff(string(want), string(got)); diff != "" {
    			t.Errorf("Compare value is mismatch (-want +got):%s\n", diff)
    		}
    	})
    }
    
    bug 
    opened by nao1215 0
Releases(v1.2.0)
Owner
CHIKAMATSU Naohiro
Love CLI tool for Linux.
CHIKAMATSU Naohiro
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

null 0 Oct 20, 2021
A Go SQL query builder and struct mapper.

godb - a Go query builder and struct mapper godb is a simple Go query builder and struct mapper, not a full-featured ORM. godb does not manage relatio

Samuel GAY 707 May 9, 2022
A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management

ploto A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management It's not an ORM. wo

solar 2 Mar 16, 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

Mohammed Al Ashaal 2k May 8, 2022
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

null 0 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

Joe Schafer 156 May 13, 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

WeLong 1 Nov 13, 2021
OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.

OctoSQL OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases, streaming sources and file formats using

Jacob Martin 3.1k May 18, 2022
Use SQL to instantly query file, domain, URL and IP scanning results from VirusTotal.

VirusTotal Plugin for Steampipe Use SQL to query file, domain, URL and IP scanning results from VirusTotal. Get started → Documentation: Table definit

Turbot 24 Dec 16, 2021
一个使 mysql,pgsql 数据库表自动生成 go struct 的工具

db2go 一个使 mysql、pgsql 数据库表自动生成 go struct 的工具 快速使用 将项目放入到GOPATH/src目录下

易水韩 17 Mar 24, 2022
database to golang struct

中文文档 mysql database to golang struct conversion tools base on gorm(v1/v2),You can automatically generate golang sturct from mysql database. big Camel-

xxj 1.9k May 18, 2022
Schemable - Schemable provides basic struct mapping against a database, using the squirrel package

Schemable Schemable provides basic struct mapping against a database, using the

null 6 Mar 7, 2022
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

BlasTrain Co., Ltd. 162 May 11, 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

Max Chechel 31 Jan 23, 2022
🐳 A most popular sql audit platform for mysql

?? A most popular sql audit platform for mysql

Henry Yee 6.4k May 10, 2022
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.

Jason Moiron 11.9k May 16, 2022
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

S Santhosh Nagaraj 18 Apr 21, 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

PingCAP 260 Apr 21, 2022
Universal command-line interface for SQL databases

usql A universal command-line interface for PostgreSQL, MySQL, Oracle Database, SQLite3, Microsoft SQL Server, and many other databases including NoSQ

XO 7.2k May 17, 2022