psql

package module
v1.12.3 Latest Latest
Warning

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

 Go to latest Published: Jan 13, 2025 License: MIT Imports: 14 Imported by: 2

README

gopsql

gopsql is a simple tool to make PostgreSQL database queries, especially for developing RESTful API with JSON. Some features are learned from Rails.

From furk.

Features

  • ✅ Model is a database table and it is created from struct. Column names are inferred from struct field names. You can write less SQL statements for CRUD operations.
  • ✅ Support JSONB data type, you can store many fields in one jsonb column, like Rails' store_accessor.
  • ✅ Safely insert and update record with Filter() to filter unwanted data, like Rails' permit.
  • ✅ Migrate your database like Rails database migrations.
  • ✅ Support pq, pgx and go-pg, you can switch driver at runtime.

Model

For more use cases, see Examples or Tests.

Database Connection

You can choose one of three PostgreSQL drivers (pq, pgx, gopq) to use at runtime:

  1. github.com/lib/pq v1.9.0
  2. github.com/jackc/pgx v4.10.1
  3. github.com/go-pg/pg v10.9.0
// import "github.com/gopsql/db"
// import "github.com/gopsql/gopg"
// import "github.com/gopsql/pgx"
// import "github.com/gopsql/pq"
connStr := "postgres://localhost:5432/gopsql?sslmode=disable"
connDrv := "gopg"
var conn db.DB
if connDrv == "pgx" {
	conn = pgx.MustOpen(connStr)
} else if connDrv == "gopg" {
	conn = gopg.MustOpen(connStr)
} else {
	conn = pq.MustOpen(connStr)
}
defer conn.Close()
var name string
conn.QueryRow("SELECT current_database()").Scan(&name)
fmt.Println(name) // gopsql

If you don't want too much entries being inserted into your go.sum file, you can use lib/pq:

// import "database/sql"
// import "github.com/gopsql/standard"
// import _ "github.com/lib/pq"
c, err := sql.Open("postgres", "postgres://localhost:5432/gopsql?sslmode=disable")
if err != nil {
	panic(err)
}
conn := &standard.DB{c}
defer conn.Close()
var name string
conn.QueryRow("SELECT current_database()").Scan(&name)
fmt.Println(name) // gopsql
Performance

Select 100 rows from database using different drivers, compared to their native usages. You can run cd tests && GENERATE=1 go test -v ./benchmark_test.go to make this benchmark chart. For more information, see Benchmark.

New Model
// type (
// 	Post struct {
// 		Id         int
// 		CategoryId int
// 		Title      string
// 		Picture    string `jsonb:"Meta"`
// 		Views      int    `dataType:"bigint DEFAULT 100"`
// 	}
// )
Posts := psql.NewModel(Post{}, conn, logger.StandardLogger)

Table name is inferred from the name of the struct, the tag of __TABLE_NAME__ field or its TableName() string receiver. Column names are inferred from struct field names or theirs "column" tags. Both table names and field names are in snake case by default.

Create Table
// CREATE TABLE Posts (
//	Id SERIAL PRIMARY KEY,
//	CategoryId bigint DEFAULT 0 NOT NULL,
//	Title text DEFAULT ''::text NOT NULL,
//	Views bigint DEFAULT 100,
//	Meta jsonb DEFAULT '{}'::jsonb NOT NULL
// )
Posts.NewSQL(Posts.Schema()).MustExecute()
Insert Record
var newPostId int
Posts.Insert(
	Posts.Permit("Title", "Picture").Filter(`{ "Title": "hello", "Picture": "world!" }`),
	"CategoryId", 2,
).Returning("Id").MustQueryRow(&newPostId)
// or:
Posts.Insert(
	"Title", "hello",
	"Picture", "world!",
	"CategoryId", 2,
).Returning("Id").MustQueryRow(&newPostId)
// INSERT INTO Posts (Title, CategoryId, Meta) VALUES ($1, $2, $3) RETURNING Id
Find Record
var firstPost Post
Posts.Find().Where("Id = $?", newPostId).MustQuery(&firstPost)
// or: Posts.WHERE("Id", "=", newPostId).Find().MustQuery(&firstPost)
// or: Posts.Where("Id = $1", newPostId).Find().MustQuery(&firstPost)
// SELECT Id, CategoryId, Title, Views, Meta FROM Posts WHERE Id = $1 [1] 1.505779ms
// {1 2 hello world! 100}

var ids []int
Posts.Select("Id").OrderBy("Id ASC").MustQuery(&ids)
// [1]

// group results by key
var id2title map[int]string
Posts.Select("Id", "Title").MustQuery(&id2title)
// map[1:hello]

// map's key and value can be int, string, bool, array or struct
// if it is one-to-many, use slice as map's value
var postsByCategoryId map[struct{ categoryId int }][]struct{ title string }
Posts.Select("CategoryId", "Title").MustQuery(&postsByCategoryId)
// map[{2}:[{hello}]]

var posts []Post
Posts.Find().MustQuery(&posts)
// [{1 2 hello world! 100}]
Update Record
var rowsAffected int
Posts.Update(
	Posts.Permit("Picture").Filter(`{ "Picture": "WORLD!" }`),
).Where("Id = $1", newPostId).MustExecute(&rowsAffected)
// or: Posts.Where(...).Update(...).MustExecute(...)
// UPDATE Posts SET Meta = jsonb_set(COALESCE(Meta, '{}'::jsonb), '{Picture}', $2) WHERE Id = $1

Posts.Update("Views", psql.String("Views * 2")).Where("Id = $?", 1).MustExecute()
// UPDATE Posts SET Views = Views * 2 WHERE Id = $1

Posts.Update("Views", psql.StringWithArg("Views + $?", 99)).Where("Id = $?", 1).MustExecute()
// UPDATE Posts SET Views = Views + $2 WHERE Id = $1
Delete Record
var rowsDeleted int
Posts.Delete().Where("Id = $?", newPostId).MustExecute(&rowsDeleted)
// or: Posts.Where(...).Delete().MustExecute(...)
// DELETE FROM Posts WHERE Id = $1
Other
Posts.Where("Id = $?", newPostId).MustExists() // true or false
Posts.MustCount() // integer

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrInvalidTarget       = errors.New("target must be pointer of a struct, slice or map")
	ErrNoConnection        = errors.New("no connection")
	ErrNoSQL               = errors.New("no sql statements to execute")
	ErrTypeAssertionFailed = errors.New("type assertion failed")
)
View Source 
var (
	// DefaultColumnNamer is default column naming function used when
	// calling NewModel. Default is null, which uses field name as column
	// name.
	DefaultColumnNamer func(string) string = nil

	// DefaultColumnNamer is default table naming function used when
	// calling NewModel. Default is ToPlural, which converts table name to
	// its plural form.
	DefaultTableNamer func(string) string = ToPlural
)
View Source 
var AddTableName fieldsFunc = func(fields []string, tableName string) (out []string) {
	for _, field := range fields {
		if strings.Contains(field, ".") {
			out = append(out, field)
			continue
		}
		out = append(out, tableName+"."+field)
	}
	return
}

Can be used in Find(), add table name to all field names.

View Source 
var (
	ErrMustBePointer = errors.New("must be pointer")
)

Functions

func FieldDataType added in v1.7.0 

func FieldDataType(fieldName, fieldType string) (dataType string)

FieldDataType generates PostgreSQL data type based on struct's field name and type. This is default function used when calling ColumnDataTypes() or Schema(). To use custom data type function, define "FieldDataType(fieldName, fieldType string) (dataType string)" function for your connection.

func StringWithArg added in v1.12.2 

func StringWithArg(str string, arg interface{}) stringWithArg

func ToPlural added in v1.8.0 

func ToPlural(in string) string

Convert a word to its plural form. Add "es" for "s" or "o" ending, "y" ending will be replaced with "ies", for other endings, add "s". For example, "product" will be converted to "products".

func ToPluralUnderscore added in v1.8.0 

func ToPluralUnderscore(in string) string

Convert a "CamelCase" word to its plural "snake_case" (underscore) form. For example, "PostComment" will be converted to "post_comments".

func ToTableName 

func ToTableName(object interface{}) (name string)

ToTableName returns table name of a struct. If struct has "TableName() string" receiver method, its return value is used. If name is empty and struct has a __TABLE_NAME__ field, its tag value is used. If it is still empty, struct's name is used. If name is still empty, "error_no_table_name" is returned.

func ToUnderscore added in v1.8.0 

func ToUnderscore(str string) string

Convert "CamelCase" word to its "snake_case" (underscore) form. For example, "FullName" will be converted to "full_name".

Types

type Changes 

type Changes map[Field]interface{}

func (Changes) MarshalJSON 

func (c Changes) MarshalJSON() ([]byte, error)

func (Changes) String 

func (c Changes) String() string

type DeleteSQL added in v1.3.0 

type DeleteSQL struct {
	*SQL
	// contains filtered or unexported fields
}

DeleteSQL can be created with Model.NewSQL().AsDelete()

func (*DeleteSQL) Reload added in v1.3.0 

func (s *DeleteSQL) Reload() *DeleteSQL

Update SQL and values in the DeleteSQL object due to changes of conditions.

func (*DeleteSQL) Returning added in v1.3.0 

func (s *DeleteSQL) Returning(expressions ...string) *DeleteSQL

Adds RETURNING clause to DELETE FROM statement.

func (*DeleteSQL) String added in v1.3.0 

func (s *DeleteSQL) String() string

func (*DeleteSQL) Tap added in v1.5.0 

func (s *DeleteSQL) Tap(funcs ...func(*DeleteSQL) *DeleteSQL) *DeleteSQL

Perform operations on the chain.

func (*DeleteSQL) Using added in v1.3.0 

func (s *DeleteSQL) Using(list ...string) *DeleteSQL

Adds RETURNING clause to DELETE FROM statement.

func (*DeleteSQL) WHERE added in v1.9.0 

func (s *DeleteSQL) WHERE(args ...interface{}) *DeleteSQL

WHERE adds conditions to DELETE statement from variadic inputs.

The args parameter contains field name, operator, value tuples with each tuple consisting of three consecutive elements: the field name as a string, an operator symbol as a string (e.g. "=", ">", "<="), and the value to match against that field.

To generate a WHERE clause matching multiple fields, use more than one set of field/operator/value tuples in the args array. For example, WHERE("A", "=", 1, "B", "!=", 2) means "WHERE (A = 1) AND (B != 2)".

func (*DeleteSQL) Where added in v1.3.0 

func (s *DeleteSQL) Where(condition string, args ...interface{}) *DeleteSQL

Adds condition to DELETE FROM statement. Arguments should use positonal parameters like $1, $2. If only one argument is provided, "$?" in the condition will be replaced with the correct positonal parameter.

type Field 

type Field struct {
	Name       string // struct field name
	ColumnName string // column name (or jsonb key name) in database
	ColumnType string // column type
	JsonName   string // key name in json input and output
	Jsonb      string // jsonb column name in database
	DataType   string // data type in database
	Exported   bool   // false if field name is lower case (unexported)
	Strict     bool   // jsonb: raise json unmarshal error if set to true
	Parent     string // parent struct name if anonymous is set
}

type InsertSQL added in v1.3.0 

type InsertSQL struct {
	*SQL
	// contains filtered or unexported fields
}

InsertSQL can be created with Model.NewSQL().AsInsert()

func (*InsertSQL) DoNothing added in v1.3.0 

func (s *InsertSQL) DoNothing() *InsertSQL

Used with OnConflict(), adds ON CONFLICT DO NOTHING clause to INSERT INTO statement.

func (*InsertSQL) DoUpdate added in v1.3.0 

func (s *InsertSQL) DoUpdate(expressions ...string) *InsertSQL

Used with OnConflict(), adds custom expressions ON CONFLICT ... DO UPDATE SET ... clause to INSERT INTO statement.

func (*InsertSQL) DoUpdateAll added in v1.3.0 

func (s *InsertSQL) DoUpdateAll() *InsertSQL

DoUpdateAll is like DoUpdate but update every field.

func (*InsertSQL) DoUpdateAllExcept added in v1.10.3 

func (s *InsertSQL) DoUpdateAllExcept(fields ...string) *InsertSQL

DoUpdateAllExcept is like DoUpdateAll but except some field names.

func (*InsertSQL) OnConflict added in v1.3.0 

func (s *InsertSQL) OnConflict(targets ...string) *InsertSQL

Used with DoNothing(), DoUpdate() or DoUpdateAll().

func (*InsertSQL) Returning added in v1.3.0 

func (s *InsertSQL) Returning(expressions ...string) *InsertSQL

Adds RETURNING clause to INSERT INTO statement.

func (InsertSQL) String added in v1.3.0 

func (s InsertSQL) String() string

func (*InsertSQL) Tap added in v1.5.0 

func (s *InsertSQL) Tap(funcs ...func(*InsertSQL) *InsertSQL) *InsertSQL

Perform operations on the chain.

type Model 

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

Model is a database table and it is created from struct. Table name is inferred from struct's name, and converted to its the plural form using psql.TransformTableName by default. To use a different table name, define a __TABLE_NAME__ field in the struct and set the tag value as the table name, or add a "TableName() string" receiver method for the struct.

Column names are inferred from struct field names. To use a different column name, set the "column" tag for the field, or use SetColumnNamer() to define column namer function to transform all field names in this model.

func NewModel 

func NewModel(object interface{}, options ...interface{}) (m *Model)

Initialize a Model from a struct. For available options, see SetOptions().

func NewModelTable 

func NewModelTable(tableName string, options ...interface{}) (m *Model)

Initialize a Model by defining table name only. Useful if you are calling functions that don't need fields, for example: 

psql.NewModelTable("users", conn).MustCount()

For available options, see SetOptions().

func (Model) AddTableName added in v1.12.0 

func (m Model) AddTableName(fields ...string) []string

AddTableName adds table name to fields.

func (Model) Assign 

func (m Model) Assign(target interface{}, lotsOfChanges ...interface{}) (out []interface{}, err error)

Assign changes to target object. Useful if you want to validate your struct. 

func create(c echo.Context) error {
	var user models.User
	m := psql.NewModel(user, conn)
	changes := m.MustAssign(
		&user,
		m.Permit("Name").Filter(c.Request().Body),
	)
	if err := c.Validate(user); err != nil {
		panic(err)
	}
	var id int
	m.Insert(changes...).Returning("id").MustQueryRow(&id)
	// ...
}

func (Model) Changes 

func (m Model) Changes(in RawChanges) (out Changes)

Convert RawChanges to Changes. Keys are JSON key names. See FieldChanges(). 

m := psql.NewModel(struct {
	Age *int `json:"age"`
}{})
m.Changes(map[string]interface{}{
	"age": 99,
})

func (*Model) Clone added in v1.2.2 

func (m *Model) Clone() *Model

Clone returns a copy of the model.

func (Model) ColumnDataTypes added in v1.4.0 

func (m Model) ColumnDataTypes() map[string]string

func (Model) Columns added in v1.4.0 

func (m Model) Columns() []string

Column names of the Model.

func (*Model) Connection added in v1.0.1 

func (m *Model) Connection() db.DB

Return database connection for the Model.

func (Model) Count 

func (m Model) Count(optional ...string) (count int, err error)

Create and execute a SELECT COUNT(*) statement, return number of rows.

func (Model) CreatedAt 

func (m Model) CreatedAt() Changes

Helper to add CreatedAt of current time changes.

func (Model) Delete 

func (m Model) Delete() *DeleteSQL

Delete builds a DELETE statement. You can add extra clause (like WHERE, RETURNING) to the statement as the first argument. The rest arguments are for any placeholder parameters in the statement. 

var ids []int
psql.NewModelTable("reports", conn).Delete().Returning("id").MustQuery(&ids)

func (Model) DropSchema 

func (m Model) DropSchema() string

Generate DROP TABLE ("DROP TABLE IF EXISTS <table_name>;") SQL statement from a Model.

func (Model) Exists 

func (m Model) Exists() (exists bool, err error)

Create and execute a SELECT 1 AS one statement. Returns true if record exists, false if not exists.

func (Model) FieldByName 

func (m Model) FieldByName(name string) *Field

Get field by struct field name, nil will be returned if no such field.

func (Model) FieldChanges added in v1.1.0 

func (m Model) FieldChanges(in RawChanges) (out Changes)

Convert RawChanges to Changes. Keys are field names. See Changes().

func (Model) Fields added in v1.12.0 

func (m Model) Fields() []string

Fields returns field names of the Model. For JSONB fields, see JSONBFields().

func (Model) Find 

func (m Model) Find(options ...interface{}) *SelectSQL

Create a SELECT query statement with all fields of a Model. If you want to use other data type than the type of struct passed in NewModel(), see Select(). 

// put results into a slice
var users []models.User
psql.NewModel(models.User{}, conn).Find().MustQuery(&users)

// put results into a struct
var user models.User
psql.NewModel(models.User{}, conn).Find().Where("id = $1", 1).MustQuery(&user)

You can pass options to modify Find(). For example, Find(psql.AddTableName) adds table name to every field.

func (Model) Insert 

func (m Model) Insert(lotsOfChanges ...interface{}) *InsertSQL

Insert builds an INSERT INTO statement with fields and values in the changes. 

var id int
m.Insert(changes...).Returning("id").MustQueryRow(&id)

Changes can be a list of field name and value pairs and can also be obtained from methods like Changes(), FieldChanges(), Assign(), Bind(), Filter(). 

m.Insert("FieldA", 123, "FieldB", "other").MustExecute()

func (Model) JSONBFields added in v1.12.0 

func (m Model) JSONBFields() []string

JSONBFields returns JSONB field names of the Model.

func (Model) Join added in v1.3.1 

func (m Model) Join(expressions ...string) *SelectSQL

Create a SELECT query statement with joins.

func (Model) MustAssign 

func (m Model) MustAssign(i interface{}, lotsOfChanges ...interface{}) []interface{}

MustAssign is like Assign but panics if assign operation fails.

func (Model) MustCount 

func (m Model) MustCount(optional ...string) int

MustCount is like Count but panics if count operation fails.

func (Model) MustExists 

func (m Model) MustExists() bool

MustExists is like Exists but panics if existence check operation fails. Returns true if record exists, false if not exists.

func (Model) MustTransaction added in v1.6.0 

func (m Model) MustTransaction(block TransactionBlock)

MustTransaction starts a transaction, uses context.Background() internally and panics if transaction fails.

func (Model) MustTransactionCtx added in v1.6.0 

func (m Model) MustTransactionCtx(ctx context.Context, block TransactionBlock)

MustTransactionCtx starts a transaction and panics if transaction fails.

func (Model) New added in v1.10.0 

func (m Model) New() reflect.Value

New returns a reflect.Value representing a pointer to a new zero value for model's struct type.

func (Model) NewSQL added in v1.3.0 

func (m Model) NewSQL(sql string, values ...interface{}) *SQL

Create new SQL with SQL statement as first argument, The rest arguments are for any placeholder parameters in the statement.

func (Model) NewSlice added in v1.10.0 

func (m Model) NewSlice() reflect.Value

NewSlice returns a reflect.Value representing a pointer to a new zero-initialized slice value for model's struct type.

func (Model) Permit 

func (m Model) Permit(fieldNames ...string) *ModelWithPermittedFields

Permits list of field names of a Model to limit Filter() which fields should be allowed for mass updating. If no field names are provided ("Permit()"), no fields are permitted.

func (Model) PermitAllExcept 

func (m Model) PermitAllExcept(fieldNames ...string) *ModelWithPermittedFields

Permits all available fields except provided of a Model to limit Filter() which fields should be allowed for mass updating. If no field names are provided ("PermitAllExcept()"), all available fields are permitted.

func (*Model) Quiet added in v1.2.2 

func (m *Model) Quiet() *Model

Quiet returns a copy of the model without logger.

func (Model) Schema 

func (m Model) Schema() string

Generate CREATE TABLE SQL statement from a Model. 

| Go Type                                        | PostgreSQL Data Type |
|------------------------------------------------|----------------------|
| int8 / int16 / int32 / uint8 / uint16 / uint32 | integer              |
| int64 / uint64 / int / uint                    | bigint               |
| time.Time                                      | timestamptz          |
| float32 / float64 / decimal.Decimal            | numeric              |
| bool                                           | boolean              |
| other                                          | text                 |

You can use "dataType" tag to customize the data type. "NOT NULL" is added if the struct field is not a pointer. You can also set SQL statements before or after this statement by defining "BeforeCreateSchema() string" (for example the CREATE EXTENSION statement) or "AfterCreateSchema() string" (for example the CREATE INDEX statement) function for the struct. Set dataType to "-" to ignore this field in migration. 

psql.NewModel(struct {
	__TABLE_NAME__ string `users`

	Id        int
	Name      string
	Age       *int
	Numbers   []int
	CreatedAt time.Time
	DeletedAt *time.Time `dataType:"timestamptz"`
	FullName  string     `jsonb:"meta"`
	NickName  string     `jsonb:"meta"`
}{}).Schema()
// CREATE TABLE users (
//         id SERIAL PRIMARY KEY,
//         name text DEFAULT ''::text NOT NULL,
//         age bigint DEFAULT 0,
//         numbers bigint[] DEFAULT '{}' NOT NULL,
//         created_at timestamptz DEFAULT NOW() NOT NULL,
//         deleted_at timestamptz,
//         meta jsonb DEFAULT '{}'::jsonb NOT NULL
// );

func (Model) Select 

func (m Model) Select(fields ...string) *SelectSQL

Select is like Find but can choose what columns to retrieve.

To put results into a slice of strings: 

var names []string
psql.NewModelTable("users", conn).Select("name").OrderBy("id ASC").MustQuery(&names)

To put results into a slice of custom struct: 

var users []struct {
	name string
	id   int
}
psql.NewModelTable("users", conn).Select("name", "id").OrderBy("id ASC").MustQuery(&users)

To group results by the key: 

var id2name map[int]string
psql.NewModelTable("users", conn).Select("id", "name").MustQuery(&id2name)

If it is one-to-many, use slice as map's value: 

var users map[[2]string][]struct {
	id   int
	name string
}
psql.NewModelTable("users", conn).Select("country, city, id, name").MustQuery(&users)

func (*Model) SetColumnNamer added in v1.8.0 

func (m *Model) SetColumnNamer(namer func(string) string) *Model

Change the column namer function for the Model.

func (*Model) SetConnection 

func (m *Model) SetConnection(db db.DB) *Model

Set a database connection for the Model. ErrNoConnection is returned if no connection is set.

func (*Model) SetLogger 

func (m *Model) SetLogger(logger logger.Logger) *Model

Set the logger for the Model. Use logger.StandardLogger if you want to use Go's built-in standard logging package. By default, no logger is used, so the SQL statements are not printed to the console.

func (*Model) SetOptions 

func (m *Model) SetOptions(options ...interface{}) *Model

SetOptions sets database connection (see SetConnection()) and/or logger (see SetLogger()).

func (Model) String 

func (m Model) String() string

func (Model) TableName 

func (m Model) TableName() string

Table name of the Model.

func (Model) ToColumnName added in v1.8.0 

func (mi Model) ToColumnName(in string) string

Function to convert field name to name used in database.

func (Model) Transaction added in v1.6.0 

func (m Model) Transaction(block TransactionBlock) error

Transaction starts a transaction, uses context.Background() internally.

func (Model) TransactionCtx added in v1.6.0 

func (m Model) TransactionCtx(ctx context.Context, block TransactionBlock) (err error)

TransactionCtx starts a transaction.

func (Model) TypeName added in v1.2.1 

func (m Model) TypeName() string

Type name of the Model.

func (Model) Update 

func (m Model) Update(lotsOfChanges ...interface{}) *UpdateSQL

Update builds an UPDATE statement with fields and values in the changes. 

var rowsAffected int
m.Update(changes...).Where("user_id = $1", 1).MustExecute(&rowsAffected)

Changes can be a list of field name and value pairs and can also be obtained from methods like Changes(), FieldChanges(), Assign(), Bind(), Filter(). 

m.Update("FieldA", 123, "FieldB", "other").MustExecute()

func (Model) UpdatedAt 

func (m Model) UpdatedAt() Changes

Helper to add UpdatedAt of current time changes.

func (Model) WHERE added in v1.9.0 

func (m Model) WHERE(args ...interface{}) *SelectSQL

Create a SELECT query statement with condition.

The args parameter contains field name, operator, value tuples with each tuple consisting of three consecutive elements: the field name as a string, an operator symbol as a string (e.g. "=", ">", "<="), and the value to match against that field.

To generate a WHERE clause matching multiple fields, use more than one set of field/operator/value tuples in the args array. For example, WHERE("A", "=", 1, "B", "!=", 2) means "WHERE (A = 1) AND (B != 2)".

func (Model) Where added in v1.3.0 

func (m Model) Where(condition string, args ...interface{}) *SelectSQL

Create a SELECT query statement with condition. Arguments should use positonal parameters like $1, $2. If only one argument is provided, "$?" in the condition will be replaced with the correct positonal parameter.

func (*Model) WithoutFields added in v1.8.3 

func (m *Model) WithoutFields(fieldNames ...string) *Model

WithoutFields returns a copy of the model without given fields.

type ModelWithPermittedFields 

type ModelWithPermittedFields struct {
	*Model
	// contains filtered or unexported fields
}

func (ModelWithPermittedFields) Bind 

func (m ModelWithPermittedFields) Bind(ctx interface{ Bind(interface{}) error }, target interface{}) (Changes, error)

Bind data of permitted fields to target structure using echo.Context#Bind function. The "target" must be a pointer to struct. 

// request with ?name=x&age=10
func list(c echo.Context) error {
	obj := struct {
		Name string `query:"name"`
		Age  int    `query:"age"`
	}{}
	m := psql.NewModel(obj)
	fmt.Println(m.Permit("Name").Bind(c, &obj))
	fmt.Println(obj) // "Name" is "x" and "Age" is 0 (default), because only "Name" is permitted to change
	// ...
}

func (ModelWithPermittedFields) Filter 

func (m ModelWithPermittedFields) Filter(inputs ...interface{}) (out Changes)

Filter keeps data of permitted fields set by Permit() from multiple inputs. Inputs can be RawChanges (map[string]interface{}) or JSON-encoded data (string, []byte or io.Reader), their keys must be fields' JSON names. Input can also be a struct. The "Changes" outputs can be arguments for Insert() or Update(). 

m := psql.NewModel(struct {
	Age *int `json:"age"`
}{})
m.Permit("Age").Filter(
	psql.RawChanges{
		"age": 10,
	},
	map[string]interface{}{
		"age": 20,
	},
	`{"age": 30}`,
	[]byte(`{"age": 40}`),
	strings.NewReader(`{"age": 50}`),
	struct{ Age int }{60},
) // Age is 60

func (ModelWithPermittedFields) MustBind 

func (m ModelWithPermittedFields) MustBind(ctx interface{ Bind(interface{}) error }, target interface{}) Changes

MustBind is like Bind but panics if bind operation fails.

func (ModelWithPermittedFields) PermittedFields 

func (m ModelWithPermittedFields) PermittedFields() (out []string)

Returns list of permitted field names.

func (ModelWithPermittedFields) ToColumnName added in v1.8.0 

func (mi ModelWithPermittedFields) ToColumnName(in string) string

Function to convert field name to name used in database.

type RawChanges 

type RawChanges map[string]interface{}

type SQL added in v1.3.0 

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

SQL can be created with Model.NewSQL()

func (SQL) AsDelete added in v1.3.0 

func (s SQL) AsDelete() *DeleteSQL

Convert SQL to DeleteSQL.

func (SQL) AsInsert added in v1.3.0 

func (s SQL) AsInsert(fields ...string) *InsertSQL

Convert SQL to InsertSQL. The optional fields will be used in DoUpdateAll().

func (SQL) AsSelect added in v1.3.0 

func (s SQL) AsSelect(fields ...string) *SelectSQL

Convert SQL to SelectSQL. The optional fields will be used in Select().

func (SQL) AsUpdate added in v1.3.0 

func (s SQL) AsUpdate(changes ...interface{}) *UpdateSQL

Convert SQL to UpdateSQL. The optional changes will be used in Reload().

func (SQL) Execute added in v1.3.0 

func (s SQL) Execute(dest ...interface{}) error

Execute executes a query without returning any rows by an UPDATE, INSERT, or DELETE. You can get number of rows affected by providing pointer of int or int64 to the optional dest. For use cases, see Update().

func (SQL) ExecuteCtx added in v1.7.0 

func (s SQL) ExecuteCtx(ctx context.Context, dest ...interface{}) error

ExecuteCtx executes a query without returning any rows by an UPDATE, INSERT, or DELETE. You can get number of rows affected by providing pointer of int or int64 to the optional dest. For use cases, see Update().

func (SQL) ExecuteCtxTx added in v1.6.0 

func (s SQL) ExecuteCtxTx(ctx context.Context, tx Tx, dest ...interface{}) error

ExecuteCtxTx executes a query without returning any rows by an UPDATE, INSERT, or DELETE. You can get number of rows affected by providing pointer of int or int64 to the optional dest. For use cases, see Update().

func (SQL) MustExecute added in v1.3.0 

func (s SQL) MustExecute(dest ...interface{})

MustExecute is like Execute but panics if execute operation fails.

func (SQL) MustExecuteCtx added in v1.7.0 

func (s SQL) MustExecuteCtx(ctx context.Context, dest ...interface{})

MustExecuteCtx is like ExecuteCtx but panics if execute operation fails.

func (SQL) MustExecuteCtxTx added in v1.6.0 

func (s SQL) MustExecuteCtxTx(ctx context.Context, tx Tx, dest ...interface{})

MustExecuteCtxTx is like ExecuteCtxTx but panics if execute operation fails.

func (SQL) MustQuery added in v1.3.0 

func (s SQL) MustQuery(target interface{})

MustQuery is like Query but panics if query operation fails.

func (SQL) MustQueryCtx added in v1.7.0 

func (s SQL) MustQueryCtx(ctx context.Context, target interface{})

MustQueryCtx is like QueryCtx but panics if query operation fails.

func (SQL) MustQueryCtxTx added in v1.6.0 

func (s SQL) MustQueryCtxTx(ctx context.Context, tx Tx, target interface{})

MustQueryCtxTx is like QueryCtxTx but panics if query operation fails.

func (SQL) MustQueryRow added in v1.3.0 

func (s SQL) MustQueryRow(dest ...interface{})

MustQueryRow is like QueryRow but panics if query row operation fails.

func (SQL) MustQueryRowCtx added in v1.7.0 

func (s SQL) MustQueryRowCtx(ctx context.Context, dest ...interface{})

MustQueryRowCtx is like QueryRowCtx but panics if query row operation fails.

func (SQL) MustQueryRowCtxTx added in v1.6.0 

func (s SQL) MustQueryRowCtxTx(ctx context.Context, tx Tx, dest ...interface{})

MustQueryRowCtxTx is like QueryRowCtxTx but panics if query row operation fails.

func (SQL) Query added in v1.3.0 

func (s SQL) Query(target interface{}) error

Query executes the SQL query and put the results into the target. Target must be a pointer to a struct, a slice or a map. For use cases, see Find() and Select().

func (SQL) QueryCtx added in v1.7.0 

func (s SQL) QueryCtx(ctx context.Context, target interface{}) error

QueryCtx executes the SQL query and put the results into the target. Target must be a pointer to a struct, a slice or a map. For use cases, see Find() and Select().

func (SQL) QueryCtxTx added in v1.6.0 

func (s SQL) QueryCtxTx(ctx context.Context, tx Tx, target interface{}) error

QueryCtxTx executes the SQL query and put the results into the target. Target must be a pointer to a struct, a slice or a map. For use cases, see Find() and Select().

func (SQL) QueryRow added in v1.3.0 

func (s SQL) QueryRow(dest ...interface{}) error

QueryRow gets results from the first row, and put values of each column to corresponding dest. For use cases, see Insert(). 

var u struct {
	name string
	id   int
}
psql.NewModelTable("users", conn).Select("name, id").MustQueryRow(&u.name, &u.id)

func (SQL) QueryRowCtx added in v1.7.0 

func (s SQL) QueryRowCtx(ctx context.Context, dest ...interface{}) error

QueryRowCtx gets results from the first row, and put values of each column to corresponding dest. For use cases, see Insert().

func (SQL) QueryRowCtxTx added in v1.6.0 

func (s SQL) QueryRowCtxTx(ctx context.Context, tx Tx, dest ...interface{}) error

QueryRowCtxTx gets results from the first row, and put values of each column to corresponding dest. For use cases, see Insert().

func (SQL) String added in v1.3.0 

func (s SQL) String() string

func (*SQL) Tap added in v1.5.0 

func (s *SQL) Tap(funcs ...func(*SQL) *SQL) *SQL

Perform operations on the chain.

func (SQL) Values added in v1.11.1 

func (s SQL) Values() []interface{}

type SelectSQL added in v1.3.0 

type SelectSQL struct {
	*SQL
	// contains filtered or unexported fields
}

SelectSQL can be created with Model.NewSQL().AsSelect()

func (*SelectSQL) Count added in v1.3.0 

func (s *SelectSQL) Count(optional ...string) (count int, err error)

Create and execute a SELECT COUNT(*) statement, return number of rows. To count in a different way: Count("COUNT(DISTINCT authors.id)").

func (*SelectSQL) Delete added in v1.3.0 

func (s *SelectSQL) Delete() *DeleteSQL

Create a DELETE statement from Where().

func (*SelectSQL) Exists added in v1.3.0 

func (s *SelectSQL) Exists() (exists bool, err error)

Create and execute a SELECT 1 AS one statement. Returns true if record exists, false if not exists.

func (*SelectSQL) Find added in v1.3.0 

func (s *SelectSQL) Find(options ...interface{}) *SelectSQL

Create a SELECT query statement with all fields of a Model. Options can be funtions like AddTableName or strings like "--no-reset" (use Select instead of ResetSelect).

func (*SelectSQL) GroupBy added in v1.3.0 

func (s *SelectSQL) GroupBy(expressions ...string) *SelectSQL

Adds GROUP BY to SELECT statement.

func (*SelectSQL) Having added in v1.3.0 

func (s *SelectSQL) Having(condition string, args ...interface{}) *SelectSQL

Adds HAVING to SELECT statement. Arguments should use positonal parameters like $1, $2. If only one argument is provided, "$?" in the condition will be replaced with the correct positonal parameter.

func (*SelectSQL) Join added in v1.3.1 

func (s *SelectSQL) Join(expressions ...string) *SelectSQL

Adds join to SELECT statement.

func (*SelectSQL) Limit added in v1.3.0 

func (s *SelectSQL) Limit(count interface{}) *SelectSQL

Adds LIMIT to SELECT statement.

func (*SelectSQL) MustCount added in v1.3.0 

func (s *SelectSQL) MustCount(optional ...string) int

MustCount is like Count but panics if count operation fails.

func (*SelectSQL) MustExists added in v1.3.0 

func (s *SelectSQL) MustExists() bool

MustExists is like Exists but panics if existence check operation fails. Returns true if record exists, false if not exists.

func (*SelectSQL) Offset added in v1.3.0 

func (s *SelectSQL) Offset(start interface{}) *SelectSQL

Adds OFFSET to SELECT statement.

func (*SelectSQL) OrderBy added in v1.3.0 

func (s *SelectSQL) OrderBy(expressions ...string) *SelectSQL

Adds ORDER BY to SELECT statement.

func (*SelectSQL) Reload added in v1.3.0 

func (s *SelectSQL) Reload() *SelectSQL

Update SQL and values in the DeleteSQL object due to changes of conditions.

func (*SelectSQL) ReplaceSelect added in v1.10.5 

func (s *SelectSQL) ReplaceSelect(old, new string) *SelectSQL

Replace old field names in existing SELECT statement with new.

func (*SelectSQL) ResetJoin added in v1.5.0 

func (s *SelectSQL) ResetJoin(expressions ...string) *SelectSQL

Clears existing JOIN statements and set new JOIN statements.

func (*SelectSQL) ResetSelect added in v1.3.0 

func (s *SelectSQL) ResetSelect(expressions ...string) *SelectSQL

Set expressions to SELECT statement.

func (*SelectSQL) Select added in v1.3.0 

func (s *SelectSQL) Select(expressions ...string) *SelectSQL

Add expressions to SELECT statement, before any existing jsonb columns.

func (*SelectSQL) String added in v1.3.0 

func (s *SelectSQL) String() string

func (*SelectSQL) Tap added in v1.5.0 

func (s *SelectSQL) Tap(funcs ...func(*SelectSQL) *SelectSQL) *SelectSQL

Perform operations on the chain.

func (*SelectSQL) Update added in v1.3.0 

func (s *SelectSQL) Update(lotsOfChanges ...interface{}) *UpdateSQL

Create a UPDATE statement from Where().

func (*SelectSQL) WHERE added in v1.9.0 

func (s *SelectSQL) WHERE(args ...interface{}) *SelectSQL

WHERE adds conditions to SELECT statement from variadic inputs.

The args parameter contains field name, operator, value tuples with each tuple consisting of three consecutive elements: the field name as a string, an operator symbol as a string (e.g. "=", ">", "<="), and the value to match against that field.

To generate a WHERE clause matching multiple fields, use more than one set of field/operator/value tuples in the args array. For example, WHERE("A", "=", 1, "B", "!=", 2) means "WHERE (A = 1) AND (B != 2)".

func (*SelectSQL) Where added in v1.3.0 

func (s *SelectSQL) Where(condition string, args ...interface{}) *SelectSQL

Adds condition to SELECT statement. Arguments should use positonal parameters like $1, $2. If only one argument is provided, "$?" in the condition will be replaced with the correct positonal parameter.

type String added in v1.12.2 

type String string

type TransactionBlock added in v1.6.0 

type TransactionBlock func(context.Context, Tx) error

type Tx added in v1.12.1 

type Tx = db.Tx

type UpdateSQL added in v1.3.0 

type UpdateSQL struct {
	*SQL
	// contains filtered or unexported fields
}

UpdateSQL can be created with Model.NewSQL().AsUpdate()

func (*UpdateSQL) Reload added in v1.3.0 

func (s *UpdateSQL) Reload() *UpdateSQL

Update SQL and values in the UpdateSQL object due to changes of columns and conditions.

func (*UpdateSQL) Returning added in v1.3.0 

func (s *UpdateSQL) Returning(expressions ...string) *UpdateSQL

Adds RETURNING clause to UPDATE statement.

func (*UpdateSQL) String added in v1.3.0 

func (s *UpdateSQL) String() string

func (*UpdateSQL) Tap added in v1.5.0 

func (s *UpdateSQL) Tap(funcs ...func(*UpdateSQL) *UpdateSQL) *UpdateSQL

Perform operations on the chain.

func (*UpdateSQL) WHERE added in v1.9.0 

func (s *UpdateSQL) WHERE(args ...interface{}) *UpdateSQL

WHERE adds conditions to UPDATE statement from variadic inputs.

The args parameter contains field name, operator, value tuples with each tuple consisting of three consecutive elements: the field name as a string, an operator symbol as a string (e.g. "=", ">", "<="), and the value to match against that field.

To generate a WHERE clause matching multiple fields, use more than one set of field/operator/value tuples in the args array. For example, WHERE("A", "=", 1, "B", "!=", 2) means "WHERE (A = 1) AND (B != 2)".

func (*UpdateSQL) Where added in v1.3.0 

func (s *UpdateSQL) Where(condition string, args ...interface{}) *UpdateSQL

Adds condition to UPDATE statement. Arguments should use positonal parameters like $1, $2. If only one argument is provided, "$?" in the condition will be replaced with the correct positonal parameter.

Source Files

View all Source files

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.