twowaysql

README

twowaysql

2-Way-SQL Go implementation

Installation

go get github.com/future-architect/go-twowaysql

Usage

TODO Below is an example which shows some common use cases for twowaysql.

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/future-architect/go-twowaysql"
	"github.com/jmoiron/sqlx"
	_ "github.com/jackc/pgx/v4/stdlib"
)

type Person struct {
	EmpNo     int    `db:"employee_no"`
	DeptNo    int    `db:"dept_no"`
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
	Email     string `db:"email"`
}

type Params struct {
	Name     string `twowaysql:"name"`
	EmpNo    int    `twowaysql:"EmpNo"`
	MaxEmpNo int    `twowaysql:"maxEmpNo"`
	DeptNo   int    `twowaysql:"deptNo"`
}

func main() {
	ctx := context.Background()

	db, err := sqlx.Open("pgx", "user=postgres password=postgres dbname=postgres sslmode=disable")

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

	tw := twowaysql.New(db)

	var people []Person
	var params = Params{
		MaxEmpNo: 2000,
		DeptNo:   15,
	}

	err = tw.Select(ctx, &people, `SELECT * FROM persons WHERE employee_no < /*maxEmpNo*/1000 /* IF deptNo */ AND dept_no < /*deptNo*/1 /* END */`, &params)
	if err != nil {
		log.Fatalf("select failed: %v", err)
	}

	fmt.Printf("%#v\n%#v\n%#v", people[0], people[1], people[2])
	//Person{EmpNo:1, DeptNo:10, FirstName:"Evan", LastName:"MacMans", Email:"evanmacmans@example.com"}
	//Person{EmpNo:3, DeptNo:12, FirstName:"Jimmie", LastName:"Bruce", Email:"jimmiebruce@example.com"}
	//Person{EmpNo:2, DeptNo:11, FirstName:"Malvina", LastName:"FitzSimons", Email:"malvinafitzsimons@example.com"}

}

CLI Tool

CLI tool twowaysql provides helper functions about two way sql

go install github.com/future-architect/go-twowaysql/...

Database Connection

To connect database, driver and source strings are required. Driver is like pgx and source is postgres://user:pass@host/dbname?sslmode=disable.

You can pass them via options(-d DRIVER, --driver=DRIVER, -c SOURCE, --source=SOURCE) or by using TWOWAYSQL_DRIVER/TWOWAYSQL_CONNECTION environment variables.

This tool also read .env and .env.local files.

Execute SQL

$ twowaysql run -p first_name=Malvina testdata/postgres/sql/select_person.sql
┌───────────────────────────────┬────────────┬────────────┐
│ email                         │ first_name │ last_name  │
╞═══════════════════════════════╪════════════╪════════════╡
│ malvinafitzsimons@example.com │ Malvina    │ FitzSimons │
└───────────────────────────────┴────────────┴────────────┘

Query takes 22.804166ms

• -p, --param=PARAM ... Parameter in single value or JSON (name=bob, or {"name": "bob"})
• -e, --explain Run with EXPLAIN to show execution plan
• -r, --rollback Run within transaction and then rollback
• -o, --output-format=default Result output format (default, md, json, yaml)

Evaluate 2-Way-SQL

$ twowaysql eval -p first_name=Malvina testdata/postgres/sql/select_person.sql
# Converted Source

SELECT email, first_name, last_name FROM persons WHERE first_name=?/*first_name*/;

# Parameters

- Malvina

Unittesting

$ twowaysql test testdata/postgres/sql/select_person.sql

Test code is written in Markdown with the following format:

• Single level 2 heading with "Test" or "Tests" label that contains all tests
• Each test has level 3 headings with "Case:" prefix and test name
• Each test can have YAML as a test code with the following keys:
  • fixtures(optional): These contents are imported as a test data
  • params(optional): This is an parameter of two way SQL
  • testQuery(optional): This is an query SQL to access table to check result. If you omit this, test runner gets result from SQL itself.
  • expect: This is an expected result.

Fixtures and expect should be nested list(first line is header) or list of maps.

## Tests

### Case: Query Evan Test

