Embed hard-coded SQL into binaries for a cleaner look!

Reo Uehara — Sun, 12 Feb 2023 16:46:02 +0000

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_

Forem: Reo Uehara