dbx

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

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

Summary

• Description
• Requirements
• Installation
• Supported Databases
• Getting Started
• Connecting to Database
• Executing Queries
• Binding Parameters
• Building Queries
  • Building SELECT Queries
  • Building Query Conditions
  • Building Data Manipulation Queries
  • Building Schema Manipulation Queries
• CRUD Operations
  • Create
  • Read
  • Update
  • Delete
  • Null Handling
• Quoting Table and Column Names
• Using Transactions
• Logging Executed SQL Statements
• Supporting New Databases

Description

dbxis a Go package that enhances the standarddatabase/sqlpackage by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.dbxis 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 togo-rest-apiwhich 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 /pocketbase/dbx

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

go get github /go-sql-driver/mysql

and import it in your main code like the following:

import_"github /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.

packagemain

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

funcmain() {
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
varusers[]struct{
ID,Namestring
}
q.All(&users)

// fetch a single row into a struct
varuserstruct{
ID,Namestring
}
q.One(&user)

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

// fetch row by row
rows2,_:=q.Rows()
forrows2.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.

packagemain

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

funcmain() {
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
varusers[]struct{
ID,Namestring
}
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, calldbx.Open()in the same way as you would do with theOpen()method indatabase/sql.

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

The method returns adbx.DBinstance 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 returneddbx.DBinstance. It also does not check the correctness of the data source name either. Calldbx.MustOpen()to make sure the data source name is correct.

Executing Queries

To execute a SQL statement, first create adbx.Queryinstance by callingDB.NewQuery()with the SQL statement to be executed. And then callQuery.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 orNullStringmaps.
• Query.One():populate the first row of the result into a struct or aNullStringmap.
• 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 adbx.Rowsinstance to allow retrieving data row by row.

For example,

typeUserstruct{
IDint
Namestring
}

var(
users[]User
userUser

rowdbx.NullStringMap

idint
namestring

errerror
)

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"])

varids[]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()
forrows.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 functionQuery.FieldMapper. The default field mapping function separates words in a field name by underscores and turns them into lower case. For example, a field nameFirstNamewill be mapped to the column namefirst_name,andMyIDtomy_id.
• If a field has adbtag, the tag value will be used as the corresponding column name. If thedbtag 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 implementssql.Scanneror when it istime.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:

typeUserstruct{
idint
Typeint`db: "-" `
MyNamestring`db: "name" `
Profile
AddressAddress`db: "addr" `
}

typeProfilestruct{
Ageint
}

typeAddressstruct{
Citystring
}

• User.id:not populated because the field is not exported;
• User.Type:not populated because thedbtag is-;
• User.MyName:to be populated from thenamecolumn, according to thedbtag;
• Profile.Age:to be populated from theagecolumn, sinceProfileis an anonymous field;
• Address.City:to be populated from theaddr.citycolumn, sinceAddressis a named field of struct type and its fields will be prefixed withaddr.according to thedbtag.

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. Unlikedatabase/sqlwhich does anonymous parameter binding, dbxuses 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 whoseidis 100. The methodQuery.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()
deferq.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 withcontext.Context.In particular, by callingQuery.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,dbxallows 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 callingDB.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 ofp0in theWHEREclause.

Every SQL keyword has a corresponding query building method. For example,SELECTcorresponds toSelect(), FROMcorresponds toFrom(),WHEREcorresponds toWhere(),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 typedbx.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 callBuild() to build the query and turn it into adbx.Queryinstance which may allow you to get the SQL statement and do other interesting work.

Building Query Conditions

dbxsupports very flexible and powerful query condition building which can be used to build SQL clauses such asWHERE,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 expressionid=100.
• dbx.HashExp:a map type that represents name-value pairs concatenated byANDoperators. For example, dbx.HashExp{ "id":100, "status":1}would createid=100 AND status=1.
• dbx.Not():creating aNOTexpression by prependingNOTto the given expression.
• dbx.And():creating anANDexpression by concatenating the given expressions with theANDoperators.
• dbx.Or():creating anORexpression by concatenating the given expressions with theORoperators.
• dbx.In():creating anINexpression for the specified column and the range of values. For example,dbx.In( "age", 30, 40, 50)would create the expressionage IN (30, 40, 50). Note that if the value range is empty, it will generate an expression representing a false value.
• dbx.NotIn():creating anNOT INexpression. This is very similar todbx.In().
• dbx.Like():creating aLIKEexpression for the specified column and the range of values. For example, dbx.Like( "title", "golang", "framework" )would create the expressiontitle LIKE "%golang%" AND title LIKE "%framework%". You can further customize a LIKE expression by callingEscape()and/orMatch()functions of the resulting expression. Note that if the value range is empty, it will generate an empty expression.
• dbx.NotLike():creating aNOT LIKEexpression. This is very similar todbx.Like().
• dbx.OrLike():creating aLIKEexpression but concatenating differentLIKEsub-expressions usingORinstead ofAND.
• dbx.OrNotLike():creating aNOT LIKEexpression and concatenating differentNOT LIKEsub-expressions usingORinstead ofAND.
• dbx.Exists():creating anEXISTSexpression by prependingEXISTSto the given expression.
• dbx.NotExists():creating aNOT EXISTSexpression by prependingNOT EXISTSto the given expression.
• dbx.Between():creating aBETWEENexpression. For example,dbx.Between( "age", 30, 40)would create the expressionage BETWEEN 30 AND 40.
• dbx.NotBetween():creating aNOT BETWEENexpression. For example

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

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 ofDB.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",
}).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 callExecute()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 ofDB.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

