sqlgen2

package module
v0.8.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jan 14, 2018 License: BSD-2-Clause Imports: 7 Imported by: 0

README

sqlgen generates SQL statements and database helper functions from your Go structs. It can be used in place of a simple ORM or hand-written SQL.

See the demo directory for examples. Look in the generated files *_sql.go and the hand-crafted files (hook.go, issue.go, user.go).

Install

Install or upgrade with this command:

go get -u github.com/rickb777/sqlgen2

Usage

sqlgen [option [arg]] file.go ...

Options:
  -type pkg.Type
    	primary type to analyse, which must be a struct type; required
  -o string
    	output file name; required
  -tags string
    	a YAML file containing tags that augment and override any attached to the fields in Go struct(s); optional
  -list string
    	names some type that is a collection of the primary type; optional; otherwise []*Type is used
  -prefix string
        prefix for names of generated types; optional, default is blank
  -kind string
    	suffix for names of generated types to indicate the intent; optional, default is "Table" but you might find nouns like "Join" or "View" are helpful
  -schema=true
    	generate sql schema and queries; default true
  -funcs=true
    	generate sql crud functions; default true
  -setters string
    	generate setter methods for fields in the primary type: none, optional (i.e. fields that are pointers), exported, all; default: none
  -gofmt
    	format and simplify the generated code nicely; default false
  -v
    	verbose progress; default false
  -z
    	debug info; default false
  -ast
    	debug AST info; default false

Tutorial

sqlgen gives you a table-focussed view of your database. It generates code that maps each struct you specify onto a database table (or view, or join result).

sqlgen is not an ORM: it does not automatically handle 'object' - relational mapping. It's simpler - but just as comprehensive.

First, let's start with a simple User struct in user.go:

type User struct {
	ID     int64
	Login  string
	Email  string
}

We can run the following command:

sqlgen -file user.go -type User -pkg demo

The tool outputs the following generated code:

func ScanUser(row *sql.Row) (*User, error) {
	var v0 int64
	var v1 string
	var v2 string

	err := row.Scan(
		&v0,
		&v1,
		&v2,
	)
	if err != nil {
		return nil, err
	}

	v := &User{}
	v.ID = v0
	v.Login = v1
	v.Email = v2

	return v, nil
}

const CreateUserStmt = `
CREATE TABLE IF NOT EXISTS users (
 id     INTEGER,
 login  TEXT,
 email  TEXT
);
`

const SelectUserStmt = `
SELECT 
 id,
 login,
 email
FROM users 
`

const SelectUserRangeStmt = `
SELECT 
 id,
 login,
 email
FROM users 
LIMIT ? OFFSET ?
`


// more functions and sql statements not displayed

This is a great start, but what if we want to specify primary keys, column sizes and more? This may be acheived by annotating your code using Go tags. For example, we can tag the ID field to indicate it is a primary key and will auto increment:

type User struct {
-   ID      int64
+   ID      int64  `sql:"pk: true, auto: true"`
    Login   string
    Email   string
}

This information allows the tool to generate smarter SQL statements:

CREATE TABLE IF NOT EXISTS users (
-user_id     INTEGER
+user_id     INTEGER PRIMARY KEY AUTOINCREMENT
,user_login  TEXT
,user_email  TEXT
);

Including SQL statements to select, insert, update and delete data using the primary key:

const SelectUserPkeyStmt = `
SELECT 
 id,
 login,
 email
WHERE user_id=?
`

const UpdateUserPkeyStmt = `
UPDATE users SET 
 id=?,
 login=?,
 email=?
WHERE user_id=?
`

const DeleteUserPkeyStmt = `
DELETE FROM users 
WHERE user_id=?
`

We can take this one step further and annotate indexes. In our example, we probably want to make sure the user_login field has a unique index:

type User struct {
    ID      int64  `sql:"pk: true, auto: true"`
-   Login   string
+   Login   string `sql:"unique: login"`
    Email   string
}

This information instructs the tool to generate the following:

const CreateUserLogin = `
CREATE UNIQUE INDEX IF NOT EXISTS user_login ON users (user_login)

The tool also assumes that we probably intend to fetch data from the database using this index. The tool will therefore automatically generate the following queries:

const SelectUserLoginStmt = `
SELECT 
 id,
 login,
 email
WHERE user_login=?
`

const UpdateUserLoginStmt = `
UPDATE users SET 
 id=?,
 login=?,
 email=?
