sql

Documentation

Overview

Package sql is an extension to standard library "database/sql.DB" that provide common functionality across DBMS.

Index

• Constants
• type Client
  • func NewClient(opts ClientOptions) (cl *Client, err error)
  • func (cl *Client) FetchTableNames() (tableNames []string, err error)
  • func (cl *Client) Migrate(tableMigration string, fs http.FileSystem) (err error)
  • func (cl *Client) TruncateTable(tableName string) (err error)
• type ClientOptions
• type DmlKind
• type Row
  • func (row Row) ExtractSQLFields(driverName string) (names, holders []string, values []interface{})
• type Session
• type Table
  • func (table *Table) Insert(tx *sql.Tx) (ids []int64, err error)

Examples

• Row.ExtractSQLFields

Constants

const (
	DriverNameMysql    = "mysql"
	DriverNamePostgres = "postgres"
)

List of known driver name for database connection.

const DefaultPlaceHolder = "?"

DefaultPlaceHolder define default placeholder for DML, which is placeholder for MySQL.

Types

type Client

type Client struct {
	*sql.DB
	ClientOptions
	TableNames []string // List of tables in database.
}

Client provide a wrapper for generic database instance.

func NewClient

func NewClient(opts ClientOptions) (cl *Client, err error)

NewClient create and initialize new database client.

func (*Client) FetchTableNames

func (cl *Client) FetchTableNames() (tableNames []string, err error)

FetchTableNames return the table names in current database schema sorted in ascending order.

func (*Client) Migrate

func (cl *Client) Migrate(tableMigration string, fs http.FileSystem) (err error)

Migrate the database using list of SQL files inside a directory. Each SQL file in directory will be executed in alphabetical order based on the last state.

The table parameter contains the name of table where the state of migration will be saved. If its empty default to "_migration". The state including the SQL file name that has been executed and the timestamp.

func (*Client) TruncateTable

func (cl *Client) TruncateTable(tableName string) (err error)

TruncateTable truncate all data on table `tableName` with cascade option. On PostgreSQL, any identity columns (for example, serial) will be reset back to its initial value.

type ClientOptions

type ClientOptions struct {
	DriverName   string
	DSN          string
	MigrationDir string
}

ClientOptions contains options to connect to database server, including the migration directory.

type DmlKind

type DmlKind string

DmlKind define the kind for Data Manipulation Language (DML).

const (
	DmlKindDelete DmlKind = `DELETE`
	DmlKindInsert DmlKind = `INSERT`
	DmlKindSelect DmlKind = `SELECT`
	DmlKindUpdate DmlKind = `UPDATE`
)

List of valid DmlKind.

type Row

type Row map[string]interface{}

Row represent a column-name and value in a tuple. The map's key is the column name in database and the map's value is the column's value. This type can be used to create dynamic insert-update fields.

func (Row) ExtractSQLFields

func (row Row) ExtractSQLFields(driverName string) (names, holders []string, values []interface{})

ExtractSQLFields extract the column's name, column place holder, and column values as slices.

The driverName define the returned place holders. If the driverName is "postgres" then the list of holders will be returned as counter, for example "$1", "$2" and so on. If the driverName is "mysql" or empty or unknown the the list of holders will be returned as list of "?".

The returned names will be sorted in ascending order.

Example

row := Row{
	"col_3": "'update'",
	"col_2": 1,
	"col_1": true,
}
names, holders, values := row.ExtractSQLFields("?")
fnames := strings.Join(names, ",")
fholders := strings.Join(holders, ",")
q := `INSERT INTO table (` + fnames + `) VALUES (` + fholders + `)`
fmt.Printf("Query: %s\n", q)

// err := db.Exec(q, values...)
fmt.Println(values)

names, holders, values = row.ExtractSQLFields("postgres")
fnames = strings.Join(names, ",")
fholders = strings.Join(holders, ",")
q = `INSERT INTO table (` + fnames + `) VALUES (` + fholders + `)`
fmt.Printf("Query for PostgreSQL: %s\n", q)

// err := db.Exec(q, values...)
fmt.Println(values)

Output:

Query: INSERT INTO table (col_1,col_2,col_3) VALUES (?,?,?)
[true 1 'update']
Query for PostgreSQL: INSERT INTO table (col_1,col_2,col_3) VALUES ($1,$2,$3)
[true 1 'update']

type Session

type Session interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	Prepare(query string) (*sql.Stmt, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Session is an interface that represent both sql.DB and sql.Tx.

type Table

type Table struct {
	Name       string // Table name, required.
	PrimaryKey string // Primary key of table, optional.
	Rows       []Row  // The row or data in the table, optional.
}

Table represent a tuple or table in database.

A table has Name, PrimaryKey, and list of Row.

func (*Table) Insert

func (table *Table) Insert(tx *sql.Tx) (ids []int64, err error)

Insert all rows into table, one by one.

On success, it will return list of ID, if table has primary key.