sqlw

package module
v0.0.2 Latest Latest
Warning

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

 Go to latest Published: Dec 1, 2020 License: MIT Imports: 10 Imported by: 2

README

sqlw

CI for Pull Request Godoc GitHub license

database/sql wrapper library for Go
Manages automatically on the master/replica databases

In addition to the godoc API documentation.

Install

$ go get github.com/glassonion1/sqlw

Usage

Database connection

Connects to the database that with one master and two replicas.

// Settings for master
master := sqlw.Config{
  User: "root", Password: "password",
  Host: "127.0.0.1", Port: "3306", DBName: "app",
}
// Settings for replica1
rep1 := sqlw.Config{
  User: "root", Password: "password",
  Host: "127.0.0.1", Port: "3307", DBName: "app",
}
// Settings for replica2
rep2 := sqlw.Config{
  User: "root", Password: "password",
  Host: "127.0.0.1", Port: "3308", DBName: "app",
}
// Connects to mysql
db, err := sqlw.NewMySQLDB(master, rep1, rep2)
if err != nil {
  // TODO: Handle error.
}

Connects to the database that without replicas

// Settings for master
master := sqlw.Config{
  User: "root", Password: "password",
  Host: "127.0.0.1", Port: "3306", DBName: "app",
}
// Connects to mysql
db, err := sqlw.NewMySQLDB(master)
if err != nil {
  // TODO: Handle error.
}

To confirm the database connection.

db, err := sqlw.NewMySQLDB(master, rep1, rep2)
if err != nil {
  // TODO: Handle error.
}
// Is it readable?
if err := db.Readable(); err != nil {
  // not readable
}
// Is it writable?
if err := db.Writable(); err != nil {
  // not writable
}
Executes query

Query the database

db, err := sqlw.NewMySQLDB(master, rep1, rep2)
if err != nil {
  // TODO: Handle error.
}

// table definition
type User struct {
  ID   string
  Name string
}

// Query the database(exec on replica)
rows, err := db.Query("SELECT * FROM users WHERE name = ?", "hoge")
if err != nil {
  // TODO: Handle error.
}
defer rows.Close()

// Scan for the selected data
users := []User{}
for rows.Next() {
  user := User{}
  if err := rows.Scan(&user.ID, &user.Name); err != nil {
    // TODO: Handle error.
  }
  users = append(users, user)
}

Query the database uses prepare method(exec on replica)

// Instanciates statement object
stmt, err := db.PrepareQuery("SELECT * FROM users WHERE name = ?")
if err != nil {
  // TODO: Handle error.
}
defer stmt.Close()
// Executes query
rows, err := stmt.Query("hoge")
if err != nil {
  // TODO: Handle error.
}
defer rows.Close()

users := []User{}
for rows.Next() {
  user := User{}
  if err := rows.Scan(&user.ID, &user.Name); err != nil {
    // TODO: Handle error.
  }
  users = append(users, user)
}

Executes the mutation query(exec on master)

db, err := sqlw.NewMySQLDB(master, rep1, rep2)
if err != nil {
  // TODO: Handle error.
}

res, err := db.Exec("INSERT INTO users(id, name) VALUES(?, ?)", "id:001", "hoge")
if err != nil {
  // TODO: Handle error.
}

Executes the mutation query uses prepare method(exec on master)

// Instanciates statement object
stmt, err := db.PrepareMutation("INSERT INTO users(id, name) VALUES(?, ?)")
if err != nil {
  // TODO: Handle error.
}
defer stmt.Close()

res, err := stmt.Exec("id:001", "hoge")
if err != nil {
  // TODO: Handle error.
}
Transaction

Automatically commit or rollback on transaction

db, err := sqlw.NewMySQLDB(master, rep1, rep2)
if err != nil {
  // TODO: Handle error.
}
// Processes the transaction on the function
fn := func(tx *sqlw.Tx) error {
  _, err := tx.Exec("INSER INTO users(id, name) VALUES(?, ?)", "id:001", "hoge")
  if err != nil {
    // rollback on automatically
    return err
  }
  _, err := tx.Exec("UPDATE users SET name=? WHERE id=?", "piyo", "id:001")
  if err != nil {
    // rollback on automatically
    return err
  }
  return nil
}
// Executes transaction function
if err := db.Transaction(fn); err != nil {
  // TODO: Handle error.
}

