README
¶
sqload
Personally, I don't like writing SQL code inside the Go source files, so I made this simple and thoroughly tested module to load SQL queries from files.
This library is inspired by Yesql.
How to use it?
Add sqload to your go.mod file running the following command:
$ go get github.com/midir99/sqload
Each SQL query must include a comment at the beginning; the comment must be something like:
-- query: FindCatById
SELECT * FROM cat WHERE id = :id;
This comment is mandatory so the loader can match the name of your query with the tagged struct field where the SQL code of your query will be stored. In this case, the struct would look like this:
struct {
FindCatById string `query:"FindCatById"`
}
Load SQL code from strings
package main
import (
"fmt"
"github.com/midir99/sqload"
)
var Q = sqload.MustLoadFromString[struct {
FindUserById string `query:"FindUserById"`
UpdateFirstNameById string `query:"UpdateFirstNameById"`
DeleteUserById string `query:"DeleteUserById"`
}](`
-- query: FindUserById
SELECT first_name,
last_name,
dob,
email
FROM user
WHERE id = :id;
-- query: UpdateFirstNameById
UPDATE user
SET first_name = 'Ernesto'
WHERE id = :id;
-- query: DeleteUserById
DELETE FROM user
WHERE id = :id;
`)
func main() {
fmt.Printf("- FindUserById\n%s\n\n", Q.FindUserById)
fmt.Printf("- UpdateFirstNameById\n%s\n\n", Q.UpdateFirstNameById)
fmt.Printf("- DeleteUserById\n%s\n\n", Q.DeleteUserById)
}
Load SQL code from files using embed
Using the module embed to load your SQL files into strings and then passing those to sqload functions is a convenient approach.
File queries.sql:
-- query: FindUserById
-- Finds a user by its id (optionally, you can add comments to describe your queries;
-- sqload won't include these comments on the final query string).
SELECT first_name,
last_name,
dob,
email
FROM user
WHERE id = :id;
-- query: UpdateFirstNameById
UPDATE user
SET first_name = 'Ernesto'
WHERE id = :id;
-- query: DeleteUserById
DELETE FROM user
WHERE id = :id;
File main.go:
package main
import (
_ "embed"
"fmt"
"github.com/midir99/sqload"
)
//go:embed queries.sql
var sqlCode string
var Q = sqload.MustLoadFromString[struct {
FindUserById string `query:"FindUserById"`
UpdateFirstNameById string `query:"UpdateFirstNameById"`
DeleteUserById string `query:"DeleteUserById"`
}](sqlCode)
func main() {
fmt.Printf("- FindUserById\n%s\n\n", Q.FindUserById)
fmt.Printf("- UpdateFirstNameById\n%s\n\n", Q.UpdateFirstNameById)
fmt.Printf("- DeleteUserById\n%s\n\n", Q.DeleteUserById)
}
Load SQL code from directories containing .sql files using embed
Lets say you have a directory containing your SQL files:
.
├── go.mod
├── main.go
└── sql <- this one
├── cats.sql
└── users.sql
File sql/cats.sql:
-- query: CreatePsychoCat
-- Run, run, run, run, run, run, run away, oh-oh-oh!
INSERT INTO Cat (name, color) VALUES ('Puca', 'Orange');
File sql/users.sql:
-- query: DeleteUserById
DELETE FROM user WHERE id = :id;
File main.go:
package main
import (
"embed"
"fmt"
"github.com/midir99/sqload"
)
//go:embed sql
var fsys embed.FS
var Q = sqload.MustLoadFromFS[struct {
CreatePsychoCat string `query:"CreatePsychoCat"`
DeleteUserById string `query:"DeleteUserById"`
}](fsys)
func main() {
fmt.Printf("- CreatePsychoCat\n%s\n\n", Q.CreatePsychoCat)
fmt.Printf("- DeleteUserById\n%s\n\n", Q.DeleteUserById)
}
Check more examples in the official documentation: https://pkg.go.dev/github.com/midir99/sqload
Documentation
¶
Overview ¶
Package sqload provides functions to load SQL code from strings or .sql files into tagged struct fields.
Its usage is very straightforward; let's suppose you have the following SQL file:
File queries.sql:
-- query: FindUserById
-- Finds a user by its id.
SELECT first_name,
last_name,
dob,
email
FROM user
WHERE id = :id;
-- query: UpdateFirstNameById
UPDATE user
SET first_name = 'Ernesto'
WHERE id = :id;
-- query: DeleteUserById
-- Deletes a user by its id.
DELETE FROM user
WHERE id = :id;
You could load the SQL code of those queries into strings using the following:
File main.go:
package main
import (
_ "embed"
"fmt"
"github.com/midir99/sqload"
)
//go:embed queries.sql
var sqlCode string
var Q = sqload.MustLoadFromString[struct {
FindUserById string `query:"FindUserById"`
UpdateFirstNameById string `query:"UpdateFirstNameById"`
DeleteUserById string `query:"DeleteUserById"`
}](sqlCode)
func main() {
fmt.Printf("- FindUserById\n%s\n\n", Q.FindUserById)
fmt.Printf("- UpdateFirstNameById\n%s\n\n", Q.UpdateFirstNameById)
fmt.Printf("- DeleteUserById\n%s\n\n", Q.DeleteUserById)
}
The module maps the fields of your struct and the queries from the SQL file using the query tag (in the struct):
`query:"NameOfYourQuery"`
And the query comment (in the SQL code):
-- query: NameOfYourQuery
Index ¶
- func ExtractQueryMap(sql string) (map[string]string, error)
- func LoadFromDir[V Struct](dirname string) (*V, error)
- func LoadFromFS[V Struct](fsys fs.FS) (*V, error)
- func LoadFromFile[V Struct](filename string) (*V, error)
- func LoadFromString[V Struct](s string) (*V, error)
- func MustLoadFromDir[V Struct](dirname string) *V
- func MustLoadFromFS[V Struct](fsys fs.FS) *V
- func MustLoadFromFile[V Struct](filename string) *V
- func MustLoadFromString[V Struct](s string) *V
- type Struct
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func ExtractQueryMap ¶ added in v1.1.0
ExtractQueryMap extracts the SQL code from the string and returns a map containing the queries. The query name is the key in each map entry, and the SQL code is its value.
package main
import (
"fmt"
"os"
"github.com/midir99/sqload"
)
func main() {
q, err := sqload.ExtractQueryMap(`
-- query: FindUserById
SELECT first_name,
last_name,
dob,
email
FROM user
WHERE id = :id;
-- query: DeleteUserById
DELETE FROM user
WHERE id = :id;
`)
if err != nil {
fmt.Printf("Unable to load SQL queries: %s\n", err)
os.Exit(1)
}
if findUserById, found := q["FindUserById"]; found {
fmt.Printf("- FindUserById\n%s\n\n", findUserById)
}
for k, v := range q {
fmt.Printf("- %s\n%s\n\n", k, v)
}
}
func LoadFromDir ¶ added in v1.0.1
LoadFromDir loads the SQL code from all the .sql files in the directory dirname (recursively) and returns a pointer to a struct. Each struct field will contain the SQL query code it was tagged with.
If some query has an invalid name in the string or is not found in the string, it will return a nil pointer and an error.
If the directory can not be read or does not exist, it will return a nil pointer and an error.
If any .sql file can not be read, it will return a nil pointer and an error.
Project directory:
.
├── go.mod
├── main.go
└── sql
├── cats.sql
└── users.sql
File sql/cats.sql:
-- query: CreatePsychoCat
INSERT INTO Cat (name, color) VALUES ('Puca', 'Orange');
File sql/users.sql:
-- query: DeleteUserById DELETE FROM user WHERE id = :id;
File main.go:
package main
import (
"fmt"
"os"
"github.com/midir99/sqload"
)
func main() {
q, err := sqload.LoadFromDir[struct {
CreatePsychoCat string `query:"CreatePsychoCat"`
DeleteUserById string `query:"DeleteUserById"`
}]("sql")
if err != nil {
fmt.Printf("Unable to load SQL queries: %s\n", err)
os.Exit(1)
}
fmt.Printf("- CreatePsychoCat\n%s\n\n", q.CreatePsychoCat)
fmt.Printf("- DeleteUserById\n%s\n\n", q.DeleteUserById)
}
func LoadFromFS ¶ added in v1.0.1
LoadFromFS loads the SQL code from all the .sql files in the fsys file system (recursively) and returns a pointer to a struct. Each struct field will contain the SQL query code it was tagged with.
If some query has an invalid name in the string or is not found in the string, it will return a nil pointer and an error.
If the fsys can not be read or does not exist, it will return a nil pointer and an error.
If any .sql file can not be read, it will return a nil pointer and an error.
Project directory:
.
├── go.mod
├── main.go
└── sql
├── cats.sql
└── users.sql
File sql/cats.sql:
-- query: CreatePsychoCat
INSERT INTO Cat (name, color) VALUES ('Puca', 'Orange');
File sql/users.sql:
-- query: DeleteUserById DELETE FROM user WHERE id = :id;
File main.go:
package main
import (
"embed"
"fmt"
"os"
"github.com/midir99/sqload"
)
//go:embed sql/*.sql
var fsys embed.FS
func main() {
q, err := sqload.LoadFromFS[struct {
CreatePsychoCat string `query:"CreatePsychoCat"`
DeleteUserById string `query:"DeleteUserById"`
}](fsys)
if err != nil {
fmt.Printf("Unable to load SQL queries: %s\n", err)
os.Exit(1)
}
fmt.Printf("- CreatePsychoCat\n%s\n\n", q.CreatePsychoCat)
fmt.Printf("- DeleteUserById\n%s\n\n", q.DeleteUserById)
}
func LoadFromFile ¶ added in v1.0.1
LoadFromFile loads the SQL code from the file filename and returns a pointer to a struct. Each struct field will contain the SQL query code it was tagged with.
If some query has an invalid name in the string or is not found in the string, it will return a nil pointer and an error.
If the file can not be read or does not exist, it will return a nil pointer and an error.
File queries.sql:
-- query: FindUserById
SELECT first_name,
last_name,
dob,
email
FROM user
WHERE id = :id;
-- query: UpdateFirstNameById
UPDATE user
SET first_name = 'Ernesto'
WHERE id = :id;
-- query: DeleteUserById
DELETE FROM user
WHERE id = :id;
File main.go:
package main
import (
"fmt"
"os"
"github.com/midir99/sqload"
)
func main() {
q, err := sqload.LoadFromFile[struct {
FindUserById string `query:"FindUserById"`
UpdateFirstNameById string `query:"UpdateFirstNameById"`
DeleteUserById string `query:"DeleteUserById"`
}]("queries.sql")
if err != nil {
fmt.Printf("Unable to load SQL queries: %s\n", err)
os.Exit(1)
}
fmt.Printf("- FindUserById\n%s\n\n", q.FindUserById)
fmt.Printf("- UpdateFirstNameById\n%s\n\n", q.UpdateFirstNameById)
fmt.Printf("- DeleteUserById\n%s\n\n", q.DeleteUserById)
}
func LoadFromString ¶ added in v1.0.1
LoadFromString loads the SQL code from the string and returns a pointer to a struct. Each struct field will contain the SQL query code it was tagged with.
If some query has an invalid name in the string or is not found in the string, it will return a nil pointer and an error.
package main
import (
"fmt"
"os"
"github.com/midir99/sqload"
)
func main() {
q, err := sqload.LoadFromString[struct {
FindUserById string `query:"FindUserById"`
UpdateFirstNameById string `query:"UpdateFirstNameById"`
DeleteUserById string `query:"DeleteUserById"`
}](`
-- query: FindUserById
SELECT first_name,
last_name,
dob,
email
FROM user
WHERE id = :id;
-- query: UpdateFirstNameById
UPDATE user
SET first_name = 'Ernesto'
WHERE id = :id;
-- query: DeleteUserById
DELETE FROM user
WHERE id = :id;
`)
if err != nil {
fmt.Printf("Unable to load SQL queries: %s\n", err)
os.Exit(1)
}
fmt.Printf("- FindUserById\n%s\n\n", q.FindUserById)
fmt.Printf("- UpdateFirstNameById\n%s\n\n", q.UpdateFirstNameById)
fmt.Printf("- DeleteUserById\n%s\n\n", q.DeleteUserById)
}
func MustLoadFromDir ¶ added in v1.0.1
MustLoadFromDir is like LoadFromDir but panics if any error occurs. It simplifies the safe initialization of global variables holding struct pointers containing SQL queries.
func MustLoadFromFS ¶ added in v1.0.1
MustLoadFromFS is like LoadFromFS but panics if any error occurs. It simplifies the safe initialization of global variables holding struct pointers containing SQL queries.
func MustLoadFromFile ¶ added in v1.0.1
MustLoadFromFile is like LoadFromFile but panics if any error occurs. It simplifies the safe initialization of global variables holding struct pointers containing SQL queries.
func MustLoadFromString ¶ added in v1.0.1
MustLoadFromString is like LoadFromString but panics if any error occurs. It simplifies the safe initialization of global variables holding struct pointers containing SQL queries.
Source Files