dbx

package module
v1.11.0 Latest Latest
Warning

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

 Go to latest Published: Dec 10, 2024 License: MIT Imports: 13 Imported by: 645

README

dbx Go Report Card GoDoc

⚠️ This is a maintained fork of go-ozzo/ozzo-dbx (see #103).

Currently, the changes are primarily related to better SQLite support and some other minor improvements, implementing #99, #100 and #102.

Summary

Description

dbx is a Go package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities. dbx is not an ORM. It has the following features:

  • Populating data into structs and NullString maps
  • Named parameter binding
  • DB-agnostic query building methods, including SELECT queries, data manipulation queries, and schema manipulation queries
  • Inserting, updating, and deleting model structs
  • Powerful query condition building
  • Open architecture allowing addition of new database support or customization of existing support
  • Logging executed SQL statements
  • Supporting major relational databases

For an example on how this library is used in an application, please refer to go-rest-api which is a starter kit for building RESTful APIs in Go.

Requirements

Go 1.13 or above.

Installation

Run the following command to install the package:

go get github.com/pocketbase/dbx

In addition, install the specific DB driver package for the kind of database to be used. Please refer to SQL database drivers for a complete list. For example, if you are using MySQL, you may install the following package:

go get github.com/go-sql-driver/mysql

and import it in your main code like the following:

import _ "github.com/go-sql-driver/mysql"

Supported Databases

The following databases are fully supported out of box:

  • SQLite
  • MySQL
  • PostgreSQL
  • MS SQL Server (2012 or above)
  • Oracle

For other databases, the query building feature may not work as expected. You can create a custom builder to solve the problem. Please see the last section for more details.

Getting Started

The following code snippet shows how you can use this package in order to access data from a MySQL database.

package main

import (
	"github.com/pocketbase/dbx"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	// create a new query
	q := db.NewQuery("SELECT id, name FROM users LIMIT 10")

	// fetch all rows into a struct array
	var users []struct {
		ID, Name string
	}
	q.All(&users)

	// fetch a single row into a struct
	var user struct {
		ID, Name string
	}
	q.One(&user)

	// fetch a single row into a string map
	data := dbx.NullStringMap{}
	q.One(data)

	// fetch row by row
	rows2, _ := q.Rows()
	for rows2.Next() {
		rows2.ScanStruct(&user)
		// rows.ScanMap(data)
		// rows.Scan(&id, &name)
	}
}

And the following example shows how to use the query building capability of this package.

package main

import (
	"github.com/pocketbase/dbx"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	// build a SELECT query
	//   SELECT `id`, `name` FROM `users` WHERE `name` LIKE '%Charles%' ORDER BY `id`
	q := db.Select("id", "name").
		From("users").
		Where(dbx.Like("name", "Charles")).
		OrderBy("id")

	// fetch all rows into a struct array
	var users []struct {
		ID, Name string
	}
	q.All(&users)

	// build an INSERT query
	//   INSERT INTO `users` (`name`) VALUES ('James')
	db.Insert("users", dbx.Params{
		"name": "James",
	}).Execute()
}

Connecting to Database

To connect to a database, call dbx.Open() in the same way as you would do with the Open() method in database/sql.

db, err := dbx.Open("mysql", "user:pass@hostname/db_name")

The method returns a dbx.DB instance which can be used to create and execute DB queries. Note that the method does not really establish a connection until a query is made using the returned dbx.DB instance. It also does not check the correctness of the data source name either. Call dbx.MustOpen() to make sure the data source name is correct.

Executing Queries

To execute a SQL statement, first create a dbx.Query instance by calling DB.NewQuery() with the SQL statement to be executed. And then call Query.Execute() to execute the query if the query is not meant to retrieving data. For example,

q := db.NewQuery("UPDATE users SET status=1 WHERE id=100")
result, err := q.Execute()

If the SQL statement does retrieve data (e.g. a SELECT statement), one of the following methods should be called, which will execute the query and populate the result into the specified variable(s).

  • Query.All(): populate all rows of the result into a slice of structs or NullString maps.
  • Query.One(): populate the first row of the result into a struct or a NullString map.
  • Query.Column(): populate the first column of the result into a slice.
  • Query.Row(): populate the first row of the result into a list of variables, one for each returning column.
  • Query.Rows(): returns a dbx.Rows instance to allow retrieving data row by row.

For example,

type User struct {
	ID   int
	Name string
}

var (
	users []User
	user  User

	row dbx.NullStringMap

	id   int
	name string

	err error
)

q := db.NewQuery("SELECT id, name FROM users LIMIT 10")

// populate all rows into a User slice
err = q.All(&users)
fmt.Println(users[0].ID, users[0].Name)

// populate the first row into a User struct
err = q.One(&user)
fmt.Println(user.ID, user.Name)

// populate the first row into a NullString map
err = q.One(&row)
fmt.Println(row["id"], row["name"])

var ids []int
err = q.Column(&ids)
fmt.Println(ids)

// populate the first row into id and name
err = q.Row(&id, &name)

// populate data row by row
rows, _ := q.Rows()
for rows.Next() {
	_ = rows.ScanMap(&row)
}

When populating a struct, the following rules are used to determine which columns should go into which struct fields:

  • Only exported struct fields can be populated.
  • A field receives data if its name is mapped to a column according to the field mapping function Query.FieldMapper. The default field mapping function separates words in a field name by underscores and turns them into lower case. For example, a field name FirstName will be mapped to the column name first_name, and MyID to my_id.
  • If a field has a db tag, the tag value will be used as the corresponding column name. If the db tag is a dash -, it means the field should NOT be populated.
  • For anonymous fields that are of struct type, they will be expanded and their component fields will be populated according to the rules described above.
  • For named fields that are of struct type, they will also be expanded. But their component fields will be prefixed with the struct names when being populated.

An exception to the above struct expansion is that when a struct type implements sql.Scanner or when it is time.Time. In this case, the field will be populated as a whole by the DB driver. Also, if a field is a pointer to some type, the field will be allocated memory and populated with the query result if it is not null.

The following example shows how fields are populated according to the rules above:

type User struct {
	id     int
	Type   int    `db:"-"`
	MyName string `db:"name"`
	Profile
	Address Address `db:"addr"`
}

type Profile struct {
	Age int
}

type Address struct {
	City string
}
  • User.id: not populated because the field is not exported;
  • User.Type: not populated because the db tag is -;
  • User.MyName: to be populated from the name column, according to the db tag;
  • Profile.Age: to be populated from the age column, since Profile is an anonymous field;
  • Address.City: to be populated from the addr.city column, since Address is a named field of struct type and its fields will be prefixed with addr. according to the db tag.

Note that if a column in the result does not have a corresponding struct field, it will be ignored. Similarly, if a struct field does not have a corresponding column in the result, it will not be populated.

Binding Parameters

A SQL statement is usually parameterized with dynamic values. For example, you may want to select the user record according to the user ID received from the client. Parameter binding should be used in this case, and it is almost always preferred to prevent from SQL injection attacks. Unlike database/sql which does anonymous parameter binding, dbx uses named parameter binding. Anonymous parameter binding is not supported, as it will mess up with named parameters. For example,

q := db.NewQuery("SELECT id, name FROM users WHERE id={:id}")
q.Bind(dbx.Params{"id": 100})
err := q.One(&user)

The above example will select the user record whose id is 100. The method Query.Bind() binds a set of named parameters to a SQL statement which contains parameter placeholders in the format of {:ParamName}.

If a SQL statement needs to be executed multiple times with different parameter values, it may be prepared to improve the performance. For example,

q := db.NewQuery("SELECT id, name FROM users WHERE id={:id}")
q.Prepare()
defer q.Close()

q.Bind(dbx.Params{"id": 100})
err := q.One(&user)

q.Bind(dbx.Params{"id": 200})
err = q.One(&user)

// ...

Cancelable Queries

Queries are cancelable when they are used with context.Context. In particular, by calling Query.WithContext() you can associate a context with a query and use the context to cancel the query while it is running. For example,

q := db.NewQuery("SELECT id, name FROM users")
err := q.WithContext(ctx).All(&users)

Building Queries

Instead of writing plain SQLs, dbx allows you to build SQLs programmatically, which often leads to cleaner, more secure, and DB-agnostic code. You can build three types of queries: the SELECT queries, the data manipulation queries, and the schema manipulation queries.

Building SELECT Queries

Building a SELECT query starts by calling DB.Select(). You can build different clauses of a SELECT query using the corresponding query building methods. For example,

db, _ := dbx.Open("mysql", "user:pass@/example")
err := db.Select("id", "name").
	From("users").
	Where(dbx.HashExp{"id": 100}).
	One(&user)

The above code will generate and execute the following SQL statement:

SELECT `id`, `name` FROM `users` WHERE `id`={:p0}

Notice how the table and column names are properly quoted according to the currently using database type. And parameter binding is used to populate the value of p0 in the WHERE clause.

Every SQL keyword has a corresponding query building method. For example, SELECT corresponds to Select(), FROM corresponds to From(), WHERE corresponds to Where(), and so on. You can chain these method calls together, just like you would do when writing a plain SQL. Each of these methods returns the query instance (of type dbx.SelectQuery) that is being built. Once you finish building a query, you may call methods such as One(), All() to execute the query and populate data into variables. You may also explicitly call Build() to build the query and turn it into a dbx.Query instance which may allow you to get the SQL statement and do other interesting work.

Building Query Conditions

dbx supports very flexible and powerful query condition building which can be used to build SQL clauses such as WHERE, HAVING, etc. For example,

// id=100
dbx.NewExp("id={:id}", dbx.Params{"id": 100})

// id=100 AND status=1
dbx.HashExp{"id": 100, "status": 1}

// status=1 OR age>30
dbx.Or(dbx.HashExp{"status": 1}, dbx.NewExp("age>30"))

// name LIKE '%admin%' AND name LIKE '%example%'
dbx.Like("name", "admin", "example")

When building a query condition expression, its parameter values will be populated using parameter binding, which prevents SQL injection from happening. Also if an expression involves column names, they will be properly quoted. The following condition building functions are available:

  • dbx.NewExp(): creating a condition using the given expression string and binding parameters. For example, dbx.NewExp("id={:id}", dbx.Params{"id":100}) would create the expression id=100.
  • dbx.HashExp: a map type that represents name-value pairs concatenated by AND operators. For example, dbx.HashExp{"id":100, "status":1} would create id=100 AND status=1.
  • dbx.Not(): creating a NOT expression by prepending NOT to the given expression.
  • dbx.And(): creating an AND expression by concatenating the given expressions with the AND operators.
  • dbx.Or(): creating an OR expression by concatenating the given expressions with the OR operators.
  • dbx.In(): creating an IN expression for the specified column and the range of values. For example, dbx.In("age", 30, 40, 50) would create the expression age IN (30, 40, 50). Note that if the value range is empty, it will generate an expression representing a false value.
  • dbx.NotIn(): creating an NOT IN expression. This is very similar to dbx.In().
  • dbx.Like(): creating a LIKE expression for the specified column and the range of values. For example, dbx.Like("title", "golang", "framework") would create the expression title LIKE "%golang%" AND title LIKE "%framework%". You can further customize a LIKE expression by calling Escape() and/or Match() functions of the resulting expression. Note that if the value range is empty, it will generate an empty expression.
  • dbx.NotLike(): creating a NOT LIKE expression. This is very similar to dbx.Like().
  • dbx.OrLike(): creating a LIKE expression but concatenating different LIKE sub-expressions using OR instead of AND.
  • dbx.OrNotLike(): creating a NOT LIKE expression and concatenating different NOT LIKE sub-expressions using OR instead of AND.
  • dbx.Exists(): creating an EXISTS expression by prepending EXISTS to the given expression.
  • dbx.NotExists(): creating a NOT EXISTS expression by prepending NOT EXISTS to the given expression.
  • dbx.Between(): creating a BETWEEN expression. For example, dbx.Between("age", 30, 40) would create the expression age BETWEEN 30 AND 40.
  • dbx.NotBetween(): creating a NOT BETWEEN expression. For example

You may also create other convenient functions to help building query conditions, as long as the functions return an object implementing the dbx.Expression interface.

Building Data Manipulation Queries

Data manipulation queries are those changing the data in the database, such as INSERT, UPDATE, DELETE statements. Such queries can be built by calling the corresponding methods of DB. For example,

db, _ := dbx.Open("mysql", "user:pass@/example")

// INSERT INTO `users` (`name`, `email`) VALUES ({:p0}, {:p1})
err := db.Insert("users", dbx.Params{
	"name": "James",
	"email": "james@example.com",
}).Execute()

// UPDATE `users` SET `status`={:p0} WHERE `id`={:p1}
err = db.Update("users", dbx.Params{"status": 1}, dbx.HashExp{"id": 100}).Execute()

// DELETE FROM `users` WHERE `status`={:p0}
err = db.Delete("users", dbx.HashExp{"status": 2}).Execute()

When building data manipulation queries, remember to call Execute() at the end to execute the queries.

Building Schema Manipulation Queries

Schema manipulation queries are those changing the database schema, such as creating a new table, adding a new column. These queries can be built by calling the corresponding methods of DB. For example,

db, _ := dbx.Open("mysql", "user:pass@/example")

// CREATE TABLE `users` (`id` int primary key, `name` varchar(255))
q := db.CreateTable("users", map[string]string{
	"id": "int primary key",
	"name": "varchar(255)",
})
err := q.Execute()

CRUD Operations

Although dbx is not an ORM, it does provide a very convenient way to do typical CRUD (Create, Read, Update, Delete) operations without the need of writing plain SQL statements.

To use the CRUD feature, first define a struct type for a table. By default, a struct is associated with a table whose name is the snake case version of the struct type name. For example, a struct named MyCustomer corresponds to the table name my_customer. You may explicitly specify the table name for a struct by implementing the dbx.TableModel interface. For example,

type MyCustomer struct{}

func (c MyCustomer) TableName() string {
	return "customer"
}

Note that the TableName method should be defined with a value receiver instead of a pointer receiver.

If the struct has a field named ID or Id, by default the field will be treated as the primary key field. If you want to use a different field as the primary key, tag it with db:"pk". You may tag multiple fields for composite primary keys. Note that if you also want to explicitly specify the column name for a primary key field, you should use the tag format db:"pk,col_name".

You can give a common prefix or suffix to your table names by defining your own table name mapping via DB.TableMapFunc. For example, the following code prefixes tbl_ to all table names.

db.TableMapper = func(a interface{}) string {
    return "tbl_" + GetTableName(a)
}
Create

To create (insert) a new row using a model, call the ModelQuery.Insert() method. For example,

type Customer struct {
	ID     int
	Name   string
	Email  string
	Status int
}

db, _ := dbx.Open("mysql", "user:pass@/example")

customer := Customer{
	Name: "example",
	Email: "test@example.com",
}
// INSERT INTO customer (name, email, status) VALUES ('example', 'test@example.com', 0)
err := db.Model(&customer).Insert()

This will insert a row using the values from all public fields (except the primary key field if it is empty) in the struct. If a primary key field is zero (a integer zero or a nil pointer), it is assumed to be auto-incremental and will be automatically filled with the last insertion ID after a successful insertion.

You can explicitly specify the fields that should be inserted by passing the list of the field names to the Insert() method. You can also exclude certain fields from being inserted by calling Exclude() before calling Insert(). For example,

db, _ := dbx.Open("mysql", "user:pass@/example")

// insert only Name and Email fields
err := db.Model(&customer).Insert("Name", "Email")
// insert all public fields except Status
err = db.Model(&customer).Exclude("Status").Insert()
// insert only Name
err = db.Model(&customer).Exclude("Status").Insert("Name", "Status")
Read

To read a model by a given primary key value, call SelectQuery.Model().

db, _ := dbx.Open("mysql", "user:pass@/example")

var customer Customer
// SELECT * FROM customer WHERE id=100
err := db.Select().Model(100, &customer)

// SELECT name, email FROM customer WHERE status=1 AND id=100
err = db.Select("name", "email").Where(dbx.HashExp{"status": 1}).Model(100, &customer)

Note that SelectQuery.Model() does not support composite primary keys. You should use SelectQuery.One() in this case. For example,

db, _ := dbx.Open("mysql", "user:pass@/example")

var orderItem OrderItem

// SELECT * FROM order_item WHERE order_id=100 AND item_id=20
err := db.Select().Where(dbx.HashExp{"order_id": 100, "item_id": 20}).One(&orderItem)

In the above queries, we do not call From() to specify which table to select data from. This is because the select query automatically sets the table according to the model struct being populated. If the struct implements TableModel, the value returned by its TableName() method will be used as the table name. Otherwise, the snake case version of the struct type name will be the table name.

You may also call SelectQuery.All() to read a list of model structs. Similarly, you do not need to call From() if the table name can be inferred from the model structs.

Update

To update a model, call the ModelQuery.Update() method. Like Insert(), by default, the Update() method will update all public fields except primary key fields of the model. You can explicitly specify which fields can be updated and which cannot in the same way as described for the Insert() method. For example,

db, _ := dbx.Open("mysql", "user:pass@/example")

// update all public fields of customer
err := db.Model(&customer).Update()
// update only Status
err = db.Model(&customer).Update("Status")
// update all public fields except Status
err = db.Model(&customer).Exclude("Status").Update()

Note that the Update() method assumes that the primary keys are immutable. It uses the primary key value of the model to look for the row that should be updated. An error will be returned if a model does not have a primary key.

Delete

To delete a model, call the ModelQuery.Delete() method. The method deletes the row using the primary key value specified by the model. If the model does not have a primary key, an error will be returned. For example,

db, _ := dbx.Open("mysql", "user:pass@/example")

err := db.Model(&customer).Delete()
Null Handling

To represent a nullable database value, you can use a pointer type. If the pointer is nil, it means the corresponding database value is null.

Another option to represent a database null is to use sql.NullXyz types. For example, if a string column is nullable, you may use sql.NullString. The NullString.Valid field indicates whether the value is a null or not, and NullString.String returns the string value when it is not null. Because sql.NulLXyz types do not handle JSON marshalling, you may use the null package, instead.

Below is an example of handling nulls:

type Customer struct {
	ID        int
	Email     string
	FirstName *string        // use pointer to represent null
	LastName  sql.NullString // use sql.NullString to represent null
}

Quoting Table and Column Names

Databases vary in quoting table and column names. To allow writing DB-agnostic SQLs, dbx introduces a special syntax in quoting table and column names. A word enclosed within {{ and }} is treated as a table name and will be quoted according to the particular DB driver. Similarly, a word enclosed within [[ and ]] is treated as a column name and will be quoted accordingly as well. For example, when working with a MySQL database, the following query will be properly quoted:

// SELECT * FROM `users` WHERE `status`=1
q := db.NewQuery("SELECT * FROM {{users}} WHERE [[status]]=1")

Note that if a table or column name contains a prefix, it will still be properly quoted. For example, {{public.users}} will be quoted as "public"."users" for PostgreSQL.

Using Transactions

You can use all aforementioned query execution and building methods with transaction. For example,

db, _ := dbx.Open("mysql", "user:pass@/example")

tx, _ := db.Begin()

_, err1 := tx.Insert("users", dbx.Params{
	"name": "user1",
}).Execute()
_, err2 := tx.Insert("users", dbx.Params{
	"name": "user2",
}).Execute()

if err1 == nil && err2 == nil {
	tx.Commit()
} else {
	tx.Rollback()
}

You may use DB.Transactional() to simplify your transactional code without explicitly committing or rolling back transactions. The method will start a transaction and automatically roll back the transaction if the callback returns an error. Otherwise it will automatically commit the transaction.

db, _ := dbx.Open("mysql", "user:pass@/example")

err := db.Transactional(func(tx *dbx.Tx) error {
	var err error
	_, err = tx.Insert("users", dbx.Params{
		"name": "user1",
	}).Execute()
	if err != nil {
		return err
	}
	_, err = tx.Insert("users", dbx.Params{
		"name": "user2",
	}).Execute()
	return err
})

fmt.Println(err)

Logging Executed SQL Statements

You can log and instrument DB queries by installing loggers with a DB connection. There are three kinds of loggers you can install:

  • DB.LogFunc: this is called each time when a SQL statement is queried or executed. The function signature is the same as that of fmt.Printf, which makes it very easy to use.
  • DB.QueryLogFunc: this is called each time when querying with a SQL statement.
  • DB.ExecLogFunc: this is called when executing a SQL statement.

The following example shows how you can make use of these loggers.

package main

import (
	"context"
	"database/sql"
	"log"
	"time"

	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	// simple logging
	db.LogFunc = log.Printf

	// or you can use the following more flexible logging
	db.QueryLogFunc = func(ctx context.Context, t time.Duration, sql string, rows *sql.Rows, err error) {
		log.Printf("[%.2fms] Query SQL: %v", float64(t.Milliseconds()), sql)
	}
	db.ExecLogFunc = func(ctx context.Context, t time.Duration, sql string, result sql.Result, err error) {
		log.Printf("[%.2fms] Execute SQL: %v", float64(t.Milliseconds()), sql)
	}
	// ...
}

Supporting New Databases

While dbx provides out-of-box query building support for most major relational databases, its open architecture allows you to add support for new databases. The effort of adding support for a new database involves:

  • Create a struct that implements the QueryBuilder interface. You may use BaseQueryBuilder directly or extend it via composition.
  • Create a struct that implements the Builder interface. You may extend BaseBuilder via composition.
  • Write an init() function to register the new builder in dbx.BuilderFuncMap.

Documentation

Overview

Package dbx provides a set of DB-agnostic and easy-to-use query building methods for relational databases.

Example (CrudOperations)

This example shows how to do CRUD operations. 

package main

import (
	"github.com/pocketbase/dbx"
)

type Customer struct {
	ID   string
	Name string
}

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	var customer Customer

	// read a customer: SELECT * FROM customer WHERE id=100
	db.Select().Model(100, &customer)

	// create a customer: INSERT INTO customer (name) VALUES ('test')
	db.Model(&customer).Insert()

	// update a customer: UPDATE customer SET name='test' WHERE id=100
	db.Model(&customer).Update()

	// delete a customer: DELETE FROM customer WHERE id=100
	db.Model(&customer).Delete()
}

Output:
Example (DbQueries)

This example shows how to populate DB data in different ways. 

package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	// create a new query
	q := db.NewQuery("SELECT id, name FROM users LIMIT 10")

	// fetch all rows into a struct array
	var users []struct {
		ID, Name string
	}
	q.All(&users)

	// fetch a single row into a struct
	var user struct {
		ID, Name string
	}
	q.One(&user)

	// fetch a single row into a string map
	data := dbx.NullStringMap{}
	q.One(data)

	// fetch row by row
	rows2, _ := q.Rows()
	for rows2.Next() {
		rows2.ScanStruct(&user)
		// rows.ScanMap(data)
		// rows.Scan(&id, &name)
	}
}

Output:
Example (QueryBuilder)

This example shows how to use query builder to build DB queries. 

package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	// build a SELECT query
	//   SELECT `id`, `name` FROM `users` WHERE `name` LIKE '%Charles%' ORDER BY `id`
	q := db.Select("id", "name").
		From("users").
		Where(dbx.Like("name", "Charles")).
		OrderBy("id")

	// fetch all rows into a struct array
	var users []struct {
		ID, Name string
	}
	q.All(&users)

	// build an INSERT query
	//   INSERT INTO `users` (name) VALUES ('James')
	db.Insert("users", dbx.Params{
		"name": "James",
	}).Execute()
}

Output:
Example (Transactions)

This example shows how to use query builder in transactions. 

package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	db.Transactional(func(tx *dbx.Tx) error {
		_, err := tx.Insert("user", dbx.Params{
			"name": "user1",
		}).Execute()
		if err != nil {
			return err
		}
		_, err = tx.Insert("user", dbx.Params{
			"name": "user2",
		}).Execute()
		return err
	})
}

Output:

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	MissingPKError   = errors.New("missing primary key declaration")
	CompositePKError = errors.New("composite primary key is not supported")
)
View Source 
var BuilderFuncMap = map[string]BuilderFunc{
	"sqlite":   NewSqliteBuilder,
	"sqlite3":  NewSqliteBuilder,
	"mysql":    NewMysqlBuilder,
	"postgres": NewPgsqlBuilder,
	"pgx":      NewPgsqlBuilder,
	"mssql":    NewMssqlBuilder,
	"oci8":     NewOciBuilder,
}

BuilderFuncMap lists supported BuilderFunc according to DB driver names. You may modify this variable to add the builder support for a new DB driver. If a DB driver is not listed here, the StandardBuilder will be used.

View Source 
var (
	// DbTag is the name of the struct tag used to specify the column name for the associated struct field
	DbTag = "db"
)
View Source 
var DefaultLikeEscape = []string{"\\", "\\\\", "%", "\\%", "_", "\\_"}

DefaultLikeEscape specifies the default special character escaping for LIKE expressions The strings at 2i positions are the special characters to be escaped while those at 2i+1 positions are the corresponding escaped versions.

Functions

func DefaultFieldMapFunc 

func DefaultFieldMapFunc(f string) string

DefaultFieldMapFunc maps a field name to a DB column name. The mapping rule set by this method is that words in a field name will be separated by underscores and the name will be turned into lower case. For example, "FirstName" maps to "first_name", and "MyID" becomes "my_id". See DB.FieldMapper for more details.

func GetTableName added in v1.0.4 

func GetTableName(a interface{}) string

GetTableName implements the default way of determining the table name corresponding to the given model struct or slice of structs. To get the actual table name for a model, you should use DB.TableMapFunc() instead. Do not call this method in a model's TableName() method because it will cause infinite loop.

Types

type AllHookFunc added in v1.10.0 

type AllHookFunc func(q *Query, sliceA interface{}, op func(sliceB interface{}) error) error

AllHookFunc executes right before the query populate the row result from All() call (aka. op).

type AndOrExp 

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

AndOrExp represents an expression that concatenates multiple expressions using either "AND" or "OR".

func (*AndOrExp) Build 

func (e *AndOrExp) Build(db *DB, params Params) string

Build converts an expression into a SQL fragment.

type BaseBuilder 

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

BaseBuilder provides a basic implementation of the Builder interface.

func NewBaseBuilder 

func NewBaseBuilder(db *DB, executor Executor) *BaseBuilder

NewBaseBuilder creates a new BaseBuilder instance.

func (*BaseBuilder) AddColumn 

func (b *BaseBuilder) AddColumn(table, col, typ string) *Query

AddColumn creates a Query that can be used to add a column to a table.

func (*BaseBuilder) AddForeignKey 

func (b *BaseBuilder) AddForeignKey(table, name string, cols, refCols []string, refTable string, options ...string) *Query

AddForeignKey creates a Query that can be used to add a foreign key constraint to a table. The length of cols and refCols must be the same as they refer to the primary and referential columns. The optional "options" parameters will be appended to the SQL statement. They can be used to specify options such as "ON DELETE CASCADE".

func (*BaseBuilder) AddPrimaryKey 

func (b *BaseBuilder) AddPrimaryKey(table, name string, cols ...string) *Query

AddPrimaryKey creates a Query that can be used to specify primary key(s) for a table. The "name" parameter specifies the name of the primary key constraint.

func (*BaseBuilder) AlterColumn 

func (b *BaseBuilder) AlterColumn(table, col, typ string) *Query

AlterColumn creates a Query that can be used to change the definition of a table column.

func (*BaseBuilder) CreateIndex 

func (b *BaseBuilder) CreateIndex(table, name string, cols ...string) *Query

CreateIndex creates a Query that can be used to create an index for a table.

func (*BaseBuilder) CreateTable 

func (b *BaseBuilder) CreateTable(table string, cols map[string]string, options ...string) *Query

CreateTable creates a Query that represents a CREATE TABLE SQL statement. The keys of cols are the column names, while the values of cols are the corresponding column types. The optional "options" parameters will be appended to the generated SQL statement.

func (*BaseBuilder) CreateUniqueIndex 

func (b *BaseBuilder) CreateUniqueIndex(table, name string, cols ...string) *Query

CreateUniqueIndex creates a Query that can be used to create a unique index for a table.

func (*BaseBuilder) DB 

func (b *BaseBuilder) DB() *DB

DB returns the DB instance that this builder is associated with.

func (*BaseBuilder) Delete 

func (b *BaseBuilder) Delete(table string, where Expression) *Query

Delete creates a Query that represents a DELETE SQL statement. If the "where" expression is nil, the DELETE SQL statement will have no WHERE clause (be careful in this case as the SQL statement will delete ALL rows in the table).

func (*BaseBuilder) DropColumn 

func (b *BaseBuilder) DropColumn(table, col string) *Query

DropColumn creates a Query that can be used to drop a column from a table.

func (*BaseBuilder) DropForeignKey 

func (b *BaseBuilder) DropForeignKey(table, name string) *Query

DropForeignKey creates a Query that can be used to remove the named foreign key constraint from a table.

func (*BaseBuilder) DropIndex 

func (b *BaseBuilder) DropIndex(table, name string) *Query

DropIndex creates a Query that can be used to remove the named index from a table.

func (*BaseBuilder) DropPrimaryKey 

func (b *BaseBuilder) DropPrimaryKey(table, name string) *Query

DropPrimaryKey creates a Query that can be used to remove the named primary key constraint from a table.

func (*BaseBuilder) DropTable 

func (b *BaseBuilder) DropTable(table string) *Query

DropTable creates a Query that can be used to drop a table.

func (*BaseBuilder) Executor 

func (b *BaseBuilder) Executor() Executor

Executor returns the executor object (a DB instance or a transaction) for executing SQL statements.

func (*BaseBuilder) GeneratePlaceholder 

func (b *BaseBuilder) GeneratePlaceholder(int) string

GeneratePlaceholder generates an anonymous parameter placeholder with the given parameter ID.

func (*BaseBuilder) Insert 

func (b *BaseBuilder) Insert(table string, cols Params) *Query

Insert creates a Query that represents an INSERT SQL statement. The keys of cols are the column names, while the values of cols are the corresponding column values to be inserted.

func (*BaseBuilder) NewQuery 

func (b *BaseBuilder) NewQuery(sql string) *Query

NewQuery creates a new Query object with the given SQL statement. The SQL statement may contain parameter placeholders which can be bound with actual parameter values before the statement is executed.

func (*BaseBuilder) Quote 

func (b *BaseBuilder) Quote(s string) string

Quote quotes a string so that it can be embedded in a SQL statement as a string value.

func (*BaseBuilder) QuoteSimpleColumnName 

func (b *BaseBuilder) QuoteSimpleColumnName(s string) string

QuoteSimpleColumnName quotes a simple column name. A simple column name does not contain any table prefix.

func (*BaseBuilder) QuoteSimpleTableName 

func (b *BaseBuilder) QuoteSimpleTableName(s string) string

QuoteSimpleTableName quotes a simple table name. A simple table name does not contain any schema prefix.

func (*BaseBuilder) RenameColumn 

func (b *BaseBuilder) RenameColumn(table, oldName, newName string) *Query

RenameColumn creates a Query that can be used to rename a column in a table.

func (*BaseBuilder) RenameTable 

func (b *BaseBuilder) RenameTable(oldName, newName string) *Query

RenameTable creates a Query that can be used to rename a table.

func (*BaseBuilder) TruncateTable 

func (b *BaseBuilder) TruncateTable(table string) *Query

TruncateTable creates a Query that can be used to truncate a table.

func (*BaseBuilder) Update 

func (b *BaseBuilder) Update(table string, cols Params, where Expression) *Query

Update creates a Query that represents an UPDATE SQL statement. The keys of cols are the column names, while the values of cols are the corresponding new column values. If the "where" expression is nil, the UPDATE SQL statement will have no WHERE clause (be careful in this case as the SQL statement will update ALL rows in the table).

func (*BaseBuilder) Upsert 

func (b *BaseBuilder) Upsert(table string, cols Params, constraints ...string) *Query

Upsert creates a Query that represents an UPSERT SQL statement. Upsert inserts a row into the table if the primary key or unique index is not found. Otherwise it will update the row with the new values. The keys of cols are the column names, while the values of cols are the corresponding column values to be inserted.

type BaseQueryBuilder 

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

BaseQueryBuilder provides a basic implementation of QueryBuilder.

func NewBaseQueryBuilder 

func NewBaseQueryBuilder(db *DB) *BaseQueryBuilder

NewBaseQueryBuilder creates a new BaseQueryBuilder instance.

func (*BaseQueryBuilder) BuildFrom 

func (q *BaseQueryBuilder) BuildFrom(tables []string) string

BuildFrom generates a FROM clause from the given tables.

func (*BaseQueryBuilder) BuildGroupBy 

func (q *BaseQueryBuilder) BuildGroupBy(cols []string) string

BuildGroupBy generates a GROUP BY clause from the given group-by columns.

func (*BaseQueryBuilder) BuildHaving 

func (q *BaseQueryBuilder) BuildHaving(e Expression, params Params) string

BuildHaving generates a HAVING clause from the given expression.

func (*BaseQueryBuilder) BuildJoin 

func (q *BaseQueryBuilder) BuildJoin(joins []JoinInfo, params Params) string

BuildJoin generates a JOIN clause from the given join information.

func (*BaseQueryBuilder) BuildLimit 

func (q *BaseQueryBuilder) BuildLimit(limit int64, offset int64) string

BuildLimit generates the LIMIT clause.

func (*BaseQueryBuilder) BuildOrderBy 

func (q *BaseQueryBuilder) BuildOrderBy(cols []string) string

BuildOrderBy generates the ORDER BY clause.

func (*BaseQueryBuilder) BuildOrderByAndLimit 

func (q *BaseQueryBuilder) BuildOrderByAndLimit(sql string, cols []string, limit int64, offset int64) string

BuildOrderByAndLimit generates the ORDER BY and LIMIT clauses.

func (*BaseQueryBuilder) BuildSelect 

func (q *BaseQueryBuilder) BuildSelect(cols []string, distinct bool, option string) string

BuildSelect generates a SELECT clause from the given selected column names.

func (*BaseQueryBuilder) BuildUnion 

func (q *BaseQueryBuilder) BuildUnion(unions []UnionInfo, params Params) string

BuildUnion generates a UNION clause from the given union information.

func (*BaseQueryBuilder) BuildWhere 

func (q *BaseQueryBuilder) BuildWhere(e Expression, params Params) string

BuildWhere generates a WHERE clause from the given expression.

func (*BaseQueryBuilder) DB 

func (q *BaseQueryBuilder) DB() *DB

DB returns the DB instance associated with the query builder.

type BetweenExp 

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

BetweenExp represents a BETWEEN or a NOT BETWEEN expression.

func (*BetweenExp) Build 

func (e *BetweenExp) Build(db *DB, params Params) string

Build converts an expression into a SQL fragment.

type BuildHookFunc added in v1.10.0 

type BuildHookFunc func(q *Query)

BuildHookFunc defines a callback function that is executed on Query creation.

type Builder 

type Builder interface {
	// NewQuery creates a new Query object with the given SQL statement.
	// The SQL statement may contain parameter placeholders which can be bound with actual parameter
	// values before the statement is executed.
	NewQuery(string) *Query
	// Select returns a new SelectQuery object that can be used to build a SELECT statement.
	// The parameters to this method should be the list column names to be selected.
	// A column name may have an optional alias name. For example, Select("id", "my_name AS name").
	Select(...string) *SelectQuery
	// ModelQuery returns a new ModelQuery object that can be used to perform model insertion, update, and deletion.
	// The parameter to this method should be a pointer to the model struct that needs to be inserted, updated, or deleted.
	Model(interface{}) *ModelQuery

	// GeneratePlaceholder generates an anonymous parameter placeholder with the given parameter ID.
	GeneratePlaceholder(int) string

	// Quote quotes a string so that it can be embedded in a SQL statement as a string value.
	Quote(string) string
	// QuoteSimpleTableName quotes a simple table name.
	// A simple table name does not contain any schema prefix.
	QuoteSimpleTableName(string) string
	// QuoteSimpleColumnName quotes a simple column name.
	// A simple column name does not contain any table prefix.
	QuoteSimpleColumnName(string) string

	// QueryBuilder returns the query builder supporting the current DB.
	QueryBuilder() QueryBuilder

	// Insert creates a Query that represents an INSERT SQL statement.
	// The keys of cols are the column names, while the values of cols are the corresponding column
	// values to be inserted.
	Insert(table string, cols Params) *Query
	// Upsert creates a Query that represents an UPSERT SQL statement.
	// Upsert inserts a row into the table if the primary key or unique index is not found.
	// Otherwise it will update the row with the new values.
	// The keys of cols are the column names, while the values of cols are the corresponding column
	// values to be inserted.
	Upsert(table string, cols Params, constraints ...string) *Query
	// Update creates a Query that represents an UPDATE SQL statement.
	// The keys of cols are the column names, while the values of cols are the corresponding new column
	// values. If the "where" expression is nil, the UPDATE SQL statement will have no WHERE clause
	// (be careful in this case as the SQL statement will update ALL rows in the table).
	Update(table string, cols Params, where Expression) *Query
	// Delete creates a Query that represents a DELETE SQL statement.
	// If the "where" expression is nil, the DELETE SQL statement will have no WHERE clause
	// (be careful in this case as the SQL statement will delete ALL rows in the table).
	Delete(table string, where Expression) *Query

	// CreateTable creates a Query that represents a CREATE TABLE SQL statement.
	// The keys of cols are the column names, while the values of cols are the corresponding column types.
	// The optional "options" parameters will be appended to the generated SQL statement.
	CreateTable(table string, cols map[string]string, options ...string) *Query
	// RenameTable creates a Query that can be used to rename a table.
	RenameTable(oldName, newName string) *Query
	// DropTable creates a Query that can be used to drop a table.
	DropTable(table string) *Query
	// TruncateTable creates a Query that can be used to truncate a table.
	TruncateTable(table string) *Query

	// AddColumn creates a Query that can be used to add a column to a table.
	AddColumn(table, col, typ string) *Query
	// DropColumn creates a Query that can be used to drop a column from a table.
	DropColumn(table, col string) *Query
	// RenameColumn creates a Query that can be used to rename a column in a table.
	RenameColumn(table, oldName, newName string) *Query
	// AlterColumn creates a Query that can be used to change the definition of a table column.
	AlterColumn(table, col, typ string) *Query

	// AddPrimaryKey creates a Query that can be used to specify primary key(s) for a table.
	// The "name" parameter specifies the name of the primary key constraint.
	AddPrimaryKey(table, name string, cols ...string) *Query
	// DropPrimaryKey creates a Query that can be used to remove the named primary key constraint from a table.
	DropPrimaryKey(table, name string) *Query

	// AddForeignKey creates a Query that can be used to add a foreign key constraint to a table.
	// The length of cols and refCols must be the same as they refer to the primary and referential columns.
	// The optional "options" parameters will be appended to the SQL statement. They can be used to
	// specify options such as "ON DELETE CASCADE".
	AddForeignKey(table, name string, cols, refCols []string, refTable string, options ...string) *Query
	// DropForeignKey creates a Query that can be used to remove the named foreign key constraint from a table.
	DropForeignKey(table, name string) *Query

	// CreateIndex creates a Query that can be used to create an index for a table.
	CreateIndex(table, name string, cols ...string) *Query
	// CreateUniqueIndex creates a Query that can be used to create a unique index for a table.
	CreateUniqueIndex(table, name string, cols ...string) *Query
	// DropIndex creates a Query that can be used to remove the named index from a table.
	DropIndex(table, name string) *Query
}

Builder supports building SQL statements in a DB-agnostic way. Builder mainly provides two sets of query building methods: those building SELECT statements and those manipulating DB data or schema (e.g. INSERT statements, CREATE TABLE statements).

func NewMssqlBuilder 

func NewMssqlBuilder(db *DB, executor Executor) Builder

NewMssqlBuilder creates a new MssqlBuilder instance.

func NewMysqlBuilder 

func NewMysqlBuilder(db *DB, executor Executor) Builder

NewMysqlBuilder creates a new MysqlBuilder instance.

func NewOciBuilder 

func NewOciBuilder(db *DB, executor Executor) Builder

NewOciBuilder creates a new OciBuilder instance.

func NewPgsqlBuilder 

func NewPgsqlBuilder(db *DB, executor Executor) Builder

NewPgsqlBuilder creates a new PgsqlBuilder instance.

func NewSqliteBuilder 

func NewSqliteBuilder(db *DB, executor Executor) Builder

NewSqliteBuilder creates a new SqliteBuilder instance.

func NewStandardBuilder 

func NewStandardBuilder(db *DB, executor Executor) Builder

NewStandardBuilder creates a new StandardBuilder instance.

type BuilderFunc 

type BuilderFunc func(*DB, Executor) Builder

BuilderFunc creates a Builder instance using the given DB instance and Executor.

type DB 

type DB struct {
	Builder

	// FieldMapper maps struct fields to DB columns. Defaults to DefaultFieldMapFunc.
	FieldMapper FieldMapFunc
	// TableMapper maps structs to table names. Defaults to GetTableName.
	TableMapper TableMapFunc
	// LogFunc logs the SQL statements being executed. Defaults to nil, meaning no logging.
	LogFunc LogFunc
	// PerfFunc logs the SQL execution time. Defaults to nil, meaning no performance profiling.
	// Deprecated: Please use QueryLogFunc and ExecLogFunc instead.
	PerfFunc PerfFunc
	// QueryLogFunc is called each time when performing a SQL query that returns data.
	QueryLogFunc QueryLogFunc
	// ExecLogFunc is called each time when a SQL statement is executed.
	ExecLogFunc ExecLogFunc
	// contains filtered or unexported fields
}

DB enhances sql.DB by providing a set of DB-agnostic query building methods. DB allows easier query building and population of data into Go variables.

Example 
package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	// queries data through a plain SQL
	var users []struct {
		ID, Name string
	}
	db.NewQuery("SELECT id, name FROM users WHERE age=30").All(&users)

	// queries data using query builder
	db.Select("id", "name").From("users").Where(dbx.HashExp{"age": 30}).All(&users)

	// executes a plain SQL
	db.NewQuery("INSERT INTO users (name) SET ({:name})").Bind(dbx.Params{"name": "James"}).Execute()

	// executes a SQL using query builder
	db.Insert("users", dbx.Params{"name": "James"}).Execute()
}

Output:

func MustOpen 

func MustOpen(driverName, dsn string) (*DB, error)

MustOpen opens a database and establishes a connection to it. Please refer to sql.Open() and sql.Ping() for more information.

func NewFromDB added in v1.0.12 

func NewFromDB(sqlDB *sql.DB, driverName string) *DB

NewFromDB encapsulates an existing database connection.

func Open 

func Open(driverName, dsn string) (*DB, error)

Open opens a database specified by a driver name and data source name (DSN). Note that Open does not check if DSN is specified correctly. It doesn't try to establish a DB connection either. Please refer to sql.Open() for more information.

func (*DB) Begin 

func (db *DB) Begin() (*Tx, error)

Begin starts a transaction.

Example 
package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	tx, _ := db.Begin()

	_, err1 := tx.Insert("user", dbx.Params{
		"name": "user1",
	}).Execute()
	_, err2 := tx.Insert("user", dbx.Params{
		"name": "user2",
	}).Execute()

	if err1 == nil && err2 == nil {
		tx.Commit()
	} else {
		tx.Rollback()
	}
}

Output:

func (*DB) BeginTx added in v1.6.0 

func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)

BeginTx starts a transaction with the given context and transaction options.

func (*DB) Clone added in v1.0.12 

func (db *DB) Clone() *DB

Clone makes a shallow copy of DB.

func (*DB) Close 

func (db *DB) Close() error

Close closes the database, releasing any open resources. It is rare to Close a DB, as the DB handle is meant to be long-lived and shared between many goroutines.

func (*DB) Context added in v1.6.0 

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

Context returns the context associated with the DB instance. It returns nil if no context is associated.

func (*DB) DB 

func (db *DB) DB() *sql.DB

DB returns the sql.DB instance encapsulated by dbx.DB.

func (*DB) DriverName 

func (db *DB) DriverName() string

DriverName returns the name of the DB driver.

func (*DB) QuoteColumnName 

func (db *DB) QuoteColumnName(s string) string

QuoteColumnName quotes the given column name appropriately. If the table name contains table name prefix, it will be handled accordingly. This method will do nothing if the column name is already quoted or if it contains parenthesis.

func (*DB) QuoteTableName 

func (db *DB) QuoteTableName(s string) string

QuoteTableName quotes the given table name appropriately. If the table name contains DB schema prefix, it will be handled accordingly. This method will do nothing if the table name is already quoted or if it contains parenthesis.

func (*DB) Transactional 

func (db *DB) Transactional(f func(*Tx) error) (err error)

Transactional starts a transaction and executes the given function. If the function returns an error, the transaction will be rolled back. Otherwise, the transaction will be committed.

func (*DB) TransactionalContext added in v1.6.0 

func (db *DB) TransactionalContext(ctx context.Context, opts *sql.TxOptions, f func(*Tx) error) (err error)

TransactionalContext starts a transaction and executes the given function with the given context and transaction options. If the function returns an error, the transaction will be rolled back. Otherwise, the transaction will be committed.

func (*DB) WithContext added in v1.6.0 

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

WithContext returns a new instance of DB associated with the given context.

func (*DB) Wrap added in v1.0.12 

func (db *DB) Wrap(sqlTx *sql.Tx) *Tx

Wrap encapsulates an existing transaction.

type EncloseExp added in v1.9.0 

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

EncloseExp represents a parenthesis enclosed expression.

func (*EncloseExp) Build added in v1.9.0 

func (e *EncloseExp) Build(db *DB, params Params) string

Build converts an expression into a SQL fragment.

type Errors 

type Errors []error

Errors represents a list of errors.

func (Errors) Error 

func (errs Errors) Error() string

Error returns the error string of Errors.

type ExecHookFunc added in v1.10.0 

type ExecHookFunc func(q *Query, op func() error) error

ExecHookFunc executes before op allowing custom handling like auto fail/retry.

type ExecLogFunc added in v1.6.0 

type ExecLogFunc func(ctx context.Context, t time.Duration, sql string, result sql.Result, err error)

ExecLogFunc is called each time when a SQL statement is executed. The "t" parameter gives the time that the SQL statement takes to execute, while result and err refer to the result of the execution.

type Executor 

type Executor interface {
	// Exec executes a SQL statement
	Exec(query string, args ...interface{}) (sql.Result, error)
	// ExecContext executes a SQL statement with the given context
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	// Query queries a SQL statement
	Query(query string, args ...interface{}) (*sql.Rows, error)
	// QueryContext queries a SQL statement with the given context
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	// Prepare creates a prepared statement
	Prepare(query string) (*sql.Stmt, error)
}

Executor prepares, executes, or queries a SQL statement.

type ExistsExp 

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

ExistsExp represents an EXISTS or NOT EXISTS expression.

func (*ExistsExp) Build 

func (e *ExistsExp) Build(db *DB, params Params) string

Build converts an expression into a SQL fragment.

type Exp 

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

Exp represents an expression with a SQL fragment and a list of optional binding parameters.

func (*Exp) Build 

func (e *Exp) Build(db *DB, params Params) string

Build converts an expression into a SQL fragment.

type Expression 

type Expression interface {
	// Build converts an expression into a SQL fragment.
	// If the expression contains binding parameters, they will be added to the given Params.
	Build(*DB, Params) string
}

Expression represents a DB expression that can be embedded in a SQL statement.

func And 

func And(exps ...Expression) Expression

And generates an AND expression which concatenates the given expressions with "AND".

func Between 

func Between(col string, from, to interface{}) Expression

Between generates a BETWEEN expression. For example, Between("age", 10, 30) generates: "age" BETWEEN 10 AND 30

func Enclose added in v1.9.0 

func Enclose(exp Expression) Expression

Enclose surrounds the provided nonempty expression with parenthesis "()".

func Exists 

func Exists(exp Expression) Expression

Exists generates an EXISTS expression by prefixing "EXISTS" to the given expression.

func In 

func In(col string, values ...interface{}) Expression

In generates an IN expression for the specified column and the list of allowed values. If values is empty, a SQL "0=1" will be generated which represents a false expression.

func NewExp 

func NewExp(e string, params ...Params) Expression

NewExp generates an expression with the specified SQL fragment and the optional binding parameters.

func Not 

func Not(e Expression) Expression

Not generates a NOT expression which prefixes "NOT" to the specified expression.

func NotBetween 

func NotBetween(col string, from, to interface{}) Expression

NotBetween generates a NOT BETWEEN expression. For example, NotBetween("age", 10, 30) generates: "age" NOT BETWEEN 10 AND 30

func NotExists 

func NotExists(exp Expression) Expression

NotExists generates an EXISTS expression by prefixing "NOT EXISTS" to the given expression.

func NotIn 

func NotIn(col string, values ...interface{}) Expression

NotIn generates an NOT IN expression for the specified column and the list of disallowed values. If values is empty, an empty string will be returned indicating a true expression.

func Or 

func Or(exps ...Expression) Expression

Or generates an OR expression which concatenates the given expressions with "OR".

type FieldMapFunc 

type FieldMapFunc func(string) string

FieldMapFunc converts a struct field name into a DB column name.

type HashExp 

type HashExp map[string]interface{}

HashExp represents a hash expression.

A hash expression is a map whose keys are DB column names which need to be filtered according to the corresponding values. For example, HashExp{"level": 2, "dept": 10} will generate the SQL: "level"=2 AND "dept"=10.

HashExp also handles nil values and slice values. For example, HashExp{"level": []interface{}{1, 2}, "dept": nil} will generate: "level" IN (1, 2) AND "dept" IS NULL.

func (HashExp) Build 

func (e HashExp) Build(db *DB, params Params) string

Build converts an expression into a SQL fragment.

type InExp 

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

InExp represents an "IN" or "NOT IN" expression.

func (*InExp) Build 

func (e *InExp) Build(db *DB, params Params) string

Build converts an expression into a SQL fragment.

type JoinInfo 

type JoinInfo struct {
	Join  string
	Table string
	On    Expression
}

JoinInfo contains the specification for a JOIN clause.

type LikeExp 

type LikeExp struct {

	// Like stores the LIKE operator. It can be "LIKE", "NOT LIKE".
	// It may also be customized as something like "ILIKE".
	Like string
	// contains filtered or unexported fields
}

LikeExp represents a variant of LIKE expressions.

func Like 

func Like(col string, values ...string) *LikeExp

Like generates a LIKE expression for the specified column and the possible strings that the column should be like. If multiple values are present, the column should be like *all* of them. For example, Like("name", "key", "word") will generate a SQL expression: "name" LIKE "%key%" AND "name" LIKE "%word%".

By default, each value will be surrounded by "%" to enable partial matching. If a value contains special characters such as "%", "\", "_", they will also be properly escaped.

You may call Escape() and/or Match() to change the default behavior. For example, Like("name", "key").Match(false, true) generates "name" LIKE "key%".

func NotLike 

func NotLike(col string, values ...string) *LikeExp

NotLike generates a NOT LIKE expression. For example, NotLike("name", "key", "word") will generate a SQL expression: "name" NOT LIKE "%key%" AND "name" NOT LIKE "%word%". Please see Like() for more details.

func OrLike 

func OrLike(col string, values ...string) *LikeExp

OrLike generates an OR LIKE expression. This is similar to Like() except that the column should be like one of the possible values. For example, OrLike("name", "key", "word") will generate a SQL expression: "name" LIKE "%key%" OR "name" LIKE "%word%". Please see Like() for more details.

func OrNotLike 

func OrNotLike(col string, values ...string) *LikeExp

OrNotLike generates an OR NOT LIKE expression. For example, OrNotLike("name", "key", "word") will generate a SQL expression: "name" NOT LIKE "%key%" OR "name" NOT LIKE "%word%". Please see Like() for more details.

func (*LikeExp) Build 

func (e *LikeExp) Build(db *DB, params Params) string

Build converts an expression into a SQL fragment.

func (*LikeExp) Escape 

func (e *LikeExp) Escape(chars ...string) *LikeExp

Escape specifies how a LIKE expression should be escaped. Each string at position 2i represents a special character and the string at position 2i+1 is the corresponding escaped version.

func (*LikeExp) Match 

func (e *LikeExp) Match(left, right bool) *LikeExp

Match specifies whether to do wildcard matching on the left and/or right of given strings.

type LogFunc 

type LogFunc func(format string, a ...interface{})

LogFunc logs a message for each SQL statement being executed. This method takes one or multiple parameters. If a single parameter is provided, it will be treated as the log message. If multiple parameters are provided, they will be passed to fmt.Sprintf() to generate the log message.

type ModelQuery 

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

ModelQuery represents a query associated with a struct model.

func NewModelQuery added in v1.0.6 

func NewModelQuery(model interface{}, fieldMapFunc FieldMapFunc, db *DB, builder Builder) *ModelQuery

func (*ModelQuery) Context added in v1.6.0 

func (q *ModelQuery) Context() context.Context

Context returns the context associated with the query.

func (*ModelQuery) Delete 

func (q *ModelQuery) Delete() error

Delete deletes a row in the table using the primary key specified by the struct model associated with this query.

func (*ModelQuery) Exclude 

func (q *ModelQuery) Exclude(attrs ...string) *ModelQuery

Exclude excludes the specified struct fields from being inserted/updated into the DB table.

func (*ModelQuery) Insert 

func (q *ModelQuery) Insert(attrs ...string) error

Insert inserts a row in the table using the struct model associated with this query.

By default, it inserts *all* public fields into the table, including those nil or empty ones. You may pass a list of the fields to this method to indicate that only those fields should be inserted. You may also call Exclude to exclude some fields from being inserted.

If a model has an empty primary key, it is considered auto-incremental and the corresponding struct field will be filled with the generated primary key value after a successful insertion.

func (*ModelQuery) Update 

func (q *ModelQuery) Update(attrs ...string) error

Update updates a row in the table using the struct model associated with this query. The row being updated has the same primary key as specified by the model.

By default, it updates *all* public fields in the table, including those nil or empty ones. You may pass a list of the fields to this method to indicate that only those fields should be updated. You may also call Exclude to exclude some fields from being updated.

func (*ModelQuery) WithContext added in v1.6.0 

func (q *ModelQuery) WithContext(ctx context.Context) *ModelQuery

WithContext associates a context with the query.

type MssqlBuilder 

type MssqlBuilder struct {
	*BaseBuilder
	// contains filtered or unexported fields
}

MssqlBuilder is the builder for SQL Server databases.

func (*MssqlBuilder) AlterColumn 

func (b *MssqlBuilder) AlterColumn(table, col, typ string) *Query

AlterColumn creates a Query that can be used to change the definition of a table column.

func (*MssqlBuilder) Model added in v1.0.6 

func (b *MssqlBuilder) Model(model interface{}) *ModelQuery

Model returns a new ModelQuery object that can be used to perform model-based DB operations. The model passed to this method should be a pointer to a model struct.

func (*MssqlBuilder) QueryBuilder 

func (b *MssqlBuilder) QueryBuilder() QueryBuilder

QueryBuilder returns the query builder supporting the current DB.

func (*MssqlBuilder) QuoteSimpleColumnName 

func (b *MssqlBuilder) QuoteSimpleColumnName(s string) string

QuoteSimpleColumnName quotes a simple column name. A simple column name does not contain any table prefix.

func (*MssqlBuilder) QuoteSimpleTableName 

func (b *MssqlBuilder) QuoteSimpleTableName(s string) string

QuoteSimpleTableName quotes a simple table name. A simple table name does not contain any schema prefix.

func (*MssqlBuilder) RenameColumn 

func (b *MssqlBuilder) RenameColumn(table, oldName, newName string) *Query

RenameColumn creates a Query that can be used to rename a column in a table.

func (*MssqlBuilder) RenameTable 

func (b *MssqlBuilder) RenameTable(oldName, newName string) *Query

RenameTable creates a Query that can be used to rename a table.

func (*MssqlBuilder) Select added in v1.0.7 

func (b *MssqlBuilder) Select(cols ...string) *SelectQuery

Select returns a new SelectQuery object that can be used to build a SELECT statement. The parameters to this method should be the list column names to be selected. A column name may have an optional alias name. For example, Select("id", "my_name AS name").

type MssqlQueryBuilder 

type MssqlQueryBuilder struct {
	*BaseQueryBuilder
}

MssqlQueryBuilder is the query builder for SQL Server databases.

func (*MssqlQueryBuilder) BuildOrderByAndLimit 

func (q *MssqlQueryBuilder) BuildOrderByAndLimit(sql string, cols []string, limit int64, offset int64) string

BuildOrderByAndLimit generates the ORDER BY and LIMIT clauses.

type MysqlBuilder 

type MysqlBuilder struct {
	*BaseBuilder
	// contains filtered or unexported fields
}

MysqlBuilder is the builder for MySQL databases.

func (*MysqlBuilder) DropForeignKey 

func (b *MysqlBuilder) DropForeignKey(table, name string) *Query

DropForeignKey creates a Query that can be used to remove the named foreign key constraint from a table.

func (*MysqlBuilder) DropPrimaryKey 

func (b *MysqlBuilder) DropPrimaryKey(table, name string) *Query

DropPrimaryKey creates a Query that can be used to remove the named primary key constraint from a table.

func (*MysqlBuilder) Model added in v1.0.6 

func (b *MysqlBuilder) Model(model interface{}) *ModelQuery

Model returns a new ModelQuery object that can be used to perform model-based DB operations. The model passed to this method should be a pointer to a model struct.

func (*MysqlBuilder) QueryBuilder 

func (b *MysqlBuilder) QueryBuilder() QueryBuilder

QueryBuilder returns the query builder supporting the current DB.

func (*MysqlBuilder) QuoteSimpleColumnName 

func (b *MysqlBuilder) QuoteSimpleColumnName(s string) string

QuoteSimpleColumnName quotes a simple column name. A simple column name does not contain any table prefix.

func (*MysqlBuilder) QuoteSimpleTableName 

func (b *MysqlBuilder) QuoteSimpleTableName(s string) string

QuoteSimpleTableName quotes a simple table name. A simple table name does not contain any schema prefix.

func (*MysqlBuilder) RenameColumn 

func (b *MysqlBuilder) RenameColumn(table, oldName, newName string) *Query

RenameColumn creates a Query that can be used to rename a column in a table.

func (*MysqlBuilder) Select added in v1.0.7 

func (b *MysqlBuilder) Select(cols ...string) *SelectQuery

Select returns a new SelectQuery object that can be used to build a SELECT statement. The parameters to this method should be the list column names to be selected. A column name may have an optional alias name. For example, Select("id", "my_name AS name").

func (*MysqlBuilder) Upsert 

func (b *MysqlBuilder) Upsert(table string, cols Params, constraints ...string) *Query

Upsert creates a Query that represents an UPSERT SQL statement. Upsert inserts a row into the table if the primary key or unique index is not found. Otherwise it will update the row with the new values. The keys of cols are the column names, while the values of cols are the corresponding column values to be inserted.

type NotExp 

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

NotExp represents an expression that should prefix "NOT" to a specified expression.

func (*NotExp) Build 

func (e *NotExp) Build(db *DB, params Params) string

Build converts an expression into a SQL fragment.

type NullStringMap 

type NullStringMap map[string]sql.NullString

NullStringMap is a map of sql.NullString that can be used to hold DB query result. The map keys correspond to the DB column names, while the map values are their corresponding column values.

type OciBuilder 

type OciBuilder struct {
	*BaseBuilder
	// contains filtered or unexported fields
}

OciBuilder is the builder for Oracle databases.

func (*OciBuilder) AlterColumn 

func (b *OciBuilder) AlterColumn(table, col, typ string) *Query

AlterColumn creates a Query that can be used to change the definition of a table column.

func (*OciBuilder) DropIndex 

func (b *OciBuilder) DropIndex(table, name string) *Query

DropIndex creates a Query that can be used to remove the named index from a table.

func (*OciBuilder) GeneratePlaceholder 

func (b *OciBuilder) GeneratePlaceholder(i int) string

GeneratePlaceholder generates an anonymous parameter placeholder with the given parameter ID.

func (*OciBuilder) Model added in v1.0.6 

func (b *OciBuilder) Model(model interface{}) *ModelQuery

Model returns a new ModelQuery object that can be used to perform model-based DB operations. The model passed to this method should be a pointer to a model struct.

func (*OciBuilder) QueryBuilder 

func (b *OciBuilder) QueryBuilder() QueryBuilder

QueryBuilder returns the query builder supporting the current DB.

func (*OciBuilder) RenameTable 

func (b *OciBuilder) RenameTable(oldName, newName string) *Query

RenameTable creates a Query that can be used to rename a table.

func (*OciBuilder) Select added in v1.0.7 

func (b *OciBuilder) Select(cols ...string) *SelectQuery

Select returns a new SelectQuery object that can be used to build a SELECT statement. The parameters to this method should be the list column names to be selected. A column name may have an optional alias name. For example, Select("id", "my_name AS name").

type OciQueryBuilder 

type OciQueryBuilder struct {
	*BaseQueryBuilder
}

OciQueryBuilder is the query builder for Oracle databases.

func (*OciQueryBuilder) BuildOrderByAndLimit 

func (q *OciQueryBuilder) BuildOrderByAndLimit(sql string, cols []string, limit int64, offset int64) string

BuildOrderByAndLimit generates the ORDER BY and LIMIT clauses.

type OneHookFunc added in v1.10.0 

type OneHookFunc func(q *Query, a interface{}, op func(b interface{}) error) error

OneHookFunc executes right before the query populate the row result from One() call (aka. op).

type Params 

type Params map[string]interface{}

Params represents a list of parameter values to be bound to a SQL statement. The map keys are the parameter names while the map values are the corresponding parameter values.

type PerfFunc added in v1.0.11 

type PerfFunc func(ns int64, sql string, execute bool)

PerfFunc is called when a query finishes execution. The query execution time is passed to this function so that the DB performance can be profiled. The "ns" parameter gives the number of nanoseconds that the SQL statement takes to execute, while the "execute" parameter indicates whether the SQL statement is executed or queried (usually SELECT statements).

type PgsqlBuilder 

type PgsqlBuilder struct {
	*BaseBuilder
	// contains filtered or unexported fields
}

PgsqlBuilder is the builder for PostgreSQL databases.

func (*PgsqlBuilder) AlterColumn 

func (b *PgsqlBuilder) AlterColumn(table, col, typ string) *Query

AlterColumn creates a Query that can be used to change the definition of a table column.

func (*PgsqlBuilder) DropIndex 

func (b *PgsqlBuilder) DropIndex(table, name string) *Query

DropIndex creates a Query that can be used to remove the named index from a table.

func (*PgsqlBuilder) GeneratePlaceholder 

func (b *PgsqlBuilder) GeneratePlaceholder(i int) string

GeneratePlaceholder generates an anonymous parameter placeholder with the given parameter ID.

func (*PgsqlBuilder) Model added in v1.0.6 

func (b *PgsqlBuilder) Model(model interface{}) *ModelQuery

Model returns a new ModelQuery object that can be used to perform model-based DB operations. The model passed to this method should be a pointer to a model struct.

func (*PgsqlBuilder) QueryBuilder 

func (b *PgsqlBuilder) QueryBuilder() QueryBuilder

QueryBuilder returns the query builder supporting the current DB.

func (*PgsqlBuilder) RenameTable 

func (b *PgsqlBuilder) RenameTable(oldName, newName string) *Query

RenameTable creates a Query that can be used to rename a table.

func (*PgsqlBuilder) Select added in v1.0.7 

func (b *PgsqlBuilder) Select(cols ...string) *SelectQuery

Select returns a new SelectQuery object that can be used to build a SELECT statement. The parameters to this method should be the list column names to be selected. A column name may have an optional alias name. For example, Select("id", "my_name AS name").

func (*PgsqlBuilder) Upsert 

func (b *PgsqlBuilder) Upsert(table string, cols Params, constraints ...string) *Query

Upsert creates a Query that represents an UPSERT SQL statement. Upsert inserts a row into the table if the primary key or unique index is not found. Otherwise it will update the row with the new values. The keys of cols are the column names, while the values of cols are the corresponding column values to be inserted.

type PostScanner added in v1.8.0 

type PostScanner interface {
	// PostScan executes right after the struct has been populated
	// with the DB values, allowing you to further normalize or validate
	// the loaded data.
	PostScan() error
}

PostScanner is an optional interface used by ScanStruct.

type Query 

type Query struct {

	// FieldMapper maps struct field names to DB column names.
	FieldMapper FieldMapFunc
	// LastError contains the last error (if any) of the query.
	// LastError is cleared by Execute(), Row(), Rows(), One(), and All().
	LastError error
	// LogFunc is used to log the SQL statement being executed.
	LogFunc LogFunc
	// PerfFunc is used to log the SQL execution time. It is ignored if nil.
	// Deprecated: Please use QueryLogFunc and ExecLogFunc instead.
	PerfFunc PerfFunc
	// QueryLogFunc is called each time when performing a SQL query that returns data.
	QueryLogFunc QueryLogFunc
	// ExecLogFunc is called each time when a SQL statement is executed.
	ExecLogFunc ExecLogFunc
	// contains filtered or unexported fields
}

Query represents a SQL statement to be executed.

func NewQuery 

func NewQuery(db *DB, executor Executor, sql string) *Query

NewQuery creates a new Query with the given SQL statement.

func (*Query) All 

func (q *Query) All(slice interface{}) error

All executes the SQL statement and populates all the resulting rows into a slice of struct or NullStringMap. The slice must be given as a pointer. Each slice element must be either a struct or a NullStringMap. Refer to Rows.ScanStruct() and Rows.ScanMap() for more details on how each slice element can be. If the query returns no row, the slice will be an empty slice (not nil).

Example 
package main

import (
	"fmt"

	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")
	sql := "SELECT id, name FROM users LIMIT 10"

	// fetches data into a slice of struct
	var users []struct {
		ID, Name string
	}
	db.NewQuery(sql).All(&users)

	// fetches data into a slice of NullStringMap
	var users2 []dbx.NullStringMap
	db.NewQuery(sql).All(&users2)
	for _, user := range users2 {
		fmt.Println(user["id"].String, user["name"].String)
	}
}

Output:

func (*Query) Bind 

func (q *Query) Bind(params Params) *Query

Bind sets the parameters that should be bound to the SQL statement. The parameter placeholders in the SQL statement are in the format of "{:ParamName}".

Example 
package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	var user struct {
		ID, Name string
	}

	db, _ := dbx.Open("mysql", "user:pass@/example")
	sql := "SELECT id, name FROM users WHERE age>{:age} AND status={:status}"

	q := db.NewQuery(sql)
	q.Bind(dbx.Params{"age": 30, "status": 1}).One(&user)
}

Output:

func (*Query) Close 

func (q *Query) Close() error

Close closes the underlying prepared statement. Close does nothing if the query has not been prepared before.

func (*Query) Column added in v1.0.2 

func (q *Query) Column(a interface{}) error

Column executes the SQL statement and populates the first column of the result into a slice. Note that the parameter must be a pointer to a slice.

func (*Query) Context added in v1.6.0 

func (q *Query) Context() context.Context

Context returns the context associated with the query.

func (*Query) Execute 

func (q *Query) Execute() (sql.Result, error)

Execute executes the SQL statement without retrieving data.

func (*Query) One 

func (q *Query) One(a interface{}) error

One executes the SQL statement and populates the first row of the result into a struct or NullStringMap. Refer to Rows.ScanStruct() and Rows.ScanMap() for more details on how to specify the variable to be populated. Note that when the query has no rows in the result set, an sql.ErrNoRows will be returned.

Example 
package main

import (
	"fmt"

	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")
	sql := "SELECT id, name FROM users LIMIT 10"

	// fetches data into a struct
	var user struct {
		ID, Name string
	}
	db.NewQuery(sql).One(&user)

	// fetches data into a NullStringMap
	var user2 dbx.NullStringMap
	db.NewQuery(sql).All(user2)
	fmt.Println(user2["id"].String, user2["name"].String)
}

Output:

func (*Query) Params 

func (q *Query) Params() Params

Params returns the parameters to be bound to the SQL statement represented by this query.

func (*Query) Prepare 

func (q *Query) Prepare() *Query

Prepare creates a prepared statement for later queries or executions. Close() should be called after finishing all queries.

Example 
package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	var users1, users2, users3 []struct {
		ID, Name string
	}

	db, _ := dbx.Open("mysql", "user:pass@/example")
	sql := "SELECT id, name FROM users WHERE age>{:age} AND status={:status}"

	q := db.NewQuery(sql).Prepare()
	defer q.Close()

	q.Bind(dbx.Params{"age": 30, "status": 1}).All(&users1)
	q.Bind(dbx.Params{"age": 20, "status": 1}).All(&users2)
	q.Bind(dbx.Params{"age": 10, "status": 1}).All(&users3)
}

Output:

func (*Query) Row 

func (q *Query) Row(a ...interface{}) error

Row executes the SQL statement and populates the first row of the result into a list of variables. Note that the number of the variables should match to that of the columns in the query result. Note that when the query has no rows in the result set, an sql.ErrNoRows will be returned.

Example 
package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")
	sql := "SELECT id, name FROM users LIMIT 10"

	// fetches data into a struct
	var (
		id   int
		name string
	)
	db.NewQuery(sql).Row(&id, &name)
}

Output:

func (*Query) Rows 

func (q *Query) Rows() (rows *Rows, err error)

Rows executes the SQL statement and returns a Rows object to allow retrieving data row by row.

Example 
package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	var user struct {
		ID, Name string
	}

	db, _ := dbx.Open("mysql", "user:pass@/example")
	sql := "SELECT id, name FROM users LIMIT 10"

	rows, _ := db.NewQuery(sql).Rows()
	for rows.Next() {
		rows.ScanStruct(&user)
		// ...
	}
}

Output:

func (*Query) SQL 

func (q *Query) SQL() string

SQL returns the original SQL used to create the query. The actual SQL (RawSQL) being executed is obtained by replacing the named parameter placeholders with anonymous ones.

func (*Query) WithAllHook added in v1.10.0 

func (q *Query) WithAllHook(fn AllHookFunc) *Query

WithOneHook associates the provided hook function with the query, called on q.All(), allowing you to implement custom slice scan based on the All() argument and/or result.

func (*Query) WithContext added in v1.6.0 

func (q *Query) WithContext(ctx context.Context) *Query

WithContext associates a context with the query.

func (*Query) WithExecHook added in v1.10.0 

func (q *Query) WithExecHook(fn ExecHookFunc) *Query

WithExecHook associates the provided exec hook function with the query.

It is called for every Query resolver (Execute(), One(), All(), Row(), Column()), allowing you to implement auto fail/retry or any other additional handling.

func (*Query) WithOneHook added in v1.10.0 

func (q *Query) WithOneHook(fn OneHookFunc) *Query

WithOneHook associates the provided hook function with the query, called on q.One(), allowing you to implement custom struct scan based on the One() argument and/or result.

type QueryBuilder 

type QueryBuilder interface {
	// BuildSelect generates a SELECT clause from the given selected column names.
	BuildSelect(cols []string, distinct bool, option string) string
	// BuildFrom generates a FROM clause from the given tables.
	BuildFrom(tables []string) string
	// BuildGroupBy generates a GROUP BY clause from the given group-by columns.
	BuildGroupBy(cols []string) string
	// BuildJoin generates a JOIN clause from the given join information.
	BuildJoin([]JoinInfo, Params) string
	// BuildWhere generates a WHERE clause from the given expression.
	BuildWhere(Expression, Params) string
	// BuildHaving generates a HAVING clause from the given expression.
	BuildHaving(Expression, Params) string
	// BuildOrderByAndLimit generates the ORDER BY and LIMIT clauses.
	BuildOrderByAndLimit(string, []string, int64, int64) string
	// BuildUnion generates a UNION clause from the given union information.
	BuildUnion([]UnionInfo, Params) string
}

QueryBuilder builds different clauses for a SELECT SQL statement.

type QueryInfo added in v1.7.0 

type QueryInfo struct {
	PreFragment  string
	PostFragment string
	Builder      Builder
	Selects      []string
	Distinct     bool
	SelectOption string
	From         []string
	Where        Expression
	Join         []JoinInfo
	OrderBy      []string
	GroupBy      []string
	Having       Expression
	Union        []UnionInfo
	Limit        int64
	Offset       int64
	Params       Params
	Context      context.Context
	BuildHook    BuildHookFunc
}

QueryInfo represents a debug/info struct with exported SelectQuery fields.

type QueryLogFunc added in v1.6.0 

type QueryLogFunc func(ctx context.Context, t time.Duration, sql string, rows *sql.Rows, err error)

QueryLogFunc is called each time when performing a SQL query. The "t" parameter gives the time that the SQL statement takes to execute, while rows and err are the result of the query.

type Rows 

type Rows struct {
	*sql.Rows
	// contains filtered or unexported fields
}

Rows enhances sql.Rows by providing additional data query methods. Rows can be obtained by calling Query.Rows(). It is mainly used to populate data row by row.

func (*Rows) ScanMap 

func (r *Rows) ScanMap(a NullStringMap) error

ScanMap populates the current row of data into a NullStringMap. Note that the NullStringMap must not be nil, or it will panic. The NullStringMap will be populated using column names as keys and their values as the corresponding element values.

Example 
package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	user := dbx.NullStringMap{}

	sql := "SELECT id, name FROM users LIMIT 10"
	rows, _ := db.NewQuery(sql).Rows()
	for rows.Next() {
		rows.ScanMap(user)
		// ...
	}
}

Output:

func (*Rows) ScanStruct 

func (r *Rows) ScanStruct(a interface{}) error

ScanStruct populates the current row of data into a struct. The struct must be given as a pointer.

ScanStruct associates struct fields with DB table columns through a field mapping function. It populates a struct field with the data of its associated column. Note that only exported struct fields will be populated.

By default, DefaultFieldMapFunc() is used to map struct fields to table columns. This function separates each word in a field name with a underscore and turns every letter into lower case. For example, "LastName" is mapped to "last_name", "MyID" is mapped to "my_id", and so on. To change the default behavior, set DB.FieldMapper with your custom mapping function. You may also set Query.FieldMapper to change the behavior for particular queries.

Example 
package main

import (
	"github.com/pocketbase/dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:pass@/example")

	var user struct {
		ID, Name string
	}

	sql := "SELECT id, name FROM users LIMIT 10"
	rows, _ := db.NewQuery(sql).Rows()
	for rows.Next() {
		rows.ScanStruct(&user)
		// ...
	}
}

Output:

type SelectQuery 

type SelectQuery struct {
	// FieldMapper maps struct field names to DB column names.
	FieldMapper FieldMapFunc
	// TableMapper maps structs to DB table names.
	TableMapper TableMapFunc
	// contains filtered or unexported fields
}

SelectQuery represents a DB-agnostic SELECT query. It can be built into a DB-specific query by calling the Build() method.

func NewSelectQuery 

func NewSelectQuery(builder Builder, db *DB) *SelectQuery

NewSelectQuery creates a new SelectQuery instance.

func (*SelectQuery) All 

func (s *SelectQuery) All(slice interface{}) error

All executes the SELECT query and populates all rows of the result into a slice.

Note that the slice must be passed in as a pointer.

If the query does not specify a "from" clause, the method will try to infer the name of the table to be selected from by calling getTableName() which will return either the type name of the slice elements or the TableName() method if the slice element implements the TableModel interface.

func (*SelectQuery) AndBind 

func (s *SelectQuery) AndBind(params Params) *SelectQuery

AndBind appends additional parameters to be bound to the query.

func (*SelectQuery) AndGroupBy 

func (s *SelectQuery) AndGroupBy(cols ...string) *SelectQuery

AndGroupBy appends additional columns to the existing GROUP BY clause. Column names will be properly quoted.

func (*SelectQuery) AndHaving 

func (s *SelectQuery) AndHaving(e Expression) *SelectQuery

AndHaving concatenates a new HAVING condition with the existing one (if any) using "AND".

func (*SelectQuery) AndOrderBy 

func (s *SelectQuery) AndOrderBy(cols ...string) *SelectQuery

AndOrderBy appends additional columns to the existing ORDER BY clause. Column names will be properly quoted. A column name can contain "ASC" or "DESC" to indicate its ordering direction.

func (*SelectQuery) AndSelect 

func (s *SelectQuery) AndSelect(cols ...string) *SelectQuery

AndSelect adds additional columns to be selected. Column names will be automatically quoted.

func (*SelectQuery) AndWhere 

func (s *SelectQuery) AndWhere(e Expression) *SelectQuery

AndWhere concatenates a new WHERE condition with the existing one (if any) using "AND".

func (*SelectQuery) Bind 

func (s *SelectQuery) Bind(params Params) *SelectQuery

Bind specifies the parameter values to be bound to the query.

func (*SelectQuery) Build 

func (s *SelectQuery) Build() *Query

Build builds the SELECT query and returns an executable Query object.

func (*SelectQuery) Column added in v1.0.4 

func (s *SelectQuery) Column(a interface{}) error

Column builds and executes the SELECT statement and populates the first column of the result into a slice. Note that the parameter must be a pointer to a slice. This is a shortcut to SelectQuery.Build().Column()

func (*SelectQuery) Context added in v1.6.0 

func (q *SelectQuery) Context() context.Context

Context returns the context associated with the query.

func (*SelectQuery) Distinct 

func (s *SelectQuery) Distinct(v bool) *SelectQuery

Distinct specifies whether to select columns distinctively. By default, distinct is false.

func (*SelectQuery) From 

func (s *SelectQuery) From(tables ...string) *SelectQuery

From specifies which tables to select from. Table names will be automatically quoted.

func (*SelectQuery) GroupBy 

func (s *SelectQuery) GroupBy(cols ...string) *SelectQuery

GroupBy specifies the GROUP BY clause. Column names will be properly quoted.

func (*SelectQuery) Having 

func (s *SelectQuery) Having(e Expression) *SelectQuery

Having specifies the HAVING clause.

func (*SelectQuery) Info added in v1.7.0 

func (s *SelectQuery) Info() *QueryInfo

Info exports common SelectQuery fields allowing to inspect the current select query options.

func (*SelectQuery) InnerJoin 

func (s *SelectQuery) InnerJoin(table string, on Expression) *SelectQuery

InnerJoin specifies an INNER JOIN clause. This is a shortcut method for Join.

func (*SelectQuery) Join 

func (s *SelectQuery) Join(typ string, table string, on Expression) *SelectQuery

Join specifies a JOIN clause. The "typ" parameter specifies the JOIN type (e.g. "INNER JOIN", "LEFT JOIN").

func (*SelectQuery) LeftJoin 

func (s *SelectQuery) LeftJoin(table string, on Expression) *SelectQuery

LeftJoin specifies a LEFT JOIN clause. This is a shortcut method for Join.

func (*SelectQuery) Limit 

func (s *SelectQuery) Limit(limit int64) *SelectQuery

Limit specifies the LIMIT clause. A negative limit means no limit.

func (*SelectQuery) Model 

func (s *SelectQuery) Model(pk, model interface{}) error

Model selects the row with the specified primary key and populates the model with the row data.

The model variable should be a pointer to a struct. If the query does not specify a "from" clause, it will use the model struct to determine which table to select data from. It will also use the model to infer the name of the primary key column. Only simple primary key is supported. For composite primary keys, please use Where() to specify the filtering condition.

func (*SelectQuery) Offset 

func (s *SelectQuery) Offset(offset int64) *SelectQuery

Offset specifies the OFFSET clause. A negative offset means no offset.

func (*SelectQuery) One 

func (s *SelectQuery) One(a interface{}) error

One executes the SELECT query and populates the first row of the result into the specified variable.

If the query does not specify a "from" clause, the method will try to infer the name of the table to be selected from by calling getTableName() which will return either the variable type name or the TableName() method if the variable implements the TableModel interface.

Note that when the query has no rows in the result set, an sql.ErrNoRows will be returned.

func (*SelectQuery) OrHaving 

func (s *SelectQuery) OrHaving(e Expression) *SelectQuery

OrHaving concatenates a new HAVING condition with the existing one (if any) using "OR".

func (*SelectQuery) OrWhere 

func (s *SelectQuery) OrWhere(e Expression) *SelectQuery

OrWhere concatenates a new WHERE condition with the existing one (if any) using "OR".

func (*SelectQuery) OrderBy 

func (s *SelectQuery) OrderBy(cols ...string) *SelectQuery

OrderBy specifies the ORDER BY clause. Column names will be properly quoted. A column name can contain "ASC" or "DESC" to indicate its ordering direction.

func (*SelectQuery) PostFragment added in v1.11.0 

func (s *SelectQuery) PostFragment(fragment string) *SelectQuery

PostFragment sets SQL fragment that should be appended at the end of the select query.

func (*SelectQuery) PreFragment added in v1.11.0 

func (s *SelectQuery) PreFragment(fragment string) *SelectQuery

PreFragment sets SQL fragment that should be prepended before the select query (e.g. WITH clause).

func (*SelectQuery) RightJoin 

func (s *SelectQuery) RightJoin(table string, on Expression) *SelectQuery

RightJoin specifies a RIGHT JOIN clause. This is a shortcut method for Join.

func (*SelectQuery) Row 

func (s *SelectQuery) Row(a ...interface{}) error

Row builds and executes the SELECT query and populates the first row of the result into the specified variables. This is a shortcut to SelectQuery.Build().Row()

func (*SelectQuery) Rows 

func (s *SelectQuery) Rows() (*Rows, error)

Rows builds and executes the SELECT query and returns a Rows object for data retrieval purpose. This is a shortcut to SelectQuery.Build().Rows()

func (*SelectQuery) Select 

func (s *SelectQuery) Select(cols ...string) *SelectQuery

Select specifies the columns to be selected. Column names will be automatically quoted.

func (*SelectQuery) SelectOption 

func (s *SelectQuery) SelectOption(option string) *SelectQuery

SelectOption specifies additional option that should be append to "SELECT".

func (*SelectQuery) Union 

func (s *SelectQuery) Union(q *Query) *SelectQuery

Union specifies a UNION clause.

func (*SelectQuery) UnionAll 

func (s *SelectQuery) UnionAll(q *Query) *SelectQuery

UnionAll specifies a UNION ALL clause.

func (*SelectQuery) Where 

func (s *SelectQuery) Where(e Expression) *SelectQuery

Where specifies the WHERE condition.

func (*SelectQuery) WithBuildHook added in v1.10.0 

func (q *SelectQuery) WithBuildHook(fn BuildHookFunc) *SelectQuery

WithBuildHook runs the provided hook function with the query created on Build().

func (*SelectQuery) WithContext added in v1.6.0 

func (q *SelectQuery) WithContext(ctx context.Context) *SelectQuery

WithContext associates a context with the query.

type SqliteBuilder 

type SqliteBuilder struct {
	*BaseBuilder
	// contains filtered or unexported fields
}

SqliteBuilder is the builder for SQLite databases.

func (*SqliteBuilder) AddForeignKey 

func (b *SqliteBuilder) AddForeignKey(table, name string, cols, refCols []string, refTable string, options ...string) *Query

AddForeignKey creates a Query that can be used to add a foreign key constraint to a table. The length of cols and refCols must be the same as they refer to the primary and referential columns. The optional "options" parameters will be appended to the SQL statement. They can be used to specify options such as "ON DELETE CASCADE".

func (*SqliteBuilder) AddPrimaryKey 

func (b *SqliteBuilder) AddPrimaryKey(table, name string, cols ...string) *Query

AddPrimaryKey creates a Query that can be used to specify primary key(s) for a table. The "name" parameter specifies the name of the primary key constraint.

func (*SqliteBuilder) AlterColumn 

func (b *SqliteBuilder) AlterColumn(table, col, typ string) *Query

AlterColumn creates a Query that can be used to change the definition of a table column.

func (*SqliteBuilder) DropForeignKey 

func (b *SqliteBuilder) DropForeignKey(table, name string) *Query

DropForeignKey creates a Query that can be used to remove the named foreign key constraint from a table.

func (*SqliteBuilder) DropIndex 

func (b *SqliteBuilder) DropIndex(table, name string) *Query

DropIndex creates a Query that can be used to remove the named index from a table.

func (*SqliteBuilder) DropPrimaryKey 

func (b *SqliteBuilder) DropPrimaryKey(table, name string) *Query

DropPrimaryKey creates a Query that can be used to remove the named primary key constraint from a table.

func (*SqliteBuilder) Model added in v1.0.6 

func (b *SqliteBuilder) Model(model interface{}) *ModelQuery

Model returns a new ModelQuery object that can be used to perform model-based DB operations. The model passed to this method should be a pointer to a model struct.

func (*SqliteBuilder) QueryBuilder 

func (b *SqliteBuilder) QueryBuilder() QueryBuilder

QueryBuilder returns the query builder supporting the current DB.

func (*SqliteBuilder) QuoteSimpleColumnName 

func (b *SqliteBuilder) QuoteSimpleColumnName(s string) string

QuoteSimpleColumnName quotes a simple column name. A simple column name does not contain any table prefix.

func (*SqliteBuilder) QuoteSimpleTableName 

func (b *SqliteBuilder) QuoteSimpleTableName(s string) string

QuoteSimpleTableName quotes a simple table name. A simple table name does not contain any schema prefix.

func (*SqliteBuilder) RenameTable added in v1.6.0 

func (b *SqliteBuilder) RenameTable(oldName, newName string) *Query

RenameTable creates a Query that can be used to rename a table.

func (*SqliteBuilder) Select added in v1.0.7 

func (b *SqliteBuilder) Select(cols ...string) *SelectQuery

Select returns a new SelectQuery object that can be used to build a SELECT statement. The parameters to this method should be the list column names to be selected. A column name may have an optional alias name. For example, Select("id", "my_name AS name").

func (*SqliteBuilder) TruncateTable 

func (b *SqliteBuilder) TruncateTable(table string) *Query

TruncateTable creates a Query that can be used to truncate a table.

type StandardBuilder 

type StandardBuilder struct {
	*BaseBuilder
	// contains filtered or unexported fields
}

StandardBuilder is the builder that is used by DB for an unknown driver.

func (*StandardBuilder) Model added in v1.0.6 

func (b *StandardBuilder) Model(model interface{}) *ModelQuery

Model returns a new ModelQuery object that can be used to perform model-based DB operations. The model passed to this method should be a pointer to a model struct.

func (*StandardBuilder) QueryBuilder 

func (b *StandardBuilder) QueryBuilder() QueryBuilder

QueryBuilder returns the query builder supporting the current DB.

func (*StandardBuilder) Select added in v1.0.7 

func (b *StandardBuilder) Select(cols ...string) *SelectQuery

Select returns a new SelectQuery object that can be used to build a SELECT statement. The parameters to this method should be the list column names to be selected. A column name may have an optional alias name. For example, Select("id", "my_name AS name").

type TableMapFunc added in v1.6.0 

type TableMapFunc func(a interface{}) string

TableMapFunc converts a sample struct into a DB table name.

type TableModel 

type TableModel interface {
	TableName() string
}

TableModel is the interface that should be implemented by models which have unconventional table names.

type Tx 

type Tx struct {
	Builder
	// contains filtered or unexported fields
}

Tx enhances sql.Tx with additional querying methods.

func (*Tx) Commit 

func (t *Tx) Commit() error

Commit commits the transaction.

func (*Tx) Rollback 

func (t *Tx) Rollback() error

Rollback aborts the transaction.

type UnionInfo 

type UnionInfo struct {
	All   bool
	Query *Query
}

UnionInfo contains the specification for a UNION clause.

type VarTypeError 

type VarTypeError string

VarTypeError indicates a variable type error when trying to populating a variable with DB result.

func (VarTypeError) Error 

func (s VarTypeError) Error() string

Error returns the error message.

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.