WHERE user_login=?
`

const DeleteUserLoginStmt = `
DELETE FROM users 
WHERE user_login=?
`

Tags Summary

Tag Value Purpose
pk true or false the column is the primary key
auto true or false the column is auto-incrementing (ignored if not using MySQL)
prefixed true or false the column name is made unique using a computed prefixed
name string the column name
type string overrides the column type explicitly
size integer sets the storage size for the column
encode string encodes as "json" or "text"
index string the column has an index
unique string the column has a unique index

Nesting

Nested Go structures can be flattened into a single database table. As an example, we have a User and Address with a one-to-one relationship. In some cases, we may prefer to de-normalize our data and store in a single table, avoiding un-necessary joins.

type User struct {
    ID     int64  `sql:"pk: true"`
    Login  string
    Email  string
+   Addr   *Address
}

type Address struct {
    City   string
    State  string
    Zip    string `sql:"index: user_zip"`
}

The above relationship is flattened into a single table (see below). When the data is retrieved from the database the nested structure is restored.

CREATE TALBE IF NOT EXISTS users (
 id         INTEGER PRIMARY KEY AUTO_INCREMENT,
 login      TEXT,
 email      TEXT,
 addr_city  TEXT,
 addr_state TEXT,
 addr_zip   TEXT
);

Shared Nested Structs With Tags

If you want to nest a struct into several types that you want to process with sqlgen, you might run up against a challenge: the field tags needed in one case might be inappropriate for another case. But there's an easy way around this issue: you can supply a Yaml file that sets the tags needed in each case. The Yaml file has tags that override the tags in the Go source code.

If you prefer, you can even use this approach for all tags; this means you don't need any tags in the Go source code.

This example has two fields with tags.

type User struct {
    Uid uint64 `sql:"pk: true, auto: true"`
    Name       `sql:"name: username"`
}

The Yaml below overrides the tags. When used, the effect is that the Uid field would be neither primary key nor auto-incrementing. The Name field would have size 50 instead of the default (255), but the name: username setting would be kept.

Uid:
  pk:   false
  auto: false
Name:
  size: 50

JSON Encoding

Some types in your struct may not have native equivalents in your database such as []string. These values can be marshaled and stored as JSON in the database.

type User struct {
    ID     int64  `sql:"pk: true"`
    Login  string
    Email  string
+   Label  []string `sql:"encode: json"
}

Dialects

The generated code supports the following SQL dialects: postgres, mysql and sqlite. You decide at runtime which you need to use.

Indexes

If your columns are indexes, sqlgen includes extra code for CRUD operations based on the indexed columns as well as on the primary key. This example shows a primary key column Id, a uniquely-indexed column Login, and an ordinary indexed column Email.

type User struct {
    Id     int64  `sql:"pk: true, auto: true"`
    Login  string `sql:"unique: user_login_idx"`
    Email  string `sql:"index: email_idx"`
    ...  other fields
}

Where-expressions

Select, count amd update methods accept where-expressions as parameters. Example:

    ...  set up tbl
    wh := where.Eq("name", "Andy").And(where.Gt("age", 18))
    value, err := tbl.SelectOne(wh)
    ...

Go Generate

Example use with go:generate:

package demo

//go:generate sqlgen -type User -pkg demo -o user_sql.go user.go

type User struct {
    ID     int64  `sql:"pk: true, auto: true"`
    Login  string `sql:"unique: user_login"`
    Email  string `sql:"size: 1024"`
    Avatar string
}

The current version is not smart enough to find the whole tree of source code containing dependent types. So you need to list all the Go source files you want it to parse. This is an implementation limitation in the current version.

Benchmarks

This tool demonstrates performance gains, albeit small, over light-weight ORM packages such as sqlx and meddler. Over time I plan to expand the benchmarks to include additional ORM packages.

To run the project benchmarks:

go get ./...
go generate ./...
go build
cd bench
go test -bench=Bench

Example selecing a single row:

BenchmarkMeddlerRow-4      30000        42773 ns/op
BenchmarkSqlxRow-4         30000        41554 ns/op
BenchmarkSqlgenRow-4       50000        39664 ns/op

Selecting multiple rows:

BenchmarkMeddlerRows-4      2000      1025218 ns/op
BenchmarkSqlxRows-4         2000       807213 ns/op
BenchmarkSqlgenRows-4       2000       700673 ns/op

Restrictions

  • Compound primaries are not supported
  • In the structs used for tables, the imports must not use '.' or be renamed.

Credits

This tool was derived from sqlgen by drone.io, which was itself inspired by scaneo.

Documentation

Overview

Package sqlgen2 contains a small API plus a tool that generates SQL functions for specified struct types.

Lighter than a full-blown ORM and simpler than hand-written code, the output makes it easy to write flexible yet reliable and high-performance database code.

See the README for further details: https://github.com/rickb777/sqlgen2/blob/master/README.md

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func LogQuery 

func LogQuery(logger *log.Logger, query string, args ...interface{})

LogQuery writes query info to the logger, if it is not nil.

func Named 

func Named(name string, value interface{}) sql.NamedArg

Named creates NamedArg values; it is synonymous with sql.Named().

func NamedArgString 

func NamedArgString(arg sql.NamedArg) string

NamedArgString converts the argument to a string of the form "name=value".

Types

type CanPostGet added in v0.8.0 

type CanPostGet interface {
	PostGet() error
}

CanPostGet is implemented by value types that need a hook to run just after their data is fetched from the database.

type CanPreInsert 

type CanPreInsert interface {
	PreInsert() error
}

