crud

package module
v2.5.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Nov 29, 2021 License: MIT Imports: 11 Imported by: 0

README

CRUD

A minimalistic relational database library for Go.

Table of Contents

Install

$ go get github.com/azer/crud/v2

Initialize

import (
  "github.com/azer/crud/v2"
  _ "github.com/go-sql-driver/mysql"
)

var DB *crud.DB

func init () {
  var err error
  DB, err = crud.Connect("mysql", os.Getenv("DATABASE_URL"))
  err = DB.Ping()
}

Define

type User struct {
  Id int `sql:"auto-increment primary-key"`
  FirstName string
  LastName string
  ProfileId int
}

type Profile struct {
  Id int `sql:"auto-increment primary-key"`
  Bio string `sql:"text"`
}

CRUD will automatically convert column names from "FirstName" (CamelCase) to "first_name" (snake_case) for you. You can still choose custom names though;

type Post struct {
  Slug string `sql:"name=slug_id varchar(255) primary-key required"`
}

If no primary key is specified, CRUD will look for a field named "Id" with int type, and set it as auto-incrementing primary-key field.

Create & Drop Tables

CreateTables takes list of structs and makes sure they exist in the database.

err := DB.CreateTables(User{}, Profile{})

err := DB.DropTables(User{}, Profile{})
Reset Tables

Shortcut for dropping and creating tables.

err := DB.ResetTables(User{}, Profile{})
SQL Options

CRUD tries to be smart about figuring out the best SQL options for your structs, and lets you choose manually, too. For example;

type Tweet struct {
 Text string `sql:"varchar(140) required name=tweet"`
}

Above example sets the type of the Text column as varchar(140), makes it required (NOT NULL) and changes the column name as tweet.

Here is the list of the options that you can pass;

  • Types: int, bigint, varchar, text, date, time, timestamp
  • auto-increment / autoincrement / auto_increment
  • primary-key / primarykey / primary_key
  • required
  • default='?'
  • name=?
  • table-name=?

If you'd like a struct field to be ignored by CRUD, choose - as options:

type Foo struct {
 IgnoreMe string `sql:"-"`
}

Create

Simply pass a struct. It can be pointer or not.

user := &User{1, "Foo", "Bar", 1}
err := DB.Create(user)

CreateAndRead

Create a row, and read it back from the DB. The values of the struct you passed get resetted to whatever the corresponding DB row has. In the other words, CreateAndRead creates, and reads. So you got fields generated by the DB scanned to your struct, like ID.

Make sure passing a pointer.

user := User{
  FirstName:"Foo"
}

err := DB.CreateAndRead(&user)

user.Id
// => 123

Read

You can read single/multiple rows, or custom values, with the Read method.

Reading a single row:

Pass your struct's pointer, and a query;

user := &User{}
err := DB.Read(user, "SELECT * FROM users WHERE id = ?", 1)
// => SELECT * FROM users WHERE id = 1

fmt.Println(user.Name)
// => Foo
Reading multiple rows:
users := []*User{}

err := DB.Read(&users, "SELECT * FROM users")
// => SELECT * FROM users

fmt.Println(len(users))
// => 10
Scanning to custom values:
names := []string{}
err := DB.Read(&names, "SELECT name FROM users")

name := ""
err := DB.Read(&name, "SELECT name FROM users WHERE id=1")

