sqlstmt

package module
Version: v1.0.1 Latest Latest
Warning

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

Go to latest
Published: Sep 14, 2016 License: MIT Imports: 12 Imported by: 0

README

sqlstmt: SQL statements

Package sqlstmt provides assistance in creating SQL statements.

GoDoc License Build Status (Linux) Coverage Status GoReportCard

NOTE: This package is an earlier version of the sqlrow package. It will be deleted at some point.

Overview

Package sqlstmt aims to make it easy to construct and execute SQL statements for common scenarios. Supported scenarios include:

  • Insert, update and 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 into 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. It is designed to work seamlessly with the standard library database/sql package in that 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.

The philosophy behind the design if the sqlstmt API is:

  • Simple, single-row CRUD operations should be easy to construct
  • Slightly more complex operations should be possible with only a little more effort
  • Support popular SQL dialects out of the box; provide for further customization through simple interfaces
  • Easily fallback to using database/sql and other third-party packages for any functionality that is not handled by this package

Obtaining the package

go get github.com/jjeffery/sqlstmt

Note that if you are interested in running the unit tests, you will need package github.com/mattn/sqlite3, which requires cgo and a C compiler setup to compile correctly.

Examples

Note that there are more examples in the GoDoc documentation.

The following examples use a fairly simple database schema. Note that this package becomes much more useful for database schemas where tables have many columns (and hence the row structs have many fields).

create table users(
	id integer primary key autoincrement,
	given_name text
	family_name text
	email_address text
)

A corresponding Go struct for representing a row in the users table is:

type User struct {
	ID           int `sql:primary key autoincrement`
	GivenName    string
	FamilyName   string
	EmailAddress string
}

Note the use of struct tags to include information about the primary key and auto-increment behaviour.

The following examples assume that a database has been opened and the *sql.DB is stored in variable db:

db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
	log.Fatal(err)
}

This example code is using SQLite, but the sqlstmt package supports any SQL dialect via a very simple Dialect interface, and the following SQL dialects are supported out of the box:

  • SQLite
  • PostgreSQL
  • MySQL
  • MS SQL
Inserting a row

// create the statement -- this only needs to be done once, at
// program initialization time
insertRow := sqlstmt.NewInsertRowStmt(User{}, "users")

// create the row object and populate with data
u := &User{
	GivenName:    "Jane",
	FamilyName:   "Citizen",
	EmailAddress: "jane@citizen.com",
}

// execute the insert statement using a db connection opened earlier
err := insertRow.Exec(db, u)

if err != nil {
	log.Fatal(err)
}

fmt.Println("User ID:", u.ID)

// Output: User ID: 1

Because the id column is an auto-increment column, the value of u.ID will contain the auto-generated value after the insert row statement has been executed.

Note, however, that the Postgres driver github.com/lib/pq does not support the Result.LastInsertId method, and so this feature does not work for that driver. See the pq package GoDoc for a work-around.

Updating a row

Continuing from the previous example:

// create the statement -- this only needs to be done once, at
// program initialization time
updateRow := sqlstmt.NewUpdateRowStmt(User{}, "users")

// change user details
u.EmailAddress = "jane.citizen.314159@gmail.com"

// execute the update statement
n, err = updateRow.Exec(db, u)

if err != nil {
	log.Fatal(err)
}

fmt.Println("Number of rows updated:", n)

// Output: Number of rows updated: 1
Deleting a row

Continuing from the previous example:

// create the statement -- this only needs to be done once, at
// program initialization time
deleteRow := sqlstmt.NewDeleteRowStmt(User{}, "users")

// execute the delete statement
n, err = deleteRow.Exec(db, u)

if err != nil {
	log.Fatal(err)
}

fmt.Println("Number of rows deleted:", n)

// Output: Number of rows deleted: 1
Getting a row by primary key

Pretending that we have not deleted the row in the previous example:

getRow := sqlstmt.NewGetRowStmt(User{}, "users")

// create a row variable and populate with the primary key of the row
// that we are after
u := &User{ID: 1}

n, err := getRow.Get(db, u)

if err != nil {
	log.Fatal(err)
}

fmt.Println("Rows returned:", n)
fmt.Println("User email:", u.EmailAddress)