CanPreInsert is implemented by value types that need a hook to run just before their data is inserted into the database.

type CanPreUpdate 

type CanPreUpdate interface {
	PreUpdate() error
}

CanPreUpdate is implemented by value types that need a hook to run just before their data is updated in the database.

type Execer 

type Execer interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)

	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Execer describes the methods of the core database API. See database/sql/DB and database/sql/Tx.

type NamedArgList 

type NamedArgList []sql.NamedArg

NamedArgList holds a slice of NamedArgs

func (NamedArgList) Assignments 

func (list NamedArgList) Assignments(d schema.Dialect, from int) []string

Assignments gets the assignment expressions.

func (NamedArgList) Contains 

func (list NamedArgList) Contains(name string) bool

Contains tests whether anything in the list has a certain name.

func (NamedArgList) Exists 

func (list NamedArgList) Exists(fn func(sql.NamedArg) bool) bool

Exists verifies that one or more elements of NamedArgList return true for the passed func.

func (NamedArgList) Find 

func (list NamedArgList) Find(fn func(sql.NamedArg) bool) (sql.NamedArg, bool)

Find returns the first sql.NamedArg that returns true for some function. False is returned if none match.

func (NamedArgList) FindByName 

func (list NamedArgList) FindByName(name string) (sql.NamedArg, bool)

FindByName finds the first item with a particular name.

func (NamedArgList) MkString 

func (list NamedArgList) MkString(sep string) string

MkString produces a string ontainin all the values separated by sep.

func (NamedArgList) Names 

func (list NamedArgList) Names() []string

Names gets all the names.

func (NamedArgList) String 

func (list NamedArgList) String() string

String produces a string ontainin all the values separated by comma.

func (NamedArgList) Values 

func (list NamedArgList) Values() []interface{}

Values gets all the valules

type Table 

type Table interface {
	// Name gets the table name. without prefix
	Name() TableName

	// DB gets the wrapped database handle, provided this is not within a transaction.
	// Panics if it is in the wrong state - use IsTx() if necessary.
	DB() *sql.DB

	// Tx gets the wrapped transaction handle, provided this is within a transaction.
	// Panics if it is in the wrong state - use IsTx() if necessary.
	Tx() *sql.Tx

	// IsTx tests whether this is within a transaction.
	IsTx() bool

	// Ctx gets the current request context.
	Ctx() context.Context

	// Dialect gets the database dialect.
	Dialect() schema.Dialect

	// Logger gets the trace logger.
	Logger() *log.Logger

	// SetLogger sets the trace logger.
	SetLogger(logger *log.Logger) Table

	// Wrapper gets whatever structure is present, as needed.
	Wrapper() interface{}
}

Table provides the generic features of each generated table handler.

type TableCreator 

type TableCreator interface {
	Table

	// CreateTable creates the database table.
	CreateTable(ifNotExists bool) (int64, error)

	// DropTable drops the database table.
	DropTable(ifExists bool) (int64, error)

	// Truncate empties the table
	Truncate(force bool) (err error)
}

type TableName added in v0.8.0 

type TableName struct {
	// Prefix on the table name. It can be used as the schema name, in which case
	// it should include the trailing dot. Or it can be any prefix as needed.
	Prefix string

	// The principal name of the table.
	Name string
}

TableName holds a two-part name. The prefix part is optional.

func (TableName) PrefixWithoutDot added in v0.8.0 

func (tn TableName) PrefixWithoutDot() string

PrefixWithoutDot return the prefix; if this ends with a dot, the dot is removed.

func (TableName) String added in v0.8.0 

func (tn TableName) String() string

String gets the full table name.

type TableWithCrud 

type TableWithCrud interface {
	Table

	// Exec executes a query.
	// It returns the number of rows affected (if the DB supports that).
	Exec(query string, args ...interface{}) (int64, error)

	// CountSA counts records that match a 'where' predicate.
	CountSA(where string, args ...interface{}) (count int64, err error)

	// Count counts records that match a 'where' predicate.
	Count(where where.Expression) (count int64, err error)

	// UpdateFields writes new values to the specified columns for rows that match the 'where' predicate.
	// It returns the number of rows affected (if the DB supports that).
	UpdateFields(where where.Expression, fields ...sql.NamedArg) (int64, error)

	// Delete deletes rows that match the 'where' predicate.
	// It returns the number of rows affected (if the DB supports that).
	Delete(where where.Expression) (int64, error)
}

type TableWithIndexes 

type TableWithIndexes interface {
	TableCreator

	// CreateIndexes creates the indexes for the database table.
	CreateIndexes(ifNotExist bool) (err error)

	// DropIndexes executes a query that drops all the indexes on the database table.
	DropIndexes(ifExist bool) (err error)

	// CreateTableWithIndexes creates the database table and its indexes.
	CreateTableWithIndexes(ifNotExist bool) (err error)
}

Source Files

View all Source files

Directories

Path Synopsis
code
output
parse
parse/exit

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.