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.
$ brew install mashiike/tap/mysqlbatch
like mysql-client for batch mode.
as ...
$ mysqlbatch -u root -p ${password} -h localhost < batch.sql
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.
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 deployfor example
{
"file": "./task.sql",
}output
{
"query_results": [
{
"Rows": [
[
"3",
"[email protected]",
"1"
],
[
"4",
"[email protected]",
"0"
],
[
"6",
"[email protected]",
"0"
],
[
"7",
"[email protected]",
"0"
],
[
"8",
"[email protected]",
"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
}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
}
}see LICENSE file.