// Query the master database
rows, err := db.QueryForMaster("SELECT * FROM user")

Unit tests

Executes unit tests

$ cd mysql
$ docker-compose up -d
$ cd ../
$ go test -v ./...

Documentation

Overview

Package sqlw provides general purpose wrappers to database/sql.

It is intended to seamlessly wrap database/sql and provide convenience sqlw can connect to the database without being aware of the master/replica

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	ErrNotSQLQuery    = errors.New("it is not query statement")
	ErrNotSQLMutation = errors.New("it is not mutation statement")
)

The following error is returned when the string validation of SQLQuery or SQLMutation fails.

Functions

This section is empty.

Types

type Config 

type Config struct {
	User     string
	Password string
	Host     string
	Port     string
	DBName   string
}

Config holds the database configuration information.

type DB 

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

DB is a wrapper around sql.DB

func NewDB 

func NewDB(master *sql.DB, readreplicas ...*sql.DB) *DB

NewDB returns a new sqlx DB wrapper for a pre-existing *sql.DB

This function should be used outside of Goroutine.

func NewMySQLDB 

func NewMySQLDB(masterConf Config, replicaConfs ...Config) (*DB, error)

NewMySQLDB returns a new sqlx DB wrapper for a pre-existing *sql.DB

This function should be used outside of Goroutine.

Example 
package main

import (
	"github.com/glassonion1/sqlw"
)

func main() {
	// Master
	m := sqlw.Config{
		User: "root", Password: "password",
		Host: "127.0.0.1", Port: "3306", DBName: "app",
	}
	// Replica1
	r1 := sqlw.Config{
		User: "root", Password: "password",
		Host: "127.0.0.1", Port: "3307", DBName: "app",
	}
	// Replica2
	r2 := sqlw.Config{
		User: "root", Password: "password",
		Host: "127.0.0.1", Port: "3308", DBName: "app",
	}
	// Connects to MySQL
	db, err := sqlw.NewMySQLDB(m, r1, r2)
	if err != nil {
		// TODO: Handle error.
	}
	_ = db // TODO: Use db.
}

Output:

func NewPostgresDB 

func NewPostgresDB(masterConf Config, replicaConfs ...Config) (*DB, error)

NewPostgresDB returns a new sqlx DB wrapper for a pre-existing *sql.DB

This function should be used outside of Goroutine.

func (*DB) Close 

func (db *DB) Close() error

Close closes all databases.

func (*DB) Exec 

func (db *DB) Exec(query SQLMutation, args ...interface{}) (sql.Result, error)

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query. This method is executed on the master and can use for INSERT|UPDATE|DELETE statements only.

Example 
package main

import (
	"github.com/glassonion1/sqlw"
)

var m, r1, r2 sqlw.Config

func main() {
	db, err := sqlw.NewMySQLDB(m, r1, r2)
	if err != nil {
		// TODO: Handle error.
	}
	// Executes mutation query on the master database
	res, err := db.Exec("INSERT INTO users(id, name) VALUES(?, ?)", "id:001", "hoge")
	if err != nil {
		// TODO: Handle error.
	}
	_ = res // TODO: Use res
}

Output:

func (*DB) ExecContext 

func (db *DB) ExecContext(ctx context.Context, query SQLMutation, args ...interface{}) (sql.Result, error)

ExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query. This method is executed on the master and can use for INSERT|UPDATE|DELETE statements only.

func (*DB) PrepareMutation 

func (db *DB) PrepareMutation(query SQLMutation) (*sql.Stmt, error)

PrepareMutation creates a prepared statement for later executions.The caller must call the statement's Close method when the statement is no longer needed. This method is executed on the master and can use for INSERT|UPDATE|DELETE statements only.

func (*DB) PrepareMutationContext 

func (db *DB) PrepareMutationContext(ctx context.Context, query SQLMutation) (*sql.Stmt, error)

PrepareMutationContext creates a prepared statement for later executions.The caller must call the statement's Close method when the statement is no longer needed. This method is executed on the master and can use for INSERT|UPDATE|DELETE statements only.

func (*DB) PrepareQuery 

func (db *DB) PrepareQuery(query SQLQuery) (*sql.Stmt, error)

