sqlp is a Go package extending database/sql
to make it a little easier to use by adding some features that may come in handy for you.
The key features are:
- Bulk inserting data in structs into a table
- Reading rows into structs, maps or slices
- Easier binding of values to unnamed/named placeholders
- Go 1.15 or newer
go get github.com/kaboc/sqlp
Basic usage is mostly the same as that of database/sql
, so only major differences are described in this section.
MySQL
CREATE TABLE user (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(32) NOT NULL,
age tinyint(3) unsigned NOT NULL,
recorded_at datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
PostgreSQL
create table "user" (
id serial not null primary key,
name varchar(32) not null,
age smallint not null,
recorded_at timestamp
);
Import sqlp
and other necessary packages including a database driver like go-sql-driver/mysql
.
import (
_ "github.com/go-sql-driver/mysql"
"github.com/kaboc/sqlp"
)
db, err := sqlp.Open("mysql", "user:pw@tcp(host:3306)/dbname")
Use sqlp.Init()
instead if there is a connection already opened by database/sql
's Open()
.
sqlDB, err := sql.Open("mysql", "user:pw@tcp(host:3306)/dbname")
db := sqlp.Init(sqlDB)
You can bulk insert multiple rows easily using a slice of structs containing sets of data to be inserted.
type tUser struct {
Name string
Age int
RecordedAt sqlp.NullTime `col:"recorded_at"`
}
now := sqlp.NullTime{Time: time.Now(), Valid: true}
data := []tUser{
{Name: "User1", Age: 22, RecordedAt: now},
{Name: "User2", Age: 27, RecordedAt: sqlp.NullTime{}},
{Name: "User3", Age: 31, RecordedAt: now},
}
res, err := db.Insert("user", data)
if err != nil {
log.Fatal(err)
}
cnt, _ := res.RowsAffected()
fmt.Printf("%d rows were affected", cnt) // 3 rows were affected
Struct fields need to be capitalized so that sqlp can access them.
A tag is necessary only when the field name is not the same as the column name.
In the above example, col:"age"
can be omitted since column names are case insensitive in MySQL by default and Age
and age
are not distinguished.
Values are processed via placeholders internally and escaped to be safe. There is no need to worry about SQL injection.
-
If the table name is a reserved keyword, it has to be enclosed with back quotes, double quotes, etc. depending on the DBMS. Below is an example for PostgreSQL.
res, err := db.Insert(`"user"`, data)
type tUser struct {
Name string
Age int
RecordedAt sqlp.NullTime
}
rows, err := db.Query(`SELECT name, age, recorded_at FROM user`)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var u tUser
err = rows.ScanToStruct(&u)
if err != nil {
log.Fatal(err)
}
fmt.Printf("%s: %d yo [%s, %t]\n", u.Name, u.Age, u.RecordedAt.Time, u.RecordedAt.Valid)
}
// User1: 22 yo [2018-06-24 01:23:45 +0000 UTC, true]
// User2: 27 yo [0001-01-01 00:00:00 +0000 UTC, false]
// User3: 31 yo [2018-06-24 01:23:45 +0000 UTC, true]
Columns are mapped to corresponding struct fields.
Here, unlike the previous Insert example, the RecordedAt
field does not have the `col:"recorded_at`
tag.
This is because RecordedAt
is regarded as identical to recorded_at
by case-insensitive comparison after underscores are removed.
for rows.Next() {
u, err := rows.ScanToMap()
if err != nil {
log.Fatal(err)
}
fmt.Printf("%s: %s yo [%s]\n", u["name"], u["age"], u["recorded_at"])
}
// User1: 22 yo [2018-06-24T01:23:45+00:00]
// User2: 27 yo []
// User3: 31 yo [2018-06-24T01:23:45+00:00]
for rows.Next() {
u, err := rows.ScanToSlice()
if err != nil {
log.Fatal(err)
}
fmt.Printf("%s: %s yo, [%s]\n", u[0], u[1], u[2])
}
// User1: 22 yo, [2018-06-24T01:23:45+00:00]
// User2: 27 yo, []
// User3: 31 yo, [2018-06-24T01:23:45+00:00]
type tUser struct {
Name string
Age int
RecordedAt sqlp.NullTime
}
var u []tUser
err := db.SelectToStruct(&u, `SELECT name, age, recorded_at FROM user`)
fmt.Println(u)
This saves you the bother of making a query and then scanning each row. It is convenient, but be careful not to use up huge amounts of memory by fetching too many rows at a time.
u, err := db.SelectToMap(`SELECT name, age, recorded_at FROM user`)
fmt.Println(u)
u, err := db.SelectToSlice(`SELECT name, age, recorded_at FROM user`)
fmt.Println(u)
sqlp provides both named and unnamed placeholders.
This is quite similar to database/sql
's placeholder, with only several differences:
- Only
?
is used regardless of the type of DBMS or the database driver.$1
or other types are not available. WHERE name IN (?, ?)
can be replaced withWHERE name IN ?[2]
.- Binding values are passed as literals, variables, slices, or combinations of these.
Example:
q := `SELECT name, age, recorded_at FROM user
WHERE name LIKE ? AND age IN ?[2]`
This is internally converted to the next statement:
SELECT name, age, recorded_at FROM user
WHERE name LIKE ? AND age IN (?,?)
The following three ways of binding values are all acceptable.
u, err := db.SelectToMap(q, "User%", 22, 31)
b1 := "User%"
b2 := []interface{}{22, 31}
u, err := db.SelectToMap(q, b1, b2)
b := []interface{}{"User%", 22, 31}
u, err := db.SelectToMap(q, b)
//u, err := db.SelectToMap(q, b...) // This works fine too.
If the DBMS or the database driver that you use is not compatible with the ?
type of placeholder, you will need to instruct sqlp to use another one.
For example, PostgreSQL uses $1
instead of ?
.
SELECT name, age, recorded_at FROM user
WHERE name LIKE $1 AND age IN ($2,$3)
This type of placeholder is defined as the constant placeholder.Dollar
.
If you specify it by placeholder.SetType()
, sqlp converts ?
to $1
internally so you can use ?
in your query.
placeholder.SetType(placeholder.Dollar)
q := "SELECT * FROM user WHERE name LIKE ? AND age IN ?[2]"
u, err := db.SelectToMap(q, "User%", 22, 31)
Another way is to pass your custom conversion function to placeholder.SetConvertFunc()
.
You should be able to make do with this even if definition of your required type is missing in sqlp.
placeholder.SetConvertFunc(func(query *string) {
cnt := strings.Count(*query, "?")
for i := 1; i <= cnt; i++ {
*query = strings.Replace(*query, "?", "$"+strconv.Itoa(i), 1)
}
})
This is radically different from database/sql
's named placeholder.
Here is an example similar to the previous one.
q := `SELECT name, age, recorded_at FROM user
WHERE name LIKE :like AND age IN :age[2]`
A placeholder name must start with a colon followed by either of or a combination of alphabets, numbers, or underscores.
:like
and :age[2]
are the named placeholders.
They are internally converted to unnamed ones as below:
SELECT name, age, recorded_at FROM user
WHERE name LIKE ? AND age IN (?,?)
Values are passed only in the form of a single map.
:XXXX[N]
requires a slice of interface{} with N numbers of elements.
b := map[string]interface{}{
"like": "User%",
"age": []interface{}{22, 31},
}
u, err := db.SelectToMap(q, b)
The same applies here as for placeholder.SetType()
or placeholder.SetConvertFunc()
.