sqlstmt: github.com/jjeffery/sqlstmt Index | Examples | Files | Directories

package sqlstmt

import "github.com/jjeffery/sqlstmt"

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.

Code:

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)
    }
}

Index

Examples

Package Files

checksql.go columns.go convention.go db.go dialect.go doc.go schema.go sqlclause.go stmt.go

type Convention Uses

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 Uses

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 Uses

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 Uses

func DialectFor(name string) Dialect

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 Uses

type ExecRowStmt struct {
    // contains filtered or unexported fields
}

ExecRowStmt updates or deletes a single row. It is safe for concurrent access by multiple goroutines.

Code:

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 Uses

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.

Code:

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 Uses

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.

Code:

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

func (*ExecRowStmt) Exec Uses

func (stmt *ExecRowStmt) Exec(db DB, row interface{}) (int, error)

Exec executes the statement using the row as arguments. Returns the number of rows affected.

func (*ExecRowStmt) String Uses

func (stmt *ExecRowStmt) String() string

String prints the SQL query associated with the statement.

type GetRowStmt Uses

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.

Code:

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 Uses

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.

func (*GetRowStmt) Get Uses

func (stmt *GetRowStmt) Get(db DB, dest interface{}) (int, error)

Get a single row into dest based on the fields populated in dest.

func (*GetRowStmt) String Uses

func (stmt *GetRowStmt) String() string

String prints the SQL query associated with the statement.

type InsertRowStmt Uses

type InsertRowStmt struct {
    // contains filtered or unexported fields
}

InsertRowStmt inserts a single row. It is safe for concurrent access by multiple goroutines.

Code:

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 Uses

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 Uses

func (stmt *InsertRowStmt) Exec(db DB, row interface{}) error

Exec executes the insert statement using the row as arguments.

func (*InsertRowStmt) String Uses

func (stmt *InsertRowStmt) String() string

String prints the SQL query associated with the statement.

type SQLLogger Uses

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 Uses

type SQLLoggerFunc func(query string, args []interface{}, rowsAffected int, err error)

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.

func (SQLLoggerFunc) LogSQL Uses

func (f SQLLoggerFunc) LogSQL(query string, args []interface{}, rowsAffected int, err error)

LogSQL calls f(query, args, rowsAffected, err).

type Schema Uses

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

var Default *Schema = &Schema{}

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 Uses

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 Uses

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 Uses

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 Uses

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 Uses

func (s *Schema) NewUpdateRowStmt(row interface{}, sql string) *ExecRowStmt

NewUpdateRowStmt returns a new ExecRowStmt for updating a single row.

type SelectStmt Uses

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.

Code:

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 Uses

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 Uses

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.

func (*SelectStmt) String Uses

func (stmt *SelectStmt) String() string

String prints the SQL query associated with the statement.

Directories

PathSynopsis
privatePackage private and subdirectories have no backward compatibility guarantees.
private/colnamePackage colname is concerned with inferring database table column names from the names of the associated Go struct fields.
private/columnPackage column extracts database column information from Go struct fields.
private/dialectPackage dialect handles differences in various SQL dialects.
private/scannerPackage scanner implements a simple lexical scanner for SQL statements.

Package sqlstmt imports 12 packages (graph). Updated 2016-09-29. Refresh now. Tools for package owners. This is an inactive package (no imports and no commits in at least two years).