Althoughdbxis 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 namedMyCustomer corresponds to the table namemy_customer.You may explicitly specify the table name for a struct by implementing thedbx.TableModelinterface. For example,

typeMyCustomerstruct{}

func(cMyCustomer)TableName()string{
return"customer"
}

Note that theTableNamemethod should be defined with a value receiver instead of a pointer receiver.

If the struct has a field namedIDorId,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 withdb: "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 formatdb: "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 prefixestbl_to all table names.

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

Create

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

typeCustomerstruct{
IDint
Namestring
Emailstring
Statusint
}

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

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

This will insert a row using the values fromallpublic 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 theInsert()method. You can also exclude certain fields from being inserted by callingExclude()before callingInsert().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, callSelectQuery.Model().

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

varcustomerCustomer
// 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 thatSelectQuery.Model()does not support composite primary keys. You should useSelectQuery.One()in this case. For example,

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

varorderItemOrderItem

// 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 callFrom()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 implementsTableModel, the value returned by itsTableName()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 callSelectQuery.All()to read a list of model structs. Similarly, you do not need to callFrom() if the table name can be inferred from the model structs.

Update

To update a model, call theModelQuery.Update()method. LikeInsert(),by default, theUpdate()method will updateallpublic 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 theInsert()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 theUpdate()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 theModelQuery.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 usesql.NullXyztypes. For example, if a string column is nullable, you may usesql.NullString.TheNullString.Validfield indicates whether the value is a null or not, and NullString.Stringreturns the string value when it is not null. Becausesql.NulLXyztypes do not handle JSON marshalling, you may use thenull package,instead.

Below is an example of handling nulls:

typeCustomerstruct{
IDint
Emailstring
FirstName*string// use pointer to represent null
LastNamesql.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,dbxintroduces 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()

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

You may useDB.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{
varerrerror
_,err=tx.Insert("users",dbx.Params{
"name":"user1",
}).Execute()
iferr!=nil{
returnerr
}
_,err=tx.Insert("users",dbx.Params{
"name":"user2",
}).Execute()
returnerr
})

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 offmt.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.

packagemain

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

"github /pocketbase/dbx"
)

funcmain() {
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(ctxcontext.Context,ttime.Duration,sqlstring,rows*sql.Rows,errerror) {
log.Printf("[%.2fms] Query SQL: %v",float64(t.Milliseconds()),sql)
}
db.ExecLogFunc=func(ctxcontext.Context,ttime.Duration,sqlstring,resultsql.Result,errerror) {
log.Printf("[%.2fms] Execute SQL: %v",float64(t.Milliseconds()),sql)
}
//...
}

Supporting New Databases

Whiledbxprovides 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 theQueryBuilderinterface. You may useBaseQueryBuilderdirectly or extend it via composition.
• Create a struct that implements theBuilderinterface. You may extendBaseBuildervia composition.
• Write aninit()function to register the new builder indbx.BuilderFuncMap.