```yaml
fixtures:
  persons:
    - [employee_no, dept_no, first_name, last_name, email, created_at]
    - [4, 13, Dan, Conner, dan@example.com, 2022-09-13 10:30:15]
params: { first_name: Dan }
expect:
  - { email: dan@example.com, first_name: Dan } 
```

Customize CLI tool

by default twowaysql integrated with the following drivers:

• github.com/jackc/pgx/v4
• modernc.org/sqlite
• github.com/go-sql-driver/mysql

If you want to add/remove drivers, create simple main package and call cli.Main().

package main

import (
	_ "github.com/sijms/go-ora/v2" // Oracle

	"github.com/future-architect/go-twowaysql/cli"
)

func main() {
	cli.Main()
}

License

Apache License Version 2.0

Contribution

Launch database for testing:

$ docker compose up --build

Run acceptance test:

$ docker compose -f docker-compose-test.yml up --build

Documentation

Overview

Package twowaysql provides an implementation of 2WaySQL.

Index

• func Eval(inputQuery string, inputParams interface{}) (string, []interface{}, error)
• func GenerateMarkdown(w io.Writer, lang string) error
• func ParseMarkdownFS(fsys fs.FS, pattern ...string) (map[string]*Document, error)
• func ParseMarkdownGlob(pattern ...string) (map[string]*Document, error)
• type CRUDMatrix
• type Document
  • func ParseMarkdown(r io.Reader) (*Document, error)
  • func ParseMarkdownFile(filepath string) (*Document, error)
  • func ParseMarkdownString(src string) (*Document, error)
• type Fixture
• type MatchRule
  • func (m MatchRule) MarshalJSON() ([]byte, error)
  • func (m MatchRule) String() string
  • func (m *MatchRule) UnmarshalJSON(data []byte) error
• type Param
• type ParamType
  • func (p ParamType) MarshalJSON() ([]byte, error)
  • func (p ParamType) String() string
  • func (p *ParamType) UnmarshalJSON(data []byte) error
• type Table
• type TestCase
• type Twowaysql
  • func New(db *sqlx.DB) *Twowaysql
  • func (t *Twowaysql) Begin(ctx context.Context) (*TwowaysqlTx, error)
  • func (t *Twowaysql) Close() error
  • func (t *Twowaysql) DB() *sqlx.DB
  • func (t *Twowaysql) Exec(ctx context.Context, query string, params interface{}) (sql.Result, error)
  • func (t *Twowaysql) Select(ctx context.Context, dest interface{}, query string, params interface{}) error
  • func (t *Twowaysql) Transaction(ctx context.Context, fn func(tx *TwowaysqlTx) error) error
• type TwowaysqlTx
  • func (t *TwowaysqlTx) Commit() error
  • func (t *TwowaysqlTx) Exec(ctx context.Context, query string, params interface{}) (sql.Result, error)
  • func (t *TwowaysqlTx) Rollback() error
  • func (t *TwowaysqlTx) Select(ctx context.Context, dest interface{}, query string, params interface{}) error
  • func (t *TwowaysqlTx) Tx() *sqlx.Tx

Examples

• Eval
• Twowaysql.Exec
• Twowaysql.Select

Functions

func Eval

func Eval(inputQuery string, inputParams interface{}) (string, []interface{}, error)

Eval returns converted query and bind value. inputParams takes a tagged struct. Tags must be in the form `map:"tag_name"`. The return value is expected to be used to issue queries to the database

Example

type Info struct {
	Name       string   `twowaysql:"name"`
	EmpNo      int      `twowaysql:"EmpNo"`
	MaxEmpNo   int      `twowaysql:"maxEmpNo"`
	DeptNo     int      `twowaysql:"deptNo"`
	Email      string   `twowaysql:"email"`
	GenderList []string `twowaysql:"gender_list"`
	IntList    []int    `twowaysql:"int_list"`
}

var params = Info{
	Name:       "Jeff",
	MaxEmpNo:   3,
	DeptNo:     12,
	GenderList: []string{"M", "F"},
	IntList:    []int{1, 2, 3},
}
var before = `SELECT * FROM person WHERE employee_no = /*maxEmpNo*/1000 AND /* IF int_list !== null */  person.gender in /*int_list*/(3,5,7) /* END */`

