A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

Overview

ozzo-dbx

GoDoc Build Status Coverage Status Go Report

Summary

Other Languages

Русский

Description

ozzo-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. ozzo-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/go-ozzo/ozzo-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.

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

func main() {
	db, _ := dbx.Open("mysql", "user:[email protected]/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
	}
	err := q.All(&users)

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

	// fetch a single row into a string map
	data := dbx.NullStringMap{}
	err = 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.

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

func main() {
	db, _ := dbx.Open("mysql", "user:[email protected]/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
	}
	err := q.All(&users)

	// build an INSERT query
	//   INSERT INTO `users` (`name`) VALUES ('James')
	err = 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:[email protected]/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, ozzo-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, ozzo-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:[email protected]/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

ozzo-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:[email protected]/example")

// INSERT INTO `users` (`name`, `email`) VALUES ({:p0}, {:p1})
err := db.Insert("users", dbx.Params{
	"name": "James",
	"email": "[email protected]",
}).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:[email protected]/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 ozzo-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:[email protected]/example")

customer := Customer{
	Name: "example",
	Email: "[email protected]",
}
// INSERT INTO customer (name, email, status) VALUES ('example', '[email protected]', 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:[email protected]/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:[email protected]/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:[email protected]/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:[email protected]/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:[email protected]/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, ozzo-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:[email protected]/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:[email protected]/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.

import (
	"fmt"
	"log"
	"github.com/go-ozzo/ozzo-dbx"
)

func main() {
	db, _ := dbx.Open("mysql", "user:[email protected]/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 ozzo-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.
Issues
  • How can store complex entity into database with ozzo properly?

    How can store complex entity into database with ozzo properly?

    If I populate Actor with Actor and Person attributes, how can I store (INSERT, UPDATE) it into db tables actor and person with the same structure?

    type Actor struct{
        Id         int64
        deleted    bool
        ParsonId   int64
        Alias      string
        CreateDate string
        UpdatedAt  time.Time
        Person
    }
    
    type Person struct {
        Id         int64
        LastName   string
        FirstName  string
        Patronymic string
        Birthday   string
        Gender     string
        AvatarUrl  string
    }
    
    opened by kolkov 39
  • Why transaction not working?

    Why transaction not working?

    Hi! I tried to rollback transaction, but this has no effect.

    image image In log file I see that ozzo close connection and reopen it after transaction start.

    170113 19:51:38	    1 Connect	[email protected] on inframe
    		    1 Query	SELECT @@max_allowed_packet
    		    1 Query	SET NAMES utf8
    		    1 Query	START TRANSACTION
    		    2 Connect	[email protected] on inframe
    		    2 Query	SELECT @@max_allowed_packet
    		    2 Query	SET NAMES utf8
    		    2 Prepare	SELECT `id`, `kp_id` FROM `db_movie` WHERE `kp_id`=?
    		    2 Execute	SELECT `id`, `kp_id` FROM `db_movie` WHERE `kp_id`=302
    		    2 Close stmt	
    

    Is this normal? Log file from mysql: image image

    opened by kolkov 18
  • Return

    Return "photo" table name instead "photos"?

    Is it correct?
    
    type Photo struct{
        Id int `json:"id"`
        Image string `json:"image"`
    }
    
    func (p *Photo) TableName() string {
        return "photos"
    }
    
    func (p *Photo) GetPhotos(id int) {
        var photos []Photo
        q := db.Select("id", "image").
            Where(dbx.HashExp{"actor_id":  id}).OrderBy("id")
        err := q.All(&photos)
        if err != nil {
            println("Exec err:", err.Error())
        }
        return
    }
    
    opened by kolkov 8
  • Error when scan mysql timestamp into time.Time

    Error when scan mysql timestamp into time.Time

    SELECT * FROM actor LIMIT 5

    Exec err: sql: Scan error on column index 8: unsupported Scan, storing driver.Value type []uint8 into type *time.Time

    type Actor struct{
        Id int
        deleted bool
        CreateDate string
        UpdatedAt time.Time
        Person
    }
    
    opened by kolkov 6
  • How to properly use SQL_CALC_FOUND_ROWS and FOUND_ROWS()?

    How to properly use SQL_CALC_FOUND_ROWS and FOUND_ROWS()?

    Hi!

    When i use like:

    func (c *Casting) GetAll(from, limit int64) (*Castings, int64) {
        q := db.Select("casting.id", "firstname", "lastname", "patronymic", "birthday", "casting-date", "character_id", "character.name AS character-name").
            SelectOption("SQL_CALC_FOUND_ROWS").
            From("casting").OrderBy("id").
            Join("LEFT OUTER JOIN","character", dbx.NewExp("`character`.`id` = `casting`.`character_id`")).
            Limit(limit).
            Offset(from)
    
        var castings *Castings
        err := q.All(&castings)
        if err != nil {
            println("Exec err:", err.Error())
        }
        q = db.Select("FOUND_ROWS()");
        var total int64
        q.Row(&total)
    
        return castings, total
    }
    

    FOUND_ROWS() always returns 1;

    opened by kolkov 5
  • Unable to pass slice of int to dbx.In()

    Unable to pass slice of int to dbx.In()

    When passing []int to dbx.In(), the compiler give error as such:

    sql: converting argument $3 type: unsupported type []int, a slice of int

    How can I pass a slice in the dbx.In()?

    numberList := []int{ 1, 2, 3}
    err = rs.Tx().Select().Where(dbx.And(dbx.HashExp{"creator": rs.UserID(), "status": "a"}, dbx.In("role", numberList ))).OrderBy("modified").All(&customer)
    

    tried ->

    • dbx.In("role", numberList)
    • dbx.In("role", numberList...)

    can't really get it done... may please advise...

    opened by byanjiong 4
  • Autospecify table to select data from bug

    Autospecify table to select data from bug

    Hi again, I find one bug. I try to use this code:

    type User struct {
        Uid      int64
        Email    string
        Password string
    }
    
    func (u *User) TableName() string {
        return "users"
    }
    
    func main() {
        dbCon, _ := dbx.Open("postgres", fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=%s",
            "localhost", "postgres", "mysecretpassword", "demo", "disable"))
        dbCon.LogFunc = log.Printf
    
        var user User
        if err := dbCon.Select().Where(dbx.HashExp{"email": "[email protected]"}).One(&user); err != nil {
            log.Printf("Error: %s", err)
        }
        log.Println(user)
    }
    

    But receive SQL-error:

    [5.88ms] Query SQL: SELECT * WHERE "email"='[email protected]'
    Error: pq: SELECT * with no tables specified is not valid
    

    As I see in manual dbx should add table name automatically from TableName() method or by struct name:

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

    Please check this issue. Thank you

    opened by maxvoronov 4
  • How to build sql query with additional condition properly?

    How to build sql query with additional condition properly?

    Hi! How to build sql query with additional optional condition properly? For example, if we want to add extra where expression for our query in some cases...

    Thanks!

    opened by kolkov 3
  • Why Id populates from joined table?

    Why Id populates from joined table?

    Hi, Qiang! Happy New Year 2017! I use this code to populate FeedPersonName struct from MySQL tables. But Feed.Id, Feed.CreatedAt etc. populates with Id, CreatedAt etc. from Person table not from Feed. Is it correct?

    Feed

    type Feed struct{
    	Id int
    	deleted bool
    	PersonId int
    	Heading string
    	Body string
    	CreatedAt time.Time
    	UpdatedAt time.Time
    }
    
    type FeedPersonName struct {
    	Feed
    	Person PersonName
    }
    
    func (m FeedPersonName) TableName() string {
    	return "feed"
    }
    

    PersonName

    type PersonName struct {
    	LastName string `json:"last_name"`
    	FirstName string `json:"first_name"`
    }
    
    func (m PersonName) TableName() string {
    	return "person"
    }
    

    FeedDAO

    func (dao *FeedDAO) Query(rs app.RequestScope, offset, limit int) ([]models.Feed, error) {
    	feed := []models.Feed{}
    	err := rs.Tx().Select().InnerJoin("person", dbx.NewExp("`feed`.`person_id` = `person`.`id`")).OrderBy("id").Offset(int64(offset)).OrderBy("feed.id DESC").Limit(int64(limit)).All(&feed)
    	return feed, err
    }
    

    image

    image

    image

    opened by kolkov 3
  • How to use transactions?

    How to use transactions?

    Hi! How to use transactions when database queries located in different methods? Like in my case? Or I must using this in handler functions?

    func (m *Actor) beforeInsert(){
        m.CreatedAt = time.Now()
        m.UpdatedAt = m.CreatedAt
        m.AvatarUrl = "/img/face.png"
        p := m.Person
        p.Create()
        m.PersonId = p.Id
    }
    
    func (m *Actor) Create() error {
        err := db.Transactional(func(tx *dbx.Tx) error {
            var err error
            m.beforeInsert()
            err = tx.Model(m).Exclude().Insert("Alias", "PersonId")
            if err != nil {
                fmt.Println("Exec err:", err.Error())
            }
            return err
        })
        return err
    }
    
    opened by kolkov 3
  • go get -u github.com/go-ozzo/ozzo-dbx error

    go get -u github.com/go-ozzo/ozzo-dbx error

    D:\Go\src\inframe>go get -u github.com/go-ozzo/ozzo-dbx
    # cd D:\Go\src\github.com\go-ozzo\ozzo-dbx; git pull --ff-only
    From https://github.com/go-ozzo/ozzo-dbx
       96b973a..5e793d3  master     -> origin/master
     * [new tag]         v1.0.5     -> v1.0.5
    You are not currently on a branch. Please specify which
    branch you want to merge with. See git-pull(1) for details.
    
        git pull <remote> <branch>
    
    package github.com/go-ozzo/ozzo-dbx: exit status 1
    

    Whats wrong?

    opened by kolkov 3
  • Maintenance status?

    Maintenance status?

    Hi @qiangxue,

    I'm currently using ozzo-dbx as part of another open source project that I'm soon planning to release and therefore I'm asking for the maintenance status of the package so that I can decide whether to use my fork as import path or postpone a bit the release until PRs #99, #100, #102 are reviewed and merged.

    I really like ozzo-dbx and its lightweight query builder approach and hope its not getting abandoned.

    opened by ganigeorgiev 1
  • Added 'sqlite' key to db.BuilderFuncMap for noncgo sqlite driver support

    Added 'sqlite' key to db.BuilderFuncMap for noncgo sqlite driver support

    The noncgo sqlite driver (https://pkg.go.dev/modernc.org/sqlite) uses sqlite as driver key. This PR adds the key to the db.BuilderFuncMap, so that users don't have to do:

    func init() {
        dbx.BuilderFuncMap["sqlite"] = dbx.BuilderFuncMap["sqlite3"]
    }
    
    opened by ganigeorgiev 0
  • Enable RenameTable, RenameColumn and DropColumn in the sqlite builder

    Enable RenameTable, RenameColumn and DropColumn in the sqlite builder

    SQLite 3 has ALTER TABLE support for most of the schema modify operations - https://www.sqlite.org/lang_altertable.html

    This PR enables in the builder_sqlite.go:

    • RenameTable
    • RenameColumn (the same syntax as the default builder)
    • DropColumn (the same syntax as the default builder)
    opened by ganigeorgiev 0
  • Add Upsert to model query

    Add Upsert to model query

    Hi! I can't see Upsert method in the model query. Is it possible to add it to this type of query? Thanks! https://github.com/go-ozzo/ozzo-dbx/issues/5

    opened by kolkov 0
Releases(v1.5.0)
Owner
Ozzo Framework
Ozzo is a Go (golang) framework consisting of fully decoupled packages supporting rapid Web application development.
Ozzo Framework
SQL builder and query library for golang

__ _ ___ __ _ _ _ / _` |/ _ \ / _` | | | | | (_| | (_) | (_| | |_| | \__, |\___/ \__, |\__,_| |___/ |_| goqu is an expressive SQL bu

Doug Martin 1.6k Aug 15, 2022
SQL query builder for Go

GoSQL Query builder with some handy utility functions. Documentation For full documentation see the pkg.go.dev or GitBook. Examples // Open database a

Travis Harmon 23 Aug 6, 2022
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

null 170 Aug 11, 2022
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Vlad Glushchuk 81 Aug 8, 2022
Query git repositories with SQL. Generate reports, perform status checks, analyze codebases. 🔍 📊

askgit askgit is a command-line tool for running SQL queries on git repositories. It's meant for ad-hoc querying of git repositories on disk through a

Augmentable 3.1k Aug 19, 2022
gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

Sang-gon Lee 49 Apr 26, 2022
SQL query helper

SQL query helper

Aleksey Nikitin 1 Nov 7, 2021
Go database query builder library for PostgreSQL

buildsqlx Go Database query builder library Installation Selects, Ordering, Limit & Offset GroupBy / Having Where, AndWhere, OrWhere clauses WhereIn /

Arthur Kushman 70 Aug 18, 2022
Bulk query SQLite database over the network

SQLiteQueryServer Bulk query SQLite database over the network. Way faster than SQLiteProxy!

Assaf Morami 48 May 20, 2022
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

XO 3.2k Aug 12, 2022
Go fearless SQL. Sqlvet performs static analysis on raw SQL queries in your Go code base.

Sqlvet Sqlvet performs static analysis on raw SQL queries in your Go code base to surface potential runtime errors at build time. Feature highlights:

QP Hou 451 Aug 8, 2022
Database Abstraction Layer (dbal) for Go. Support SQL builder and get result easily (now only support mysql)

godbal Database Abstraction Layer (dbal) for go (now only support mysql) Motivation I wanted a DBAL that No ORM、No Reflect、Concurrency Save, support S

徐佳军 53 Jan 23, 2022
LBADD: An experimental, distributed SQL database

LBADD Let's build a distributed database. LBADD is an experimental distributed SQL database, written in Go. The goal of this project is to build a dat

Tom Arrell 377 Aug 4, 2022
Mocking your SQL database in Go tests has never been easier.

copyist Mocking your SQL database in Go tests has never been easier. The copyist library automatically records low-level SQL calls made during your te

CockroachDB 809 Jul 20, 2022
Additions to Go's database/sql for super fast performance and convenience. (fork of gocraft/dbr)

dbr (fork of gocraft/dbr) provides additions to Go's database/sql for super fast performance and convenience. Getting Started // create a connection (

Free and open source software developed at Mail.Ru 157 Jul 11, 2022
Go library for accessing multi-host SQL database installations

hasql hasql provides simple and reliable way to access high-availability database setups with multiple hosts. Status hasql is production-ready and is

Yandex 96 Aug 3, 2022
Document-oriented, embedded SQL database

Genji Document-oriented, embedded, SQL database Table of contents Table of contents Introduction Features Installation Usage Using Genji's API Using d

Genji 978 Aug 8, 2022
Squat is an application that provides simple SQL data generation functionality.

Squat Squat is an application that provides simple SQL data generation functionality. It generates synthetic SQL data based on the table definition, t

Mateusz Urbanek 7 Mar 9, 2022
Querydecoder - Optional query parameter decoder for Golang

Optional query parameter decoder for Golang Example import ( "github.com/ritwic

Ritwick Dey 8 Jun 10, 2022