sqlj

README

sqlj

A simple struct to record mapper. Builds on top of the database/sql standard library to provide basic CRUD operations for flat structs.

Note: sqlj is in active development and should not be relied upon in production.

Install

go get github.com/JoeAxon/sqlj

Basic Usage

package main

import (
    "fmt"
	"database/sql"

	_ "github.com/mattn/go-sqlite3"
	"github.com/JoeAxon/sqlj"
)

// The "db" struct tags specify the column name in the database.
// Only fields with a "db" tag will be included in queries.
type User struct {
	ID    uint   `db:"id"`
	Name  string `db:"name"`
	Email string `db:"email"`
}

func main() {
	// DB setup, this will likely be different for you.
	db, err := sql.Open("sqlite3", ":memory:")
	db.Exec("CREATE TABLE users (id integer primary key, name text, email text)")
	defer db.Close()

	// SkipOnInsert can be omitted
	jdb := DB{
		DB:           db,
		SkipOnInsert: []string{"id"},
	}

	user := User{Name: "Joe", Email: "joe@example.com"}

	// .Insert will generate and execute an "INSERT" query on the "users" table using the struct provided.
	// The newly created user will be unmarshalled into the struct.
	if err := jdb.Insert("users", &user); err != nil {
		fmt.Fatalf("Failed to insert user: %s\n", err.Error())
	}

	foundUser := User{}

	if err := jdb.Get("users", user.ID, &foundUser); err != nil {
		fmt.Fatalf("Failed to retrieve user: %s\n", err.Error())
	}

	fmt.Println("Found the user: ", foundUser)
}

Further examples of usage can be found in sqlj_test.go.

Functions

• Get - Gets and unmarshalls a record to a struct by ID.
• GetRow - Unmarshalls the result of a SQL query to a struct.
• Select - Selects all records in a table / view and unmarshalls to a slice of structs.
• SelectAll - Unmarshalls the result of a SQL query to a slice of structs.
• Insert - Inserts a struct and unmarshalls the result to the same struct.
• InsertWithOptions - Same as Insert but also allows fields to be overriden.
• Update - Update a record by ID, unmarshalling the result to a struct.
• UpdateWithOptions - Same as Update but also allows fields to be overriden.
• Delete - Deletes a record by ID.

Documentation

Index

• Constants
• type BasicField
  • func (f BasicField) GetName() string
  • func (f BasicField) GetPlaceholder(idx int) string
  • func (f BasicField) GetValue() any
  • func (f BasicField) IsLiteral() bool
• type DB
  • func (jdb *DB) Delete(table string, id any) error
  • func (jdb *DB) From(table string) QueryDB
  • func (jdb *DB) Get(table string, id any, v any) error
  • func (jdb *DB) GetRow(sql string, v any, values ...any) error
  • func (jdb *DB) Insert(table string, v any) error
  • func (jdb *DB) InsertWithOptions(table string, options Options, v any) error
  • func (jdb *DB) Select(table string, v any) error
  • func (jdb *DB) SelectAll(sql string, v any, values ...any) error
  • func (jdb *DB) Update(table string, id any, v any) error
  • func (jdb *DB) UpdateWithOptions(table string, id any, options Options, v any) error
• type DBLike
• type Delete
• type Expr
• type Field
• type Insert
• type LiteralField
  • func (f LiteralField) GetName() string
  • func (f LiteralField) GetPlaceholder(idx int) string
  • func (f LiteralField) GetValue() any
  • func (f LiteralField) IsLiteral() bool
• type NestedExpr
  • func (e NestedExpr) String() string
• type Options
• type OrderBy
• type PageOptions
• type QueryDB
  • func (q QueryDB) All(v any) error
  • func (q QueryDB) Count() (uint, error)
  • func (q QueryDB) Get(id any, v any) error
  • func (q QueryDB) One(v any) error
  • func (q QueryDB) OrWhere(expr string, values ...any) QueryDB
  • func (q QueryDB) OrWhereExpr(expr Expr, values ...any) QueryDB
  • func (q QueryDB) Order(expression string, direction string) QueryDB
  • func (q QueryDB) Page(options PageOptions, v any) error
  • func (q QueryDB) Where(expr string, values ...any) QueryDB
  • func (q QueryDB) WhereExpr(expr Expr, values ...any) QueryDB
• type Select
• type SimpleExpr
  • func (e SimpleExpr) String() string
• type Update
• type WhereClause

Constants

const (
	AND_TYPE = "AND"
	OR_TYPE  = "OR"
)

Types

type BasicField

type BasicField struct {
	Name  string
	Value any
}

This is a standard k = v field

func (BasicField) GetName

func (f BasicField) GetName() string

func (BasicField) GetPlaceholder

func (f BasicField) GetPlaceholder(idx int) string

func (BasicField) GetValue

func (f BasicField) GetValue() any

func (BasicField) IsLiteral

func (f BasicField) IsLiteral() bool

type DB

type DB struct {
	DB           DBLike
	IDColumn     string
	SkipOnInsert []string // Allows you specify db field names to skip on insert
}

func (*DB) Delete

func (jdb *DB) Delete(table string, id any) error

Deletes a row in the given table by ID.

func (*DB) From

func (jdb *DB) From(table string) QueryDB

func (*DB) Get

func (jdb *DB) Get(table string, id any, v any) error

