sqlrow

README

sqlrow: SQL statements

Package sqlrow provides assistance in creating SQL statements.

NOTE: This package is no longer under development, and is retained here for backwards compatibility. The API underwent a series of breaking changes, and the package has been renamed to sqlr, and the repository has moved to https://github.com/jjeffery/sqlr.

• Overview
• Obtaining the package
• Examples
  • Inserting a row
  • Updating a row
  • Deleting a row
  • Getting a row by primary key
  • Performing queries
• SQL dialects
  • The default dialect
  • Specifying the SQL dialect
  • Using multiple dialects
• Column mapping
  • Simple structs
  • Anonymous structs
  • Embedded structs
• Column naming conventions
• Contributing

Overview

Package sqlrow 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 of the sqlrow 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/sqlrow

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 sqlrow 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 row object and populate with data
u := &User{
	GivenName:    "Jane",
	FamilyName:   "Citizen",
	EmailAddress: "jane@citizen.com",
}

// insert the row into the `users` table using the db connection opened earlier
err := sqlrow.Insert(db, u, "users")

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 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:

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

// update the row in the `users` table
n, err = sqlrow.Update(db, u, "users")

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:

// execute the row in the `users` table
n, err = sqlrow.Delete(db, u, "users")

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:

var user User 

n, err := sqlrow.Select(db, &user, "users", 1)

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 sqlrow package provides an extended syntax that provides a shorthand alternative to explicitly listing all columns and parameter placeholders.

// 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 = sqlrow.Select(db, &users, `
        select {}
		from users
		where family_name = ?`, "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 sqlrow 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:

// 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 = sqlrow.Select(db, &users, `
      	select {alias u}
		from users u
	   	inner join user_search_terms t
			on t.user_id = u.id
		where u.term like ?`, `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 sqlrow 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 sqlrow 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 sqlrow 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
