README
¶
sqlapi
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. sqlapi (this package) supports the generated code.
See the demo directory for examples. Look in the
generated files *_sql.go and the hand-crafted files (hook.go, issue.go, user.go).
Currently, support is included for MySQL, PostgreSQL and SQLite. Other dialects can be added relatively easy - send a Pull Request!
Features
package constraint
- Representations for inter-table constraints.
package require
- Predicates allowing easier detection of unexpected results from SELECTS, e.g. when the result set size is not exactly one.
package dialect
- SQL dialects for SQLite, MySQL, PostgreSQL and its pgx variant. This provides some conditional SQL generation and also
package where
- Fluent construction of WHERE and HAVING clauses: this is now [github.com/rickb777/where](https://github .com/rickb777/where). This also provides control over identifier quoting, e.g. ANSI SQL, back-ticks, etc.
Install
Install with this command:
go get github.com/rickb777/sqlapi
Please continue reading about sqlgen2.
Documentation
¶
Overview ¶
Package sqlapi contains a small API for a tool (sqlgen2) 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/sqlapi/blob/master/README.md
Index ¶
- Constants
- func ListTables(ex Execer, re *regexp.Regexp) ([]string, error)
- func Named(name string, value interface{}) sql.NamedArg
- func NamedArgString(arg sql.NamedArg) string
- type CanPostGet
- type CanPreInsert
- type CanPreUpdate
- type CoreTable
- type DBStats
- type Execer
- type Getter
- type Logger
- type NamedArgList
- func (list NamedArgList) Assignments(q quote.Quoter, from int) []string
- func (list NamedArgList) Contains(name string) bool
- func (list NamedArgList) Exists(fn func(sql.NamedArg) bool) bool
- func (list NamedArgList) Find(fn func(sql.NamedArg) bool) (sql.NamedArg, bool)
- func (list NamedArgList) FindByName(name string) (sql.NamedArg, bool)
- func (list NamedArgList) MkString(sep string) string
- func (list NamedArgList) Names() []string
- func (list NamedArgList) String() string
- func (list NamedArgList) Values() []interface{}
- type RowData
- type Rows
- type SqlDB
- func Connect(ctx context.Context, driver, dsn string, di driver.Dialect, lgr Logger, ...) (SqlDB, error)
- func ConnectEnv(ctx context.Context, lgr tracelog.Logger, logLevel tracelog.LogLevel, ...) (SqlDB, error)
- func MustConnect(ctx context.Context, driverName, dataSourceName string, di driver.Dialect, ...) SqlDB
- func MustConnectEnv(ctx context.Context, lgr tracelog.Logger, logLevel tracelog.LogLevel, ...) SqlDB
- func WrapDB(ex *sql.DB, di driver.Dialect, lgr Logger) SqlDB
- type SqlRow
- type SqlRows
- type SqlTx
- type StdLog
- type Table
- type TableCreator
- type TableName
- type TableWithIndexes
Constants ¶
const Version = "v0.62.7"
Variables ¶
This section is empty.
Functions ¶
func ListTables ¶ added in v0.50.0
ListTables gets all the table names in the database/schema. The regular expression supplies a filter: only names that match are returned. If the regular expression is nil, all table names are returned.
func NamedArgString ¶
NamedArgString converts the argument to a string of the form "name=value".
Types ¶
type CanPostGet ¶
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 {
Getter
// Exec executes a query without returning any rows.
// The arguments are for any placeholder parameters in the query.
Exec(ctx context.Context, sql string, arguments ...interface{}) (int64, error)
// Insert executes a query and returns the insertion ID.
// The primary key column, pk, is used for some dialects, notably PostgreSQL.
// The arguments are for any placeholder parameters in the query.
Insert(ctx context.Context, pk, query string, arguments ...interface{}) (int64, error)
IsTx() bool
// Logger gets the trace logger. Note that you can use this to rotate the output writer
// via its SetOutput method. Also, it can even disable it completely (via ioutil.Discard).
Logger() Logger
// Dialect gets the database dialect.
Dialect() driver.Dialect
}
Execer is a precis of *sql.DB and *sql.Tx (see database/sql).
type Getter ¶ added in v0.31.0
type Getter interface {
// Query executes a query that returns rows, typically a SELECT.
// The arguments are for any placeholder parameters in the query.
// Placeholders in the SQL are automatically replaced with numbered placeholders.
Query(ctx context.Context, sql string, arguments ...interface{}) (SqlRows, error)
// QueryRowContext executes a query that is expected to return at most one row.
// QueryRowContext always returns a non-nil value. Errors are deferred until
// Row's Scan method is called.
// If the query selects no rows, the *Row's Scan will return ErrNoRows.
// Otherwise, the *Row's Scan scans the first selected row and discards
// the rest.
//
// Placeholders in the SQL are automatically replaced with numbered placeholders.
QueryRow(ctx context.Context, query string, arguments ...interface{}) SqlRow
}
Getter provides the core methods for reading information from databases.
type Logger ¶ added in v0.26.0
type Logger interface {
tracelog.Logger
LogT(ctx context.Context, level tracelog.LogLevel, msg string, startTime *time.Time, data ...interface{})
LogQuery(ctx context.Context, query string, args ...interface{})
LogIfError(ctx context.Context, err error) error
LogError(ctx context.Context, err error) error
TraceLogging(on bool)
SetOutput(out io.Writer)
}
Logger provides the specialised logging operations within this API.
func NewStdLogger ¶ added in v0.57.0
type NamedArgList ¶
NamedArgList holds a slice of NamedArgs
func (NamedArgList) Assignments ¶
func (list NamedArgList) Assignments(q quote.Quoter, 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 ¶
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) 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 RowData ¶
type RowData struct {
Columns []string
ColumnTypes []*sql.ColumnType
Data map[string]any
}
RowData holds a single row result from the database.
type Rows ¶
type Rows struct {
Rows SqlRows
// contains filtered or unexported fields
}
Rows provides a tool for scanning result *sql.Rows of arbitrary or varying length. The internal *sql.Rows field is exported and is usable as per normal via its Next and Scan methods, or the Next and ScanToMap methods can be used instead.
func WrapRows ¶
WrapRows wraps a *sql.Rows result so that its data can be scanned into a series of maps, one for each row.
func (*Rows) Close ¶
Close closes the Rows, preventing further enumeration. If Next is called and returns false and there are no further result sets, the Rows are closed automatically and it will suffice to check the result of Err. Close is idempotent and does not affect the result of Err.
func (*Rows) ColumnTypes ¶
func (rams *Rows) ColumnTypes() ([]*sql.ColumnType, error)
ColumnTypes returns column information such as column type, length, and nullable. Some information may not be available from some drivers.
func (*Rows) Err ¶
Err returns the error, if any, that was encountered during iteration. Err may be called after an explicit or implicit Close.
func (*Rows) Next ¶
Next prepares the next result row for reading with the Scan method. It returns true on success, or false if there is no next result row or an error happened while preparing it. Err should be consulted to distinguish between the two cases.
Every call to Scan, even the first one, must be preceded by a call to Next.
type SqlDB ¶ added in v0.17.0
type SqlDB interface {
Execer
// Transact handles a transaction according to some function. If the function completes
// without error, the transaction will be committed automatically. If there is an error
// or a panic, the transaction will be rolled back automatically.
//
// The function fn should avoid using the original SqlDB; this is easily achieved by
// using a named function instead of an anonymous closure.
Transact(ctx context.Context, txOptions *pgx.TxOptions, fn func(SqlTx) error) error
// PingContext tests connectivity to the database server.
Ping(ctx context.Context) error
// Stats gets statistics from the database server.
Stats() DBStats
// SingleConn takes exclusive use of a connection for use by the supplied function.
// The connection will be automatically released after the function has terminated.
SingleConn(ctx context.Context, fn func(ex Execer) error) error
// Close closes the database connection.
Close() error
// With returns a modified SqlDB with a user-supplied item.
With(wrapped interface{}) SqlDB
// UserItem gets a user-supplied item associated with this DB.
UserItem() interface{}
}
SqlDB is able to make queries and begin transactions.
func Connect ¶ added in v0.55.0
func Connect(ctx context.Context, driver, dsn string, di driver.Dialect, lgr Logger, tries int) (SqlDB, error)
Connect opens a database connection and pings the server. If the connection fails, it is retried using an exponential backoff. the maximum number of (re-)tries can be specified; if this is zero, there is no limit.
func ConnectEnv ¶ added in v0.55.0
func ConnectEnv(ctx context.Context, lgr tracelog.Logger, logLevel tracelog.LogLevel, tries int) (SqlDB, error)
ConnectEnv connects to the database server using environment variables: DB_URL, DB_DRIVER and DB_QUOTE. Also available are DB_MAX_CONNECTIONS, DB_CONNECT_DELAY and DB_CONNECT_TIMEOUT. Use DB_QUOTE to set "ansi", "mysql" or "none" as the policy for quoting identifiers (the default is none).
func MustConnect ¶ added in v0.55.0
func MustConnect(ctx context.Context, driverName, dataSourceName string, di driver.Dialect, lgr Logger, tries int) SqlDB
MustConnect is as per Connect but with a fatal termination on error.
type SqlRow ¶ added in v0.17.0
type SqlRow interface {
Scan(dest ...interface{}) error
}
SqlRow is a precis of *sql.Row.
type SqlRows ¶ added in v0.17.0
type SqlRows interface {
SqlRow
// Next prepares the next row for reading. It returns true if there is another
// row and false if no more rows are available. It automatically closes rows
// when all rows are read.
Next() bool
Columns() ([]string, error)
ColumnTypes() ([]*sql.ColumnType, error)
// Close closes the rows, making the connection ready for use again. It is safe
// to call Close after rows is already closed.
Close() error
Err() error
}
SqlRows is a precis of *sql.Rows.
type SqlTx ¶ added in v0.17.0
type SqlTx interface {
Execer
Commit(ctx context.Context) error
Rollback(ctx context.Context) error
}
SqlTx is a precis of *sql.Tx. The context is unused in database/sql.
type Table ¶
type Table interface {
// Name gets the table name. without prefix
Name() TableName
// Execer gets the wrapped database or transaction handle.
Execer() Execer
// 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() SqlDB
// 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() SqlTx
// 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() driver.Dialect
// Logger gets the trace logger.
Logger() Logger
}
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)
}
TableCreator is a table with create/delete/truncate methods.
type TableName ¶
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 ¶
PrefixWithoutDot return the prefix; if this ends with a dot, the dot is removed.
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)
}
TableWithIndexes is a table creator with create/delete methods for the indexes.
Source Files
¶
Directories
¶
| Path | Synopsis |
|---|---|
|
Package constraint provides types and methods to support foreign-key relationshipd between database tables.
|
Package constraint provides types and methods to support foreign-key relationshipd between database tables. |
|
Package sqlapi contains a small API for a tool (sqlgen2) that generates SQL functions for specified struct types.
|
Package sqlapi contains a small API for a tool (sqlgen2) that generates SQL functions for specified struct types. |
|
constraint
Package constraint provides types and methods to support foreign-key relationshipd between database tables.
|
Package constraint provides types and methods to support foreign-key relationshipd between database tables. |
|
logadapter
Package logadapter provides a logger that writes to a log.Logger log.
|
Package logadapter provides a logger that writes to a log.Logger log. |
|
vanilla
Package vanilla provides a re-usable table API.
|
Package vanilla provides a re-usable table API. |
|
Package require provides simple constraints to assist with detecting errors in database queries that arise from the wrong number of result (for example no result or too many results).
|
Package require provides simple constraints to assist with detecting errors in database queries that arise from the wrong number of result (for example no result or too many results). |
|
Package vanilla provides a re-usable table API.
|
Package vanilla provides a re-usable table API. |