// Output:
// Rows returned: 1
// User email: jane.citizen.314159@gmail.com
Performing queries

Performing a query that returns zero, one or more rows involves writing some SQL. The sqlstmt package provides an extended syntax that provides a shorthand alternative to explicitly listing all columns and parameter placeholders.

familyNameQuery := sqlstmt.NewSelectStmt(User{}, `
	select {}
	from users
	where family_name = ?
`)

// declare a slice of users for receiving the result of the query
var users []User

// perform the query, specifying an argument for each of the
// placeholders in the SQL query
err = familyNameQuery.Select(db, &users, "Citizen")
if err != nil {
	log.Fatal(err)
}

// at this point, the users slice will contain one object for each
// row returned by the SQL query
for _, u := range users {
	doSomethingWith(u)
}

Note the non-standard {} in the SQL query above. The sqlstmt statement knows to substitute in column names in the appropriate format. In the example above, the SQL generated will look like the following:

select `id`,`family_name`,`given_name`,`email_address`
from users
where family_name = ?

For queries that involve multiple tables, it is always a good idea to use table aliases:

searchTermQuery := sqlstmt.NewSelectStmt(User{}, `
	select {alias u}
	from users u
	inner join user_search_terms t
	  on t.user_id = u.id
	where u.term like ?
`)

// declare a slice of users for receiving the result of the query
var users []User

// perform the query, specifying an argument for each of the
// placeholders in the SQL query
err = searchTermQuery.Select(db, &users, "Cit%")
if err != nil {
	log.Fatal(err)
}

for _, u := range users {
	doSomethingWith(u)
}

The SQL generated in this example looks like the following:

select u.`id`,u.`family_name`,u.`given_name`,u.`email_address`
from users u
inner join user_search_terms t
  on t.user_id = u.id
where u.term like ?

SQL dialects

The sqlstmt package is designed to be as SQL-agnostic as possible, but when it is generating SQL it does need to know the following:

  • How to quote column names to ensure they are not interpreted as an SQL keyword
    • PostgreSQL uses double quotes: "column_name"
    • MySQL uses back ticks: `column_name`
    • MS SQL Server uses square braces: [column_name]
  • How to write placeholders for arguments
    • PostgreSQL uses numbered placeholders: $1, $2, etc
    • Almost everyone else uses question marks: ?
The default dialect

Most programs use only one SQL driver, and in these circumstances sqlstmt will do the right thing.

For example, if a program is using Postgres, it will need to load the appropriate driver, probably in the main package:

import _ "github.com/lib/pq"

By default sqlstmt will check the list of loaded SQL drivers and pick the first one to decide on the SQL dialect to use. If only one SQL driver has been loaded, it will choose correctly. In this example it will automatically choose the "postgres" dialect.

Specifying the SQL dialect

If your program references multiple SQL drivers, it may be necesary to specify which dialect is in use. This can be done when opening the database connection:

// open the database
db, err := sql.Open("postgres", "user=test dbname=test sslmode=disable")
if err != nil {
	log.Fatal(err)
}

// specify the dialect in use
sqlstmt.Default.Dialect = sqlstmt.NewDialect("postgres")
Using multiple dialects

If your program makes use of multiple database connections with different types of server, the best thing to do is to specify a sqlstmt.Schema for each of the databases.

var postgresSchema = &sqlstmt.Schema{
	Dialect: sqlstmt.NewDialect("postgres"),
}

var mysqlSchema = &sqlstmt.Schema{
	Dialect: sqlstmt.NewDialect("mysql"),
}

When the time comes to create statements, use the appropriate schema:

// insert statement for widgets in postgres database
var insertWidget = postgresSchema.NewInsertRowStmt(Widget{}, "widgets")

// update statement for gadgets in mysql database
var updateGadget = mysqlSchema.NewUpdateRowStmt(Gadget{}, "gadgets")

Column mapping

For each statement, the sqlstmt package generates column names based on a Go struct.

Simple structs
type User struct {
	ID        int64 `sql:"primary key"`
	Name      string
	UpdatedAt time.Time
	CreatedAt time.Time
}