after, afterParams, _ := twowaysql.Eval(before, &params)

fmt.Println(after)
fmt.Println(afterParams)

Output:

SELECT * FROM person WHERE employee_no = ?/*maxEmpNo*/ AND person.gender in (?, ?, ?)/*int_list*/
[3 1 2 3]

func GenerateMarkdown

func GenerateMarkdown(w io.Writer, lang string) error

ParseMarkdown parses markdown content

func ParseMarkdownFS

func ParseMarkdownFS(fsys fs.FS, pattern ...string) (map[string]*Document, error)

ParseMarkdown parses markdown content

func ParseMarkdownGlob

func ParseMarkdownGlob(pattern ...string) (map[string]*Document, error)

ParseMarkdownGlob parses markdown files to match patterns

Types

type CRUDMatrix

type CRUDMatrix struct {
	Table       string `json:"table"`
	C           bool   `json:"c"`
	R           bool   `json:"r"`
	U           bool   `json:"u"`
	D           bool   `json:"d"`
	Description string `json:"description,omitempty"`
}

CRUDMatrix represents CRUD Matrix

type Document

type Document struct {
	SQL               string       `json:"sql"`
	Title             string       `json:"title"`
	Params            []Param      `json:"params"`
	CRUDMatrix        []CRUDMatrix `json:"crud_matrix,omitempty"`
	TestCases         []TestCase   `json:"testcases,omitempty"`
	CommonTestFixture Fixture      `json:"common_test_fixtures,omitempty"`
}

Document contains SQL and metadata

func ParseMarkdown

func ParseMarkdown(r io.Reader) (*Document, error)

ParseMarkdown parses markdown content

func ParseMarkdownFile

func ParseMarkdownFile(filepath string) (*Document, error)

ParseMarkdownFile parses markdown file

func ParseMarkdownString

func ParseMarkdownString(src string) (*Document, error)

ParseMarkdown parses markdown content

type Fixture

type Fixture struct {
	Lang   string
	Code   string
	Tables []Table
}

type MatchRule

type MatchRule int

const (
	SelectExactMatch MatchRule = iota + 1
	SelectMatch
	ExecExactMatch
	ExecMatch
)

func (MatchRule) MarshalJSON

func (m MatchRule) MarshalJSON() ([]byte, error)

func (MatchRule) String

func (m MatchRule) String() string

func (*MatchRule) UnmarshalJSON

func (m *MatchRule) UnmarshalJSON(data []byte) error

type Param

type Param struct {
	Name        string    `json:"name"`
	Type        ParamType `json:"type"`
	Value       string    `json:"value"`
	Description string    `json:"description,omitempty"`
}

Param is parameter type of 2-Way-SQL

type ParamType

type ParamType int

ParamType is for describing parameters

const (
	InvalidType ParamType = iota
	BoolType
	ByteType
	FloatType
	IntType
	TextType
	TimestampType
)

func (ParamType) MarshalJSON

func (p ParamType) MarshalJSON() ([]byte, error)

func (ParamType) String

func (p ParamType) String() string

func (*ParamType) UnmarshalJSON

func (p *ParamType) UnmarshalJSON(data []byte) error

type Table

type Table struct {
	MatchRule MatchRule  `json:"type"`
	Name      string     `json:"name"`
	Cells     [][]string `json:"cells"`
}

type TestCase

type TestCase struct {
	Name      string
	Params    map[string]string
	TestQuery string
	Expect    [][]string
	Fixtures  []Table
}

type Twowaysql

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

Twowaysql is a struct for issuing 2WaySQL query

func New

func New(db *sqlx.DB) *Twowaysql

New returns instance of Twowaysql

func (*Twowaysql) Begin

func (t *Twowaysql) Begin(ctx context.Context) (*TwowaysqlTx, error)

Begin is a thin wrapper around db.BeginTxx in the sqlx package.

func (*Twowaysql) Close

func (t *Twowaysql) Close() error

Close is a thin wrapper around db.Close in the sqlx package.

func (*Twowaysql) DB