totalUsers := 0
err := DB.Read(&totalUsers, "SELECT COUNT(id) FROM users"

Update

Updates matching row in database, returns sql.ErrNoRows nothing matched.

user := &User{}
err := DB.Read(user, "SELECT * FROM users WHERE id = ?", 1)

user.Name = "Yolo"
err := DB.Update(user)

Delete

Deletes matching row in database, returns sql.ErrNoRows nothing matched.

err := DB.Delete(&User{
  Id: 1
})

Transactions

Use Begin method of a crud.DB instance to create a new transaction. Each transaction will provide you following methods;

  • Commit
  • Rollback
  • Exec
  • Query
  • Create
  • Read
  • Update
  • Delete
tx, err := DB.Begin()

err := tx.Create(&User{
  Name: "yolo"
})

err := tx.Delete(&User{
  Id: 123
})

err := tx.Commit()

Logs

If you want to see crud's internal logs, specify crud in the LOG environment variable when you run your app. For example;

$ LOG=crud go run myapp.go

(More info about how crud's logging work)

Custom Queries

result, err := DB.Query("DROP DATABASE yolo") // or .Exec

Running Tests

DATABASE_URL="?" go test ./...

What's Missing?

  • Relationships: This was intentionally avoided. Can be considered if there is a clean way to implement it.
  • Testing Transactions: Transactions work as expected but there is a sync bug in the test causing failure. It needs to be fixed.
  • Comments:
  • Hooks: I'm not sure if this is needed, but worths to consider.
  • Foreign Keys: *
  • Query Builder: Building SQL queries programmatically is useful.
  • Make UTF-8 Default: Looks like the default charset is not UTF8.

LICENSE

WTFPL

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func HasPK 

func HasPK(fields []*Field) bool

func ReadTableColumns added in v2.3.2 

func ReadTableColumns(any interface{}) ([]string, error)

Return table columns for given struct, pointer to struct or slice of structs.

func ReadTableName 

func ReadTableName(any interface{}) (string, string)

Return struct name and SQL table name

func ResolveReadParams 

func ResolveReadParams(params []interface{}) (string, []interface{}, error)

Arguments of the common CRUD functions can start with a query in string type, followed by parameters for the query itself. ResolveReadParams takes a list of any type, returns a query as a string type, parameters as a slice of interface, and potentially an error the last parameter.

func SQLTableNameOf 

func SQLTableNameOf(any interface{}) string

Find out what given interface should be called in the database. It first looks up if a table name was explicitly specified (see "table-name" option), or automatically generates a plural name from the name of the struct type.

func SetDefaultPK 

func SetDefaultPK(fields []*Field)

If no PK is specified, then set `id` to be PK.

Types

type Client added in v2.4.2 

type Client interface {
	Exec(string, ...interface{}) (stdsql.Result, error)
	Query(string, ...interface{}) (*stdsql.Rows, error)
	Create(interface{}) error
	CreateAndRead(interface{}) error
	Read(interface{}, ...interface{}) error
	Update(interface{}) error
	Delete(interface{}) error
}

type DB 

type DB struct {
	Client *stdsql.DB
	Driver string
	URL    string
}

func Connect 

func Connect(driver, url string) (*DB, error)

Establish DB connection and return a crud.DB instance w/ methods needed for accessing / writing the database. Example call: Connect("mysql", "root:123456@tcp(localhost:3306)/database_name?parseTime=true")

func (*DB) Begin 

func (db *DB) Begin(ctx context.Context) (*Tx, error)

Start a DB transaction. It returns an interface w/ most of the methods DB provides.

func (*DB) CheckIfTableExists 

func (db *DB) CheckIfTableExists(name string) bool

Runs a query to check if the given table exists and returns bool

func (*DB) Create 

func (db *DB) Create(record interface{}) error

Inserts given record into the database, generating an insert query for it.

func (*DB) CreateAndGetResult 

func (db *DB) CreateAndGetResult(record interface{}) (stdsql.Result, error)

func (*DB) CreateAndRead 

func (db *DB) CreateAndRead(record interface{}) error

Inserts given record and scans the inserted row back to the given row.

func (*DB) CreateTable 

func (db *DB) CreateTable(st interface{}, ifexists bool) error

Takes any valid struct and creates a SQL table from it.

func (*DB) CreateTables 

func (db *DB) CreateTables(structs ...interface{}) error

Creates multiple tables from given any amount of structs. Calls `CreateTable` internally.

func (*DB) Delete 

func (db *DB) Delete(record interface{}) error

Generates and executes a DELETE query for given struct record. It matches the database row by finding out the primary key field defined in the table schema.

func (*DB) DropTable 

func (db *DB) DropTable(st interface{}, ifexists bool) error

Takes any valid struct, finds out its corresponding SQL table and drops it.

func (*DB) DropTables 

func (db *DB) DropTables(structs ...interface{}) error

Drops correspoinding SQL tables of the given structs.

func (*DB) Exec 

func (db *DB) Exec(sql string, params ...interface{}) (stdsql.Result, error)

Run any query on the database client, passing parameters optionally. Returns sql.Result.

func (*DB) Ping 

func (db *DB) Ping() error

func (*DB) Query 

func (db *DB) Query(sql string, params ...interface{}) (*stdsql.Rows, error)

Run any query on the database client, passing parameters optionally. Its difference with `Exec` method is returning `sql.Rows` instead of `sql.Result`.

func (*DB) Read 

func (db *DB) Read(scanTo interface{}, params ...interface{}) error

Runs given SQL query and scans the result rows into the given target interface. The target interface could be both a single record or a slice of records.

Usage Example:

user := &User{} err := tx.Read(user, "SELECT * FROM users WHERE id = ?", 1)

users := &[]*User{} err := tx.Read(users, "SELECT * FROM users", 1)

func (*DB) ResetTables 

func (db *DB) ResetTables(structs ...interface{}) error

Drops (if they exist) and re-creates corresponding SQL tables for the given structs.

func (*DB) Update 

func (db *DB) Update(record interface{}) error

Finding out the primary-key field of the given row, updates the corresponding record on the table with the values in the given record.

func (*DB) WithContext added in v2.5.0 

func (db *DB) WithContext(ctx context.Context) *WithContext

Return a database client that wraps underlying SQL execution methods with the context specified

type ExecFn 

type ExecFn func(string, ...interface{}) (stdsql.Result, error)

type Field 

type Field struct {
	Name  string
	Value interface{}
	SQL   *sql.Options
}

func GetFieldsOf 

func GetFieldsOf(st interface{}) ([]*Field, error)

Get DB fields of any valid struct given

type FieldIteration 

type FieldIteration struct {
	Index        int
	Length       int
	ReflectValue reflect.Value
	ReflectType  reflect.Type
}

func NewFieldIteration 

func NewFieldIteration(st interface{}) *FieldIteration

Take any kind of struct and return a FieldIteration instance which helps walking the fields of the given struct one by one reading its name, value and SQL options

func (*FieldIteration) IsEmbeddedStruct 

func (iteration *FieldIteration) IsEmbeddedStruct() bool

func (*FieldIteration) Name 

func (iteration *FieldIteration) Name() string

func (*FieldIteration) Next 

func (iteration *FieldIteration) Next() bool

func (*FieldIteration) SQLOptions 

func (iteration *FieldIteration) SQLOptions() (*sql.Options, error)

func (*FieldIteration) TypeField 

func (iteration *FieldIteration) TypeField() reflect.StructField

func (*FieldIteration) Value 

func (iteration *FieldIteration) Value() interface{}

func (*FieldIteration) ValueField 

func (iteration *FieldIteration) ValueField() reflect.Value

type QueryFn 

type QueryFn func(string, ...interface{}) (*stdsql.Rows, error)

type Row 

type Row struct {
	SQLTableName string
	Values       []*RowValue
}

func (*Row) SQLValues 

func (row *Row) SQLValues() map[string]interface{}

type RowValue 

type RowValue struct {
	SQLColumn string
	Value     interface{}
}

func GetRowValuesOf 

func GetRowValuesOf(st interface{}) ([]*RowValue, error)

Scans given struct record and returns a list of crud.Row instances for each struct field. It's useful for extracting values and corresponding SQL meta information from structs representing database tables.

type Scan 

type Scan struct {
	To            interface{}
	ToPointers    bool
	ToStructs     bool
	SQLColumnDict map[string]string
	Table         *Table
}

func NewScan 

func NewScan(to interface{}) (*Scan, error)

Create a scanner for any given interface. This function will be called for every target interface passed to DB methods that scans results.

func (*Scan) All 

func (scan *Scan) All(rows *sql.Rows) error

func (*Scan) One 

func (scan *Scan) One(rows *sql.Rows) error

func (*Scan) Scan 

func (scan *Scan) Scan(rows *sql.Rows, record reflect.Value) error

func (*Scan) ScanToStruct 

func (scan *Scan) ScanToStruct(rows *sql.Rows, record reflect.Value) error

type Table 

type Table struct {
	Name    string
	SQLName string
	Fields  []*Field
}

func NewTable 

func NewTable(any interface{}) (*Table, error)

Create an internal representation of a database table, including its fields from given struct record

func (*Table) PrimaryKeyField 

func (table *Table) PrimaryKeyField() *Field

func (*Table) SQLColumnDict 

func (table *Table) SQLColumnDict() map[string]string

func (*Table) SQLOptions 

func (table *Table) SQLOptions() []*sql.Options

func (*Table) SQLUpdateColumnSet 

func (table *Table) SQLUpdateColumnSet() []string

func (*Table) SQLUpdateValueSet 

func (table *Table) SQLUpdateValueSet() []interface{}

type Tx 

type Tx struct {
	Context context.Context
	Client  *stdsql.Tx
	Id      string
	IdKey   string
}

func (*Tx) Commit 

func (tx *Tx) Commit() error

Commit the transaction.

func (*Tx) Create 

func (tx *Tx) Create(record interface{}) error

Insert given record to the database.

func (*Tx) CreateAndRead added in v2.4.0 

func (tx *Tx) CreateAndRead(record interface{}) error

Inserts given record and scans the inserted row back to the given row.

func (*Tx) Delete 

func (tx *Tx) Delete(record interface{}) error

Executes a DELETE query on the transaction for given struct record. It matches the database row by finding out the primary key field defined in the table schema.

func (*Tx) Exec 

func (tx *Tx) Exec(sql string, params ...interface{}) (stdsql.Result, error)

Execute any SQL query on the transaction client. Returns sql.Result.

func (*Tx) Query 

func (tx *Tx) Query(sql string, params ...interface{}) (*stdsql.Rows, error)

Execute any SQL query on the transaction client. Returns sql.Rows.

func (*Tx) Read 

func (tx *Tx) Read(scanTo interface{}, params ...interface{}) error

Run a select query on the databaase (w/ given parameters optionally) and scan the result(s) to the target interface specified as the first parameter.

Usage Example:

user := &User{} err := tx.Read(user, "SELECT * FROM users WHERE id = ?", 1)

users := &[]*User{} err := tx.Read(users, "SELECT * FROM users", 1)

func (*Tx) Rollback 

func (tx *Tx) Rollback() error

Rollback the transaction.

func (*Tx) Update 

func (tx *Tx) Update(record interface{}) error

Run an update query on the transaction, finding out the primary-key field of the given row.

type WithContext added in v2.5.0 

type WithContext struct {
	Context context.Context
	DB      *stdsql.DB
	Id      string
	IdKey   string
}

func (*WithContext) Create added in v2.5.0 

func (ctx *WithContext) Create(record interface{}) error

Insert given record to the database.

func (*WithContext) CreateAndRead added in v2.5.0 

func (ctx *WithContext) CreateAndRead(record interface{}) error

Inserts given record and scans the inserted row back to the given row.

func (*WithContext) Delete added in v2.5.0 

func (ctx *WithContext) Delete(record interface{}) error

Executes a DELETE query on the transaction for given struct record. It matches the database row by finding out the primary key field defined in the table schema.

func (*WithContext) Exec added in v2.5.0 

func (ctx *WithContext) Exec(sql string, params ...interface{}) (stdsql.Result, error)

Execute any SQL query on the context client. Returns sql.Result.

func (*WithContext) Query added in v2.5.0 

func (ctx *WithContext) Query(sql string, params ...interface{}) (*stdsql.Rows, error)

Execute any SQL query on the context client. Returns sql.Rows.

func (*WithContext) Read added in v2.5.0 

func (ctx *WithContext) Read(scanTo interface{}, params ...interface{}) error

Run a select query on the databaase (w/ given parameters optionally) and scan the result(s) to the target interface specified as the first parameter.

func (*WithContext) Update added in v2.5.0 

func (ctx *WithContext) Update(record interface{}) error

Run an update query on the transaction, finding out the primary-key field of the given row.

Source Files

View all Source files

Directories

Path Synopsis
examples
context
simple
transaction

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.