Gets a single row from the given table with the given id. v must be a pointer to a struct.

func (*DB) GetRow

func (jdb *DB) GetRow(sql string, v any, values ...any) error

Gets a single row using the supplied SQL and values. The result will be marshalled into the v struct. v must be a pointer to a struct.

func (*DB) Insert

func (jdb *DB) Insert(table string, v any) error

Inserts a row into the specified `table` with the given struct. The new row is returned and marshalled into v. v must be a pointer to a struct.

func (*DB) InsertWithOptions

func (jdb *DB) InsertWithOptions(table string, options Options, v any) error

Inserts a row into the specified `table` with the given struct. The new row is returned and marshalled into v. An Options type with a slice of Fields can be included to override any values in v. v must be a pointer to a struct.

func (*DB) Select

func (jdb *DB) Select(table string, v any) error

Selects all rows from a given table. The results will be marshalled into the v slice of structs. v must be a pointer to a slice of structs.

func (*DB) SelectAll

func (jdb *DB) SelectAll(sql string, v any, values ...any) error

Selects all rows using the supplied SQL and values. The results will be marshalled into the v slice of structs. v must be a pointer to a slice of structs.

func (*DB) Update

func (jdb *DB) Update(table string, id any, v any) error

Updates a row in the specified `table` using the given struct. The updated row is returned and marshalled into v. v must be a pointer to a struct.

func (*DB) UpdateWithOptions

func (jdb *DB) UpdateWithOptions(table string, id any, options Options, v any) error

Updates a row in the specified `table` using the given struct. The updated row is returned and marshalled into v. An Options type with a slice of Fields can be included to override any values in v. v must be a pointer to a struct.

type DBLike

type DBLike interface {
	Exec(query string, args ...any) (sql.Result, error)
	Query(query string, args ...any) (*sql.Rows, error)
	QueryRow(query string, args ...any) *sql.Row
}

Represents a DB-like interface. This only specifies the methods used by sqlj. Both DB and Tx in the database/sql standard library fulfill this contract.

type Delete

type Delete struct {
	From  string
	Where []WhereClause
}

type Expr

type Expr interface {
	String() string
}

type Field

type Field interface {
	GetName() string
	GetValue() any

	// For an insert this is the $1 in the VALUES list.
	// For an update this is the $1 in the SET expression.
	GetPlaceholder(idx int) string

	// This isn't ideal but will work for now
	IsLiteral() bool
}

type Insert

type Insert struct {
	From      string
	Fields    []Field
	Returning []string
}

type LiteralField

type LiteralField struct {
	Name  string
	Value string
}

This is useful if you want to call a function. An example would be LiteralField{Name: "created_at", Value: "now()"}.

func (LiteralField) GetName

func (f LiteralField) GetName() string

func (LiteralField) GetPlaceholder

func (f LiteralField) GetPlaceholder(idx int) string

func (LiteralField) GetValue

func (f LiteralField) GetValue() any

func (LiteralField) IsLiteral

func (f LiteralField) IsLiteral() bool

type NestedExpr

type NestedExpr struct {
	// contains filtered or unexported fields
}

func (NestedExpr) String

func (e NestedExpr) String() string

type Options

type Options struct {
	Fields []Field
}

type OrderBy

type OrderBy struct {
	Expression string
	Direction  string
}

type PageOptions

type PageOptions struct {
	PageNumber uint
	PageSize   uint
	Order      []OrderBy
}

type QueryDB

type QueryDB struct {
	DB           *DB
	From         string
	OrderClauses []OrderBy
	WhereClauses []WhereClause
	WhereValues  []any
}

func (QueryDB) All

func (q QueryDB) All(v any) error

func (QueryDB) Count

func (q QueryDB) Count() (uint, error)

Counts the number of records in the table. This is intended to be used in conjunction with .Page.

func (QueryDB) Get

func (q QueryDB) Get(id any, v any) error

Get a record by ID. This will ignore any previous calls to .Where and .OrWhere

func (QueryDB) One

func (q QueryDB) One(v any) error

Get a single record from the given table.

func (QueryDB) OrWhere

func (q QueryDB) OrWhere(expr string, values ...any) QueryDB

func (QueryDB) OrWhereExpr

func (q QueryDB) OrWhereExpr(expr Expr, values ...any) QueryDB

func (QueryDB) Order

func (q QueryDB) Order(expression string, direction string) QueryDB

func (QueryDB) Page

func (q QueryDB) Page(options PageOptions, v any) error

Selects a page of data from the given table. The options parameter allows you to specify the page, page size and order by clauses. The results will be marshalled into the v slice of structs. v must be a pointer to a slice of structs.

func (QueryDB) Where

func (q QueryDB) Where(expr string, values ...any) QueryDB

func (QueryDB) WhereExpr

func (q QueryDB) WhereExpr(expr Expr, values ...any) QueryDB

type Select

type Select struct {
	From    string
	Where   []WhereClause
	OrderBy []OrderBy
	Offset  bool
	Limit   bool
	Columns []string
}

type SimpleExpr

type SimpleExpr struct {
	// contains filtered or unexported fields
}

func (SimpleExpr) String

func (e SimpleExpr) String() string

type Update

type Update struct {
	From      string
	Fields    []Field
	Returning []string
}

type WhereClause

type WhereClause struct {
	Type string
	Expr Expr
}