Documentation ¶
Overview ¶
Package sqlstmt aims to make it easy to construct and execute SQL statements for common scenarios. Supported scenarios include:
- Insert, update or delete a single row based on the contents of a Go struct
- Select a single row into a Go struct
- Select zero, one or more rows int a a slice of Go structs
This package is intended for programmers who are comfortable with writing SQL, but would like assistance with the sometimes tedious process of preparing SELECT, INSERT, UPDATE and DELETE statements for tables that have a large number of columns.
This package is designed to work seamlessly with the standard library "database/sql" package. It does not provide any layer on top of *sql.DB or *sql.Tx. If the calling program has a need to execute queries independently of this package, it can use "database/sql" directly, or make use of any other third party package that uses "database/sql".
More information about this package can be found at https://github.com/jjeffery/sqlstmt.
Example ¶
package main import ( "database/sql" "fmt" "log" "os" "github.com/jjeffery/sqlstmt" _ "github.com/mattn/go-sqlite3" ) // The UserRow struct represents a single row in the users table. // Note that the sqlstmt package becomes more useful when tables // have many more columns than shown in this example. type UserRow struct { ID int64 `sql:"primary key autoincrement"` GivenName string FamilyName string } func main() { db, err := sql.Open("sqlite3", ":memory:") exitIfError(err) setupSchema(db) insertRow := sqlstmt.NewInsertRowStmt(UserRow{}, `users`) updateRow := sqlstmt.NewUpdateRowStmt(UserRow{}, `users`) deleteRow := sqlstmt.NewDeleteRowStmt(UserRow{}, `users`) getRow := sqlstmt.NewGetRowStmt(UserRow{}, `users`) selectAllRows := sqlstmt.NewSelectStmt(UserRow{}, ` select {} from users order by id limit ? offset ? -- example of placeholders `) tx, err := db.Begin() exitIfError(err) defer tx.Rollback() // insert three rows, IDs are automatically generated (1, 2, 3) for _, givenName := range []string{"John", "Jane", "Joan"} { u := &UserRow{ GivenName: givenName, FamilyName: "Citizen", } err = insertRow.Exec(tx, u) exitIfError(err) } // get user with ID of 3 and then delete it { u := &UserRow{ID: 3} _, err = getRow.Get(tx, u) exitIfError(err) _, err = deleteRow.Exec(tx, u) exitIfError(err) } // update family name for user with ID of 2 { u := &UserRow{ID: 2} _, err = getRow.Get(tx, u) exitIfError(err) u.FamilyName = "Doe" _, err = updateRow.Exec(tx, u) exitIfError(err) } // select rows from table and print { var users []*UserRow err = selectAllRows.Select(tx, &users, 100, 0) exitIfError(err) for _, u := range users { fmt.Printf("User %d: %s, %s\n", u.ID, u.FamilyName, u.GivenName) } } } func exitIfError(err error) { if err != nil { log.Output(2, err.Error()) os.Exit(1) } } var debug = true func init() { log.SetFlags(0) // logs SQL statements if debug { sqlstmt.Default.Logger = sqlstmt.SQLLoggerFunc(logSQL) } } func setupSchema(db *sql.DB) { _, err := db.Exec(` create table users( id integer primary key autoincrement, given_name text, family_name text ) `) exitIfError(err) } func logSQL(query string, args []interface{}, rowsAffected int, err error) { if err != nil { log.Printf("query=%q, args=%v, error=%v", query, args, err) } else { log.Printf("query=%q, args=%v, rowsAffected=%d", query, args, rowsAffected) } }
Output: User 1: Citizen, John User 2: Doe, Jane
Index ¶
- type Convention
- type DB
- type Dialect
- type ExecRowStmt
- type GetRowStmt
- type InsertRowStmt
- type SQLLogger
- type SQLLoggerFunc
- type Schema
- func (s *Schema) NewDeleteRowStmt(row interface{}, sql string) *ExecRowStmt
- func (s *Schema) NewGetRowStmt(row interface{}, sql string) *GetRowStmt
- func (s *Schema) NewInsertRowStmt(row interface{}, sql string) *InsertRowStmt
- func (s *Schema) NewSelectStmt(row interface{}, sql string) *SelectStmt
- func (s *Schema) NewUpdateRowStmt(row interface{}, sql string) *ExecRowStmt
- type SelectStmt
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type Convention ¶
type Convention interface { // The name of the convention. This name is used as // a key for caching, so if If two conventions have // the same name, then they should be identical. Name() string // ColumnName returns the name of a database column based // on the name of a Go struct field. ColumnName(fieldName string) string // Join joins a prefix with a name to form a column name. // Used for naming columns based on fields within embedded // structures. The column name will be based on the name of // the Go struct field and its enclosing embedded struct fields. Join(prefix, name string) string }
Convention provides naming convention methods for inferring database column names from Go struct field names.
var ConventionSame Convention
ConventionSame is a naming convention where the column name is identical to the Go struct field name.
var ConventionSnake Convention
ConventionSnake is the default, 'snake_case' naming convention
type DB ¶
type DB interface { // Exec executes a query without returning any rows. // The args are for any placeholder parameters in the query. Exec(query string, args ...interface{}) (sql.Result, error) // Query executes a query that returns rows, typically a SELECT. // The args are for any placeholder parameters in the query. Query(query string, args ...interface{}) (*sql.Rows, error) }
DB is the interface that wraps the database access methods used by this package.
The *DB and *Tx types in the standard library package "database/sql" both implement this interface.
type Dialect ¶
type Dialect interface { // Name of the dialect. This name is used as // a key for caching, so if If two dialects have // the same name, then they should be identical. Name() string // Quote a table name or column name so that it does // not clash with any reserved words. The SQL-99 standard // specifies double quotes (eg "table_name"), but many // dialects, including MySQL use the backtick (eg `table_name`). // SQL server uses square brackets (eg [table_name]). Quote(name string) string // Return the placeholder for binding a variable value. // Most SQL dialects support a single question mark (?), but // PostgreSQL uses numbered placeholders (eg $1). Placeholder(n int) string }
Dialect is an interface used to handle differences in SQL dialects.
func DialectFor ¶
DialectFor returns the dialect for the specified database driver. If name is blank, then the dialect returned is for the first driver returned by sql.Drivers(). If only one SQL driver has been loaded by the calling program then this will return the correct dialect. If the driver name is unknown, the default dialect is returned.
Supported dialects include:
name alternative names ---- ----------------- mssql mysql postgres pq, postgresql sqlite3 sqlite ql ql-mem
type ExecRowStmt ¶
type ExecRowStmt struct {
// contains filtered or unexported fields
}
ExecRowStmt updates or deletes a single row. It is safe for concurrent access by multiple goroutines.
Example ¶
package main import ( "database/sql" "fmt" "log" "github.com/jjeffery/sqlstmt" ) func openTestDB() *sql.DB { db, err := sql.Open("sqlite3", ":memory:") if err != nil { log.Fatal(err) } _, err = db.Exec(` create table users( id integer primary key autoincrement, given_name text, family_name text ) `) if err != nil { log.Fatal(err) } _, err = db.Exec(` insert into users(given_name, family_name) values('John', 'Citizen') `) if err != nil { log.Fatal(err) } return db } func main() { type User struct { ID int64 `sql:"primary key auto increment"` GivenName string FamilyName string } updateStmt := sqlstmt.NewUpdateRowStmt(User{}, `users`) deleteStmt := sqlstmt.NewDeleteRowStmt(User{}, `users`) fmt.Println(updateStmt.String()) fmt.Println(deleteStmt.String()) var db *sql.DB = openTestDB() // Get user with specified primary key u := &User{ID: 1} _, err := sqlstmt.NewGetRowStmt(User{}, `users`).Get(db, u) if err != nil { log.Fatal(err) } // Update row u.GivenName = "Donald" n, err := updateStmt.Exec(db, u) if err != nil { log.Fatal(err) } fmt.Println("number of rows updated:", n) // Delete row n, err = deleteStmt.Exec(db, u) if err != nil { log.Fatal(err) } fmt.Println("number of rows deleted:", n) }
Output: update users set `given_name`=?,`family_name`=? where `id`=? delete from users where `id`=? number of rows updated: 1 number of rows deleted: 1
func NewDeleteRowStmt ¶
func NewDeleteRowStmt(row interface{}, sql string) *ExecRowStmt
NewDeleteRowStmt returns a new ExecRowStmt for deleting a single row. It is safe for concurrent access by multiple goroutines. The dialect and naming conventions are obtained from DefaultSchema.
Example ¶
package main import ( "database/sql" "fmt" "log" "github.com/jjeffery/sqlstmt" ) func openTestDB() *sql.DB { db, err := sql.Open("sqlite3", ":memory:") if err != nil { log.Fatal(err) } _, err = db.Exec(` create table users( id integer primary key autoincrement, given_name text, family_name text ) `) if err != nil { log.Fatal(err) } _, err = db.Exec(` insert into users(given_name, family_name) values('John', 'Citizen') `) if err != nil { log.Fatal(err) } return db } func main() { type User struct { ID int64 `sql:"primary key auto increment"` GivenName string FamilyName string } stmt := sqlstmt.NewDeleteRowStmt(User{}, `users`) fmt.Println(stmt.String()) // creates a row with ID=1 var db *sql.DB = openTestDB() // Delete user with specified primary key u := &User{ID: 1} n, err := stmt.Exec(db, u) if err != nil { log.Fatal(err) } fmt.Println("number of rows deleted:", n) }
Output: delete from users where `id`=? number of rows deleted: 1
func NewUpdateRowStmt ¶
func NewUpdateRowStmt(row interface{}, sql string) *ExecRowStmt
NewUpdateRowStmt returns a new ExecRowStmt for updating a single row. It is safe for concurrent access by multiple goroutines. The dialect and naming conventions are obtained from DefaultSchema.
Example ¶
package main import ( "database/sql" "fmt" "log" "github.com/jjeffery/sqlstmt" ) func openTestDB() *sql.DB { db, err := sql.Open("sqlite3", ":memory:") if err != nil { log.Fatal(err) } _, err = db.Exec(` create table users( id integer primary key autoincrement, given_name text, family_name text ) `) if err != nil { log.Fatal(err) } _, err = db.Exec(` insert into users(given_name, family_name) values('John', 'Citizen') `) if err != nil { log.Fatal(err) } return db } func main() { type User struct { ID int64 `sql:"primary key auto increment"` GivenName string FamilyName string } updateStmt := sqlstmt.NewUpdateRowStmt(User{}, `users`) fmt.Println(updateStmt.String()) var db *sql.DB = openTestDB() // Get user with specified primary key u := &User{ID: 1} _, err := sqlstmt.NewGetRowStmt(User{}, `users`).Get(db, u) if err != nil { log.Fatal(err) } // Update row u.GivenName = "Donald" n, err := updateStmt.Exec(db, u) if err != nil { log.Fatal(err) } fmt.Println("number of rows updated:", n) }
Output: update users set `given_name`=?,`family_name`=? where `id`=? number of rows updated: 1
type GetRowStmt ¶
type GetRowStmt struct {
// contains filtered or unexported fields
}
GetRowStmt executes a query that returns a single row. It is safe for concurrent access by multiple goroutines.
Example ¶
package main import ( "database/sql" "fmt" "log" "github.com/jjeffery/sqlstmt" ) func openTestDB() *sql.DB { db, err := sql.Open("sqlite3", ":memory:") if err != nil { log.Fatal(err) } _, err = db.Exec(` create table users( id integer primary key autoincrement, given_name text, family_name text ) `) if err != nil { log.Fatal(err) } _, err = db.Exec(` insert into users(given_name, family_name) values('John', 'Citizen') `) if err != nil { log.Fatal(err) } return db } func main() { type User struct { ID int64 `sql:"primary key auto increment"` GivenName string FamilyName string } stmt := sqlstmt.NewGetRowStmt(User{}, `users`) fmt.Println(stmt.String()) var db *sql.DB = openTestDB() // Get user with specified primary key u := &User{ID: 1} _, err := stmt.Get(db, u) if err != nil { log.Fatal(err) } fmt.Printf("ID=%d, GivenName=%q, FamilyName=%q\n", u.ID, u.GivenName, u.FamilyName) }
Output: select `id`,`given_name`,`family_name` from users where `id`=? ID=1, GivenName="John", FamilyName="Citizen"
func NewGetRowStmt ¶
func NewGetRowStmt(row interface{}, sql string) *GetRowStmt
NewGetRowStmt returns a new GetRowStmt for the given row and SQL. It is safe for concurrent access by multiple goroutines. The dialect and naming conventions are obtained from DefaultSchema.
type InsertRowStmt ¶
type InsertRowStmt struct {
// contains filtered or unexported fields
}
InsertRowStmt inserts a single row. It is safe for concurrent access by multiple goroutines.
Example ¶
package main import ( "database/sql" "fmt" "log" "github.com/jjeffery/sqlstmt" ) func openTestDB() *sql.DB { db, err := sql.Open("sqlite3", ":memory:") if err != nil { log.Fatal(err) } _, err = db.Exec(` create table users( id integer primary key autoincrement, given_name text, family_name text ) `) if err != nil { log.Fatal(err) } _, err = db.Exec(` insert into users(given_name, family_name) values('John', 'Citizen') `) if err != nil { log.Fatal(err) } return db } func main() { type User struct { ID int64 `sql:"primary key auto increment"` GivenName string FamilyName string } stmt := sqlstmt.NewInsertRowStmt(User{}, `users`) fmt.Println(stmt.String()) var db *sql.DB = openTestDB() // Get user with specified primary key u := &User{GivenName: "Jane", FamilyName: "Doe"} err := stmt.Exec(db, u) if err != nil { log.Fatal(err) } fmt.Printf("Inserted row: ID=%d\n", u.ID) }
Output: insert into users(`given_name`,`family_name`) values(?,?) Inserted row: ID=2
func NewInsertRowStmt ¶
func NewInsertRowStmt(row interface{}, sql string) *InsertRowStmt
NewInsertRowStmt returns a new InsertRowStmt for the given row and SQL. It is safe for concurrent access by multiple goroutines. The dialect and naming conventions are inferred from DefaultSchema.
func (*InsertRowStmt) Exec ¶
func (stmt *InsertRowStmt) Exec(db DB, row interface{}) error
Exec executes the insert statement using the row as arguments.
type SQLLogger ¶
type SQLLogger interface { // LogSQL is called by the sqlstmt package after it executes // an SQL query or statement. // // The query and args variables provide the query and associated // arguments supplied to the database server. The rowsAffected // and err variables provide a summary of the query results. // If the number of rows affected cannot be determined for any reason, // then rowsAffected is set to -1. LogSQL(query string, args []interface{}, rowsAffected int, err error) }
SQLLogger is an interface for logging SQL statements executed by the sqlstmt package.
type SQLLoggerFunc ¶
The SQLLoggerFunc type is an adapter to allow the use of ordinary functions as SQLLoggers. If f is a function with the appropriate signature, SQLLoggerFunc(f) is an SQLLogger that calls f.
type Schema ¶
type Schema struct { // Dialect used for constructing SQL queries. If nil, the dialect // is inferred from the list of SQL drivers loaded in the program. Dialect Dialect // Convention contains methods for inferring the name // of database columns from the associated Go struct field names. Convention Convention // Logger is used for diagnostic logging. If set then all statements // created for this schema will share this logger. Logger SQLLogger }
Schema contains configuration information that is common to statements prepared for the same database schema.
If a program works with a single database driver (eg "mysql"), and columns conform to a standard naming convention, then that progam can use the default schema (DefaultSchema) and there is no need to use the Schema type directly.
Programs that operate with a number of different database drivers and naming conventions should create a schema for each combination of driver and naming convention, and use the appropriate schema to prepare each statements
Default is the default schema, which can be modified as required.
The default schema has sensible defaults. If not explicitly specified, the dialect is determined by the SQL database drivers loaded. If the program only uses one database driver, then the default schema will use the correct dialect.
The default naming convention uses "snake case". So a struct field named "GivenName" will have an associated column name of "given_name".
func (*Schema) NewDeleteRowStmt ¶
func (s *Schema) NewDeleteRowStmt(row interface{}, sql string) *ExecRowStmt
NewDeleteRowStmt returns a new ExecRowStmt for deleting a single row. It is safe for concurrent access by multiple goroutines.
func (*Schema) NewGetRowStmt ¶
func (s *Schema) NewGetRowStmt(row interface{}, sql string) *GetRowStmt
NewGetRowStmt executes a query that returns a single row. It is safe for concurrent access by multiple goroutines.
func (*Schema) NewInsertRowStmt ¶
func (s *Schema) NewInsertRowStmt(row interface{}, sql string) *InsertRowStmt
NewInsertRowStmt returns a new InsertRowStmt for the given row and SQL. It is safe for concurrent access by multiple goroutines.
func (*Schema) NewSelectStmt ¶
func (s *Schema) NewSelectStmt(row interface{}, sql string) *SelectStmt
NewSelectStmt executes a query that returns multiple rows. It is safe for concurrent access by multiple goroutines.
func (*Schema) NewUpdateRowStmt ¶
func (s *Schema) NewUpdateRowStmt(row interface{}, sql string) *ExecRowStmt
NewUpdateRowStmt returns a new ExecRowStmt for updating a single row.
type SelectStmt ¶
type SelectStmt struct {
// contains filtered or unexported fields
}
SelectStmt executes a query that returns multiple rows. It is safe for concurrent access by multiple goroutines.
Example ¶
package main import ( "fmt" "github.com/jjeffery/sqlstmt" ) func main() { type User struct { ID int64 `sql:"primary key auto increment"` Login string HashPwd string Name string } stmt := sqlstmt.NewSelectStmt(User{}, ` select distinct {alias u} from users u inner join user_search_terms t on t.user_id = u.id where t.search_term like ? `) fmt.Println(stmt.String()) }
Output: select distinct u.`id`,u.`login`,u.`hash_pwd`,u.`name` from users u inner join user_search_terms t on t.user_id = u.id where t.search_term like ?
func NewSelectStmt ¶
func NewSelectStmt(row interface{}, sql string) *SelectStmt
NewSelectStmt returns a new SelectStmt for the given row and SQL. The dialect and naming conventions are obtained from DefaultSchema.
func (*SelectStmt) Select ¶
func (stmt *SelectStmt) Select(db DB, dest interface{}, args ...interface{}) error
Select executes the statement's query and returns the resulting rows in the slice pointed to by dest. The args are for any placeholder parameters in the query.
Source Files ¶
Directories ¶
Path | Synopsis |
---|---|
Package private and subdirectories have no backward compatibility guarantees.
|
Package private and subdirectories have no backward compatibility guarantees. |
colname
Package colname is concerned with inferring database table column names from the names of the associated Go struct fields.
|
Package colname is concerned with inferring database table column names from the names of the associated Go struct fields. |
column
Package column extracts database column information from Go struct fields.
|
Package column extracts database column information from Go struct fields. |
dialect
Package dialect handles differences in various SQL dialects.
|
Package dialect handles differences in various SQL dialects. |
scanner
Package scanner implements a simple lexical scanner for SQL statements.
|
Package scanner implements a simple lexical scanner for SQL statements. |