# README
sqlutil
A collection of helpers to deal with database.
Example:
package main
import (
"context"
"database/sql"
"fmt"
"log"
"github.com/allisson/sqlutil"
_ "github.com/lib/pq"
)
type Player struct {
ID int `db:"id"`
Name string `db:"name" fieldtag:"insert,update"`
Age int `db:"age" fieldtag:"insert,update"`
}
func main() {
// Run a database with docker: docker run --name test --restart unless-stopped -e POSTGRES_USER=user -e POSTGRES_PASSWORD=password -e POSTGRES_DB=sqlutil -p 5432:5432 -d postgres:14-alpine
// Connect to database
db, err := sql.Open("postgres", "postgres://user:password@localhost/sqlutil?sslmode=disable")
if err != nil {
log.Fatal(err)
}
err = db.Ping()
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Create table
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS players(
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
age INTEGER NOT NULL
)
`)
if err != nil {
log.Fatal(err)
}
// Insert players
r9 := Player{
Name: "Ronaldo Fenômeno",
Age: 44,
}
r10 := Player{
Name: "Ronaldinho Gaúcho",
Age: 41,
}
flavour := sqlutil.PostgreSQLFlavor
tag := "insert" // will use fields with fieldtag:"insert"
ctx := context.Background()
if err := sqlutil.Insert(ctx, db, sqlutil.PostgreSQLFlavor, tag, "players", &r9); err != nil {
log.Fatal(err)
}
if err := sqlutil.Insert(ctx, db, sqlutil.PostgreSQLFlavor, tag, "players", &r10); err != nil {
log.Fatal(err)
}
// Get player
findOptions := sqlutil.NewFindOptions(flavour).WithFilter("name", r10.Name)
if err := sqlutil.Get(ctx, db, "players", findOptions, &r10); err != nil {
log.Fatal(err)
}
findOptions = sqlutil.NewFindOptions(flavour).WithFilter("name", r9.Name)
if err := sqlutil.Get(ctx, db, "players", findOptions, &r9); err != nil {
log.Fatal(err)
}
// Select players
players := []*Player{}
findAllOptions := sqlutil.NewFindAllOptions(flavour).WithLimit(10).WithOffset(0).WithOrderBy("name asc")
if err := sqlutil.Select(ctx, db, "players", findAllOptions, &players); err != nil {
log.Fatal(err)
}
for _, p := range players {
fmt.Printf("%#v\n", p)
}
// Update player
tag = "update" // will use fields with fieldtag:"update"
r10.Name = "Ronaldinho Bruxo"
if err := sqlutil.Update(ctx, db, sqlutil.PostgreSQLFlavor, tag, "players", r10.ID, &r10); err != nil {
log.Fatal(err)
}
// Delete player
if err := sqlutil.Delete(ctx, db, sqlutil.PostgreSQLFlavor, "players", r9.ID); err != nil {
log.Fatal(err)
}
}
Options for FindOptions and FindAllOptions:
package main
import (
"github.com/allisson/sqlutil"
_ "github.com/lib/pq"
)
func main() {
findOptions := sqlutil.NewFindOptions(sqlutil.PostgreSQLFlavor).
WithFields([]string{"id", "name"}). // Return only id and name fields
WithFilter("id", 1). // WHERE id = 1
WithFilter("id", nil). // WHERE id IS NULL
WithFilter("id.in", "1,2,3"). // WHERE id IN (1, 2, 3)
WithFilter("id.notin", "1,2,3"). // WHERE id NOT IN ($1, $2, $3)
WithFilter("id.not", 1). // WHERE id <> 1
WithFilter("id.gt", 1). // WHERE id > 1
WithFilter("id.gte", 1). // WHERE id >= 1
WithFilter("id.lt", 1). // WHERE id < 1
WithFilter("id.lte", 1). // WHERE id <= 1
WithFilter("id.like", 1). // WHERE id LIKE 1
WithFilter("id.null", true). // WHERE id.null IS NULL
WithFilter("id.null", false) // WHERE id.null IS NOT NULL
findAllOptions := sqlutil.NewFindAllOptions(sqlutil.PostgreSQLFlavor).
WithFields([]string{"id", "name"}). // Return only id and name fields
WithFilter("id", 1). // WHERE id = 1
WithFilter("id", nil). // WHERE id IS NULL
WithFilter("id.in", "1,2,3"). // WHERE id IN (1, 2, 3)
WithFilter("id.notin", "1,2,3"). // WHERE id NOT IN ($1, $2, $3)
WithFilter("id.not", 1). // WHERE id <> 1
WithFilter("id.gt", 1). // WHERE id > 1
WithFilter("id.gte", 1). // WHERE id >= 1
WithFilter("id.lt", 1). // WHERE id < 1
WithFilter("id.lte", 1). // WHERE id <= 1
WithFilter("id.like", 1). // WHERE id LIKE 1
WithFilter("id.null", true). // WHERE id.null IS NULL
WithFilter("id.null", false). // WHERE id.null IS NOT NULL
WithLimit(10). // LIMIT 10
WithOffset(0). // OFFSET 0
WithOrderBy("name asc"). // ORDER BY name asc
WithForUpdate("SKIP LOCKED") // FOR UPDATE SKIP LOCKED
}
# Functions
Delete is a high-level function that calls sqlquery.DeleteQuery and db.ExecContext.
DeleteWithOptions is a high-level function that calls sqlquery.DeleteWithOptionsQuery and db.ExecContext.
Get is a high-level function that calls sqlquery.FindQuery and scany sqlscan.Get function.
Insert is a high-level function that calls sqlquery.InsertQuery and db.ExecContext.
NewDeleteOptions returns a DeleteOptions.
NewFindAllOptions returns a FindAllOptions.
NewFindOptions returns a FindOptions.
NewUpdateOptions returns a UpdateOptions.
Select is a high-level function that calls sqlquery.FindAllQuery and scany sqlscan.Select function.
Update is a high-level function that calls sqlquery.UpdateQuery and db.ExecContext.
UpdateWithOptions is a high-level function that calls sqlquery.UpdateWithOptionsQuery and db.ExecContext.
# Variables
No description provided by the author
No description provided by the author
No description provided by the author
# Interfaces
Querier is a abstraction over *sql.DB/*sql.Conn/*sql.Tx.
# Type aliases
No description provided by the author
No description provided by the author
No description provided by the author
No description provided by the author
No description provided by the author