func (t *Twowaysql) DB() *sqlx.DB

DB returns `*sqlx.DB`

func (*Twowaysql) Exec

func (t *Twowaysql) Exec(ctx context.Context, query string, params interface{}) (sql.Result, error)

Exec is a thin wrapper around db.Exec in the sqlx package. params takes a tagged struct. The tags format must be `twowaysql:"tag_name"`.

Example

type Info struct {
	Name       string   `twowaysql:"name"`
	EmpNo      int      `twowaysql:"EmpNo"`
	MaxEmpNo   int      `twowaysql:"maxEmpNo"`
	DeptNo     int      `twowaysql:"deptNo"`
	Email      string   `twowaysql:"email"`
	GenderList []string `twowaysql:"gender_list"`
	IntList    []int    `twowaysql:"int_list"`
}

var params = Info{
	MaxEmpNo: 3,
	DeptNo:   12,
}

result, err := tw.Exec(ctx, `UPDATE persons SET dept_no = /*deptNo*/1 WHERE employee_no = /*EmpNo*/1`, &params)
if err != nil {
	log.Fatal(err)
}

rows, err := result.RowsAffected()
if err != nil {
	log.Fatal(err)
}

if rows != 1 {
	log.Fatalf("expected to affect 1 row. affected %d", rows)
}

func (*Twowaysql) Select

func (t *Twowaysql) Select(ctx context.Context, dest interface{}, query string, params interface{}) error

Select is a thin wrapper around db.Select in the sqlx package. params takes a tagged struct. The tags format must be `twowaysql:"tag_name"`. dest takes a pointer to a slice of a struct. The struct tag format must be `db:"tag_name"`.

Example

type Person struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
	Email     string `db:"email"`
}

type Info struct {
	Name       string   `twowaysql:"name"`
	EmpNo      int      `twowaysql:"EmpNo"`
	MaxEmpNo   int      `twowaysql:"maxEmpNo"`
	DeptNo     int      `twowaysql:"deptNo"`
	Email      string   `twowaysql:"email"`
	GenderList []string `twowaysql:"gender_list"`
	IntList    []int    `twowaysql:"int_list"`
}

var params = Info{
	MaxEmpNo: 3,
	DeptNo:   12,
}

var people []Person
err := tw.Select(ctx, &people, `SELECT first_name, last_name, email FROM persons WHERE employee_no < /*maxEmpNo*/1000 /* IF deptNo */ AND dept_no < /*deptNo*/1 /* END */`, &params)
if err != nil {
	log.Fatal(err)
}

func (*Twowaysql) Transaction

func (t *Twowaysql) Transaction(ctx context.Context, fn func(tx *TwowaysqlTx) error) error

Transaction starts a transaction as a block. arguments function is return error will rollback, otherwise to commit.

type TwowaysqlTx

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

TwowaysqlTx is a structure for issuing 2WaySQL queries within a transaction.

func (*TwowaysqlTx) Commit

func (t *TwowaysqlTx) Commit() error

Commit is a thin wrapper around tx.Commit in the sqlx package.

func (*TwowaysqlTx) Exec

func (t *TwowaysqlTx) Exec(ctx context.Context, query string, params interface{}) (sql.Result, error)

Exec is a thin wrapper around db.Exec in the sqlx package. params takes a tagged struct. The tags format must be `twowaysql:"tag_name"`. It is an equivalent implementation of Twowaysql.Exec

func (*TwowaysqlTx) Rollback

func (t *TwowaysqlTx) Rollback() error

Rollback is a thin wrapper around tx.Rollback in the sqlx package.

func (*TwowaysqlTx) Select

func (t *TwowaysqlTx) Select(ctx context.Context, dest interface{}, query string, params interface{}) error

Select is a thin wrapper around db.Select in the sqlx package. params takes a tagged struct. The tags format must be `twowaysql:"tag_name"`. dest takes a pointer to a slice of a struct. The struct tag format must be `db:"tag_name"`. It is an equivalent implementation of Twowaysql.Select

func (*TwowaysqlTx) Tx

func (t *TwowaysqlTx) Tx() *sqlx.Tx

Tx returns `*sqlx.Tx`