Introduction
Have you ever written Go and hard-coded SQL to execute?
For example, here's a short SQL.
https://pkg.go.dev/database/sql#example-DB.QueryContext
rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
if err != nil {
log.Fatal(err)
}
In this example, you may notice any syntax errors before execution.
However, you may end up writing SQL over multiple lines, as in the following.
sqls := []string{
`UPDATE employees
SET salary = 5000;
`,
`UPDATE employees
SET salary = salary * 1.1
WHERE salary <= 10000;
`,
`UPDATE employees
SET salary = 5000
WHERE department = 'Sales';
`,
`UPDATE employees
SET salary = 5000
WHERE first_name = 'John' AND last_name = 'Doe';
`,
`UPDATE employees
SET salary = salary * 1.1
WHERE job_title = 'Manager' AND department = 'Sales';
`,
}
for _, sql :=range sqls {
_, err := tx.ExecContext(ctx, sql)
if err != nil {
log.Fatal(err)
}
}
It is attractive to be able to quickly prepare and execute SQL without using the O/R mapper.
However, there are concerns about code readability and difficulty in checking SQL syntax because it spans multiple lines.
Separate SQL into files
One solution would be to separate the SQL into files.
This would make the code more readable.
input, _ := os.ReadFile("input.sql")
// Process multiple queries read from a file so that they can be executed one query at a time
sqls := Something(input)
for _, sql := range sqls {
_, err := tx.ExecContext(ctx, sql)
if err != nil {
log.Fatal(err)
}
}
However, you need to implement the process of making the read file valid SQL by yourself.
Embed SQL files into Go binary
So, here is what we made this time!
https://github.com/uh-zz/sqload
This library reads SQL files and converts them into a form that can be executed one query at a time.
Here is a sample
package main
import (
"bytes"
"embed"
"fmt"
"github.com/uh-zz/sqload"
"github.com/uh-zz/sqload/driver/mysql"
)
//go:embed sql/*
var content embed.FS
func main() {
var (
buf bytes.Buffer // sql which read from file
sqls []string // sql after parse
)
loader := sqload.New(mysql.Dialector{}) // for PostgreSQL: postgresql.Dialector{}
if err := loader.Load(&content, &buf); err != nil {
fmt.Printf("Load error: %s", err.Error())
}
if err := loader.Parse(buf.String(), &sqls); err != nil {
fmt.Printf("Parse error: %s", err.Error())
}
fmt.Printf("%+v", sqls)
// [INSERT INTO table001 (name,age) VALUES ('alice', 10);]
}
Embed the SQL file into the executable Go binary using the go:embed
directive.
This is more efficient than reading the file from the program. (you only need to distribute the executable)
In addition, there are two points of appeal.
1. validate when parse SQL read from SQL file is valid
Each Parser uses.
MySQL
Parser for TiDB, a MySQL-compatible distributed DB
https://github.com/pingcap/tidb/tree/master/parser
PostgreSQL
PostgreSQL Parser separated from CockroachDB, a distributed DB.
https://github.com/auxten/postgresql-parser
2. Can use any SQL client
Simply make the SQL read into a []string
, so you can choose any client to execute the SQL.
End
Currently, only MySQL and PostgreSQL support the syntax.
In the future, we would like to implement our own Parser and expand the support system.
Issues and Pull Requests are welcome, so please send them to us!
If you like our work, please star us on GitHub and follow us on Twitterπ
Github: https://github.com/uh-zz
Twitter: https://twitter.com/_uhzz_
Top comments (0)