Generic SQL

Tiny wrapper built around standard Go SQL library and SQLx with generic support.

Installation

go get -v github.com/reddec/gsql

Examples

Schema for the example

CREATE TABLE book
(
    id     INTEGER NOT NULL PRIMARY KEY,
    title  TEXT    NOT NULL,
    author TEXT,
    year   INTEGER NOT NULL
)

Type in Go

type Book struct {
    ID     int64
    Title  string
    Author string
    Year   int
}

Initialization

// ...
conn, err := sqlx.Open("sqlite", "database.sqlite")
// ...

Get

Get single book by ID

book, err := gsql.Get[Book](ctx, conn, "SELECT * FROM book WHERE id = ?", 1234)

List

List books by author

books, err := gsql.List[Book](ctx, conn, "SELECT * FROM book WHERE author = ?", "O'Really")

Iterate

Iterate each book one by one

iter := gsql.Iterate[Book](ctx, conn, "SELECT * FROM book LIMIT ?", 100)
defer iter.Close()
for iter.Next() {
    book, err := iter.Get()
    // ...
}
if iter.Err() != nil {
    panic(iter.Err()) // use normal handling in real application
}

LazyGet

Save query which returns one object and execute it later

getBooks := gsql.LazyGet[Book](conn, "SELECT * FROM book WHERE id = ?", 123)
// ...
book, err := getBooks(ctx) // can be called many times

LazyList

Save query which returns multiple objects and execute it later

listBooks := gsql.LazyList[Book](conn, "SELECT * FROM book")
// ...
books, err := listBooks(ctx) // can be called many times

CachedGet

Save query which returns one object and execute it later. Once query executed, result will be cached until Invalidate or Refresh called.

cache := gsql.CachedGet[Book](conn, "SELECT * FROM book WHERE id = ?", 123)
book, err := cache.Get(ctx) // first time it will execute the query
//...
book2, err := cache.Get(ctx) // second time it will return cached information
//...
cache.Invalidate() // reset cache, the following Get will again execute the query

CachedList

Save query which returns multiple objects and execute it later. Once query executed, result will be cached until Invalidate or Refresh called.

cache := gsql.CachedList[Book](conn, "SELECT * FROM book WHERE id = ?", 123)
books, err := cache.Get(ctx) // first time it will execute the query
//...
books2, err := cache.Get(ctx) // second time it will return cached information
//...
cache.Invalidate() // reset cache, the following Get will again execute the query

JSON

Simple generic wrapper around any JSON-serializable value: JSON[T].

Support both reading (scanner) and writing (Value).

Example

Assuming schema

CREATE TABLE book
(
    id     INTEGER NOT NULL PRIMARY KEY,
    title  TEXT    NOT NULL,
    author TEXT,
    year   INTEGER NOT NULL,
    meta   JSONB NOT NULL
)

Go code

type Metadata struct {
    Zip int
}

type Book struct {
    ID     int64
    Title  string
    Author string
    Year   int
    Meta   JSON[Metadata]
}