PrepareQuery creates a prepared statement for later queries.The caller must call the statement's Close method when the statement is no longer needed. This method is executed on the read replica and can use for SELECT statements only.

func (*DB) PrepareQueryContext 

func (db *DB) PrepareQueryContext(ctx context.Context, query SQLQuery) (*sql.Stmt, error)

PrepareQueryContext creates a prepared statement for later queries.The caller must call the statement's Close method when the statement is no longer needed. This method is executed on the read replica and can use for SELECT statements only.

func (*DB) PrepareQueryContextForMaster 

func (db *DB) PrepareQueryContextForMaster(ctx context.Context, query SQLQuery) (*sql.Stmt, error)

PrepareQueryContextForMaster creates a prepared statement for later queries(SELECT).The caller must call the statement's Close method when the statement is no longer needed. This method is executed on the master and can use for SELECT statements only.

func (*DB) PrepareQueryForMaster 

func (db *DB) PrepareQueryForMaster(query SQLQuery) (*sql.Stmt, error)

PrepareQueryForMaster creates a prepared statement for later queries(SELECT).The caller must call the statement's Close method when the statement is no longer needed. This method is executed on the master and can use for SELECT statements only.

func (*DB) Query 

func (db *DB) Query(query SQLQuery, args ...interface{}) (*sql.Rows, error)

Query executes a query that returns rows, typically a SELECT. This method is executed on the read replica.

Example 
package main

import (
	"log"

	"github.com/glassonion1/sqlw"
)

var m, r1, r2 sqlw.Config

func main() {
	db, err := sqlw.NewMySQLDB(m, r1, r2)
	if err != nil {
		// TODO: Handle error.
	}

	// Model
	type User struct {
		ID   string
		Name string
	}

	// Executes query on the replica database
	rows, err := db.Query("SELECT * FROM users WHERE name = ?", "hoge")
	if err != nil {
		// TODO: Handle error.
	}
	defer rows.Close()

	// Mapping data to model
	users := []User{}
	for rows.Next() {
		user := User{}
		if err := rows.Scan(&user.ID, &user.Name); err != nil {
			// TODO: Handle error.
		}
		users = append(users, user)
	}
	log.Printf("users: %v", users)
}

Output:

func (*DB) QueryContext 

func (db *DB) QueryContext(ctx context.Context, query SQLQuery, args ...interface{}) (*sql.Rows, error)

QueryContext executes a query that returns rows, typically a SELECT. This method is executed on the read replica.

func (*DB) QueryContextForMaster 

func (db *DB) QueryContextForMaster(ctx context.Context, query SQLQuery, args ...interface{}) (*sql.Rows, error)

QueryContextForMaster executes a query that returns rows, typically a SELECT. This method is executed on the master.

It is used to refer to the data immediately after executing the mutation query(INSERT/UPDATE/DELETE).

func (*DB) QueryForMaster 

func (db *DB) QueryForMaster(query SQLQuery, args ...interface{}) (*sql.Rows, error)

QueryForMaster executes a query that returns rows, typically a SELECT. This method is executed on the master.

It is used to refer to the data immediately after executing the mutation query(INSERT/UPDATE/DELETE).

func (*DB) QueryRow 

func (db *DB) QueryRow(query SQLQuery, args ...interface{}) *sql.Row

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest. This method is executed on the read replica.

func (*DB) QueryRowContext 

func (db *DB) QueryRowContext(ctx context.Context, query SQLQuery, args ...interface{}) *sql.Row

QueryRowContext executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest. This method is executed on the read replica.

func (*DB) QueryRowContextForMaster 

func (db *DB) QueryRowContextForMaster(ctx context.Context, query SQLQuery, args ...interface{}) *sql.Row

QueryRowContextForMaster executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest. This method is executed on the master.

func (*DB) QueryRowForMaster 

func (db *DB) QueryRowForMaster(query SQLQuery, args ...interface{}) *sql.Row

QueryRowForMaster executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest. This method is executed on the master.

func (*DB) Readable 

func (db *DB) Readable() error

Readable checks if the database can be readable.

func (*DB) SetConnMaxLifetime 

func (db *DB) SetConnMaxLifetime(d time.Duration)

SetConnMaxLifetime sets the maximum amount of time a connection may be reused.

func (*DB) SetMaxIdleConns 