// Column names generated by sqlstmt:
// * id
// * name
// * updated_at
// * created_at

Note the use of the struct tag to specify the primary key. The struct tag can also be used to override the column name:

type User struct {
	ID        int64     `sql:"user_id primary key"`
	Name      string
	UpdatedAt time.Time
	CreatedAt time.Time
	DOB       time.Time `sql:"date_of_birth"`
}

// Column names generated by sqlstmt:
// * user_id
// * name
// * updated_at
// * created_at
// * date_of_birth

If you need to override the column name to be an SQL keyword, (which is rarely a good idea), you can use quotes to specify the column name.

// Not recommended
type User struct {
	ID int64 `sql:"'primary' primary key"` // setting column name to SQL keyword
	// ... rest of struct here
}
Anonymous structs

Sometimes there are a set of common columns, used by each table. Anonymous structs are a convenient way to ensure consistency across the Go structs:

type Entity struct {
	ID        int64 `sql:"primary key autoincrement"`
	UpdatedAt time.Time
	CreatedAt time.Time
}

type User struct {
	Entity
	Name  string
	Email string
}

// Column names generated by sqlstmt:
// * id
// * updated_at
// * created_at
// * name
// * email

type Vehicle struct {
	Entity
	Make string
	Model string
}

// Column names generated by sqlstmt:
// * id
// * updated_at
// * created_at
// * make
// * model

Embedded structs

In some cases it is useful to use embedded structures when representing components in a structure.


type Address struct {
	Street   string
	Locality string
	City     string
	Postcode string
	Country  string
}

type CustomerContact struct {
	CustomerID    int64 `sql:"primary key"`
	HomeAddress   Address
	PostalAddress Address
}

// Column names generated by sqlstmt:
// * id
// * home_address_street
// * home_address_locality
// * home_address_city
// * home_address_postcode
// * home_address_country
// * postal_address_street
// * postal_address_locality
// * postal_address_city
// * postal_address_postcode
// * postal_address_country

Column naming conventions

The sqlstmt package has a default naming convention which will convert a Go field name like HomeAddress into it's "snake case" equivalent: home_address. This is a popular common naming convention and is supported by default by Active Record and other popular ORM frameworks.

If this naming convention does not suit, you can override by providing an implementation of the Convention interface:

// Convention provides naming convention methods for
// inferring a database column name from Go struct field names.
type Convention interface {
	// The name of the convention. This can be 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
}

The ColumnName method accepts a field name (eg "HomeAddress") and returns the associated column name.

The Join method is used for embedded structures. It joins a prefix (for example "home_address") with a name (eg "street") to produce a joined name (eg "home_address_street").

The sqlstmt package comes with two naming conventions out of the box:

  • ConventionSnake: the default, "snake_case" convention; and
  • ConventionSame: a convention where the column name is identical to the Go field name.

To set a convention other than the default, set the Schema.Convention property:

// set the default naming convention so that column names are
// the same as Go struct field names
sqlstmt.Default.Convention = sqlstmt.ConventionSame

// create a new schema with it's own naming convention
mySchema := &sqlstmt.Schema{
	Convention: newMyCustomNamingConvention(),
}

// This will use the default convention (which is now sqlstmt.ConventionSame)
stmt1 := sqlstmt.NewInsertRowStmt(Widget{}, "widgets")

// This will use the custom convention associated with mySchema
stmt2 := mySchema.NewUpdateRowStmt(Gadget{}, "gadgets")

Contributing

Pull requests are welcome. Please include tests providing full test coverage of your changes.

If you are raising an issue that describes a bug, please include a minimal example that reproduces the bug.

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

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

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

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

func (*ExecRowStmt) Exec

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

func (stmt *ExecRowStmt) String() string

String prints the SQL query associated with the statement.

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.

func (*GetRowStmt) Get

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

func (stmt *GetRowStmt) String() string

String prints the SQL query associated with the statement.

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.

func (*InsertRowStmt) String

func (stmt *InsertRowStmt) String() string

String prints the SQL query associated with the statement.

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

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

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

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

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

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

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.

func (*SelectStmt) String

func (stmt *SelectStmt) String() string

String prints the SQL query associated with the statement.

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.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL