mysqlbatch

README

mysqlbatch

mysqlbatch accepts multiple queries from standard input. Just like the standard mysql command batch mode.

mysqlbatch can be bundled with Docker, AWS Lambda Function, etc. for one binary.

I created it because I wanted to issue a query from AWS Lambda Function on VPC to RDS Aurora (MySQL compatible) using Bash Layer.

Install

Homebrew (macOS only)

$ brew install mashiike/tap/mysqlbatch

Binary packages

Releases

Simple usecase

like mysql-client for batch mode.

as ...

$ mysqlbatch -u root -p ${password} -h localhost < batch.sql

Usage as a library

executer, err := mysqlbatch.Open("root:password@tcp(localhost:3306)/testdb?parseTime=true")
if err != nil {
    //...
}
defer executer.Close()
if err := executer.Execute(strings.NewReader("UPDATE users SET name = 'hoge';")); err != nil {
    //...
}

more infomation see go doc.

Usage with AWS Lambda (serverless)

Let's solidify the Lambda package with the following zip arcive (runtime provided.al2)

lambda.zip
├── task.sql
└── bootstrap  

A related document is https://docs.aws.amazon.com/lambda/latest/dg/runtimes-custom.html

for example.

deploy lambda functions, in lambda directory
The example of lambda directory uses lambroll for deployment.

For more information on the infrastructure around lambda functions, please refer to example.tf.

$ cd lambda/
$ make terraform/init
$ make terraform/plan
$ make terraform/apply
$ make deploy

lambda Payload

for example

{
  "file": "./task.sql",
}

output

{
  "query_results": [
    {
      "Rows": [
        [
          "3",
          "b64ab83358188d4de34fefaa5cf701da@example.com",
          "1"
        ],
        [
          "4",
          "9266d853a5da847cc3355f4b0cd78156@example.com",
          "0"
        ],
        [
          "6",
          "7bc461bfac71283be7cc2612902ec638@example.com",
          "0"
        ],
        [
          "7",
          "a576af3e065e787e691eea537b0eec7b@example.com",
          "0"
        ],
        [
          "8",
          "9c4fd932850bf2026d42ea8844209e6b@example.com",
          "0"
        ]
      ],
      "Columns": [
        "id",
        "name",
        "age"
      ],
      "Query": "SELECT * FROM users WHERE age is NOT NULL LIMIT 5"
    }
  ],
  "last_execute_time": "2023-03-16T10:09:38Z",
  "last_execute_unix_milli": 1678961378000
}

Advanced Usage: Template SQL

The SQL to be executed is rendered by pongo2, a Django-syntax like template-engine, once. Therefore, the SQL to be specified can use template notation. In CLI, --var key=value flag, in Lambda, vars key can be specified as a string hash, and variables can be passed at runtime to dynamically generate SQL by template notation. For example, if you specify --var relation=users --var limit=5 and execute the following template SQL, (environment variable ENV=dev is set)

task_template.sql

CREATE DATABASE IF NOT EXISTS {{ must_env("ENV") }}_mysqlbatch;
USE {{ must_env("ENV") }}_mysqlbatch;
DROP TABLE IF EXISTS {{ var("relation","hoge") }};
CREATE TABLE {{ var("relation","hoge") }} (
    id INTEGER auto_increment,
    name VARCHAR(191),
    age INTEGER,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `name` (`name`)
);
INSERT IGNORE INTO {{ var("relation","hoge") }}(name) VALUES(CONCAT(SUBSTRING(MD5(RAND()), 1, 40),'@example.com'));
INSERT IGNORE INTO {{ var("relation","hoge") }}(name) SELECT (CONCAT(SUBSTRING(MD5(RAND()), 1, 40),'@example.com')) FROM {{ var("relation","hoge") }};
{%- for i in  %}
INSERT IGNORE INTO {{ var("relation","hoge") }}(name, age) SELECT (CONCAT(SUBSTRING(MD5(RAND()), 1, 40),'@example.com')),RAND() FROM {{ var("relation","hoge") }};
{%- endfor %}
SELECT * FROM {{ var("relation","hoge") }} WHERE age is NOT NULL LIMIT {{ must_var("limit") }};

The following SQL is executed.

CREATE DATABASE IF NOT EXISTS dev_mysqlbatch;
USE dev_mysqlbatch;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id INTEGER auto_increment,
    name VARCHAR(191),
    age INTEGER,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `name` (`name`)
);
INSERT IGNORE INTO users(name) VALUES(CONCAT(SUBSTRING(MD5(RAND()), 1, 40),'@example.com'));
INSERT IGNORE INTO users(name) SELECT (CONCAT(SUBSTRING(MD5(RAND()), 1, 40),'@example.com')) FROM users;
INSERT IGNORE INTO users(name, age) SELECT (CONCAT(SUBSTRING(MD5(RAND()), 1, 40),'@example.com')),RAND() FROM users;
INSERT IGNORE INTO users(name, age) SELECT (CONCAT(SUBSTRING(MD5(RAND()), 1, 40),'@example.com')),RAND() FROM users;
INSERT IGNORE INTO users(name, age) SELECT (CONCAT(SUBSTRING(MD5(RAND()), 1, 40),'@example.com')),RAND() FROM users;
SELECT * FROM hoge WHERE age is NOT NULL LIMIT 5;

When executing with Lambda, the following JSON can be specified as the payload.

{
  "file": "./task_template.sql",
  "vars": {
    "relation": "users",
    "limit": 5
  }
}

License

see LICENSE file.

Documentation

Index

• Variables
• type Config
  • func NewDefaultConfig() *Config
  • func (c *Config) GetDSN(ctx context.Context) (string, error)
• type Executer
  • func New(ctx context.Context, conf *Config) (*Executer, error)
  • func NewWithDB(db *sql.DB) *Executer
  • func Open(dsn string) (*Executer, error)
  • func (e *Executer) Close() error
  • func (e *Executer) Execute(queryReader io.Reader, vars map[string]string) error
  • func (e *Executer) ExecuteContext(ctx context.Context, queryReader io.Reader, vars map[string]string) error
  • func (e *Executer) LastExecuteTime() time.Time
  • func (e *Executer) SetExecuteHook(hook func(query string, rowsAffected, lastInsertId int64))
  • func (e *Executer) SetIsSelectFunc(f func(query string) bool)
  • func (e *Executer) SetSelectHook(hook func(query string, columns []string, rows [][]string))
  • func (e *Executer) SetTableSelectHook(hook func(query, table string))
  • func (e *Executer) SetTimeCheckQuery(query string)
• type QueryScanner
  • func NewQueryScanner(queryReader io.Reader) *QueryScanner
  • func (s *QueryScanner) Query() string
• type SSMParameterFetcher
  • func (f *SSMParameterFetcher) Fetch(ctx context.Context, parameterName string) (string, error)

Variables

var DefaultSQLDumper io.Writer = io.Discard

Types

type Config

type Config struct {
	DSN      string
	User     string
	Password string
	Host     string
	Port     int
	Database string
	Location string

	PasswordSSMParameterName string
	Fetcher                  *SSMParameterFetcher
}

Config is a connection setting to MySQL. Exists to generate a Golang connection DSN to MySQL

func NewDefaultConfig

func NewDefaultConfig() *Config

NewDefaultConfig returns the config for connecting to the local MySQL server

func (*Config) GetDSN

func (c *Config) GetDSN(ctx context.Context) (string, error)

GetDSN returns a DSN dedicated to connecting to MySQL.

type Executer

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

Executer queries the DB. There is no parallelism

func New

func New(ctx context.Context, conf *Config) (*Executer, error)

New return Executer with config

func NewWithDB

func NewWithDB(db *sql.DB) *Executer

NewWithDB returns Executer with *sql.DB Note: Since it is made assuming MySQL, it may be inconvenient for other DBs.

func Open

func Open(dsn string) (*Executer, error)

Open with dsn

func (*Executer) Close

func (e *Executer) Close() error

Close DB

func (*Executer) Execute

func (e *Executer) Execute(queryReader io.Reader, vars map[string]string) error

Execute SQL

func (*Executer) ExecuteContext

func (e *Executer) ExecuteContext(ctx context.Context, queryReader io.Reader, vars map[string]string) error

ExecuteContext SQL execute with context.Context

func (*Executer) LastExecuteTime

func (e *Executer) LastExecuteTime() time.Time

LastExecuteTime returns last execute time on DB

func (*Executer) SetExecuteHook

func (e *Executer) SetExecuteHook(hook func(query string, rowsAffected, lastInsertId int64))

SetExecuteHook set non select query hook

func (*Executer) SetIsSelectFunc

func (e *Executer) SetIsSelectFunc(f func(query string) bool)

SetIsSelectFunc :Set the function to decide whether to execute in QueryContext

func (*Executer) SetSelectHook

func (e *Executer) SetSelectHook(hook func(query string, columns []string, rows [][]string))

SetSelectHook set select query hook

func (*Executer) SetTableSelectHook

func (e *Executer) SetTableSelectHook(hook func(query, table string))

SetTimeCheckQuery set select query hook, but result is table string

func (*Executer) SetTimeCheckQuery

func (e *Executer) SetTimeCheckQuery(query string)

SetTimeCheckQuery set time check query for non mysql db

type QueryScanner

type QueryScanner struct {
	*bufio.Scanner
}

QueryScanner separate string by ; and delete newline

func NewQueryScanner

func NewQueryScanner(queryReader io.Reader) *QueryScanner

NewQueryScanner returns QueryScanner

func (*QueryScanner) Query

func (s *QueryScanner) Query() string

Query return

type SSMParameterFetcher

type SSMParameterFetcher struct {
	LoadAWSDefaultConfigOptions []func(*config.LoadOptions) error
	// contains filtered or unexported fields
}

func (*SSMParameterFetcher) Fetch

func (f *SSMParameterFetcher) Fetch(ctx context.Context, parameterName string) (string, error)