func (db *DB) SetMaxIdleConns(n int)

SetMaxIdleConns sets the maximum number of connections in the idle connection pool.

func (*DB) SetMaxOpenConns 

func (db *DB) SetMaxOpenConns(n int)

SetMaxOpenConns sets the maximum number of open connections to the database.

func (*DB) Transaction 

func (db *DB) Transaction(fn TxHandlerFunc) error

Transaction executes paramed function in one database transaction. Executes the passed function and commits the transaction if there is no error. If an error occurs when executing the passed function rolls back the transaction. see sqlw/TxHandlerFunc

Example 
package main

import (
	"github.com/glassonion1/sqlw"
)

var m, r1, r2 sqlw.Config

func main() {
	db, err := sqlw.NewMySQLDB(m, r1, r2)
	if err != nil {
		// TODO: Handle error.
	}

	// Executes multiple queries in database transaction
	fn := func(tx *sqlw.Tx) error {
		_, err := tx.Exec("INSER INTO users(id, name) VALUES(?, ?)", "id:001", "hoge")
		if err != nil {
			// Rollbacks automatically
			return err
		}
		_, err = tx.Exec("UPDATE users SET name=? WHERE id=?", "piyo", "id:001")
		if err != nil {
			// Rollbacks automatically
			return err
		}

		// Warn: this query is executed outside of transaction
		_, _ = db.Exec("UPDATE hoge SET name='foo'")

		return nil
	}
	// Executes transaction and commits automatically if no errors
	if err := db.Transaction(fn); err != nil {
		// TODO: Handle error.
	}

	// Executes query for master database
	rows, err := db.QueryForMaster("SELECT * FROM user")
	if err != nil {
		// TODO: Handle error.
	}
	_ = rows // TODO: Use rows
}

Output:

func (*DB) TransactionTx 

func (db *DB) TransactionTx(ctx context.Context, fn TxHandlerFunc, opts *sql.TxOptions) error

TransactionTx executes paramed function in one database transaction. Executes the passed function and commits the transaction if there is no error. If an error occurs when executing the passed function rolls back the transaction. see sqlw/TxHandlerFunc

func (*DB) Writable 

func (db *DB) Writable() error

Writable checks if the database is writable.

type SQLMutation 

type SQLMutation string

SQLMutation provides for mutation(INSERT|UPDATE|DELETE) statements extensions to string

func (SQLMutation) String 

func (s SQLMutation) String() string

String returns a transformed string.

func (SQLMutation) Validate 

func (s SQLMutation) Validate() error

Validate validates whether the string is a mutation(INSERT|UPDATE|DELETE) statements.

type SQLQuery 

type SQLQuery string

SQLQuery provides for query(SELECT) statements extensions to string

func (SQLQuery) String 

func (s SQLQuery) String() string

String returns a transformed string.

func (SQLQuery) Validate 

func (s SQLQuery) Validate() error

Validate validates whether the string is a query(SELECT) statements.

type Tx 

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

Tx is a wrapper around sql.Tx

func (*Tx) Exec 

func (tx *Tx) Exec(query SQLMutation, args ...interface{}) (sql.Result, error)

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (*Tx) ExecContext 

func (tx *Tx) ExecContext(ctx context.Context, query SQLMutation, args ...interface{}) (sql.Result, error)

ExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (*Tx) Query 

func (tx *Tx) Query(query SQLQuery, args ...interface{}) (*sql.Rows, error)

Query executes a query that returns rows, typically a SELECT.

func (*Tx) QueryContext 

func (tx *Tx) QueryContext(ctx context.Context, query SQLQuery, args ...interface{}) (*sql.Rows, error)

QueryContext executes a query that returns rows, typically a SELECT.

func (*Tx) QueryRow 

func (tx *Tx) QueryRow(query SQLQuery, args ...interface{}) *sql.Row

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*Tx) QueryRowContext 

func (tx *Tx) QueryRowContext(ctx context.Context, query SQLQuery, args ...interface{}) *sql.Row

QueryRowContext executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

type TxHandlerFunc 

type TxHandlerFunc func(*Tx) error

TxHandlerFunc is for executing SQL on a transaction. To make the SQL to be executed a transition target, must execute it via the type sqlw.Tx.

Source Files

View all Source files

Directories

Path Synopsis
sample module

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.