sqlrow.Default.Dialect = sqlrow.DialectFor("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 sqlrow.Schema for each of the databases.

var postgresDB = &sqlrow.Schema{
	Dialect: sqlrow.NewDialect("postgres"),
}

var mysqlDB = &sqlrow.Schema{
	Dialect: sqlrow.NewDialect("mysql"),
}

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

// insert widgets in postgres database
err = postgresDB.Insert(db1, &widget, "widgets")

// update statement for gadgets in mysql database
_, err = mysqlDB.Update(db2, &gadget, "gadgets")

Column mapping

For each statement, the sqlrow 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 sqlrow:
// * 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 sqlrow:
// * 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 sqlrow:
// * id
// * updated_at
// * created_at
// * name
// * email

type Vehicle struct {
	Entity
	Make string
	Model string
}

// Column names generated by sqlrow:
// * 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 sqlrow:
// * 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 sqlrow 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 sqlrow 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
sqlrow.Default.Convention = sqlrow.ConventionSame

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

// This will use the default convention (which is now sqlrow.ConventionSame)
err := sqlrow.Insert(db1, widget, "widgets")

// This will use the custom convention associated with mySchema
_, err = mySchema.Update(db2, gadget, "gadgets")

Contributing

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

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

Documentation

Overview

Package sqlrow makes it easy to construct and execute SQL queries for common, row-based scenarios. Supported scenarios include:

(a) Insert, update or delete a single row based on the contents of a Go struct;
(b) Select a single row into a Go struct; and
(c) 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.

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".

SQL INSERT

Package sqlrow uses reflection on the supplied row to provide assistance with creating SQL. This assistance is particularly useful for tables with many columns, but the following examples use this simple structure:

 type UserRow struct {
	ID         int64 `sql:"primary key autoincrement"`
	GivenName  string
	FamilyName string
 }

For a row of type UserRow the following INSERT query:

sqlrow.Insert(db, row, `insert into users({}) values({})`)

will be translated into the following, depending on the SQL dialect:

insert into users(`given_name`,`family_name`) values(?,?)   -- MySQL, SQLite
insert into users("given_name","family_name") values($1,$2) -- PostgreSQL
insert into users([given_name],[family_name]) values(?,?)   -- MSSQL

In the above example note that the "id" column is not inserted. This is because it is defined as an auto-increment column. If it were not an auto-increment column it would be included in the column list.

This pattern is so common for inserting individual rows that, for convenience, providing just the table name has the same result:

sqlrow.Insert(db, row, `users`)

SQL UPDATE

The following UPDATE query:

sqlrow.Update(db, row, `update users set {} where {}`)

will be translated into the following:

update users set `given_name`=?,`family_name`=? where `id`=?    -- MySQL, SQLite
update users set "given_name"=$1,"family_name"=$2 where "id"=$3 -- PostgreSQL
update users set [given_name]=?,[family_name]=? where [id]=?    -- MSSQL

This pattern is so common for inserting individual rows that, for convenience, providing just the table name has the same result:

sqlrow.Update(db, row, `users`)

It is possible to construct more complex UPDATE statements. The following example can be useful for rows that make use of optimistic locking:

sqlrow.Update(db, row, `update users set {} where {} and version = ?', oldVersion)

SQL DELETE

DELETE queries are similar to UPDATE queries:

sqlrow.Delete(db, row, `delete from users where {}`)

and

sqlrow.Delete(db, row, `users`)

are both translated as (for MySQL, SQLite):

delete from users where `id`=?

SQL SELECT

SQL SELECT queries can be constructed easily

var rows []UserRow
sql.Select(db, &rows, `select {} from users where given_name=?`, "Smith")

is translated as (for MySQL, SQLite):

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

More complex queries involving joins and table aliases are possible:

sql.Select(db, &rows, `
  select {alias u}
  from users u
  inner join user_search_terms t on t.user_id = u.id
  where t.search_term like ?`, "Jon%")

is translated as (for MySQL, SQLite):

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

Performance and Caching

Package sqlrow makes use of reflection in order to build the SQL that is sent to the database server, and this imposes a performance penalty. In order to reduce this overhead the package caches queries generated. The end result is that the performance of this package is close to the performance of code that uses hand-constructed SQL queries to call package "database/sql" directly.

Source Code

More information about this package can be found at https://github.com/jjeffery/sqlrow.

Example

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"

	"github.com/jjeffery/sqlrow"
	_ "github.com/mattn/go-sqlite3"
)

// The UserRow struct represents a single row in the users table.
// Note that the sqlrow 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)

	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 = sqlrow.Insert(tx, u, `users`)
		exitIfError(err)
	}

	// get user with ID of 3 and then delete it
	{
		var u UserRow
		_, err = sqlrow.Select(tx, &u, `users`, 3)
		exitIfError(err)

		_, err = sqlrow.Delete(tx, u, `users`)
		exitIfError(err)
	}

	// update family name for user with ID of 2
	{
		var u UserRow
		_, err = sqlrow.Select(tx, &u, `users`, 2)
		exitIfError(err)

		u.FamilyName = "Doe"
		_, err = sqlrow.Update(tx, u, `users`)
		exitIfError(err)
	}

	// select rows from table and print
	{
		var users []*UserRow
		_, err = sqlrow.Select(tx, &users, `
			select {}
			from users
			order by id
			limit ? offset ?`, 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)
	}
}

func init() {
	log.SetFlags(log.Lshortfile)
}

func setupSchema(db *sql.DB) {
	_, err := db.Exec(`
		create table users(
			id integer primary key autoincrement,
			given_name text,
			family_name text
		)
	`)
	exitIfError(err)
}

Output:

User 1: Citizen, John
User 2: Doe, Jane

Index

• func Delete(db DB, row interface{}, sql string, args ...interface{}) (int, error)
• func Insert(db DB, row interface{}, sql string) error
• func Select(db DB, rows interface{}, sql string, args ...interface{}) (int, error)
• func Update(db DB, row interface{}, sql string, args ...interface{}) (int, error)
• type Convention
• type DB
• type Dialect
  • func DialectFor(name string) Dialect
• type Schema
  • func (s *Schema) Delete(db DB, row interface{}, sql string, args ...interface{}) (int, error)
  • func (s *Schema) Insert(db DB, row interface{}, sql string) error
  • func (s *Schema) Prepare(row interface{}, sql string) (*Stmt, error)
  • func (s *Schema) Select(db DB, rows interface{}, sql string, args ...interface{}) (int, error)
  • func (s *Schema) Update(db DB, row interface{}, sql string, args ...interface{}) (int, error)
• type Stmt
  • func Prepare(row interface{}, sql string) (*Stmt, error)
  • func (stmt *Stmt) Exec(db DB, row interface{}, args ...interface{}) (int, error)
  • func (stmt *Stmt) Select(db DB, rows interface{}, args ...interface{}) (int, error)
  • func (stmt *Stmt) String() string

Examples

• Package
• Delete
• DialectFor
• Insert
• Prepare
• Schema.Delete
• Schema.Insert
• Schema.Prepare
• Schema.Select (MultipleRows)
• Schema.Select (OneRow)
• Schema.Update
• Select (OneRow)
• Stmt.Exec (Delete)
• Stmt.Exec (Insert)
• Stmt.Exec (Update)
• Stmt.Select (MultipleRows)
• Stmt.Select (OneRow)
• Update

Functions

func Delete

func Delete(db DB, row interface{}, sql string, args ...interface{}) (int, error)

Delete deletes a row. Returns the number of rows affected, which should be zero or one.

Example

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	row := UserRow{
		ID:         43,
		GivenName:  "John",
		FamilyName: "Citizen",
	}
	n, err := sqlrow.Delete(db, &row, "users")
	if err != nil {
		log.Fatal(err)
	}

	log.Printf("Number of rows deleted = %d", n)
}

func Insert

func Insert(db DB, row interface{}, sql string) error

Insert inserts a row. If the row has an auto-increment column defined, then the generated value is retrieved and inserted into the row. (If the database driver provides the necessary support).

Example

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	row := UserRow{
		GivenName:  "John",
		FamilyName: "Citizen",
	}
	err := sqlrow.Insert(db, &row, "users")
	if err != nil {
		log.Fatal(err)
	}

	// row.ID will contain the new ID for the row
	log.Printf("Row inserted, ID=%d", row.ID)
}

func Select

func Select(db DB, rows interface{}, sql string, args ...interface{}) (int, error)

Select executes a SELECT query and stores the result in rows. The argument passed to rows can be one of the following:

(a) A pointer to a slice of structs; or
(b) A pointer to a slice of struct pointers; or
(c) A pointer to a struct.

When rows is a pointer to a slice, it is populated with one item for each row returned by the SELECT query.

When rows is a pointer to a struct, it is populated with the first row returned from the query. This is a good option when the query will only return one row.

Select returns the number of rows returned by the SELECT query.

Example (OneRow)

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	// find user with ID=42
	var row UserRow
	n, err := sqlrow.Select(db, &row, `select {} from users where ID=?`, 42)
	if err != nil {
		log.Fatal(err)
	}

	if n > 0 {
		log.Printf("found: %v", row)
	} else {
		log.Printf("not found")
	}
}

func Update

func Update(db DB, row interface{}, sql string, args ...interface{}) (int, error)

Update updates a row. Returns the number of rows affected, which should be zero or one.

Example

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	row := UserRow{
		ID:         43,
		GivenName:  "John",
		FamilyName: "Citizen",
	}
	n, err := sqlrow.Update(db, &row, "users")
	if err != nil {
		log.Fatal(err)
	}

	log.Printf("Number of rows updated = %d", n)
}

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 ConventionLower Convention

ConventionLower is a naming convention where the column name is the Go struct field name converted to lower case. This naming convention is useful for some PostgreSQL databases.

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

Example

package main

import (
	"github.com/jjeffery/sqlrow"
)

func main() {
	// Set the default dialect for PostgreSQL.
	sqlrow.Default.Dialect = sqlrow.DialectFor("postgres")
}

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
}

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

func (s *Schema) Delete(db DB, row interface{}, sql string, args ...interface{}) (int, error)

Delete deletes a row. Returns the number of rows affected, which should be zero or one.

Example

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	// Schema for an MSSQL database, where column names
	// are the same as the Go struct field names.
	mssql := sqlrow.Schema{
		Dialect:    sqlrow.DialectFor("mssql"),
		Convention: sqlrow.ConventionSame,
	}

	row := UserRow{
		ID:         43,
		GivenName:  "John",
		FamilyName: "Citizen",
	}
	n, err := mssql.Delete(db, &row, "users")
	if err != nil {
		log.Fatal(err)
	}

	log.Printf("Number of rows deleted = %d", n)
}

func (*Schema) Insert

func (s *Schema) Insert(db DB, row interface{}, sql string) error

Insert inserts a row. If the row has an auto-increment column defined, then the generated value is retrieved and inserted into the row. (If the database driver provides the necessary support).

Example

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	// Schema for an MSSQL database, where column names
	// are the same as the Go struct field names.
	mssql := sqlrow.Schema{
		Dialect:    sqlrow.DialectFor("mssql"),
		Convention: sqlrow.ConventionSame,
	}

	row := UserRow{
		GivenName:  "John",
		FamilyName: "Citizen",
	}
	err := mssql.Insert(db, &row, "users")
	if err != nil {
		log.Fatal(err)
	}

	// row.ID will contain the new ID for the row
	log.Printf("Row inserted, ID=%d", row.ID)
}

func (*Schema) Prepare

func (s *Schema) Prepare(row interface{}, sql string) (*Stmt, error)

Prepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement.

Example

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	// Schema for an MSSQL database, where column names
	// are the same as the Go struct field names.
	mssql := sqlrow.Schema{
		Dialect:    sqlrow.DialectFor("mssql"),
		Convention: sqlrow.ConventionSame,
	}

	stmt, err := mssql.Prepare(UserRow{}, `insert into users({}) values({})`)
	if err != nil {
		log.Fatal(err)
	}

	// ... later on ...

	row := UserRow{
		GivenName:  "John",
		FamilyName: "Citizen",
	}

	_, err = stmt.Exec(db, row)

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

func (*Schema) Select

func (s *Schema) Select(db DB, rows interface{}, sql string, args ...interface{}) (int, error)

Select executes a SELECT query and stores the result in rows. The argument passed to rows can be one of the following:

A pointer to an array of structs; or
a pointer to an array of struct pointers; or
a pointer to a struct.

When rows is a pointer to an array it is populated with one item for each row returned by the SELECT query.

When rows is a pointer to a struct, it is populated with the first row returned from the query. This is a good option when the query will only return one row.

Select returns the number of rows returned by the SELECT query.

Example (MultipleRows)

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	// Schema for an MSSQL database, where column names
	// are the same as the Go struct field names.
	mssql := sqlrow.Schema{
		Dialect:    sqlrow.DialectFor("mssql"),
		Convention: sqlrow.ConventionSame,
	}

	// find users with search terms
	var rows []UserRow
	n, err := mssql.Select(db, &rows, `
		select {alias u} 
		from [Users] u
		inner join [UserSearchTerms] t on t.UserID = u.ID
		where t.SearchTerm like ?
		limit ? offset ?`, "smith%", 0, 100)
	if err != nil {
		log.Fatal(err)
	}

	if n > 0 {
		for i, row := range rows {
			log.Printf("row %d: %v", i, row)
		}
	} else {
		log.Printf("not found")
	}
}

Example (OneRow)

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	// Schema for an MSSQL database, where column names
	// are the same as the Go struct field names.
	mssql := sqlrow.Schema{
		Dialect:    sqlrow.DialectFor("mssql"),
		Convention: sqlrow.ConventionSame,
	}

	// find user with ID=42
	var row UserRow
	n, err := mssql.Select(db, &row, `select {} from [Users] where ID=?`, 42)
	if err != nil {
		log.Fatal(err)
	}

	if n > 0 {
		log.Printf("found: %v", row)
	} else {
		log.Printf("not found")
	}
}

func (*Schema) Update

func (s *Schema) Update(db DB, row interface{}, sql string, args ...interface{}) (int, error)

Update updates a row. Returns the number of rows affected, which should be zero or one.

Example

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	// Schema for an MSSQL database, where column names
	// are the same as the Go struct field names.
	mssql := sqlrow.Schema{
		Dialect:    sqlrow.DialectFor("mssql"),
		Convention: sqlrow.ConventionSame,
	}

	row := UserRow{
		ID:         43,
		GivenName:  "John",
		FamilyName: "Citizen",
	}
	n, err := mssql.Update(db, &row, "users")
	if err != nil {
		log.Fatal(err)
	}

	log.Printf("Number of rows updated = %d", n)
}

type Stmt

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

Stmt is a prepared statement. A Stmt is safe for concurrent use by multiple goroutines.

func Prepare

func Prepare(row interface{}, sql string) (*Stmt, error)

Prepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The row parameter should be a structure or a pointer to a structure and is used to determine the type of the row used when executing the statement.

Example

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	stmt, err := sqlrow.Prepare(UserRow{}, `insert into users({}) values({})`)
	if err != nil {
		log.Fatal(err)
	}

	// ... later on ...

	row := UserRow{
		GivenName:  "John",
		FamilyName: "Citizen",
	}

	_, err = stmt.Exec(db, row)

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

func (*Stmt) Exec

func (stmt *Stmt) Exec(db DB, row interface{}, args ...interface{}) (int, error)

Exec executes the prepared statement with the given row and optional arguments. It returns the number of rows affected by the statement.

If the statement is an INSERT statement and the row has an auto-increment field, then the row is updated with the value of the auto-increment column as long as the SQL driver supports this functionality.

Example (Delete)

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	stmt, err := sqlrow.Prepare(UserRow{}, `delete from users where {}`)
	if err != nil {
		log.Fatal(err)
	}

	// ... later on ...

	row := UserRow{
		ID:         42,
		GivenName:  "John",
		FamilyName: "Citizen",
	}

	_, err = stmt.Exec(db, row)

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

Example (Insert)

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	stmt, err := sqlrow.Prepare(UserRow{}, `insert into users({}) values({})`)
	if err != nil {
		log.Fatal(err)
	}

	// ... later on ...

	row := UserRow{
		GivenName:  "John",
		FamilyName: "Citizen",
	}

	_, err = stmt.Exec(db, row)

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

Example (Update)

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	stmt, err := sqlrow.Prepare(UserRow{}, `update users set {} where {}`)
	if err != nil {
		log.Fatal(err)
	}

	// ... later on ...

	row := UserRow{
		ID:         42,
		GivenName:  "John",
		FamilyName: "Citizen",
	}

	_, err = stmt.Exec(db, row)

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

func (*Stmt) Select

func (stmt *Stmt) Select(db DB, rows interface{}, args ...interface{}) (int, error)

Select executes the prepared query statement with the given arguments and returns the query results in rows. If rows is a pointer to a slice of structs then one item is added to the slice for each row returned by the query. If row is a pointer to a struct then that struct is filled with the result of the first row returned by the query. In both cases Select returns the number of rows returned by the query.

Example (MultipleRows)

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	stmt, err := sqlrow.Prepare(UserRow{}, `
		select {alias u} 
		from users u
		inner join user_search_terms t on t.user_id = u.id
		where t.search_term like ?
		limit ? offset ?`)
	if err != nil {
		log.Fatal(err)
	}

	// ... later on ...

	// find users with search terms
	var rows []UserRow
	n, err := stmt.Select(db, &rows, "smith%", 0, 100)
	if err != nil {
		log.Fatal(err)
	}
	if n > 0 {
		for i, row := range rows {
			log.Printf("row %d: %v", i, row)
		}
	} else {
		log.Printf("not found")
	}
}

Example (OneRow)

package main

import (
	"database/sql"
	"log"

	"github.com/jjeffery/sqlrow"
)

var db *sql.DB

func main() {
	type UserRow struct {
		ID         int `sql:"primary key autoincrement"`
		GivenName  string
		FamilyName string
	}

	stmt, err := sqlrow.Prepare(UserRow{}, `select {} from users where {}`)
	if err != nil {
		log.Fatal(err)
	}

	// ... later on ...

	// find user with ID=42
	var row UserRow
	n, err := stmt.Select(db, &row, 42)
	if err != nil {
		log.Fatal(err)
	}
	if n > 0 {
		log.Printf("found: %v", row)
	} else {
		log.Printf("not found")
	}
}

func (*Stmt) String

func (stmt *Stmt) String() string

String prints the SQL query associated with the statement.