SQL query builder, fork of Squirrel with improved performance

# sqrl - fat-free version of squirrel - fluent SQL generator for Go

**Non thread safe** fork of [squirrel](http://github.com/lann/squirrel). The same handy fluffy helper, but with extra letters removed :)

```go
import "github.com/elgris/sqrl"
```

[![GoDoc](https://godoc.org/github.com/elgris/sqrl?status.svg)](https://godoc.org/github.com/elgris/sqrl)
[![Build Status](https://travis-ci.org/elgris/sqrl.svg?branch=master)](https://travis-ci.org/elgris/sqrl)

**Requires Go 1.8 and higher**

## Inspired by

- [squirrel](https://github.com/lann/squirrel)
- [dbr](https://github.com/gocraft/dbr)

## Why to make good squirrel lighter?

Ask [benchmarks](https://github.com/elgris/golang-sql-builder-benchmark) about that ;). Squirrel is good, reliable and thread-safe with it's immutable query builder. Although immutability is nice, it's resource consuming and sometimes redundant. As authors of `dbr` say: "100% of our application code was written without the need for this".

## Why not to use dbr then?

Although, `dbr`'s query builder is proven to be much [faster than squirrel](https://github.com/tyler-smith/golang-sql-benchmark) and even faster than [sqrl](https://github.com/elgris/golang-sql-builder-benchmark), it doesn't have all syntax sugar. Especially I miss support of JOINs, subqueries and aliases.

## Usage

**sqrl is not an ORM.**, it helps you build SQL queries from composable parts.
**sqrl is non thread safe**. SQL builders change their state, so using the same builder in parallel is dangerous.

It's very easy to switch between original squirrel and sqrl, because there is no change in interface:

```go
import sq "github.com/elgris/sqrl" // you can easily use github.com/lann/squirrel here

users := sq.Select("*").From("users").Join("emails USING (email_id)")

active := users.Where(sq.Eq{"deleted_at": nil})

sql, args, err := active.ToSql()

sql == "SELECT * FROM users JOIN emails USING (email_id) WHERE deleted_at IS NULL"
```

```go
sql, args, err := sq.
    Insert("users").Columns("name", "age").
    Values("moe", 13).Values("larry", sq.Expr("? + 5", 12)).
    ToSql()

sql == "INSERT INTO users (name,age) VALUES (?,?),(?,? + 5)"
```

Like [squirrel](https://github.com/lann/squirrel), sqrl can execute queries directly:

```go
stooges := users.Where(sq.Eq{"username": []string{"moe", "larry", "curly", "shemp"}})
three_stooges := stooges.Limit(3)
rows, err := three_stooges.RunWith(db).Query()

// Behaves like:
rows, err := db.Query("SELECT * FROM users WHERE username IN (?,?,?,?) LIMIT 3", "moe", "larry", "curly", "shemp")
```

Build conditional queries with ease:

```go
if len(q) > 0 {
    users = users.Where("name LIKE ?", q)
}
```

### MySQL-specific functions

#### [Multi-table delete](https://dev.mysql.com/doc/refman/5.7/en/delete.html)

```go
sql, args, err := sq.Delete("a1", "a2").
    From("z1 AS a1").
    JoinClause("INNER JOIN a2 ON a1.id = a2.ref_id").
    Where("b = ?", 1).
    ToSql()
```

```go
sql, args, err := sq.Delete("a1").
    Using("a2").
    Where("a1.id = a2.ref_id AND a2.num = ?", 42).
    ToSql()
```

### PostgreSQL-specific functions

Package [pg](https://godoc.org/github.com/elgris/sqrl/pg) contains PostgreSQL specific operators.

#### [Update from](https://www.postgresql.org/docs/current/static/sql-update.html)

```go
sql, args, err := sq.Update("a1").
    Set("foo", 1).
    From("a2").
    Where("id = a2.ref_id AND a2.num = ?", 42).
    ToSql()
```

#### [Delete using](https://www.postgresql.org/docs/current/static/sql-delete.html)
```go
sql, args, err := sq.Delete("a1").
    Using("a2").
    Where("id = a2.ref_id AND a2.num = ?", 42).
    ToSql()
```

#### [Returning clause](https://www.postgresql.org/docs/current/static/dml-returning.html)
```go
sql, args, err := Update("a").
    Set("foo", 1).
    Where("id = ?", 42).
    Returning("bar").
    ToSql()
```

#### [JSON values](https://www.postgresql.org/docs/current/static/functions-json.html)

JSON and JSONB use json.Marshal to serialize values and cast them to appropriate column type.

```go
sql, args, err := sq.Insert("posts").
    Columns("content", "tags").
    Values("Lorem Ipsum", pg.JSONB([]string{"foo", "bar"})).
    ToSql()
```

#### [Array values](https://www.postgresql.org/docs/current/static/arrays.html)

Array serializes single and multidimensional slices of string, int, float32 and float64 values.

```go
sql, args, err := sqrl.Insert("posts").
    Columns("content", "tags").
    Values("Lorem Ipsum", pg.Array([]string{"foo", "bar"})).
    ToSql()
```

## License

Sqrl is released under the
[MIT License](http://www.opensource.org/licenses/MIT).


sqrl

Go library that helps you build SQL queries

[![Stability: Maintenance](https://masterminds.github.io/stability/maintenance.svg)](https://masterminds.github.io/stability/maintenance.html)
### Squirrel is "complete".
Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork (or substantially similar project) actively improves on what Squirrel does, let me know and I may link to it here.


# Squirrel - fluent SQL generator for Go

```go
import "github.com/Masterminds/squirrel"
```


[![GoDoc](https://godoc.org/github.com/Masterminds/squirrel?status.png)](https://godoc.org/github.com/Masterminds/squirrel)
[![Build Status](https://api.travis-ci.org/Masterminds/squirrel.svg?branch=master)](https://travis-ci.org/Masterminds/squirrel)

**Squirrel is not an ORM.** For an application of Squirrel, check out
[structable, a table-struct mapper](https://github.com/Masterminds/structable)


Squirrel helps you build SQL queries from composable parts:

```go
import sq "github.com/Masterminds/squirrel"

users := sq.Select("*").From("users").Join("emails USING (email_id)")

active := users.Where(sq.Eq{"deleted_at": nil})

sql, args, err := active.ToSql()

sql == "SELECT * FROM users JOIN emails USING (email_id) WHERE deleted_at IS NULL"
```

```go
sql, args, err := sq.
    Insert("users").Columns("name", "age").
    Values("moe", 13).Values("larry", sq.Expr("? + 5", 12)).
    ToSql()

sql == "INSERT INTO users (name,age) VALUES (?,?),(?,? + 5)"
```

Squirrel can also execute queries directly:

```go
stooges := users.Where(sq.Eq{"username": []string{"moe", "larry", "curly", "shemp"}})
three_stooges := stooges.Limit(3)
rows, err := three_stooges.RunWith(db).Query()

// Behaves like:
rows, err := db.Query("SELECT * FROM users WHERE username IN (?,?,?,?) LIMIT 3",
                      "moe", "larry", "curly", "shemp")
```

Squirrel makes conditional query building a breeze:

```go
if len(q) > 0 {
    users = users.Where("name LIKE ?", fmt.Sprint("%", q, "%"))
}
```

Squirrel wants to make your life easier:

```go
// StmtCache caches Prepared Stmts for you
dbCache := sq.NewStmtCache(db)

// StatementBuilder keeps your syntax neat
mydb := sq.StatementBuilder.RunWith(dbCache)
select_users := mydb.Select("*").From("users")
```

Squirrel loves PostgreSQL:

```go
psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)

// You use question marks for placeholders...
sql, _, _ := psql.Select("*").From("elephants").Where("name IN (?,?)", "Dumbo", "Verna").ToSql()

/// ...squirrel replaces them using PlaceholderFormat.
sql == "SELECT * FROM elephants WHERE name IN ($1,$2)"


/// You can retrieve id ...
query := sq.Insert("nodes").
    Columns("uuid", "type", "data").
    Values(node.Uuid, node.Type, node.Data).
    Suffix("RETURNING \"id\"").
    RunWith(m.db).
    PlaceholderFormat(sq.Dollar)

query.QueryRow().Scan(&node.id)
```

You can escape question marks by inserting two question marks:

```sql
SELECT * FROM nodes WHERE meta->'format' ??| array[?,?]
```

will generate with the Dollar Placeholder:

```sql
SELECT * FROM nodes WHERE meta->'format' ?| array[$1,$2]
```

## FAQ

* **How can I build an IN query on composite keys / tuples, e.g. `WHERE (col1, col2) IN ((1,2),(3,4))`? ([#104](https://github.com/Masterminds/squirrel/issues/104))**

    Squirrel does not explicitly support tuples, but you can get the same effect with e.g.:

    ```go
    sq.Or{
      sq.Eq{"col1": 1, "col2": 2},
      sq.Eq{"col1": 3, "col2": 4}}
    ```

    ```sql
    WHERE (col1 = 1 AND col2 = 2) OR (col1 = 3 AND col2 = 4)
    ```

    (which should produce the same query plan as the tuple version)

* **Why doesn't `Eq{"mynumber": []uint8{1,2,3}}` turn into an `IN` query? ([#114](https://github.com/Masterminds/squirrel/issues/114))**

    Values of type `[]byte` are handled specially by `database/sql`. In Go, [`byte` is just an alias of `uint8`](https://golang.org/pkg/builtin/#byte), so there is no way to distinguish `[]uint8` from `[]byte`.

* **Some features are poorly documented!**

    This isn't a frequent complaints section!

* **Some features are poorly documented?**

    Yes. The tests should be considered a part of the documentation; take a look at those for ideas on how to express more complex queries.

## License

Squirrel is released under the
[MIT License](http://www.opensource.org/licenses/MIT).


Squirrel

Generate idiomatic Go code for databases based on existing schema definitions or custom queries supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

Lightweight and easy to learn query builder

# Basic Query Builder

[![Tests Status](https://github.com/nullism/bqb/actions/workflows/tests.yml/badge.svg?branch=main)](https://github.com/nullism/bqb/actions/workflows/tests.yml) [![GoDoc](https://godoc.org/github.com/nullism/bqb?status.svg)](https://godoc.org/github.com/nullism/bqb) [![code coverage](coverage.svg)](https://github.com/nullism/bqb/actions/workflows/tests.yml) [![Go Report Card](https://img.shields.io/badge/go%20report-A+-brightgreen.svg?style=flat)](https://goreportcard.com/report/github.com/nullism/bqb) [![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go)

# Compatibility

This has been tested with sqlite, PostGres, and MySQL, using `database/sql`, `pq`, `pgx`, and `sqlx`.
By the nature of how it works it should be fully compatible with any DB interface and database that uses `?` or `$` parameter syntax.

_Note: Go `v1.20+` is required for BQB `>= v1.4.0`. Go `v1.17+` is required for BQB `<= v1.3.0`._

# Why

1. Simple, lightweight, and fast
2. Supports any and all syntax by the nature of how it works
3. Doesn't require learning special syntax or operators
4. 100% test coverage

# Examples

## Basic

```golang
q := bqb.New("SELECT * FROM places WHERE id = ?", 1234)
sql, params, err := q.ToSql()
```

Produces

```sql
SELECT * FROM places WHERE id = ?
```

```
PARAMS: [1234]
```

### Escaping `?`

Use the double question mark `??` value to escape the `?` in Postgres queries.
For example:

```golang
q := bqb.New("SELECT * FROM places WHERE json_obj_column ?? 'key'")
sql, params, err := q.ToPgsql()
```

This query uses the `?` operator for jsonb types in Postgres to test an object
for the presence of a key. It should not be interpreted as an escaped value by
bqb.

```sql
SELECT * FROM places WHERE json_obj_column ? 'key'
```

```
PARAMS: []
```

## Postgres - ToPgsql()

Just call the `ToPgsql()` method instead of `ToSql()` to convert the query to Postgres syntax

```golang
q := bqb.New("DELETE FROM users").
    Space("WHERE id = ? OR name IN (?)", 7, []string{"delete", "remove"}).
    Space("LIMIT ?", 5)
sql, params, err := q.ToPgsql()
```

Produces

```sql
DELETE FROM users WHERE id = $1 OR name IN ($2, $3) LIMIT $4
```

```
PARAMS: [7, "delete", "remove", 5]
```

## Raw - ToRaw()

_Obvious warning: You should not use this for user input_

The `ToRaw()` call returns a string with the values filled in rather than parameterized

```golang
q := New("a = ?, b = ?, c = ?", "my a", 1234, nil)
sql, err := q.ToRaw()
```

Produces

```
a = 'my a', b = 1234, c = NULL
```

## Types

```golang
q := bqb.New(
    "int:? string:? []int:? []string:? Query:? JsonMap:? nil:? []intf:?",
    1, "2", []int{3, 3}, []string{"4", "4"}, bqb.New("5"), bqb.JsonMap{"6": 6}, nil, []interface{}{"a",1,true},
)
sql, _ := q.ToRaw()
```

Produces

```
int:1 string:'2' []int:3,3 []string:'4','4' Query:5 JsonMap:'{"6":6}' nil:NULL []intf:'a',1,true
```

### driver.Valuer

The [driver.Valuer](https://pkg.go.dev/database/sql/driver#Valuer) interface is supported for types that are able to convert
themselves to a sql driver value. See [examples/main.go:valuer](./examples/main.go#L102).

```
q := bqb.New("?", valuer)
```

### Embedder

BQB provides an Embedder interface for directly replacing `?` with a string returned by the `RawValue` method on the Embedder implementation.

This can be useful for changing sort direction or embedding table and column names. See [examples/main.go:embedder](./examples/main.go#L122) for an example.

_Note: Since this is a raw value, special attention should be paid to ensure user-input is checked and sanitized._

### Folded

The `Folded` type and corresponding `ToFolded` generic function will prevent spreading of `[]string`, `[]int`, or `[]any`. For example, `bqb.New("?", []string{"a","b"})` will become `("?,?", "a", "b")` by default.

```go
strns := []string{"a", "b"}
q := bqb.New("Folded: ? - Default: ?", bqb.ToFolded(strns), strns)
// sql = Folded: ? - Default: ?,?
// params = { []string{"a", "b"}, "a", "b" }
```

## Query IN

Arguments of type `[]string`,`[]*string`, `[]int`,`[]*int`, and `[]any` / `[]interface{}` are automatically expanded.

```golang
    q := bqb.New(
        "strs:(?) *strs:(?) ints:(?) *ints:(?) intfs:(?)",
        []string{"a", "b"}, []*string{}, []int{1, 2}, []*int{}, []any{3, true},
    )
    sql, params, _ := q.ToSql()
```

Produces

```
SQL: strs:(?,?) *strs:(?) ints:(?,?) *ints:(?) intfs:(?,?)
PARAMS: [a b <nil> 1 2 <nil> 3 true]
```

## Json Arguments

There are two helper structs, `JsonMap` and `JsonList` to make JSON conversion a little simpler.

```golang

sql, err := bqb.New(
    "INSERT INTO my_table (json_map, json_list) VALUES (?, ?)",
    bqb.JsonMap{"a": 1, "b": []string{"a","b","c"}},
    bqb.JsonList{"string",1,true,nil},
).ToRaw()
```

Produces

```sql
INSERT INTO my_table (json_map, json_list)
VALUES ('{"a": 1, "b": ["a","b","c"]}', '["string",1,true,null]')
```

## Query Building

Since queries are built in an additive way by reference rather than value, it's easy to mutate a query without
having to reassign the result.

### Basic Example

```golang
sel := bqb.New("SELECT")

...

// later
sel.Space("id")

...

// even later
sel.Comma("age").Comma("email")
```

Produces

```sql
SELECT id,age,email
```

### Advanced Example

The `Optional(string)` function returns a query that resolves to an empty string if no query parts have
been added via methods on the query instance, and joins with a space to the next query part.
For example `q := Optional("SELECT")` will resolve to an empty string unless parts have been added by one of the methods,
e.g `q.Space("* FROM my_table")` would make `q.ToSql()` resolve to `SELECT * FROM my_table`.

```golang

sel := bqb.Optional("SELECT")

if getName {
    sel.Comma("name")
}

if getId {
    sel.Comma("id")
}

if !getName && !getId {
    sel.Comma("*")
}

from := bqb.New("FROM my_table")

where := bqb.Optional("WHERE")

if filterAdult {
    adultCond := bqb.New("name = ?", "adult")
    if ageCheck {
        adultCond.And("age > ?", 20)
    }
    where.And("(?)", adultCond)
}

if filterChild {
    where.Or("(name = ? AND age < ?)", "youth", 21)
}

q := bqb.New("? ? ?", sel, from, where).Space("LIMIT ?", 10)
```

Assuming all values are true, the query would look like:

```sql
SELECT name,id FROM my_table WHERE (name = 'adult' AND age > 20) OR (name = 'youth' AND age < 21) LIMIT 10
```

If `getName` and `getId` are false, the query would be

```sql
SELECT * FROM my_table WHERE (name = 'adult' AND age > 20) OR (name = 'youth' AND age < 21) LIMIT 10
```

If `filterAdult` is `false`, the query would be:

```sql
SELECT name,id FROM my_table WHERE (name = 'youth' AND age < 21) LIMIT 10
```

If all values are `false`, the query would be:

```sql
SELECT * FROM my_table LIMIT 10
```

## Methods

Methods on the bqb `Query` struct follow the same pattern.

All query-modifying methods take a string (the query text) and variable length interface (the query args).

For example `q.And("abc")` will add `AND abc` to the query.

Take the following

```golang
q := bqb.Optional("WHERE")
q.Empty() // returns true
q.Len() // returns 0
q.Space("1 = 2") // query is now WHERE 1 = 2
q.Empty() // returns false
q.Len() // returns 1
q.And("b") // query is now WHERE 1 = 2 AND b
q.Or("c") // query is now WHERE 1 = 2 AND b OR c
q.Concat("d") // query is now WHERE 1 = 2 AND b OR cd
q.Comma("e") // query is now WHERE 1 = 2 AND b OR cd,e
q.Join("+", "f") // query is now WHERE 1 = 2 AND b OR cd,e+f
```

Valid `args` include `string`, `int`, `floatN`, `*Query`, `[]int`, `Embedder`, `Embedded`, `driver.Valuer` or `[]string`.

# Frequently Asked Questions

## Is there more documentation?

It's not really necessary because the API is so tiny and public methods are documented in code,
see [godoc](https://godoc.org/github.com/nullism/bqb).
However, you can check out the [tests](./query_test.go) and [examples](./examples/main.go) to see the variety of usages.

## Why not just use a string builder?

Bqb provides several benefits over a string builder:

For example let's say we use the string builder way to build the following:

```golang
var params []interface{}
var whereParts []string
q := "SELECT * FROM my_table "
if filterAge {
    params = append(params, 21)
    whereParts = append(whereParts, fmt.Sprintf("age > $%d ", len(params)))
}

if filterBobs {
    params = append(params, "Bob%")
    whereParts = append(whereParts, fmt.Sprintf("name LIKE $%d ", len(params)))
}

if len(whereParts) > 0 {
    q += "WHERE " + strings.Join(whereParts, " AND ") + " "
}

if limit != nil {
    params = append(params, limit)
    q += fmt.Sprintf("LIMIT $%d", len(params))
}

// SELECT * FROM my_table WHERE age > $1 AND name LIKE $2 LIMIT $3
```

Some problems with that approach

1. You must perform a string join for the various parts of the where clause
2. You must remember to include a trailing or leading space for each clause
3. You have to keep track of parameter count (for Postgres anyway)
4. It's kind of ugly

The same logic can be achieved with `bqb` a bit more cleanly

```golang
q := bqb.New("SELECT * FROM my_table")
where := bqb.Optional("WHERE")
if filterAge {
    where.And("age > ?", 21)
}

if filterBobs {
    where.And("name LIKE ?", "Bob%")
}

q.Space("?", where)

if limit != nil {
    q.Space("LIMIT ?", limit)
}

// SELECT * FROM my_table WHERE age > $1 AND name LIKE $2 LIMIT $3
```

Both methods will allow you to remain close to the SQL, however the `bqb` approach will

1. Easily adapt to MySQL or Postgres without changing parameters
2. Hide the "WHERE" clause if both `filterBobs` and `filterAge` are false

## Why not use a full query builder?

Take the following _typical_ query example:

```golang
q := qb.Select("*").From("users").Where(qb.And{qb.Eq{"name": "Ed"}, qb.Gt{"age": 21}})
```

Vs the bqb way:

```golang
q := bqb.New("SELECT * FROM users WHERE name = ? AND age > ?", "ed", 21)
```

## Okay, so a simple query it might make sense to use something like `bqb`, but what about grouped queries?

A query builder can handle this in multiple ways, a fairly common pattern might be:

```golang
q := qb.Select("name").From("users")

and := qb.And{}

if checkAge {
    and = append(and, qb.Gt{"age": 21})
}

if checkName {
    or := qb.Or{qb.Eq{"name":"trusted"}}
    if nullNameOkay {
        or = append(or, qb.Is{"name": nil})
    }
    and = append(and, or)
}

q = q.Where(and)

// SELECT name FROM users WHERE age > 21 AND (name = 'trusted' OR name IS NULL)
```

Contrast that with the `bqb` approach:

```golang

q := bqb.New("SELECT name FROM users")

where := bqb.Optional("WHERE")

if checkAge {
    where.And("age > ?", 21)
}

if checkName {
    or := bqb.New("name = ?", "trusted")
    if nullNameOkay {
        or.Or("name IS ?", nil)
    }
    where.And("(?)", or)
}

q.Space("?", where)

// SELECT name FROM users WHERE age > 21 AND (name = 'trusted' OR name IS NULL)
```

It seems to be a matter of taste as to which method appears cleaner.


Go database query builder library for PostgreSQL

# buildsqlx

Go Database query builder
library [![Tweet](http://jpillora.com/github-twitter-button/img/tweet.png)](https://twitter.com/intent/tweet?text=Go%20database%20query%20builder%20library%20&url=https://github.com/arthurkushman/buildsqlx&hashtags=go,golang,sql,builder,postgresql,sql-builder,developers)

[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go)
[![Go Report Card](https://goreportcard.com/badge/github.com/arthurkushman/buildsqlx)](https://goreportcard.com/report/github.com/arthurkushman/buildsqlx)
[![Build and run](https://github.com/arthurkushman/buildsqlx/workflows/Build%20and%20run/badge.svg)](https://github.com/arthurkushman/buildsqlx/actions)
[![GoDoc](https://github.com/golang/gddo/blob/c782c79e0a3c3282dacdaaebeff9e6fd99cb2919/gddo-server/assets/status.svg)](https://godoc.org/github.com/arthurkushman/buildsqlx)
[![codecov](https://codecov.io/gh/arthurkushman/buildsqlx/branch/master/graph/badge.svg)](https://codecov.io/gh/arthurkushman/buildsqlx)
[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](https://opensource.org/licenses/MIT)

* [Installation](#user-content-installation)
* [Selects, Ordering, Limit & Offset](#user-content-selects-ordering-limit--offset)
* [GroupBy / Having](#user-content-groupby--having)
* [Where, AndWhere, OrWhere clauses](#user-content-where-andwhere-orwhere-clauses)
* [WhereIn / WhereNotIn](#user-content-wherein--wherenotin)
* [WhereNull / WhereNotNull](#user-content-wherenull--wherenotnull)
* [Left / Right / Cross / Inner / Left Outer Joins](#user-content-left--right--cross--inner--left-outer-joins)
* [Inserts](#user-content-inserts)
* [Updates](#user-content-updates)
* [Delete](#user-content-delete)
* [Drop, Truncate, Rename](#user-content-drop-truncate-rename)
* [Increment & Decrement](#user-content-increment--decrement)
* [Union / Union All](#user-content-union--union-all)
* [Transaction mode](#user-content-transaction-mode)
* [Dump, Dd](#user-content-dump-dd)
* [Check if table exists](#user-content-check-if-table-exists)
* [Check if columns exist in a table within schema](#user-content-check-if-columns-exist-in-a-table-within-schema)
* [Retrieving A Single Row / Column From A Table](#user-content-retrieving-a-single-row--column-from-a-table)
* [WhereExists / WhereNotExists](#user-content-whereexists--wherenotexists)
* [Determining If Records Exist](#user-content-determining-if-records-exist)
* [Aggregates](#user-content-aggregates)
* [Create table](#user-content-create-table)
* [Add / Modify / Drop columns](#user-content-add--modify--drop-columns)
* [Chunking Results](#user-content-chunking-results)
* [Pluck / PluckMap](#user-content-pluck--pluckmap)

## Installation

```bash
go get -u github.com/arthurkushman/buildsqlx
```

## Selects, Ordering, Limit & Offset

You may not always want to select all columns from a database table. Using the select method, you can specify a custom
select clause for the query:

```go
package yourpackage

import (
	"database/sql"

	"github.com/arthurkushman/buildsqlx"
	_ "github.com/lib/pq"
)

var db = buildsqlx.NewDb(buildsqlx.NewConnection("postgres", "user=postgres dbname=postgres password=postgres sslmode=disable"))

func main() {
	qDb := db.Table("posts").Select("title", "body")

	type DataStruct struct {
		Foo string
		Bar string
		Baz *int64
	}

	dataStruct := DataStruct{}
	var testStructs []DataStruct
	// If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the addSelect method:
	err := qDb.AddSelect("points").GroupBy("topic").OrderBy("points", "DESC").Limit(15).Offset(5).EachToStruct(func(rows *sql.Rows) error {
		err = db.Next(rows, &dataStruct)
		if err != nil {
			return err
		}

		testStructs = append(testStructs, dataStruct)
		return nil
	})
}
```

### InRandomOrder

```go
err = db.Table("users").Select("name", "post", "user_id").InRandomOrder().ScanStruct(dataStruct)
```

## GroupBy / Having

The `GroupBy` and `Having` methods may be used to group the query results.
The having method's signature is similar to that of the `Where` method:

```go
err = db.table("users").GroupBy("account_id").Having("account_id", ">", 100).ScanStruct(dataStruct)
```

## Where, AndWhere, OrWhere clauses

You may use the `Where` method on a query builder instance to add where clauses to the query.
The most basic call to where requires three arguments.
The first argument is the name of the column.
The second argument is an operator, which can be any of the database's supported operators.
Finally, the third argument is the value to evaluate against the column.

```go
err = db.Table("table1").Select("foo", "bar", "baz").Where("foo", "=", cmp).AndWhere("bar", "!=", "foo").OrWhere("baz", "=", 123)..ScanStruct(dataStruct)
```

You may chain where constraints together as well as add or clauses to the query.
The `OrWhere` method accepts the same arguments as the `Where` method.

## WhereIn / WhereNotIn

The `WhereIn` method verifies that a given column's value is contained within the given slice:

```go
err = db.Table("table1").WhereIn("id", []int64{1, 2, 3}).OrWhereIn("name", []string{"John", "Paul"}).ScanStruct(dataStruct)
```

## WhereNull / WhereNotNull

The `WhereNull` method verifies that the value of the given column is `NULL`:

```go
err = db.Table("posts").WhereNull("points").OrWhereNotNull("title")..ScanStruct(dataStruct)
```

## Left / Right / Cross / Inner / Left Outer Joins

The query builder may also be used to write join statements.
To perform a basic "inner join", you may use the `InnerJoin` method on a query builder instance.
The first argument passed to the join method is the name of the table you need to join to,
while the remaining arguments specify the column constraints for the join.
You can even join to multiple tables in a single query:

```go
err = db.Table("users").Select("name", "post", "user_id").LeftJoin("posts", "users.id", "=", "posts.user_id").EachToStruct(func(rows *sql.Rows) error {
    err = db.Next(rows, &dataStruct)
    if err != nil {
        return err
    }
    
    testStructs = append(testStructs, dataStruct)
    return nil
})
```

## Inserts

The query builder also provides an `Insert` method for inserting records into the database table.
The `Insert/InsertBatch` methods accept a structure (or slice of structs) of column names and values:

```go
// insert without getting id
err = db.Table("table1").Insert(DataStruct{
    Foo: "foo foo foo",
    Bar: "bar bar bar",
    Baz: &baz,
})

// insert returning id
id, err := db.Table("table1").InsertGetId(DataStruct{
    Foo: "foo foo foo",
    Bar: "bar bar bar",
    Baz: &baz,
})

// batch insert 
err = db.Table("table1").InsertBatch([]DataStruct{
    {Foo: "foo foo foo", Bar: "bar bar bar", Baz: &baz},
    {Foo: "foo foo foo foo", Bar: "bar bar bar bar", Baz: &baz},
    {Foo: "foo foo foo foo foo", Bar: "bar bar bar bar bar", Baz: &baz},
})
```

## Updates

In addition to inserting records into the database,
the query builder can also update existing records using the update method.
The update method, like the insert method, accepts a slice of column and value pairs containing the columns to be
updated.
You may constrain the update query using where clauses:

```go
rows, err := db.Table("posts").Where("points", ">", 3).Update(DataStruct{
    Title: "awesome",
})
```

## Delete

The query builder may also be used to delete records from the table via the delete method.
You may constrain delete statements by adding where clauses before calling the delete method:

```go
rows, err := db.Table("posts").Where("points", "=", 123).Delete()
```

## Drop, Truncate, Rename

```go
db.Drop("table_name")

db.DropIfExists("table_name")

db.Truncate("table_name")

db.Rename("table_name1", "table_name2")
```

## Increment & Decrement

The query builder also provides convenient methods for incrementing or decrementing the value of a given column.
This is a shortcut, providing a more expressive and terse interface compared to manually writing the update statement.

Both of these methods accept 2 arguments: the column to modify, a second argument to control the amount by which the
column should be incremented or decremented:

```go
db.Table("users").Increment("votes", 3)

db.Table("users").Decrement("votes", 1)
```

## Union / Union All

The query builder also provides a quick way to "union" two queries together.
For example, you may create an initial query and use the union method to union it with a second query:

```go
union := db.Table("posts").Select("title", "likes").Union()
res, err := union.Table("users").Select("name", "points").ScanStruct(dataStruct)

// or if UNION ALL is of need
// union := db.Table("posts").Select("title", "likes").UnionAll()
```

## Transaction mode

You can run arbitrary queries mixed with any code in transaction mode getting an error and as a result rollback if
something went wrong
or committed if everything is ok:

```go
err := db.InTransaction(func () (interface{}, error) {
    return db.Table("users").Select("name", "post", "user_id").ScanStruct(dataStruct)
})
```

## Dump, Dd

You may use the Dd or Dump methods while building a query to dump the query bindings and SQL.
The dd method will display the debug information and then stop executing the request.
The dump method will display the debug information but allow the request to keep executing:

```go
	// to print raw sql query to stdout 
	db.Table("table_name").Select("foo", "bar", "baz").Where("foo", "=", cmp).AndWhere("bar", "!=", "foo").Dump()

	// or to print to stdout and exit a.k.a dump and die
	db.Table("table_name").Select("foo", "bar", "baz").Where("foo", "=", cmp).AndWhere("bar", "!=", "foo").Dd()
```

## Check if table exists

```go
tblExists, err := db.HasTable("public", "posts")
```

## Check if columns exist in a table within schema

```go
colsExists, err := db.HasColumns("public", "posts", "title", "user_id")
```

## Retrieving A Single Row / Column From A Table

If you just need to retrieve a single row from the database table, you may use the `First` func.
This method will return a single `map[string]interface{}`:

```go
err = db.Table("posts").Select("title").OrderBy("created_at", "desc").First(dataStruct)

// usage ex: dataStruct.Title
```

If you don't even need an entire row, you may extract a single value from a record using the `Value` method.
This method will return the value of the column directly:

```go
err = db.Table("users").OrderBy("points", "desc").Value(dataStruct, "name")

// dataStruct.Name -> "Alex Shmidt"
```

To retrieve a single row by its id column value, use the `find` method:

```go
user, err := db.Table("users").Find(dataStruct, id)

// dataStruct.ID, dataStruct.Name, dataStruct.Email etc
```

## WhereExists / WhereNotExists

The whereExists method allows you to write where exists SQL clauses.
The whereExists method accepts a *DB argument,
which will receive a query builder instance allowing you to define the query that should be placed inside the "exists" clause:

```go
err = db.Table("users").Select("name").WhereExists(
    db.Table("users").Select("name").Where("points", ">=", int64(12345)),
).First(dataStruct)
```

Any query that is of need to build one can place inside `WhereExists` clause/func.

## WhereBetween / WhereNotBetween

The whereBetween func verifies that a column's value is between two values:

```go
err = db.Table(UsersTable).Select("name").WhereBetween("points", 1233, 12345).ScanStruct(&testStruct)
```

The whereNotBetween func verifies that a column's value lies outside of two values:

```go
err = db.Table(UsersTable).Select("name").WhereNotBetween("points", 123, 123456).ScanStruct(&testStruct)
```

## Determining If Records Exist

Instead of using the `Count` method to determine if any records exist that match your query's constraints,
you may use the exists and doesntExist methods:

```go
exists, err := db.Table(UsersTable).Select("name").Where("points", ">=", int64(12345)).Exists()
// use an inverse DoesntExists() if needed
```

## Aggregates

The query builder also provides a variety of aggregate methods such as Count, Max, Min, Avg, and Sum.
You may call any of these methods after constructing your query:

```go
cnt, err := db.Table(UsersTable).WHere("points", ">=", 1234).Count()

avg, err := db.Table(UsersTable).Avg("points")

mx, err := db.Table(UsersTable).Max("points")

mn, err := db.Table(UsersTable).Min("points")

sum, err := db.Table(UsersTable).Sum("points")
```

## Create table

To create a new database table, use the CreateTable method.
The Schema method accepts two arguments.
The first is the name of the table, while the second is an anonymous function/closure which receives a Table struct that
may be used to define the new table:

```go
res, err := db.Schema("big_tbl", func(table *Table) error {
    table.Increments("id")
    table.String("title", 128).Default("The quick brown fox jumped over the lazy dog").Unique("idx_ttl")
    table.SmallInt("cnt").Default(1)
    table.Integer("points").NotNull()
    table.BigInt("likes").Index("idx_likes")
    table.Text("comment").Comment("user comment").Collation("de_DE")
    table.DblPrecision("likes_to_points").Default(0.0)
    table.Char("tag", 10)
    table.DateTime("created_at", true)
    table.DateTimeTz("updated_at", true)
    table.Decimal("tax", 2, 2)
    table.TsVector("body")
    table.TsQuery("body_query")
    table.Jsonb("settings")
    table.Point("pt")
    table.Polygon("poly")
    table.TableComment("big table for big data")
    
    return nil
})

// to make a foreign key constraint from another table
_, err = db.Schema("tbl_to_ref", func (table *Table) error {
    table.Increments("id")
    table.Integer("big_tbl_id").ForeignKey("fk_idx_big_tbl_id", "big_tbl", "id").Concurrently().IfNotExists()
    // to add index on existing column just repeat stmt + index e.g.:
    table.Char("tag", 10).Index("idx_tag").Include("likes", "created_at")
    table.Rename("settings", "options")
    
    return nil
})    
```

## Add / Modify / Drop columns

The Table structure in the Schema's 2nd argument may be used to update existing tables. Just the way you've been created
it.
The Change method allows you to modify some existing column types to a new type or modify the column's attributes.

```go
res, err := db.Schema("tbl_name", func(table *Table) error {
    table.String("title", 128).Change()
    
    return nil
})
```

Use DropColumn method to remove any column:

```go
res, err := db.Schema("tbl_name", func(table *Table) error {
    table.DropColumn("deleted_at").IfExists()
    // To drop an index on the column    
    table.DropIndex("idx_title")
    
    return nil
})
```

## Chunking Results

If you need to work with thousands of database records, consider using the chunk method.
This method retrieves a small chunk of the results at a time and feeds each chunk into a closure for processing.

```go
var sumOfPoints int64
dataStruct := &DataStructUser{}
err = db.Table(UsersTable).Select("name", "points").Chunk(dataStruct, 100, func(users []any) bool {
    for _, v := range users {
        user := v.(DataStructUser) 
        // your code goes here e.g.:
        sumOfPoints += user.Points
    }
	
    // or you can return false here to stop running chunks 
    return true
})
```

## Pluck / PluckMap

If you would like to get values of a particular column(s) of a struct and place them into slice - use `Pluck` method:
```go
    dataStruct := &DataStructUser{}
    res, err := db.Table(UsersTable).Pluck(dataStruct)
    for k, v := range res {
        val := v.(DataStructUser)
        fmt.Println(val.Name) // f.e.: Alex Shmidt
    }
	
    // or use a PluckMap method to aggregate key/value pairs to a map
    res, err := db.Table(UsersTable).PluckMap(dataStruct, "name", "points")
    for k, m := range res {
        for key, value := range m {
            keyVal := key.(string)
            valueVal := value.(DataStructUser) 
            // rest of the code ...
        }
    }
```

PS Why use buildsqlx? Because it is simple and fast, yet versatile. The builder code-style has been inherited from greatest web-frameworks, so u can easily query anything from db. 

Supporters gratitude:

<img src="https://github.com/SoliDry/laravel-api/blob/master/tests/images/jetbrains-logo.png" alt="JetBrains logo" width="200" height="166" />

buildsqlx

Zero boilerplate database operations for Go

<p align="right">
  <a href="http://godoc.org/github.com/rocketlaunchr/dbq/v2"><img src="http://godoc.org/github.com/rocketlaunchr/dbq?status.svg" /></a>
  <a href="https://goreportcard.com/report/github.com/rocketlaunchr/dbq"><img src="https://goreportcard.com/badge/github.com/rocketlaunchr/dbq" /></a>
</p>

<p align="center">
<img src="https://github.com/rocketlaunchr/dbq/raw/master/logo.png" alt="dbq" />
</p>

(Now compatible with MySQL and PostgreSQL!)

Everyone knows that performing simple **DATABASE queries** in Go takes numerous lines of code that is often repetitive. If you want to avoid the cruft, you have two options: A heavy-duty ORM that is not up to the standard of Laravel or Django. Or DBQ!

⚠️ **WARNING: You will seriously reduce your database code to a few lines**

⭐ **the project to show your appreciation.**

## What is included

- Supports ANY type of query
- **MySQL** and **PostgreSQL** compatible
- **Convenient** and **Developer Friendly**
- Accepts any type of slice for query args
- Flattens query arg slices to individual values
- Bulk Insert seamlessly
- Automatically unmarshal query results directly to a struct using [mapstructure](https://github.com/mitchellh/mapstructure) package
- Lightweight
- Compatible with [mysql-go](https://github.com/rocketlaunchr/mysql-go) for proper MySQL query cancelation
- Automatically retry query with exponential backoff if operation fails
- Transaction management (automatic rollback)

## Dependencies

- [MySQL driver](https://github.com/go-sql-driver/mysql) OR
- [PostgreSQL driver](https://github.com/lib/pq)

**NOTE:** For mysql driver, [`parseTime=true`](https://github.com/go-sql-driver/mysql#parsetime) setting can interfere with unmarshaling to [`civil.*`](https://pkg.go.dev/cloud.google.com/go/civil?tab=doc) types.

## Installation

```
go get -u github.com/rocketlaunchr/dbq/v2
```

## Examples

Let's assume a table called `users`:

| id  | name  | age | created_at |
| --- | ----- | --- | ---------- |
| 1   | Sally | 12  | 2019-03-01 |
| 2   | Peter | 15  | 2019-02-01 |
| 3   | Tom   | 18  | 2019-01-01 |

### Query

[`Q`](https://godoc.org/github.com/rocketlaunchr/dbq/v2#Q) ordinarily returns `[]map[string]interface{}` results, but you can automatically
unmarshal to a struct. You will need to type assert the results.

```go

type user struct {
  ID        int       `dbq:"id"`
  Name      string    `dbq:"name"`
  Age       int       `dbq:"age"`
  CreatedAt time.Time `dbq:"created_at"`
}

opts := &dbq.Options{ConcreteStruct: user{}, DecoderConfig:x}

results, err := dbq.Q(ctx, db, "SELECT * FROM users", opts)
results, err := dbq.Qs(ctx, db, "SELECT * FROM users", user{}, nil)

```

Results:

```groovy
([]*main.user) (len=6 cap=8) {
 (*main.user)(0xc00009e1c0)({
  ID: (int) 1,
  Name: (string) (len=5) "Sally",
  Age: (int) 12,
  CreatedAt: (time.Time) 2019-03-01 00:00:00 +0000 UTC
 }),
 (*main.user)(0xc00009e300)({
  ID: (int) 2,
  Name: (string) (len=5) "Peter",
  Age: (int) 15,
  CreatedAt: (time.Time) 2019-02-01 00:00:00 +0000 UTC
 }),
 (*main.user)(0xc00009e440)({
  ID: (int) 3,
  Name: (string) (len=3) "Tom",
  Age: (int) 18,
  CreatedAt: (time.Time) 2019-01-01 00:00:00 +0000 UTC
 })
}
```

### Query Single Row

If you know that the query will return at maximum 1 row:

```go
result := dbq.MustQ(ctx, db, "SELECT * FROM users LIMIT 1", dbq.SingleResult)
if result == nil {
  // no result
} else {
  result.(map[string]interface{})
}

```

### Bulk Insert

You can insert multiple rows at once.

```go

db, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/db")

type Row struct {
  Name      string
  Age       int
  CreatedAt time.Time
}

users := []interface{}{
  dbq.Struct(Row{"Brad", 45, time.Now()}),
  dbq.Struct(Row{"Ange", 36, time.Now()}),
  dbq.Struct(Row{"Emily", 22, time.Now()}),
}

stmt := dbq.INSERTStmt("users", []string{"name", "age", "created_at"}, len(users))

dbq.E(ctx, db, stmt, nil, users)

```

### Flatten Query Args

All slices are flattened automatically.

```go
args1 := []string{"A", "B", "C"}
args2 := []interface{}{2, "D"}
args3 := dbq.Struct(Row{"Brad Pitt", 45, time.Now()})

results := dbq.MustQ(ctx, db, stmt, args1, args2, args3)

// Placeholder arguments will get flattened to:
results := dbq.MustQ(ctx, db, stmt, "A", "B", "C", 2, "D", "Brad Pitt", 45, time.Now())

```

**NOTE:** [FlattenArgs](https://godoc.org/github.com/rocketlaunchr/dbq/v2#FlattenArgs) function can be used more generally.

### MySQL cancelation

To properly cancel a MySQL query, you need to use the [mysql-go](https://github.com/rocketlaunchr/mysql-go) package. `dbq` plays nicely with it.

```go
import sql "github.com/rocketlaunchr/mysql-go"

pool, _ := sql.Open("user:password@tcp(localhost:3306)/db")

conn, err := pool.Conn(ctx)

opts := &dbq.Options{
  SingleResult: true,
  PostFetch: func(ctx context.Context) error {
    return conn.Close()
  },
}

result := dbq.MustQ(ctx, conn, "SELECT * FROM users LIMIT 1", opts)
if result == nil {
  // no result
} else {
  result.(map[string]interface{})
}
```

### PostUnmarshaler

After fetching the results, you can further modify the results by implementing the [`PostUnmarshaler`](https://godoc.org/github.com/rocketlaunchr/dbq/v2#PostUnmarshaler) interface. The `PostUnmarshal` function must be attached to the pointer of the struct.

```go
type user struct {
  ID        int       `dbq:"id"`
  Name      string    `dbq:"name"`
  Age       int       `dbq:"age"`
  CreatedAt time.Time `dbq:"created_at"`
  HashedID  string    `dbq:"-"`          // Obfuscate ID
}

func (u *user) PostUnmarshal(ctx context.Context, row, total int) error {
  u.HashedID = obfuscate(u.ID)
  return nil
}
```

### ScanFaster

The [`ScanFaster`](https://godoc.org/github.com/rocketlaunchr/dbq/v2#ScanFaster) interface eradicates the use of the reflect package when unmarshaling. If you don't need to perform fancy time conversions or interpret weakly typed data, then it is more performant.

```go
type user struct {
  ID       int    `dbq:"id"`
  Name     string `dbq:"name"`
}

func (u *user) ScanFast() []interface{} {
  return []interface{}{&u.ID, &u.Name}
}
```

### Retry with Exponential Backoff

If the database operation fails, you can automatically retry with exponentially increasing intervals between each retry attempt. You can also set the maximum number of retries.

```go
opts := &dbq.Options{
  RetryPolicy:  dbq.ExponentialRetryPolicy(60 * time.Second, 3),
}
```

### Transaction Management

You can conveniently perform numerous complex database operations within a transaction without having to worry about rolling back. Unless you explicitly commit, it will automatically rollback.

You have access to the `Q` and `E` function as well as the underlying `tx` for performance purposes.

```go
ctx := context.Background()
pool, _ := sql.Open("mysql", "user:password@tcp(localhost:3306)/db")

dbq.Tx(ctx, pool, func(tx interface{}, Q dbq.QFn, E dbq.EFn, txCommit dbq.TxCommit) {
  
  stmt := dbq.INSERTStmt("table", []string{"name", "age", "created_at"}, 1)
  res, err := E(ctx, stmt, nil, "test name", 34, time.Now())
  if err != nil {
    return // Automatic rollback
  }
  txCommit() // Commit
})
```

## Custom Queries

The `v2/x` subpackage will house functions to perform custom SQL queries. If they are general to both MySQL and PostgreSQL, they are inside the `x` subpackage. If they are specific to MySQL xor PostgreSQL, they are in the `x/mysql` xor `x/pg` subpackage respectively.


### This is your package too!

If you want your own custom functions included, just submit a PR and place it in your **own directory** inside `v2/x`. As long as it compiles and is well documented it is welcome.

### Bulk Update

As a warmup, I have included a [Bulk Update](https://godoc.org/github.com/rocketlaunchr/dbq/v2/x#BulkUpdate) function that works with MySQL and PostgreSQL. It allows you to update thousands of rows in 1 query without a transaction!

## Difference between v1 and v2

When a `ConcreteStruct` is provided, in `v1`, the `Q` and `MustQ` functions return `[]interface{}` while in `v2` they return `[]*struct`.

**NOTE:** `v1` is obsolete and will no longer receive updates.

## Other useful packages

- [dataframe-go](https://github.com/rocketlaunchr/dataframe-go) - Statistics and data manipulation
- [electron-alert](https://github.com/rocketlaunchr/electron-alert) - SweetAlert2 for Electron Applications
- [igo](https://github.com/rocketlaunchr/igo) - A Go transpiler with cool new syntax such as fordefer (defer for for-loops)
- [mysql-go](https://github.com/rocketlaunchr/mysql-go) - Properly cancel slow MySQL queries
- [react](https://github.com/rocketlaunchr/react) - Build front end applications using Go
- [remember-go](https://github.com/rocketlaunchr/remember-go) - Cache slow database queries

#

### Legal Information

The license is a modified MIT license. Refer to the `LICENSE` file for more details.

**© 2019-20 PJ Engineering and Business Solutions Pty. Ltd.**

### Final Notes

Feel free to enhance features by issuing pull-requests. Note that the project is written in [igo](https://github.com/rocketlaunchr/igo) and transpiled into Go.


Go library that helps you keep sql files in one place and use them with ease

dotsql 
[![GoDoc](https://godoc.org/github.com/qustavo/dotsql?status.svg)](https://godoc.org/github.com/qustavo/dotsql)
[![Build Status](https://travis-ci.org/qustavo/dotsql.svg)](https://travis-ci.org/qustavo/dotsql)
[![Test coverage](https://gocover.io/_badge/github.com/qustavo/dotsql)](https://gocover.io/github.com/qustavo/dotsql)
[![Go Report Card](https://goreportcard.com/badge/github.com/qustavo/dotsql)](https://goreportcard.com/report/github.com/qustavo/dotsql)
======

A Golang library for using SQL.

It is not an ORM, it is not a query builder. Dotsql is a library that helps you
keep sql files in one place and use it with ease.

_Dotsql is heavily inspired by_ [yesql](https://github.com/krisajenkins/yesql).

Installation
--
```bash
$ go get github.com/qustavo/dotsql
```

Usage 
--

First of all, you need to define queries inside your sql file:

```sql
-- name: create-users-table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name VARCHAR(255),
    email VARCHAR(255)
);

-- name: create-user
INSERT INTO users (name, email) VALUES(?, ?)

-- name: find-users-by-email
SELECT id,name,email FROM users WHERE email = ?

-- name: find-one-user-by-email
SELECT id,name,email FROM users WHERE email = ? LIMIT 1

--name: drop-users-table
DROP TABLE users
```

Notice that every query has a name tag (`--name:<some name>`),
this is needed to be able to uniquely identify each query
inside dotsql.

With your sql file prepared, you can load it up and start utilizing your queries:

```go
// Get a database handle
db, err := sql.Open("sqlite3", ":memory:")

// Loads queries from file
dot, err := dotsql.LoadFromFile("queries.sql")

// Run queries
res, err := dot.Exec(db, "create-users-table")
res, err := dot.Exec(db, "create-user", "User Name", "main@example.com")
rows, err := dot.Query(db, "find-users-by-email", "main@example.com")
row, err := dot.QueryRow(db, "find-one-user-by-email", "user@example.com")

stmt, err := dot.Prepare(db, "drop-users-table")
result, err := stmt.Exec()
```

You can also merge multiple dotsql instances created from different sql file inputs:
```go
dot1, err := dotsql.LoadFromFile("queries1.sql")
dot2, err := dotsql.LoadFromFile("queries2.sql")
dot := dotsql.Merge(dot1, dot2)
```

Text Interpolation
--
[text/template](https://pkg.go.dev/text/template)-style text interpolation is supported.

To use, call `.WithData(any)` on your dotsql instance to
create a new instance which passes those values into the templating library.

```sql
-- name: count-users
SELECT count(*) FROM users {{if .exclude_deleted}}WHERE deleted IS NULL{{end}}
```

```go
dotsql.WithData(map[string]any{"exclude_deleted": true}).Query(db, "count-users")
```

Embeding
--
To avoid distributing `sql` files alongside the binary file, you will need to use tools like 
[gotic](https://github.com/qustavo/gotic) to embed / pack everything into one file.

SQLX
--
For [sqlx](https://github.com/jmoiron/sqlx) support check [dotsqlx](https://github.com/swithek/dotsqlx)


Dotsql

Non-invasive SQL builder and powerful data binder

## Gendry
[![Build Status](https://github.com/didi/gendry/workflows/Go/badge.svg)](https://github.com/didi/gendry/actions)
[![Gitter](https://badges.gitter.im/Join%20Chat.svg)](https://gitter.im/didi-gendry/Lobby)
[![Hex.pm](https://img.shields.io/hexpm/l/plug.svg)](https://github.com/didi/gendry/blob/master/LICENSE)
[![GoDoc](https://godoc.org/github.com/didi/gendry?status.svg)](https://godoc.org/github.com/didi/gendry)

**gendry** is a Go library that helps you operate database. Based on `go-sql-driver/mysql`, it provides a series of simple but useful tools to prepare parameters for calling methods in standard library `database/sql`.

The name **gendry** comes from the role in the hottest drama: `The Game of Throne`, in which Gendry is not only the bastardy of the late king Robert Baratheon but also a skilled blacksmith. Like the one in drama, this library also forges something called `SQL`.

**gendry** consists of three isolated parts, and you can use each one of them partially:

* [manager](#manager)
* [builder](#builder)
* [scanner](#scanner)
* [CLI tool](#tools)

### Translation
* [Chinese Simplified (中文)](translation/zhcn/README.md)



<h3 id="manager">Manager</h3>

The manager is used for initializing the database connection pool(i.e., `sql.DB`).
You can set almost all parameters for those MySQL drivers supported. For example, initializing a database connection pool:

``` go
var db *sql.DB
var err error
db, err = manager
		.New(dbName, user, password, host)
		.Set(
			manager.SetCharset("utf8"),
			manager.SetAllowCleartextPasswords(true),
			manager.SetInterpolateParams(true),
			manager.SetTimeout(1 * time.Second),
			manager.SetReadTimeout(1 * time.Second)
		).Port(3302).Open(true)
```
In fact, all things manager does is just to generate the `dataSourceName`

the format of a `dataSourceName` is：

```
[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]
```

Manager is based on `go-mysql-driver/mysql`. If you don't know some of the manager and SetXXX series functions, see it on [mysql driver's github home page](https://github.com/go-sql-driver/mysql). For more details see [manager's doc](manager/README.md)

<h3 id="builder">Builder</h3>
Builder as its name says is for building SQL. Writing SQL manually is intuitive but somewhat difficult to maintain. And for `where in,` if you have a huge amount of elements in the `in` set, it's very hard to write.

Builder isn't an ORM. In fact, one of the most important reasons we create Gendry is we don't like ORM. So Gendry just provides some simple APIs to help you build SQLs:

```go
where := map[string]interface{}{
	"city": []string{"beijing", "shanghai"},
	// The in operator can be omitted by default,
	// which is equivalent to:
	// "city in": []string{"beijing", "shanghai"},
	"score": 5,
	"age >": 35,
	"address": builder.IsNotNull,
	"_or": []map[string]interface{}{
		{
			"x1":    11,
			"x2 >=": 45,
		},
		{
			"x3":    "234",
			"x4 <>": "tx2",
		},
	},
	"_orderby": "bonus desc",
	"_groupby": "department",
}
table := "some_table"
selectFields := []string{"name", "age", "sex"}
cond, values, err := builder.BuildSelect(table, where, selectFields)

//cond = SELECT name,age,sex FROM some_table WHERE (((x1=? AND x2>=?) OR (x3=? AND x4!=?)) AND score=? AND city IN (?,?) AND age>? AND address IS NOT NULL) GROUP BY department ORDER BY bonus DESC
//values = []interface{}{11, 45, "234", "tx2", 5, "beijing", "shanghai", 35}

rows, err := db.Query(cond, values...)


// support builder.Raw in where & update
where := map[string]interface{}{"gmt_create <": builder.Raw("gmt_modified")}
cond, values, err := builder.BuildSelect(table, where, selectFields)
// SELECT * FROM x WHERE gmt_create < gmt_modified

update = map[string]interface{}{
    "code": builder.Raw("VALUES(code)"), // mysql 8.x  builder.Raw("new.code")
    "name": builder.Raw("VALUES(name)"), // mysql 8.x  builder.Raw("new.name")
}
cond, values, err := builder.BuildInsertOnDuplicate(table, data, update)
// INSERT INTO country (id, code, name) VALUES (?,?,?),(?,?,?),(?,?,?) 
// ON DUPLICATE KEY UPDATE code=VALUES(code),name=VALUES(name)
```

In the `where` param, `in` operator is automatically added by value type(reflect.Slice).

```go
where := map[string]interface{}{
	"city": []string{"beijing", "shanghai"},
}
```
the same as
```go
where := map[string]interface{}{
	"city in": []string{"beijing", "shanghai"},
}
```

Besides, the library provide a useful API for executing aggregate queries like count, sum, max, min, avg

```go
where := map[string]interface{}{
    "score > ": 100,
    "city": []interface{}{"Beijing", "Shijiazhuang", }
}
// AggregateSum, AggregateMax, AggregateMin, AggregateCount, AggregateAvg are supported
result, err := AggregateQuery(ctx, db, "tableName", where, AggregateSum("age"))
sumAge := result.Int64()
result, err = AggregateQuery(ctx, db, "tableName", where, AggregateCount("*")) 
numberOfRecords := result.Int64()
result, err = AggregateQuery(ctx, db, "tableName", where, AggregateAvg("score"))
averageScore := result.Float64()
```

multi `or` condition can use multi `_or` prefix string mark 
``` go
where := map[string]interface{}{
    // location
    "_or_location": []map[string]interface{}{{
        "subway": "beijing_15", 
    }, {
        "district": "Chaoyang", 
    }},
    // functions
    "_or_functions": []map[string]interface{}{{
         "has_gas": true,
    }, {
        "has_lift": true,
}}}

// query = (((subway=?) OR (district=?)) AND ((has_gas=?) OR (has_lift=?)))
// args = ["beijing_15", "Chaoyang", true, true]
```

If you want to clear the value '0' in the where map, you can use builder.OmitEmpty

``` go
where := map[string]interface{}{
		"score": 0,
		"age >": 35,
	}
finalWhere := builder.OmitEmpty(where, []string{"score", "age"})
// finalWhere = map[string]interface{}{"age >": 35}

// support: Bool, Array, String, Float32, Float64, Int, Int8, Int16, Int32, Int64, Uint, Uint8, Uint16, Uint32, Uint64, Uintptr, Map, Slice, Interface, Struct
```

For complex queries, `NamedQuery` may be helpful:
```go
cond, vals, err := builder.NamedQuery("select * from tb where name={{name}} and id in (select uid from anothertable where score in {{m_score}})", map[string]interface{}{
	"name": "caibirdme",
	"m_score": []float64{3.0, 5.8, 7.9},
})

assert.Equal("select * from tb where name=? and id in (select uid from anothertable where score in (?,?,?))", cond)
assert.Equal([]interface{}{"caibirdme", 3.0, 5.8, 7.9}, vals)
```
slice type can be expanded automatically according to its length. Thus, these SQLs are very convenient for DBA to review.  
**For critical system, this is recommended**

For more detail, see [builder's doc](builder/README.md) or just use `godoc`

<h3 id="scanner">Scanner</h3>
For each response from MySQL, you want to map it with your well-defined structure.
Scanner provides a straightforward API to do this, it's based on reflection:

##### standard library
```go
type Person struct {
	Name string
	Age int
}

rows, err := db.Query("SELECT age as m_age, name from g_xxx where xxx")
defer rows.Close()

var students []Person

for rows.Next() {
	var student Person
	rows.Scan(student.Age, student.Name)
	students = append(students, student)
}
```
##### using scanner
```go
type Person struct {
	Name string `ddb:"name"`
	Age int `ddb:"m_age"`
}

rows, err := db.Query("SELECT age as m_age, name from g_xxx where xxx")
defer rows.Close()

var students []Person

scanner.Scan(rows, &students)
```
Types that implement the interface
```go
type ByteUnmarshaler interface {
	UnmarshalByte(data []byte) error
}
```
will take over the corresponding unmarshal work.

```go
type human struct {
	Age   int       `ddb:"ag"`
	Extra *extraInfo `ddb:"ext"`
}

type extraInfo struct {
	Hobbies     []string `json:"hobbies"`
	LuckyNumber int      `json:"ln"`
}

func (ext *extraInfo) UnmarshalByte(data []byte) error {
	return json.Unmarshal(data, ext)
}

//if the type of ext column in a table is varchar(stored legal json string) or json(mysql5.7)
var student human
err := scanner.Scan(rows, &student)
// ...
```

The extra tag of the struct will be used by scanner resolve data from response.The default tag name is `ddb:"tagname"`, but you can specify your own such as:

``` go
scanner.SetTagName("json")
type Person struct {
	Name string `json:"name"`
	Age int `json:"m_age"`
}

// ...
var student Person
scanner.Scan(rows, &student)
```

**scanner.SetTagName is a global setting and it can be invoked only once**

#### ScanMap
```go
rows, _ := db.Query("select name, age as m_age from person")
result, err := scanner.ScanMap(rows)
for _, record := range result {
	fmt.Println(record["name"], record["m_age"])
}
```
ScanMap scans data from rows and returns a `[]map[string]interface{}`  
int, float, string type may be stored as []uint8 by MySQL driver. ScanMap copies those values into the map. If you're sure that there's no binary data type in your MySQL table(in most cases, this is true), you can use ScanMapDecode instead which will convert []uint8 to int, float64 or string

For more detail, see [scanner's doc](scanner/README.md)

PS：

* Don't forget close rows if you don't use ScanXXXClose
* The second parameter of Scan must be a reference

<h3 id="tools">Tools</h3>
Besides APIs above, Gendry provides a [CLI tool](https://github.com/caibirdme/gforge) to help generating codes.







gendry

# sqlf

[![GoDoc Reference](https://godoc.org/github.com/leporo/sqlf?status.svg)](http://godoc.org/github.com/leporo/sqlf)
![Build Status](https://github.com/leporo/sqlf/actions/workflows/go.yml/badge.svg)
[![Go Report Card](https://goreportcard.com/badge/github.com/leporo/sqlf)](https://goreportcard.com/report/github.com/leporo/sqlf)
[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge-flat.svg)](https://github.com/avelino/awesome-go#sql-query-builders)


A fast SQL query builder for Go.

What `sqlf` does?

- It helps you efficiently build an SQL statement in run-time.
- You may change the number of affected columns and change the number of arguments in a safe way.
- You may use SQL expressions (like `UPDATE counters SET counter = counter + 1`) in your SQL statements.
- You may dynamically apply filters by adding where conditions, change result ordering, etc.
- You may safely use `?` placeholders in your SQL fragments - `sqlf` converts them to PostgreSQL-like `$1, $2, ...` placeholders if needed and does the numbering for you.
- You may `.Bind` your structure to database columns like you do with other similar libraries.
- `sqlf.Stmt` has methods to execute a query using any `database/sql` compatible driver.

What `sqlf` doesn't?

- `sqlf` isn't an ORM, you'll still have to use raw SQL.
- There are no database schema migrations or any other database schema maintenance tools.
- There are no compile-time type checks for query arguments, column and table names.
- There is no wrapper for `OR` clause. It affects performance and in most cases can be avoided by using `UNION` expressions, `WITH` clause or window functions. Other option is to split a query into two.
- `sqlf` doesn't help a developer to pinpoint the cause of issue with SQL statement.

## Is It Fast?

It is. See benchmarks: https://github.com/leporo/golang-sql-builder-benchmark

In order to maximize performance and minimize memory footprint, `sqlf` reuses memory allocated for query building. The heavier load is, the faster `sqlf` works.

## Usage

Build complex statements:

```go
var (
    region       string
    product      string
    productUnits int
    productSales float64
)

sqlf.SetDialect(sqlf.PostgreSQL)

err := sqlf.From("orders").
    With("regional_sales",
        sqlf.From("orders").
            Select("region, SUM(amount) AS total_sales").
            GroupBy("region")).
    With("top_regions",
        sqlf.From("regional_sales").
            Select("region").
            Where("total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)")).
    // Map query fields to variables
    Select("region").To(&region).
    Select("product").To(&product).
    Select("SUM(quantity)").To(&productUnits).
    Select("SUM(amount) AS product_sales").To(&productSales).
    //
    Where("region IN (SELECT region FROM top_regions)").
    GroupBy("region, product").
    OrderBy("product_sales DESC").
    // Execute the query
    QueryAndClose(ctx, db, func(row *sql.Rows){
        // Callback function is called for every returned row.
        // Row values are scanned automatically to bound variables.
        fmt.Printf("%s\t%s\t%d\t$%.2f\n", region, product, productUnits, productSales)
    })
if err != nil {
    panic(err)
}
```

Bind a structure:

```go
type Offer struct {
    Id        int64   `db:"id"`
    ProductId int64   `db:"product_id"`
    Price     float64 `db:"price"`
    IsDeleted bool    `db:"is_deleted"`
}

var o Offer

err := sqlf.From("offers").
    Bind(&o).
    Where("id = ?", 42).
    QueryRowAndClose(ctx, db)
if err != nil {
    panic(err)
}
```

Retrieve data to private fields with more granular control on retrieved fields:

```go
type Offer struct {
    id        int64
    productId int64
    price     float64
    isDeleted bool
}

var o Offer

err := sqlf.From("offers").
    Select("id").To(&o.id).
    Select("product_id").To(&o.productId).
    Select("price").To(&o.price).
    Select("is_deleted").To(&o.isDeleted).
    Where("id = ?", 42).
    QueryRowAndClose(ctx, db)
if err != nil {
    panic(err)
}
```

Some SQL fragments, like a list of fields to be selected or filtering condition may appear over and over. It can be annoying to repeat them or combine an SQL statement from chunks. Use `sqlf.Stmt` to construct a basic query and extend it for a case:

```go
func (o *Offer) Select() *sqlf.Stmt {
    return sqlf.From("products").
        .Bind(o)
        // Ignore records, marked as deleted
        Where("is_deleted = false")
}

func (o Offer) Print() {
    fmt.Printf("%d\t%s\t$%.2f\n", o.id, o.name, o.price)
}

var o Offer

// Fetch offer data
err := o.Select().
    Where("id = ?", offerId).
    QueryRowAndClose(ctx, db)
if err != nil {
    panic(err)
}
o.Print()
// ...

// Select and print 5 most recently placed
// offers for a given product
err = o.Select().
    Where("product_id = ?", productId).
    OrderBy("id DESC").
    Limit(5).
    QueryAndClose(ctx, db, func(row *sql.Rows){
        o.Print()
    })
if err != nil {
    panic(err)
}
// ...

```

## SQL Statement Construction and Execution

### SELECT

#### Value Binding

Bind columns to values using `To` method:

```go
var (
    minAmountRequested = true
    maxAmount float64
    minAmount float64
)

q := sqlf.From("offers").
    Select("MAX(amount)").To(&maxAmount).
    Where("is_deleted = false")

if minAmountRequested {
    q.Select("MIN(amount)").To(&minAmount)
}

err := q.QueryRowAndClose(ctx, db)
if err != nil {
    panic(err)
}
if minAmountRequested {
    fmt.Printf("Cheapest offer: $%.2f\n", minAmount)
}
fmt.Printf("Most expensive offer: $%.2f\n", minAmount)
```

#### Joins

There are helper methods to construct a JOIN clause: `Join`, `LeftJoin`, `RightJoin` and `FullJoin`.

```go
var (
    offerId     int64
    productName string
    price       float64
}

err := sqlf.From("offers o").
    Select("o.id").To(&offerId).
    Select("price").To(&price).
    Where("is_deleted = false").
    // Join
    LeftJoin("products p", "p.id = o.product_id").
    // Bind a column from joined table to variable
    Select("p.name").To(&productName).
    // Print top 10 offers
    OrderBy("price DEST").
    Limit(10).
    QueryAndClose(ctx, db, func(row *sql.Rows){
        fmt.Printf("%d\t%s\t$%.2f\n", offerId, productName, price)
    })
if err != nil {
    panic(err)
}
```

Use plain SQL for more fancy cases:

```go
var (
    num   int64
    name  string
    value string
)
err := sqlf.From("t1 CROSS JOIN t2 ON t1.num = t2.num AND t2.value IN (?, ?)", "xxx", "yyy").
    Select("t1.num").To(&num).
    Select("t1.name").To(&name).
    Select("t2.value").To(&value).
    QueryAndClose(ctx, db, func(row *sql.Rows){
        fmt.Printf("%d\t%s\ts\n", num, name, value)
    })
if err != nil {
    panic(err)
}
```

#### Subqueries

Use `SubQuery` method to add a sub query to a statement:

```go
	q := sqlf.From("orders o").
		Select("date, region").
		SubQuery("(", ") AS prev_order_date",
			sqlf.From("orders po").
				Select("date").
				Where("region = o.region").
				Where("id < o.id").
				OrderBy("id DESC").
				Clause("LIMIT 1")).
		Where("date > CURRENT_DATE - interval '1 day'").
		OrderBy("id DESC")
	fmt.Println(q.String())
	q.Close()
```

Note that if a subquery uses no arguments, it's more effective to add it as SQL fragment:

```go
	q := sqlf.From("orders o").
		Select("date, region").
		Where("date > CURRENT_DATE - interval '1 day'").
        Where("exists (SELECT 1 FROM orders po WHERE region = o.region AND id < o.id ORDER BY id DESC LIMIT 1)").
        OrderBy("id DESC")
    // ...
    q.Close()
```

To select from sub-query pass an empty string to From and immediately call a SubQuery method.

The query constructed by the following example returns top 5 news in each section:

```go
	q := sqlf.Select("").
		From("").
		SubQuery(
			"(", ") counted_news",
			sqlf.From("news").
				Select("id, section, header, score").
				Select("row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section").
				OrderBy("section, rating_in_section")).
		Where("rating_in_section <= 5")
    // ...
    q.Close()
```

#### Unions

Use `Union` method to combine results of two queries:

```go
	q := sqlf.From("tasks").
		Select("id, status").
		Where("status = ?", "new").
		Union(true, sqlf.PostgreSQL.From("tasks").
			Select("id, status").
            Where("status = ?", "wip"))
    // ...
	q.Close()
```

### INSERT

`sqlf` provides a `Set` method to be used both for UPDATE and INSERT statements:

```go
var userId int64

_, err := sqlf.InsertInto("users").
    Set("email", "new@email.com").
    Set("address", "320 Some Avenue, Somewhereville, GA, US").
    Returning("id").To(&userId).
    Clause("ON CONFLICT (email) DO UPDATE SET address = users.address").
    QueryRowAndClose(ctx, db)
```

The same statement execution using the `database/sql` standard library looks like this:

```go
var userId int64

// database/sql
err := db.ExecContext(ctx, "INSERT INTO users (email, address) VALUES ($1, $2) RETURNING id ON CONFLICT (email) DO UPDATE SET address = users.address", "new@email.com", "320 Some Avenue, Somewhereville, GA, US").Scan(&userId)
```

There are just 2 fields of a new database record to be populated, and yet it takes some time to figure out what columns are being updated and what values are to be assigned to them.

In real-world cases there are tens of fields. On any update both the list of field names and the list of values, passed to `ExecContext` method, have to to be reviewed and updated. It's a common thing to have values misplaced.

The use of `Set` method to maintain a field-value map is a way to solve this issue.

#### Bulk Insert

To insert a multiple rows via a single query, use `NewRow` method:

```
_, err := sqlf.InsertInto("users").
    NewRow().
        Set("email", "first@email.com").
        Set("address", "320 Some Avenue, Somewhereville, GA, US").
    NewRow().
        Set("email", "second@email.com").
        Set("address", "320 Some Avenue, Somewhereville, GA, US").
    ExecAndClose(ctx, db)
```

### UPDATE

```go
_, err := sqlf.Update("users").
    Set("email", "new@email.com").
    ExecAndClose(ctx, db)
```

### DELETE

```go
_, err := sqlf.DeleteFrom("products").
    Where("id = ?", 42)
    ExecAndClose(ctx, db)
```


sqlf

```
  __ _  ___   __ _ _   _
 / _` |/ _ \ / _` | | | |
| (_| | (_) | (_| | |_| |
 \__, |\___/ \__, |\__,_|
 |___/          |_|
```
[![GitHub tag](https://img.shields.io/github/tag/doug-martin/goqu.svg?style=flat)](https://github.com/doug-martin/goqu/releases)
[![Test](https://github.com/doug-martin/goqu/workflows/Test/badge.svg?branch=master&event=push)](https://github.com/doug-martin/goqu/actions?query=workflow%3ATest+and+branch%3Amaster+)
[![Go Reference](https://pkg.go.dev/badge/github.com/doug-martin/goqu/v9.svg)](https://pkg.go.dev/github.com/doug-martin/goqu/v9)
[![codecov](https://codecov.io/gh/doug-martin/goqu/branch/master/graph/badge.svg)](https://codecov.io/gh/doug-martin/goqu)
[![Go Report Card](https://goreportcard.com/badge/github.com/doug-martin/goqu/v9)](https://goreportcard.com/report/github.com/doug-martin/goqu/v9)

`goqu` is an expressive SQL builder and executor
    
If you are upgrading from an older version please read the [Migrating Between Versions](./docs/version_migration.md) docs.


## Installation

If using go modules.

```sh
go get -u github.com/doug-martin/goqu/v9
```

If you are not using go modules...

**NOTE** You should still be able to use this package if you are using go version `>v1.10` but, you will need to drop the version from the package. `import "github.com/doug-martin/goqu/v9` -> `import "github.com/doug-martin/goqu"`

```sh
go get -u github.com/doug-martin/goqu
```

### [Migrating Between Versions](./docs/version_migration.md)

## Features

`goqu` comes with many features but here are a few of the more notable ones

* Query Builder
* Parameter interpolation (e.g `SELECT * FROM "items" WHERE "id" = ?` -> `SELECT * FROM "items" WHERE "id" = 1`)
* Built from the ground up with multiple dialects in mind
* Insert, Multi Insert, Update, and Delete support
* Scanning of rows to struct[s] or primitive value[s]

While goqu may support the scanning of rows into structs it is not intended to be used as an ORM if you are looking for common ORM features like associations,
or hooks I would recommend looking at some of the great ORM libraries such as:

* [gorm](https://github.com/jinzhu/gorm)
* [hood](https://github.com/eaigner/hood)

## Why?

We tried a few other sql builders but each was a thin wrapper around sql fragments that we found error prone. `goqu` was built with the following goals in mind:

* Make the generation of SQL easy and enjoyable
* Create an expressive DSL that would find common errors with SQL at compile time.
* Provide a DSL that accounts for the common SQL expressions, NOT every nuance for each database.
* Provide developers the ability to:
  * Use SQL when desired
  * Easily scan results into primitive values and structs
  * Use the native sql.Db methods when desired

## Docs

* [Dialect](./docs/dialect.md) - Introduction to different dialects (`mysql`, `postgres`, `sqlite3`, `sqlserver` etc) 
* [Expressions](./docs/expressions.md) - Introduction to `goqu` expressions and common examples.
* [Select Dataset](./docs/selecting.md) - Docs and examples about creating and executing SELECT sql statements.
* [Insert Dataset](./docs/inserting.md) - Docs and examples about creating and executing INSERT sql statements.
* [Update Dataset](./docs/updating.md) - Docs and examples about creating and executing UPDATE sql statements.
* [Delete Dataset](./docs/deleting.md) - Docs and examples about creating and executing DELETE sql statements.
* [Prepared Statements](./docs/interpolation.md) - Docs about interpolation and prepared statements in `goqu`.
* [Database](./docs/database.md) - Docs and examples of using a Database to execute queries in `goqu`
* [Working with time.Time](./docs/time.md) - Docs on how to use alternate time locations.

## Quick Examples

### Select

See the [select dataset](./docs/selecting.md) docs for more in depth examples

```go
sql, _, _ := goqu.From("test").ToSQL()
fmt.Println(sql)
```

Output:

```
SELECT * FROM "test"
```

```go
sql, _, _ := goqu.From("test").Where(goqu.Ex{
	"d": []string{"a", "b", "c"},
}).ToSQL()
fmt.Println(sql)
```

Output:

```
SELECT * FROM "test" WHERE ("d" IN ('a', 'b', 'c'))
```

### Insert

See the [insert dataset](./docs/inserting.md) docs for more in depth examples

```go
ds := goqu.Insert("user").
	Cols("first_name", "last_name").
	Vals(
		goqu.Vals{"Greg", "Farley"},
		goqu.Vals{"Jimmy", "Stewart"},
		goqu.Vals{"Jeff", "Jeffers"},
	)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)
```

Output: 
```sql
INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
```

```go
ds := goqu.Insert("user").Rows(
	goqu.Record{"first_name": "Greg", "last_name": "Farley"},
	goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"},
	goqu.Record{"first_name": "Jeff", "last_name": "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)
```

Output:
```
INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
```


```go
type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
ds := goqu.Insert("user").Rows(
	User{FirstName: "Greg", LastName: "Farley"},
	User{FirstName: "Jimmy", LastName: "Stewart"},
	User{FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)
```

Output:
```
INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []
```

```go
ds := goqu.Insert("user").Prepared(true).
	FromQuery(goqu.From("other_table"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)
```

Output:
```
INSERT INTO "user" SELECT * FROM "other_table" []
```

```go
ds := goqu.Insert("user").Prepared(true).
	Cols("first_name", "last_name").
	FromQuery(goqu.From("other_table").Select("fn", "ln"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)
```

Output:
```
INSERT INTO "user" ("first_name", "last_name") SELECT "fn", "ln" FROM "other_table" []
```

### Update

See the [update dataset](./docs/updating.md) docs for more in depth examples

```go
sql, args, _ := goqu.Update("items").Set(
	goqu.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)
```

Output:
```
UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
```

```go
type item struct {
	Address string `db:"address"`
	Name    string `db:"name" goqu:"skipupdate"`
}
sql, args, _ := goqu.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)
```

Output:
```
UPDATE "items" SET "address"='111 Test Addr' []
```

```go
sql, _, _ := goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Where(goqu.Ex{
		"a": goqu.Op{"gt": 10}
	}).ToSQL()
fmt.Println(sql)
```

Output:
```
UPDATE "test" SET "foo"='bar' WHERE ("a" > 10)
```

### Delete

See the [delete dataset](./docs/deleting.md) docs for more in depth examples

```go
ds := goqu.Delete("items")

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)
```

```go
sql, _, _ := goqu.Delete("test").Where(goqu.Ex{
		"c": nil
	}).ToSQL()
fmt.Println(sql)
```

Output:
```
DELETE FROM "test" WHERE ("c" IS NULL)
```

<a name="contributions"></a>
## Contributions

I am always welcoming contributions of any type. Please open an issue or create a PR if you find an issue with any of the following.

* An issue with Documentation
* You found the documentation lacking in some way

If you have an issue with the package please include the following

* The dialect you are using
* A description of the problem
* A short example of how to reproduce (if applicable)

Without those basics it can be difficult to reproduce your issue locally. You may be asked for more information but that is a good starting point.

### New Features

New features and/or enhancements are great and I encourage you to either submit a PR or create an issue. In both cases include the following as the need/requirement may not be readily apparent.

1. The use case
2. A short example

If you are issuing a PR also include the following

1. Tests - otherwise the PR will not be merged
2. Documentation - otherwise the PR will not be merged
3. Examples - [If applicable] see example_test.go for examples

If you find an issue you want to work on please comment on it letting other people know you are looking at it and I will assign the issue to you.

If want to work on an issue but dont know where to start just leave a comment and I'll be more than happy to point you in the right direction.

### Running tests
The test suite requires a postgres, mysql and sqlserver databases. You can override the connection strings with the [`MYSQL_URI`, `PG_URI`, `SQLSERVER_URI` environment variables](https://github.com/doug-martin/goqu/blob/2fe3349/docker-compose.yml#L26)*

```sh
go test -v -race ./...
```

You can also run the tests in a container using [docker-compose](https://docs.docker.com/compose/).

```sh
MYSQL_VERSION=8 POSTGRES_VERSION=13.4 SQLSERVER_VERSION=2017-CU8-ubuntu GO_VERSION=latest docker-compose run goqu
```

## License

`goqu` is released under the [MIT License](http://www.opensource.org/licenses/MIT).







goqu

Secure your handcrafted SQL against injection

# Hotcoal

[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go)
[![Go Reference](https://pkg.go.dev/badge/github.com/motrboat/hotcoal.svg)](https://pkg.go.dev/github.com/motrboat/hotcoal)
[![Go Report Card](https://goreportcard.com/badge/github.com/motrboat/hotcoal)](https://goreportcard.com/report/github.com/motrboat/hotcoal)
![Coverage](https://img.shields.io/badge/Coverage-97.3%25-brightgreen)

## Secure your handcrafted SQL against injection

Many Golang apps do not use an ORM, and instead prefer to use raw SQL for their database operations.

Even though writing raw SQL can be sometimes more flexible, efficient and clean than using an ORM, it can make you vulnerable to SQL injection.

While most database technologies (PostreSQL, MySQL, Sqlite etc.) and Golang SQL libraries (`database/sql`, `sqlx` etc.) allow you to use prepared statements, which take parameters and sanitize them, there are some limitations:
 * you can use parameters for values, but not for column names or table names
 * for more complex stuff, a good ORM can generate SQL dynamically for you, and do it safely; but you aren't using an ORM, so you start handcrafting SQL ...

This is where Hotcoal comes in.

## How it works

Hotcoal provides a **minimal** API which helps you guard your handcrafted SQL against SQL injection.

1. Handcraft your SQL using Hotcoal instead of plain strings. This way all your parameters are validated against the allowlists you provide.

2. Convert the result to a plain string and use it with your SQL library.

You can use Hotcoal with any SQL library you like.

Hotcoal does not replace prepared queries with parameters, but complements it.

## Examples

We use this example table:

```sql
CREATE TABLE "users" (
	"id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"first_name"	TEXT NOT NULL,
	"middle_name"	TEXT NOT NULL,
	"last_name"	TEXT NOT NULL,
	"nickname"	TEXT,
	"email"	TEXT NOT NULL
);
```

### Validate column name

We use Hotcoal to validate the column name and create a SQL query string.

String constants can be converted to a hotcoalString directly, without validation, using `Wrap`.

But string variables cannot be converted to a hotcoalString directly. This helps protect against SQL injection.

Instead, you must validate the string variable against an allowlist, using `Allowlist` and `Validate`. <br>
If you try to convert a string variable to a hotcoalString without validation,
using `Wrap`, it doesn't compile.


```golang
import (
  "database/sql"
  "github.com/motrboat/hotcoal"  
)

func queryCount(db *sql.DB, columnName string, value string) *sql.Row {

  // validating the columnName variable against an allowlist
  // it returns a hotcoalString
  validatedColumnName, err := hotcoal.
    Allowlist("first_name", "middle_name", "last_name").
    Validate(columnName)

  if err != nil {
    return nil, err
  }  

  // handcraft SQL using hotcoalStrings
  query := hotcoal.Wrap("SELECT COUNT(*) FROM users WHERE ") +
    validatedColumnName +
    hotcoal.Wrap(" = ?;")

  // if you try to use a regular string variable, which has not been validated,
  // it's vulnerable to SQL injection, so it doesn't compile:
  //       query := hotcoal.Wrap("SELECT COUNT(*) FROM users WHERE ") +
  //         columnName +
  //         hotcoal.Wrap(" = ?;")

  // convert the hotcoalString back to a regular string only when it is passed to the DB library
  // use prepared query with parameter to sanitize the value
  row := db.QueryRow(query.String(), value)

  return row
}
```

### Validate handcrafted SQL

To handcraft more complicated SQL, you can also use the hotcoal equivalents
of `strings` functions `Join`, `Replace` and `ReplaceAll`.
These can be called with hotcoalStrings, or string constants.
But not string variables - those must be validated first.

```golang
import (
  "database/sql"
  "github.com/motrboat/hotcoal"  
)

type Filter struct {
  ColumnName string
  Value string
}

func queryCount(db *sql.DB, tableName string, filters []Filter) (*sql.Row, error) {
  validatedTableName, err := hotcoal.
    Allowlist("users", "customers").
    Validate(tableName)
  if err != nil {
    return nil, err
  }

  allowlist := hotcoal.Allowlist("first_name", "middle_name", "last_name", "nickname")

  sqlArr := hotcoal.Slice{}
  values := []string{}

  for _, filter := range filters {
    validatedColumnName, err := allowlist.Validate(filter.ColumnName)
    if err != nil {
      return nil, err
    }

    sqlArr = append(sqlArr, validatedColumnName + hotcoal.Wrap(" = ?"))
    values = append(values, filter.Value)
  }

  query := hotcoal.Wrap("SELECT COUNT(*) FROM {{TABLE}} WHERE {{FILTERS}};").
    ReplaceAll(
      "{{TABLE}}",
      validatedTableName,
    ).
    ReplaceAll(
      "{{FILTERS}}",
      hotcoal.Join(sqlArr, " OR "),
    )

  row := db.QueryRow(query.String(), values...)

  return row
}
```

### String builder

Hotcoal also offers a version of `strings.Builder` using `hotcoalStrings`. It minimizes memory copying and is more efficient.

```golang
import (
  "database/sql"
  "github.com/motrboat/hotcoal"  
)

type Filter struct {
  ColumnName string
  Value string
}

func queryCount(db *sql.DB, filters []Filter) *sql.Row {
  allowlist := hotcoal.Allowlist("first_name", "middle_name", "last_name", "nickname")

  builder := hotcoal.Builder{}
  values := []string{}

  builder.Write("SELECT COUNT(*) FROM users WHERE ")

  for i, filter := range filters {
    if i != 0 {
      builder.Write(" OR ")
    }

    validatedColumnName, err := allowlist.Validate(filter.ColumnName)
    if err != nil {
      return nil, err
    }

    builder.Write(validatedColumnName + hotcoal.Wrap(" = ?"))
    values = append(values, filter.Value)
  }

  builder.Write(";")

  row := db.QueryRow(builder.String(), values...)

  return row
}
```

## Documentation

- [type hotcoalString](<#type-hotcoalstring>)
  - [func Wrap$s hotcoalString$ hotcoalString](<#func-wrap>)
  - [func W$s hotcoalString$ hotcoalString](<#func-w>)
  - [func Itoa$i int$ hotcoalString](<#func-itoa>)
  - [func $s hotcoalString$ String string](<#func-hotcoalstring-string>)
- [type Slice](<#type-slice>)
- [type allowlistT](<#type-allowlistt>)
  - [func Allowlist$firstAllowlistItem hotcoalString, otherAllowlistItems ...hotcoalString$ allowlistT](<#func-allowlist>)
  - [func $a allowlistT$ Validate$value string$ $hotcoalString, error$](<#func-allowlistt-validate>)
  - [func $a allowlistT$ V$value string$ $hotcoalString, error$](<#func-allowlistt-v>)
  - [func $a allowlistT$ MustValidate$value string$ hotcoalString](<#func-allowlistt-mustvalidate>)
  - [func $a allowlistT$ MV$value string$ hotcoalString](<#func-allowlistt-mv>)
- [func Join$elems \[\]hotcoalString, sep hotcoalString$ hotcoalString](<#func-join>)
- [func $s hotcoalString$ Replace$old, new hotcoalString, n int$ hotcoalString](<#func-hotcoalstring-replace>)
- [func $s hotcoalString$ ReplaceAll$old, new hotcoalString$ hotcoalString](<#func-hotcoalstring-replaceall>)
- [type Builder](<#type-builder>)
  - [func $b \*Builder$ Cap int](<#func-builder-cap>)
  - [func $b \*Builder$ Grow$n int$](<#func-builder-grow>)
  - [func $b \*Builder$ Len int](<#func-builder-len>)
  - [func $b \*Builder$ Reset](<#func-builder-reset>)
  - [func $b \*Builder$ Write$s hotcoalString$ \*Builder](<#func-builder-write>)
  - [func $b \*Builder$ HotcoalString hotcoalString](<#func-builder-hotcoalstring>)
  - [func $b \*Builder$ String string](<#func-builder-string>)


### type [hotcoalString](<https://github.com/motrboat/hotcoal/blob/main/hotcoal.go#L6>)

hotcoalString is an abstract data type, which is used for handcrafting SQL, protecting against SQL injection

```go
type hotcoalString string
```


#### func [Wrap](<https://github.com/motrboat/hotcoal/blob/main/hotcoal.go#L25>)

```go
func Wrap(s hotcoalString) hotcoalString
```

The Wrap function converts an untyped string constant to a hotcoalString. You can only use it with an untyped string constant, not with a string variable. For the latter, please use an Allowlist to validate the variable and guard against SQL injection.


#### func [W](<https://github.com/motrboat/hotcoal/blob/main/hotcoal.go#L30>)

```go
func W(s hotcoalString) hotcoalString
```

The W function is a shorthand for Wrap


#### func [Itoa](<https://github.com/motrboat/hotcoal/blob/main/strconv.go#L6>)

```go
func Itoa(i int) hotcoalString
```

The Itoa function converts an int to a hotcoalString.


#### func $hotcoalString$ [String](<https://github.com/motrboat/hotcoal/blob/main/hotcoal.go#L17>)

```go
func (s hotcoalString) String() string
```

The String method converts a hotcoalString to a plain string. Please do all your SQL handcrafting using hotcoalStrings, and convert the result to a plain string only when you pass it to the SQL library.


### type [Slice](<https://github.com/motrboat/hotcoal/blob/main/hotcoal.go#L11>)

Slice is an alias for a slice of hotcoalStrings. Since hotcoalString is not exported, we export this alias, which allows you to create slices.

```go
type Slice = []hotcoalString
```


### type [allowlistT](<https://github.com/motrboat/hotcoal/blob/main/allowlist.go#L7-L9>)

allowlistT holds an allowlist of items, which is used to validate string variables such as column names or table names, guarding against SQL injection

```go
type allowlistT struct {
    items map[hotcoalString]unitT
}
```


#### func [Allowlist](<https://github.com/motrboat/hotcoal/blob/main/allowlist.go#L17>)

```go
func Allowlist(firstAllowlistItem hotcoalString, otherAllowlistItems ...hotcoalString) allowlistT
```

Allowlist creates an allowlistT, which is used to validate validate string variables such as column names or table names, guarding against SQL injection


#### func $allowlistT$ [Validate](<https://github.com/motrboat/hotcoal/blob/main/allowlist.go#L33>)

```go
func (a allowlistT) Validate(value string) (hotcoalString, error)
```

The Validate method validates a string variable against the allowlist and returns a hotcoalString. If the value is not in the allowlist, it returns an error.


#### func $allowlistT$ [V](<https://github.com/motrboat/hotcoal/blob/main/allowlist.go#L42>)

```go
func (a allowlistT) V(value string) (hotcoalString, error)
```

The V method is an shorthand for Validate


#### func $allowlistT$ [MustValidate](<https://github.com/motrboat/hotcoal/blob/main/allowlist.go#L48>)

```go
func (a allowlistT) MustValidate(value string) hotcoalString
```

The MustValidate method validates a string variable against the allowlist and returns a hotcoalString. If the value is not in the allowlist, it panics.


#### func $allowlistT$ [MV](<https://github.com/motrboat/hotcoal/blob/main/allowlist.go#L58>)

```go
func (a allowlistT) MV(value string) hotcoalString
```

The MV method is an shorthand for MustValidate


### func [Join](<https://github.com/motrboat/hotcoal/blob/main/strings.go#L9>)

```go
func Join(elems []hotcoalString, sep hotcoalString) hotcoalString
```

Join concatenates the elements of its first argument to create a single hotcoalString. The separator hotcoalString sep is placed between elements in the resulting hotcoalString.

Under the hood, it uses strings.Join https://pkg.go.dev/strings#Join


### func $hotcoalString$ [Replace](<https://github.com/motrboat/hotcoal/blob/main/strings.go#L68>)

```go
func (s hotcoalString) Replace(old, new hotcoalString, n int) hotcoalString
```

Replace returns a copy of the hotcoalString s with the first n non\-overlapping instances of old replaced by new. If old is empty, it matches at the beginning of the hotcoalString and after each UTF\-8 sequence, yielding up to k\+1 replacements for a k\-rune hotcoalString. If n \< 0, there is no limit on the number of replacements.

You can chain method calls.

Under the hood, it uses strings.Replace https://pkg.go.dev/strings#Replace

### func $hotcoalString$ [ReplaceAll](<https://github.com/motrboat/hotcoal/blob/main/strings.go#L88>)

```go
func (s hotcoalString) ReplaceAll(old, new hotcoalString) hotcoalString
```

ReplaceAll returns a copy of the string s with all non\-overlapping instances of old replaced by new. If old is empty, it matches at the beginning of the string and after each UTF\-8 sequence, yielding up to k\+1 replacements for a k\-rune string.

You can chain method calls.

Under the hood, it uses strings.ReplaceAll https://pkg.go.dev/strings#ReplaceAll


### type [Builder](<https://github.com/motrboat/hotcoal/blob/main/builder.go#L13-L15>)

A Builder is used to efficiently build a hotcoalString using the Write method. It minimizes memory copying. The zero value is ready to use. Do not copy a non\-zero Builder.

Under the hood, it uses strings.Builder https://pkg.go.dev/strings#Builder

```go
type Builder struct {
    stringBuilder strings.Builder
}
```


#### func $\*Builder$ [Cap](<https://github.com/motrboat/hotcoal/blob/main/builder.go#L20>)

```go
func (b *Builder) Cap() int
```

Cap returns the capacity of the builder's underlying byte slice. It is the total space allocated for the hotcoalString being built and includes any bytes already written.


#### func $\*Builder$ [Grow](<https://github.com/motrboat/hotcoal/blob/main/builder.go#L27>)

```go
func (b *Builder) Grow(n int)
```

Grow grows b's capacity, if necessary, to guarantee space for another n bytes. After Grow$n$, at least n bytes can be written to b without another allocation. If n is negative, Grow panics.


#### func $\*Builder$ [Len](<https://github.com/motrboat/hotcoal/blob/main/builder.go#L32>)

```go
func (b *Builder) Len() int
```

Len returns the number of accumulated bytes; b.Len == len$b.String\($\).


#### func $\*Builder$ [Reset](<https://github.com/motrboat/hotcoal/blob/main/builder.go#L37>)

```go
func (b *Builder) Reset()
```

Reset resets the Builder to be empty.


#### func $\*Builder$ [Write](<https://github.com/motrboat/hotcoal/blob/main/builder.go#L42>)

```go
func (b *Builder) Write(s hotcoalString) *Builder
```

Write appends the contents of s to b's buffer. It returns b, you can chain method calls.


#### func $\*Builder$ [HotcoalString](<https://github.com/motrboat/hotcoal/blob/main/builder.go#L54>)

```go
func (b *Builder) HotcoalString() hotcoalString
```

String returns the accumulated string as a hotcoalString.


#### func $\*Builder$ [String](<https://github.com/motrboat/hotcoal/blob/main/builder.go#L59>)

```go
func (b *Builder) String() string
```

String returns the accumulated string as a plain string.

## Disclaimer

Hotcoal comes without any warranty.


Hotcoal

Abstraction layer for PostgreSQL that supports advanced functionality and uses gorm-like syntax

igor

Framework for writing type-safe SQL queries in Go, with ability to easily convert database query result into desired arbitrary object structure

Lightweight package containing some ORM-like features and helpers for sqlite databases

# ormlite
Lightweight package implementing some ORM-like features and helpers for sqlite databases.

[![Build Status](https://app.travis-ci.com/pupizoid/ormlite.svg?branch=master)](https://app.travis-ci.com/pupizoid/ormlite)
[![codecov](https://codecov.io/gh/pupizoid/ormlite/branch/master/graph/badge.svg?token=44ngQeqfvl)](https://codecov.io/gh/pupizoid/ormlite)
[![GoDoc](https://godoc.org/github.com/pupizoid/ormlite?status.svg)](https://godoc.org/github.com/pupizoid/ormlite)
[![Go Report Card](https://goreportcard.com/badge/github.com/pupizoid/ormlite)](https://goreportcard.com/report/github.com/pupizoid/ormlite)
[![Awesome](https://cdn.rawgit.com/sindresorhus/awesome/d7305f38d29fed78fa85652e3a63e154dd8e8829/media/badge.svg)](https://github.com/avelino/awesome-go)

## Model
```go
type Model interface {
    Table() string
}
```
This package operates models which are described by `Model` interface. We call any entry a model if it's a struct and has a table where data is stored.

## CRUD
This package provides a bunch of functions to allow you create, read, update and delete data.
  
### QueryStruct
Loads data from table and scans it into provided struct. If query was too broad to load more than one rows, the latest of them will be scanned. Also this function supports loading relations which will be described below.

```go
type SimpleStruct struct {
  IntField int64 `ormlite:"col=rowid,primary"`
  Text string
  UnusedField bool `ormlite:"-"
}

var s SimpleStruct
err := QueryStruct(db, "", nil, &s)
```

Let's describe some tags used in example struct:
- `col` - let you specify custom column name to be scanned to the field
- `primary` - indicates model primary key, it's basically used when saving model
- `-` - hide field for package so it won't be affected at any kind

### QuerySlice
This is very similar to QueryStruct except that it loads multiple rows in a slice.

### Upsert
This function is used to save or update existing model, if model has `primary` field and it's value is zero - this model will be inserted to the model's table. Otherwise model's row will be updated according it's current values (except `has-one` relation). This function also supports updating related models except creating or editing `many-to-many` related models.
```go
err := Upsert(db, &s)
```

### Insert 
Function used for inserting Models. Despite of `Upsert` it returns an error in case of constraint errors. 

### Delete
This function... yea, it deletes model from database using it's primary key value. If model does not have primary key or it has zero value an error will ne returned.
Since sometimes it's useful to know that delete operation is really took place in database, function will check number of affected rows and return a special `ErrNoRowsAffected`
if it's not positive.

## Options

```go
type Options struct {
   // Add where clause to query
   Where         Where    
   Limit         int      
   Offset        int      
   OrderBy       *OrderBy 
   // Load relations to specified depth,
   // if depth is 0 don't load any relations
   RelationDepth int      
}
```

For most queries is't enough to use `DefaultOptions()` which has relation depth equal to 1. 

If you already have variable containing Options, you can extend them with additional settings with following functions:
- WithLimit
- WithOffset
- WithOrder
- WithWhere

For example:

```go
opts := ormlite.WithWhere(ormlite.DefaultOptions(), ormlite.Where{"id": 1})
```

## Relations

QueryStruct, QuerySlice and Upsert support loading relations between models, the supported relation types are:
- Has One
- Has Many
- Many To Many

Since you can control depth of loaded relations, there is no need to be afraid of cycle loading. But there are several tags to configure relations.

### Has One

```go
type Model struct {
   Related ormlite.Model `ormlite:"has_one,col=related_model_id"`
}
```

`has_one` indicates that this field represents has one relations type to other model.

`col` is an optional parameter to specify custom column name of foreign id of related model.

### Has Many

```go
type Model struct {
   Related []ormlite.Model `ormlite:"has_many"`
}
```

`has_many` is the only parameter to indicate has many relation, however there is a requirement that related model must have `primary`
 field.
 
 ### Many To Many
 
 ```go
type Model struct {
   Related       []ormlite.Model `ormlite:"many_to_many,table=mapping_table,field=model_id"`
   RelatedActive []ormlite.Model `ormlite:"many_to_many,table=mapping_table(active=1),field=model_id"`
}
```

`many_to_many` indicates that field represents many-to-many relation.

`table(additional condition)` should contain mapping table name to retrieve relation information. If it's necessary to map entities with additional conditions you can specify sql describing them in brackets. For now only one additional field is supported.

`field` should specify column in mapping table that has foreign key of original model

Also there is a requirement to related model primary key field to contain `ref` setting that specifies column name of it's foreign key in mapping table.

### Search by related

Sometimes it's useful to search many-to-many model by related ones, so running the following code

```go
type Author struct {
    Id     int      `ormlite:"primary,ref=author_id"`
    Topics []*Topic `ormlite:"many_to_many,table=author_topics,field=author_id"`
    Name   string
}

func (a *Author) Table() string { return "authors" }

type Topic struct {
    Id      int       `ormlite:"primary,ref=topic_id"`
    Authors []*Author `ormlite:"many_to_many,table=author_topics,field=topic_id"`
    Content string
}

func (p *Topic) Table() string { return "topics" }

func main() {
    db, err := sql.Open("sqlite3", ":memory:?_fk=1")
    if err != nil {
        panic(err)
    }

    _, err = db.Exec(`
       create table authors(id integer primary key, name text);
       create table topics(id integer primary key, content text, author_id int);
       create table author_topics(author_id integer, topic_id integer);
    `)
    if err != nil {
        panic(err)
    }

    john := &Author{Name: "John"}
    err = ormlite.Upsert(db, john)
    if err != nil {
        panic(err)
    }

    pete := &Author{Name: "Pete"}
    err = ormlite.Upsert(db, pete)
    if err != nil {
        panic(err)
    }

    cars := &Topic{Content: "Cars", Authors: []*Author{john, pete}}
    err = ormlite.Upsert(db, cars)
    if err != nil {
        panic(err)
    }

    bikes := &Topic{Content: "Bikes", Authors: []*Author{john}}
    err = ormlite.Upsert(db, bikes)
    if err != nil {
        panic(err)
    }

    planes := &Topic{Content: "Plains", Authors: []*Author{pete}}
    err = ormlite.Upsert(db, planes)
    if err != nil {
        panic(err)
    }

    var carAuthors []*Author
    err = ormlite.QuerySlice(db, &ormlite.Options{RelatedTo: []ormlite.IModel{&Topic{Id: cars.Id}}}, &carAuthors)
    if err != nil {
        panic(err)
    }

    var planeAuthors []*Author
    err = ormlite.QuerySlice(db, &ormlite.Options{RelatedTo: []ormlite.IModel{&Topic{Id: planes.Id}}}, &planeAuthors)
    if err != nil {
        panic(err)
    }

    fmt.Print("Car authors: ")
    for _, a := range carAuthors {
        fmt.Printf("%s ", a.Name)
    }
    fmt.Print("\n")

    fmt.Print("Plane authors: ")
    for _, a := range planeAuthors {
        fmt.Printf("%s ", a.Name)
    }
```
will result
```
Car authors: John Pete 
Plane authors: Pete 
```
### Comparison operators

By default package use `=` operator to compare values introduced in `Where` struct, except strings, they are compared by `LIKE` operator. But there is a list of other operators that you can use:

- `Greater` stands for `>`
- `GreaterOrEqual` stands for `>=`
- `Less` stands for `<`
- `LessOrEqual` stands for `<=`
- `NotEqual` stands for `!=`
- `BitwiseAND` stands for `value&? > 0`
- `BitwiseANDStrict` stand for `value&? = 0`
- `StrictString` - by default string comparison are done using `LIKE` operator, `StrictString` will force using `=`
 
To use these operators just wrap value with them

```go
opts := &ormlite.Options{Where: {"Age": GreaterOrEqual(10)}}
```

### More Examples

See tests.


ormlite

Powerful data retrieval methods as well as DB-agnostic query building capabilities

ozzo-dbx

Tool that generates constants from files with raw SQL queries

[![buddy pipeline](https://eu.buddy.works/treinis/qry/pipelines/pipeline/191916/badge.svg?token=b46003623254cc1f4e162f3911b6075d87b9121676c88ffebfbe34d562d75d96 "buddy pipeline")](https://eu.buddy.works/treinis/qry/pipelines/pipeline/191916)
[![codecov](https://codecov.io/gh/HnH/qry/branch/master/graph/badge.svg)](https://codecov.io/gh/HnH/qry)
[![Go Report Card](https://goreportcard.com/badge/github.com/HnH/qry)](https://goreportcard.com/report/github.com/HnH/qry)
[![Godoc Reference](https://img.shields.io/badge/godoc-reference-blue.svg)](https://godoc.org/github.com/HnH/qry)

# About

**qry** is a general purpose library for storing your raw database queries in .sql files with all benefits of modern IDEs, instead of strings and constants in the code, and using them in an easy way inside your application with all the profit of compile time constants.

**qry** recursively loads all .sql files from a specified folder, parses them according to predefined rules and returns a reusable object, which is actually just a `map[string]string` with some sugar. Multiple queries inside a single file are separated with standard SQL comment syntax: `-- qry: QueryName`. A `QueryName` must match `[A-Za-z_]+`.

[gen](https://github.com/HnH/qry/tree/master/cmd/qry-gen) tool is used for automatic generation of constants for all user specified `query_names`.

# Installation

`go install github.com/HnH/qry/cmd/qry-gen@latest` 

# Usage

Prepare sql files: `queries/one.sql`:

```sql
-- qry: InsertUser
INSERT INTO `users` (`name`) VALUES (?);

-- qry: GetUserById
SELECT * FROM `users` WHERE `user_id` = ?;
```

And the second one `queries/two.sql`:

```sql
-- qry: DeleteUsersByIds
DELETE FROM `users` WHERE `user_id` IN ({ids});
```

[Gen](https://github.com/HnH/qry/tree/master/cmd/qry-gen)erate constants: `qry-gen -dir=./queries -pkg=/path/to/your/go/pkg` Will produce `/path/to/your/go/pkg/qry.go` with:

```go
package pkg

const (
	// one.sql
	InsertUser  = "INSERT INTO `users` (`name`) VALUES (?);"
	GetUserById = "SELECT * FROM `users` WHERE `user_id` = ?;"

	// two.sql
	DeleteUsersByIds = "DELETE FROM `users` WHERE `user_id` IN ({ids});"
)
```

As a best practice include this qry-gen call in your source code with go:generate prefix: `//go:generate qry-gen -dir=./queries -pkg=/path/to/your/go/pkg` and just execute `go generate` before each build.
Now it's time to use **qry** inside your project:

```go
func main() {
	/**
	 * The most obvious way is to use generated constants in the source code
	 */
	 
	// INSERT INTO `users` (`name`) VALUES (?);
	println(pkg.InsertUser)
	
	// DELETE FROM `users` WHERE `user_id` IN (?,?,?);
	println(qry.Query(pkg.DeleteUsersByIds).Replace("{ids}", qry.In(3)))
	
	/**
	 * As an alternative you can manually parse .sql files in the directory and work with output
	 */
	if q, err := qry.Dir("/path/to/your/go/pkg/queries"); err != nil {
		log.Fatal(err)
	}

	// SELECT * FROM `users` WHERE `user_id` = ?;
	println(q["one.sql"]["GetUserById"])
  
	// DELETE FROM `users` WHERE `user_id` IN (?,?,?);
	println(q["two.sql"]["DeleteUsersByIds"].Replace("{ids}", qry.In(3)))
}
```


A SQL Gen for generating standard SQLs(supports: CRUD) written in Go

# sg

sg: A simple standard SQL generator written in Go.

[![CircleCI](https://circleci.com/gh/go-the-way/sg/tree/main.svg?style=shield)](https://circleci.com/gh/go-the-way/sg/tree/main)
![GitHub go.mod Go version](https://img.shields.io/github/go-mod/go-version/go-the-way/sg)
[![codecov](https://codecov.io/gh/go-the-way/sg/branch/main/graph/badge.svg?token=8MAR3J959H)](https://codecov.io/gh/go-the-way/sg)
[![Go Report Card](https://goreportcard.com/badge/github.com/go-the-way/sg)](https://goreportcard.com/report/github.com/go-the-way/sg)
[![GoDoc](https://pkg.go.dev/badge/github.com/go-the-way/sg?status.svg)](https://pkg.go.dev/github.com/go-the-way/sg?tab=doc)
[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go#sql-query-builders)

## Overview
- [Builders](#builders)
  - [Insert](#insert-builder)
  - [Delete](#delete-builder)
  - [Update](#update-builder)
  - [Select](#select-builder)
- [Generators](#generators)
  - [Create view](#create-view)
  - [Create index](#create-index)
  - [Create unique index](#create-unique-index)
  - [Index definition](#index-definition)
  - [Column definition](#column-definition)
  - [Primary key](#primary-key)
  - [Default](#default)
  - [Delete](#delete)
  - [Delete from](#delete-from)
  - [Drop table](#drop-table)
  - [Drop view](#drop-view)
  - [Drop event](#drop-event)
  - [Drop procedure](#drop-procedure)
  - [Insert](#insert)
  - [Values](#values)
  - [Alias](#alias)
  - [Arg](#arg)
  - [From](#from)
  - [Left join](#left-join)
  - [Right join](#right-join)
  - [Inner join](#inner-join)
  - [On](#on)
  - [Select](#select)
  - [Order by](#order-by)
  - [Asc](#asc)
  - [Desc](#desc)
  - [Asc group](#asc-group)
  - [Desc group](#desc-group)
  - [Group by](#group-by)
  - [Having](#having)
  - [Update](#update)
  - [Set](#set)
  - [Set eq](#set-eq)
  - [Where](#where)
  - [And](#and)
  - [Or](#or)
  - [Not](#not)
  - [And group](#and-group)
  - [Or group](#or-group)
  - [Eq](#eq)
  - [Not eq](#not-eq)
  - [Gt](#gt)
  - [Gt eq](#gt-eq)
  - [Lt](#lt)
  - [Lt eq](#lt-eq)
  - [Like](#like)
  - [Left like](#left-like)
  - [Right like](#right-like)
  - [Instr](#instr)
  - [In](#in)
  - [Between and](#between-and)

###  Builders

#### Insert Builder

```go
package main

import (
  "fmt"
  . "github.com/go-the-way/sg"
)

func main() {
  builder := InsertBuilder().
    Table(T("table_person")).
    Column(C("col1"), C("col2")).
    Value(Arg(100), Arg(200))
  fmt.Println(builder.Build())
  // Output:
  // INSERT INTO table_person (col1, col2) VALUES (?, ?) [100 200]
}
```

#### Delete Builder

```go
package main

import (
	"fmt"
	. "github.com/go-the-way/sg"
)

func main() {
	builder := DeleteBuilder().
		Delete(T("t1.*")).
		From(As(C("table1"), "t1"), As(C("table2"), "t2")).
		Where(AndGroup(Gt("t1.col1", 100), Gt("t2.col2", 200)))
	fmt.Println(builder.Build())
	// Output:
	// DELETE t1.* FROM table1 AS t1, table2 AS t2 WHERE ((t1.col1 > ?) AND (t2.col2 > ?)) [100 200]
}
```

#### Update Builder

```go
package main

import (
	"fmt"
	. "github.com/go-the-way/sg"
)

func main() {
	builder := UpdateBuilder().
		Update(As(T("table_person"), "t")).
		Join(LeftJoin(As(T("table_a"), "ta"), On(C("t.col1 = ta.col1")))).
		Set(SetEq("col1", 100), SetEq("col2", 200)).
		Where(AndGroup(Eq("a", 100), Eq("b", 200)))
	fmt.Println(builder.Build())
	// Output:
	// UPDATE table_person AS t LEFT JOIN table_a AS ta ON (t.col1 = ta.col1) SET col1 = ?, col2 = ? WHERE ((a = ?) AND (b = ?)) [100 200 100 200]
}
```

#### Select Builder

```go
package main

import (
	"fmt"
	. "github.com/go-the-way/sg"
)

func main() {
	builder := SelectBuilder().
		Select(C("a"), C("b")).
		From(T("table_person")).
		Join(LeftJoin(As(T("table_a"), "ta"), On(C("ta.col1 = tb.col1")))).
		Where(AndGroup(Eq("a", 100), Eq("b", 200))).
		OrderBy(DescGroup(C("a"), C("b")))
	fmt.Println(builder.Build())
	// Output:
	// SELECT a, b FROM table_person LEFT JOIN table_a AS ta ON (ta.col1 = tb.col1) WHERE ((a = ?) AND (b = ?)) ORDER BY a DESC, b DESC [100 200]
}
```

###  Generators

#### Create view

```
CreateView(P("vm_nowTime"), P(`select NOW() AS t`)
```

#### Create index

```
CreateIndex(false, P("idx_name"), T("table"), C("name"))
```

#### Create unique index

```
CreateUniqueIndex(P("idx_name"), T("table"), C("name"))
```

#### Index definition

```
IndexDefinition(false, P("idx_name"), C("name"))
```

#### Column definition

```
ColumnDefinition(P("id"), P("int"), false, true, false, "", "ID")
```

#### Primary key

```
PrimaryKey(C("id"))
```

#### Default

```
Default(C("1"))
```

#### Delete

```
Delete([]Ge{}, T("table_a"))
```

#### Delete from

```
DeleteFrom(T("table_a"))
```

#### Drop table

```
DropTable(T("table"))
```

#### Drop view

```
DropView(T("view"))
```

#### Drop event

```
DropEvent(T("event"))
```

#### Drop procedure

```
DropProcedure(T("procedure"))
```

#### Insert

```
Insert(C("table"), C("aa"))
```

#### Values

```
Values(Arg(100))
```

#### Alias

```
Alias(C("hello"), "hello_lo")
```

#### Arg

```
Arg(100)
```

#### From

```
From(T("table_a"), T("table_b"))
```

#### Left join

```
LeftJoin(As(T("table_a"), "ta"), On(C("ta.col1 = tb.col1"))
```

#### Right join

```
RightJoin(As(T("table_a"), "ta"), On(C("ta.col1 = tb.col1"))
```

#### Inner join

```
InnerJoin(As(T("table_a"), "ta"), On(C("ta.col1 = tb.col1"))
```

#### On

```
On(C("ta.col1 = tb.col1"))
```

#### Select

```
Select(C("t.col_a"))
```

#### Order by

```
OrderBy(AscGroup(C("t.abc"), C("t.xxx")))
```

#### Asc

```
Asc(C("t.abc"))
```

#### Desc

```
Desc(C("t.abc"))
```

#### Asc group

```
AscGroup(C("t.abc"), C("t.xxx"))
```

#### Desc group

```
DescGroup(C("t.abc"), C("t.xxx"))
```

#### Group by

```
GroupBy(C("t.abc"), C("t.xyz"))
```

#### Having

```
Having(AndGroup(Eq("a", 1)))
```

#### Update

```
Update(T("table_a"))
```

#### Set

```
Set(C("t.a = t.b"))
```

#### Set eq

```
SetEq("col1", 100)
```

#### Where

```
Where(AndGroup(Eq("a", 1)))
```

#### And

```
And(Eq("c", 100))
```

#### Or

```
Or(Eq("c", 100))
```

#### Not

```
Not(Eq("c", 100))
```

#### And group

```
AndGroup(Gt("t1.col1", 100), Gt("t2.col2", 200))
```

#### Or group

```
OrGroup(Eq("a", 1), Eq("b", 100))
```

#### Eq

```
Eq("a", 1))
```

#### Not eq

```
NotEq("a", 1))
```

#### Gt

```
Gt("a", 1))
```

#### Gt eq

```
Lt("a", 1))
```

#### Lt

```
Lt("a", 1))
```

#### Lt eq

```
LtEq("a", 1))
```

#### Like

```
Like("a", 1)
```

#### Left like

```
LeftLike("a", 1)
```

#### Right like

```
RightLike("a", 1)
```

#### Instr

```
Instr("a", 1)
```

#### In

```
In("a", 1)
```

#### Between and

```
BetweenAnd(c, rune(100), rune(100))
```



Type-safe SQL builder and struct mapper for Go

[![GoDoc-postgres](https://img.shields.io/badge/pkg.go.dev-postgres-blue)](https://pkg.go.dev/github.com/bokwoon95/go-structured-query/postgres)
[![GoDoc-mysql](https://img.shields.io/badge/pkg.go.dev-mysql-blue)](https://pkg.go.dev/github.com/bokwoon95/go-structured-query/mysql)
![CI](https://github.com/bokwoon95/go-structured-query/workflows/CI/badge.svg?branch=master)
[![Go Report Card](https://goreportcard.com/badge/github.com/bokwoon95/go-structured-query)](https://goreportcard.com/report/github.com/bokwoon95/go-structured-query)
[![Coverage Status](https://coveralls.io/repos/github/bokwoon95/go-structured-query/badge.svg?branch=master)](https://coveralls.io/github/bokwoon95/go-structured-query?branch=master)


<h1>Hey! Development has stopped on this for a while. Please see https://github.com/bokwoon95/sq for the spiritual successor to this library. I will still support this library, but new features will not be added. For the main changes and how to migrate from go-structured-query to sq, see https://bokwoon.neocities.org/sq#migrating-from-go-structured-query</h1>

<div align="center"><h1>sq (Structured Query)</h1></div>
<div align="center"><h5>🎯🏆 sq is a code-generated, type safe query builder and struct mapper for Go. 🏆🎯</h5></div>
<div align="center">


<a href="https://bokwoon95.github.io/sq/">Documentation</a>
<span>&nbsp;&nbsp;•&nbsp;&nbsp;</span>
<a href="https://bokwoon95.github.io/sq/basics/tables-fields-and-predicates.html#query-builder-reference">Reference</a>
<span>&nbsp;&nbsp;•&nbsp;&nbsp;</span>
<a href="http://bokwoon95.github.io/sq/#query-building">Examples</a>
</div>
<hr>

This package provides type safe querying on top of Go's database/sql. It is essentially a glorified string builder, but automates things in all the right places to make working with SQL queries pleasant and boilerplate-free.

- <b>Avoid magic strings.</b> SQL queries written in Go are full of [magic strings](https://deviq.com/magic-strings/): strings specified directly within application code that have an impact on the application's behavior. Specifically, you have to hardcode table or column names over and over into your queries (even ORMs are guilty of this!). Such magic strings are prone to typos and hard to change as your database schema changes. sq generates table structs from your database and ensures that whatever query you write is always reflective of what's actually in your database. [more info](https://bokwoon95.github.io/sq/basics/generating-table-types.html)

- <b>Better null handling</b>. Handling NULLs is a bit of a pain in the ass in Go. You have to either use pointers (cannot be used in HTML templates) or sql.NullXXX structs (extra layer of indirection). sq scans NULLs as zero values, while still offering you the ability to check if the column was NULL. [more info](https://bokwoon95.github.io/sq/basics/struct-mapping.html#nulls)

- <b>The mapper function *is* the SELECT clause</b>.
    - database/sql requires you to repeat the list of columns twice in the exact same order, once for SELECT-ing and once for scanning. If you mess the order up, that's an error.
    - Reflection-based mapping (struct tags) has you defining a set of possible column names to map, and then requires you repeat those columns names again in your query. If you mistype a column name in the struct tag, that's an error. If you SELECT a column that's not present in the struct, that's an error.
    - In sq whatever you SELECT is automatically mapped. **This means you just have to write your query, execute it and if there were no errors, the data is already in your Go variables.** No iterating rows, no specifying column scan order, no error checking three times. *Write your query, run it, you're done*.
    - [more info](https://bokwoon95.github.io/sq/basics/struct-mapping.html)

## Features
- [WITH (Common Table Expressions)](https://bokwoon95.github.io/sq/sql-expressions/common-table-expressions.html)
- [Window Functions](https://bokwoon95.github.io/sq/sql-expressions/window-functions.html)
- [Subqueries](https://bokwoon95.github.io/sq/sql-expressions/subqueries.html)
- [IN](https://bokwoon95.github.io/sq/sql-expressions/in.html) (supports [Go slices](https://bokwoon95.github.io/sq/sql-expressions/in.html#in-slice) and [subqueries](https://bokwoon95.github.io/sq/sql-expressions/in.html#in-subquery))
- [COUNT/SUM/AVG/MIN/MAX](https://bokwoon95.github.io/sq/sql-expressions/aggregate-functions.html)
- [UNION/INTERSECT/EXCEPT](https://bokwoon95.github.io/sq/sql-expressions/union.html)
- [CASE](https://bokwoon95.github.io/sq/sql-expressions/case.html)
- [DISTINCT ON](https://bokwoon95.github.io/sq/query-building/select.html#distinct-on) (postgres only)
- [EXISTS](https://bokwoon95.github.io/sq/query-building/select.html#exists)
- [Upsert](https://bokwoon95.github.io/sq/query-building/insert.html#upsert)
- [INSERT ... SELECT](https://bokwoon95.github.io/sq/query-building/insert.html#insert-from-select)
- [UPDATE with JOIN](https://bokwoon95.github.io/sq/query-building/update.html#join)
- [DELETE with JOIN](https://bokwoon95.github.io/sq/query-building/delete.html#join)

## Getting started
```bash
go get github.com/bokwoon95/go-structured-query
```
You will also need the dialect-specific code generator
```bash
# Postgres
go get github.com/bokwoon95/go-structured-query/cmd/sqgen-postgres

# MySQL
go get github.com/bokwoon95/go-structured-query/cmd/sqgen-mysql
```
Generate tables from your database
```bash
# for more options, check out --help

# Postgres
sqgen-postgres tables --database 'postgres://name:pass@localhost:5432/dbname?sslmode=disable' --overwrite

# MySQL
sqgen-mysql tables --database 'name:pass@tcp(127.0.0.1:3306)/dbname' --schemas dbname --overwrite
```

For an example of what the generated file looks like, check out [postgres/devlab\_tables\_test.go](postgres/devlab_tables_test.go).

### Importing sq

Each SQL dialect has its own sq package. Import the sq package for the dialect you are using accordingly:
```go
// Postgres
import (
    sq "github.com/bokwoon95/go-structured-query/postgres"
)

// MySQL
import (
    sq "github.com/bokwoon95/go-structured-query/mysql"
)
```

## Examples
You just want to see code, right? Here's some.

#### SELECT
```sql
-- SQL
SELECT u.user_id, u.name, u.email, u.created_at
FROM public.users AS u
WHERE u.name = 'Bob';
```
```go
// Go
u := tables.USERS().As("u") // table is code generated
var user User
var users []User
err := sq.
    From(u).
    Where(u.NAME.EqString("Bob")).
    Selectx(func(row *sq.Row) {
        user.UserID = row.Int(u.USER_ID)
        user.Name = row.String(u.NAME)
        user.Email = row.String(u.EMAIL)
        user.CreatedAt = row.Time(u.CREATED_AT)
    }, func() {
        users = append(users, user)
    }).
    Fetch(db)
if err != nil {
    // handle error
}
```

#### INSERT
```sql
-- SQL
INSERT INTO public.users (name, email)
VALUES ('Bob', 'bob@email.com'), ('Alice', 'alice@email.com'), ('Eve', 'eve@email.com');
```
```go
// Go
u := tables.USERS().As("u") // table is code generated
users := []User{
    {Name: "Bob",   Email: "bob@email.com"},
    {Name: "Alice", Email: "alice@email.com"},
    {Name: "Eve  ", Email: "eve@email.com"},
}
rowsAffected, err := sq.
    InsertInto(u).
    Valuesx(func(col *sq.Column) {
        for _, user := range users {
            col.SetString(u.NAME, user.Name)
            col.SetString(u.EMAIL, user.Email)
        }
    }).
    Exec(db, sq.ErowsAffected)
if err != nil {
    // handle error
}
```

#### UPDATE
```sql
-- SQL
UPDATE public.users
SET name = 'Bob', password = 'qwertyuiop'
WHERE email = 'bob@email.com';
```
```go
// Go
u := tables.USERS().As("u") // table is code generated
user := User{
    Name:     "Bob",
    Email:    "bob@email.com",
    Password: "qwertyuiop",
}
rowsAffected, err := sq.
    Update(u).
    Setx(func(col *sq.Column) {
        col.SetString(u.NAME, user.Name)
        col.SetString(u.PASSWORD, user.Password)
    }).
    Where(u.EMAIL.EqString(user.Email)).
    Exec(db, sq.ErowsAffected)
if err != nil {
    // handle error
}
```

#### DELETE
```sql
-- SQL
DELETE FROM public.users AS u
USING public.user_roles AS ur
JOIN public.user_roles_students AS urs ON urs.user_role_id = ur.user_role_id
WHERE u.user_id = ur.user_id AND urs.team_id = 15;
```
```go
// Go
u   := tables.USERS().As("u")                 // tables are code generated
ur  := tables.USER_ROLES().As("ur")           // tables are code generated
urs := tables.USER_ROLES_STUDENTS().As("urs") // tables are code generated
rowsAffected, err := sq.
    DeleteFrom(u).
    Using(ur).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(
        u.USER_ID.Eq(ur.USER_ID),
        urs.TEAM_ID.EqInt(15),
    ).
    Exec(db, sq.ErowsAffected)
if err != nil {
    // handle error
}
```

For more information, check out the [Basics](http://bokwoon95.github.io/sq/#basics).

For a list of example queries, check out [Query Building](http://bokwoon95.github.io/sq/#query-building).

## Project Status
The external API is considered stable. Any changes will only be add to the API (like support for custom loggers and structured logging). If you have any feature requests or if you find bugs do open a [new issue](https://github.com/bokwoon95/go-structured-query/issues).

## Contributing
See [CONTRIBUTING.md](CONTRIBUTING.md)

## Appendix

### Why this exists

I wrote this because I needed a more convenient way to scan database rows into nested structs, some of which exist twice in the same struct due to self joined tables.
That made sqlx's StructScan unsuitable ([e.g. cannot handle `type Child struct { Father Person; Mother Person; }`](https://jmoiron.github.io/sqlx/#advancedScanning)).
database/sql's way of scanning is really verbose especially since I had about ~25 fields to scan into, some of which could be null. That's a lot of sql Null structs needed! Because I had opted to -not- pollute my domain structs with `sql.NullInt64`/ `sql.NullString` etc, I had to create a ton of intermediate Null structs just to contain the possible null fields, then transfer their zero value back into the domain struct. There had to be a better way. I just wanted their zero values, since everything in Go accomodates the zero value.

sq is therefore a data mapper first, and query builder second. I try my best to make the query builder as faithful to SQL as possible, but the main reason for its existence was always the [struct mapping](http://bokwoon95.github.io/sq/basics/struct-mapping.html).

### The case for ALL\_CAPS
Here are the reasons why ALL\_CAPS is used for table and column names over the idiomatic MixedCaps:
1) [jOOQ](https://www.jooq.org/doc/latest/manual/getting-started/use-cases/jooq-as-a-sql-builder-with-code-generation/) does it.
2) It's SQL. It's fine if it doesn't follow Go convention, because it isn't Go.
    - Go requires exported fields by capitalized.
    - SQL, being case insensitive, generally uses underscores as word delimiters.
    - ALL\_CAPS is a blend that satisfies both Go's export rules and SQL's naming conventions.
    - In my opinion, it is also easier to read because table and column names visually stand out from application code.
3) Avoids clashing with interface methods. For a struct to fit the Table interface, it has to possess the methods `GetAlias()` and `GetName()`. This means that no columns can be called 'GetAlias' or 'GetName' because it would clash with the interface methods. This is sidestepped by following an entirely different naming scheme for columns i.e. ALL\_CAPS.

### On SQL Type Safety

sq makes no effort to check the semantics of your SQL queries at runtime. Any type checking is entirely enforced by what methods that you can call and argument types that you can pass to these methods. For example, You can call Asc()/Desc() and NullsFirst()/NullsLast() on any selected field and it would pass the type checker, because Asc()/Desc()/NullsFirst()/NullsLast() still return a Field interface:
```go
u := tables.USERS().As("u")
sq.Select(u.USER_ID, u.USERNAME.Asc().NullsLast()).From(u)
```
which would translate to
```sql
SELECT u.user_id, u.username ASC NULLS LAST FROM users AS u
-- obviously wrong, you can't use ASC NULLS LAST inside the SELECT clause
```
The above example passes the Go type checker, so sq will happily build the query string -- even if that SQL query is sematically wrong. In practice, as long as you aren't trying to actively do the wrong thing (like in the above example), the limited type safety will prevent you from making the most common types of errors.

It also means the query builder will never fail: there's no boilerplate error checking required. Any semantic errors will be deferred to the database to point it out to you.

### Dialect agnostic query builder?
sq is not dialect agnostic. This means I can add your favorite dialect specific SQL features without the headache of cross-dialect compatibility. It also makes contributions easier, as you just have to focus on your own SQL dialect and not care about the others.

<img src="https://raw.githubusercontent.com/lqs/sqlingo/master/logo.png" width="236" height="106">

[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go)
[![go.dev](https://img.shields.io/badge/go.dev-reference-007d9c)](https://pkg.go.dev/github.com/lqs/sqlingo?tab=doc)
[![Travis CI](https://api.travis-ci.com/lqs/sqlingo.svg?branch=master)](https://app.travis-ci.com/github/lqs/sqlingo)
[![Go Report Card](https://goreportcard.com/badge/github.com/lqs/sqlingo)](https://goreportcard.com/report/github.com/lqs/sqlingo)
[![codecov](https://codecov.io/gh/lqs/sqlingo/branch/master/graph/badge.svg)](https://codecov.io/gh/lqs/sqlingo)
[![MIT license](http://img.shields.io/badge/license-MIT-9d1f14)](http://opensource.org/licenses/MIT)
[![last commit](https://img.shields.io/github/last-commit/lqs/sqlingo.svg)](https://github.com/lqs/sqlingo/commits)

**sqlingo** is a SQL DSL (a.k.a. SQL Builder or ORM) library in Go. It generates code from the database and lets you write SQL queries in an elegant way.

<img src="https://lqs-public-us-west.oss-us-west-1.aliyuncs.com/sqlingo/demo2.gif" width="443" height="297">

## Features
* Auto-generating DSL objects and model structs from the database so you don't need to manually keep things in sync
* SQL DML (SELECT / INSERT / UPDATE / DELETE) with some advanced SQL query syntaxes
* Many common errors could be detected at compile time
* Your can use the features in your editor / IDE, such as autocompleting the fields and queries, or finding the usage of a field or a table
* Context support
* Transaction support
* Interceptor support
* Golang time.Time is supported now, but you can still use the string type by adding `-timeAsString` when generating the model

## Database Support Status
| Database    | Status       |
------------- | --------------
| MySQL       | stable       |
| PostgreSQL  | experimental |
| SQLite      | experimental |

## Tutorial

### Install and use sqlingo code generator
The first step is to generate code from the database. In order to generate code, sqlingo requires your tables are already created in the database.

```
$ go install github.com/lqs/sqlingo/sqlingo-gen-mysql@latest
$ mkdir -p generated/sqlingo
$ sqlingo-gen-mysql root:123456@/database_name >generated/sqlingo/database_name.dsl.go
```


### Write your application
Here's a demonstration of some simple & advanced usage of sqlingo.
```go
package main

import (
    "github.com/lqs/sqlingo"
    . "./generated/sqlingo"
)

func main() {
    db, err := sqlingo.Open("mysql", "root:123456@/database_name")
    if err != nil {
        panic(err)
    }

    // a simple query
    var customers []*CustomerModel
    db.SelectFrom(Customer).
        Where(Customer.Id.In(1, 2)).
    	OrderBy(Customer.Name.Desc()).
        FetchAll(&customers)

    // query from multiple tables
    var customerId int64
    var orderId int64
    err = db.Select(Customer.Id, Order.Id).
        From(Customer, Order).
        Where(Customer.Id.Equals(Order.CustomerId), Order.Id.Equals(1)).
        FetchFirst(&customerId, &orderId)
    
    // subquery and count
    count, err := db.SelectFrom(Order)
        Where(Order.CustomerId.In(db.Select(Customer.Id).
            From(Customer).
            Where(Customer.Name.Equals("Customer One")))).
    	Count()
        
    // group-by with auto conversion to map
    var customerIdToOrderCount map[int64]int64
    err = db.Select(Order.CustomerId, f.Count(1)).
    	From(Order).
    	GroupBy(Order.CustomerId).
    	FetchAll(&customerIdToOrderCount)
    if err != nil {
    	println(err)
    }
    
    // insert some rows
    customer1 := &CustomerModel{name: "Customer One"}
    customer2 := &CustomerModel{name: "Customer Two"}
    _, err = db.InsertInto(Customer).
        Models(customer1, customer2).
        Execute()
    
    // insert with on-duplicate-key-update
    _, err = db.InsertInto(Customer).
    	Fields(Customer.Id, Customer.Name).
    	Values(42, "Universe").
    	OnDuplicateKeyUpdate().
    	Set(Customer.Name, Customer.Name.Concat(" 2")).
    	Execute()
}
```


sqlingo

# builq

[![build-img]][build-url]
[![pkg-img]][pkg-url]
[![reportcard-img]][reportcard-url]
[![coverage-img]][coverage-url]
[![version-img]][version-url]

Easily build queries in Go.

## Rationale

The simplest way to represent SQL query is a string.
But the query arguments and their indexing (`$1`, `$2` etc) require additional attention.
This tiny library helps to build queries and handles parameter indexing.

## Features

* Simple and easy.
* Safe and fast.
* Tested.
* Language-agnostic.
* Dependency-free.

See [docs][pkg-url] or [GUIDE.md](GUIDE.md) for more details.

## Install

Go version 1.19+

```
go get github.com/cristalhq/builq
```

## Example

```go
cols := builq.Columns{"foo, bar"}

q := builq.New()
q("SELECT %s FROM %s", cols, "users")
q("WHERE active IS TRUE")
q("AND user_id = %$ OR user = %$", 42, "root")

query, args, err := q.Build()
if err != nil {
	panic(err)
}

debug := q.DebugBuild()

fmt.Println("query:")
fmt.Println(query)
fmt.Println("\nargs:")
fmt.Println(args)
fmt.Println("\ndebug:")
fmt.Println(debug)

// query:
// SELECT foo, bar FROM users
// WHERE active IS TRUE
// AND user_id = $1 OR user = $2
//
// args:
// [42 root]
//
// debug:
// SELECT foo, bar FROM 'users'
// WHERE active IS TRUE
// AND user_id = 42 OR user = 'root'
```

See examples: [example_test.go](example_test.go).

## License

[MIT License](LICENSE).

[build-img]: https://github.com/cristalhq/builq/workflows/build/badge.svg
[build-url]: https://github.com/cristalhq/builq/actions
[pkg-img]: https://pkg.go.dev/badge/cristalhq/builq
[pkg-url]: https://pkg.go.dev/github.com/cristalhq/builq
[reportcard-img]: https://goreportcard.com/badge/cristalhq/builq
[reportcard-url]: https://goreportcard.com/report/cristalhq/builq
[coverage-img]: https://codecov.io/gh/cristalhq/builq/branch/main/graph/badge.svg
[coverage-url]: https://codecov.io/gh/cristalhq/builq
[version-img]: https://img.shields.io/github/v/release/cristalhq/builq
[version-url]: https://github.com/cristalhq/builq/releases


builq

SQL Query builder with better null values support

# GoSQL

[![Go Reference](https://pkg.go.dev/badge/github.com/twharmon/gosql.svg)](https://pkg.go.dev/github.com/twharmon/gosql) ![](https://github.com/twharmon/gosql/workflows/Test/badge.svg) [![](https://goreportcard.com/badge/github.com/twharmon/gosql)](https://goreportcard.com/report/github.com/twharmon/gosql) [![codecov](https://codecov.io/gh/twharmon/gosql/branch/main/graph/badge.svg?token=K0P59TPRAL)](https://codecov.io/gh/twharmon/gosql)

Query builder with some handy utility functions.

## Documentation
For full documentation see the [pkg.go.dev](https://pkg.go.dev/github.com/twharmon/gosql?tab=doc).

## Examples
```go
// Open database and create connection
sqliteDB, _ := sql.Open("sqlite3", "my-db.sql")
db := gosql.New(sqliteDB)

// Define a struct that includes a primary key
type User struct {
    ID       int `idx:"primary"`
    Email    string
    IsActive bool
}

// Insert a row into the table
db.Insert(&User{
    ID: 1,
    Email: "gopher@example.com",
    IsActive: true,
})

// Select a row from the table
var user User
db.Select("*").Where("id = ?", 1).Get(&user)

// Update the row in the table
user.Email = "gosql@example.com"
db.Update(&user)

// Delete the row from the table
db.Delete(&user)
```

## Benchmarks
```
BenchmarkInsert-10            	    5637	    209484 ns/op	     448 B/op	      23 allocs/op
BenchmarkUpdate-10            	   90866	     12887 ns/op	     576 B/op	      27 allocs/op
BenchmarkSelect-10            	   90318	     13125 ns/op	     768 B/op	      41 allocs/op
BenchmarkSelectMany-10        	   12435	     96761 ns/op	   10640 B/op	     838 allocs/op
BenchmarkSelectManyPtrs-10    	   10000	    100512 ns/op	   11248 B/op	     938 allocs/op
```

## Contribute
Make a pull request


gosql

# sqlc: A SQL Compiler

![go](https://github.com/sqlc-dev/sqlc/workflows/go/badge.svg)
[![Go Report Card](https://goreportcard.com/badge/github.com/sqlc-dev/sqlc)](https://goreportcard.com/report/github.com/sqlc-dev/sqlc)

sqlc generates **type-safe code** from SQL. Here's how it works:

1. You write queries in SQL.
1. You run sqlc to generate code with type-safe interfaces to those queries.
1. You write application code that calls the generated code.

Check out [an interactive example](https://play.sqlc.dev/) to see it in action, and the [introductory blog post](https://conroy.org/introducing-sqlc) for the motivation behind sqlc.

## Overview

- [Documentation](https://docs.sqlc.dev)
- [Installation](https://docs.sqlc.dev/en/latest/overview/install.html)
- [Playground](https://play.sqlc.dev)
- [Website](https://sqlc.dev)
- [Downloads](https://downloads.sqlc.dev/)
- [Community](https://discord.gg/EcXzGe5SEs)

## Supported languages

- [sqlc-gen-go](https://github.com/sqlc-dev/sqlc-gen-go)
- [sqlc-gen-kotlin](https://github.com/sqlc-dev/sqlc-gen-kotlin)
- [sqlc-gen-python](https://github.com/sqlc-dev/sqlc-gen-python)
- [sqlc-gen-typescript](https://github.com/sqlc-dev/sqlc-gen-typescript)

Additional languages can be added via [plugins](https://docs.sqlc.dev/en/latest/reference/language-support.html#community-language-support).

## Sponsors

Development is possible thanks to our sponsors. If you would like to support sqlc,
please consider [sponsoring on GitHub](https://github.com/sponsors/kyleconroy).

<p align="center">
  <a href="https://riza.io?utm_source=sqlc+readme"><img width=400 src="https://sqlc.dev/sponsors/riza-readme.png" alt="Riza.io"></a>
</p>

<p align="center">
  <a href="https://coder.com?utm_source=sqlc+readme"><img width=200 src="https://sqlc.dev/sponsors/coder-readme.png" alt="Coder.com" /></a>
  <a href="https://mint.fun?utm_source=sqlc+readme"><img width=200 src="https://sqlc.dev/sponsors/mint-readme.png" alt="Mint.fun" /></a>
  <a href="https://mux.com?utm_source=sqlc+readme"><img width=200 src="https://sqlc.dev/sponsors/mux-readme.png" alt="Mux.com" /></a>
</p>

<p align="center">
  <a href="https://github.com/Cyberax">Cyberax</a> - 
  <a href="https://github.com/NaNuNaNu">NaNuNaNu</a> - 
  <a href="https://github.com/Stumble">Stumble</a> - 
  <a href="https://github.com/WestfalNamur">WestfalNamur</a> - 
  <a href="https://github.com/alecthomas">alecthomas</a> - 
  <a href="https://github.com/cameronnewman">cameronnewman</a> - 
  <a href="https://github.com/danielbprice">danielbprice</a> - 
  <a href="https://github.com/davherrmann">davherrmann</a> - 
  <a href="https://github.com/dvob">dvob</a> - 
  <a href="https://github.com/gilcrest">gilcrest</a> - 
  <a href="https://github.com/gzuidhof">gzuidhof</a> - 
  <a href="https://github.com/jeffreylo">jeffreylo</a> - 
  <a href="https://github.com/mmcloughlin">mmcloughlin</a> - 
  <a href="https://github.com/ryohei1216">ryohei1216</a> - 
  <a href="https://github.com/sgielen">sgielen</a>
</p>


sqlc

Database Abstraction Layer (dbal) for go. Support SQL builder and get result easily

# godbal  [![GoDoc](https://godoc.org/github.com/xujiajun/godbal/driver/mysql?status.svg)](https://godoc.org/github.com/xujiajun/godbal/driver/mysql) [![GoDoc](https://godoc.org/github.com/xujiajun/godbal?status.svg)](https://godoc.org/github.com/xujiajun/godbal) [![Go Report Card](https://goreportcard.com/badge/github.com/xujiajun/godbal)](https://goreportcard.com/report/github.com/xujiajun/godbal)  <a href="https://travis-ci.org/xujiajun/godbal"><img src="https://travis-ci.org/xujiajun/godbal.svg?branch=master" alt="Build Status"></a> [![Coverage Status](https://coveralls.io/repos/github/xujiajun/godbal/badge.svg?branch=master)](https://coveralls.io/github/xujiajun/godbal?branch=master)  [![License](http://img.shields.io/badge/license-MIT-blue.svg?style=flat-square)](https://raw.githubusercontent.com/xujiajun/godbal/master/LICENSE) [![Awesome](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go#database)
Database Abstraction Layer (dbal) for go (now only support mysql)

## Motivation

I wanted a DBAL that ***No ORM***、***No Reflect***、***Concurrency Save***, support ***SQL builder***  following good practices and well tested code.

## Requirements

Go 1.7 or above.

## Installation

```
go get github.com/xujiajun/godbal
```

## Supported Databases

* mysql


## Getting Started

Godbal helps you build SQL queries from composable parts easily:

```
database, _ := godbal.NewMysql("root:123@tcp(127.0.0.1:3306)/test?charset=utf8").Open()
queryBuilder := mysql.NewQueryBuilder(database)
	sql := queryBuilder.Select("uid,username,price,flag").From("userinfo", "").SetFirstResult(0).
		SetMaxResults(3).OrderBy("uid", "DESC").GetSQL()

	fmt.Println(sql) 
```

Output:

```
SELECT uid,username,price,flag FROM userinfo ORDER BY uid DESC LIMIT 0,3
```

Godbal helps you get result easily:

```
rows, _ := queryBuilder.QueryAndGetMap()
jsonString, _ := json.Marshal(&rows)
fmt.Print(string(jsonString)) 

```

Output like:

```
 {"0":{"flag":"1","price":"111.00","uid":"6","username":"johnny2"},"1":{"flag":"1","price":"111.00","uid":"5","username":"johnny2"},"2":{"flag":"0","price":"123.99","uid":"4","username":"joe"}}
```

### Full example:

```
package main

import (
	"encoding/json"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	"github.com/xujiajun/godbal"
	"github.com/xujiajun/godbal/driver/mysql"
)

func main() {
	database, err := godbal.NewMysql("root:123@tcp(127.0.0.1:3306)/test?charset=utf8").Open()
	if err != nil {
		panic(err)
	}

	err = database.Ping()
	if err != nil {
		panic(err)
	}

	queryBuilder := mysql.NewQueryBuilder(database)
	sql := queryBuilder.Select("uid,username,price,flag").From("userinfo", "").SetFirstResult(0).
		SetMaxResults(3).OrderBy("uid", "DESC").GetSQL()

	fmt.Println(sql) // SELECT uid,username,price,flag FROM userinfo ORDER BY uid DESC LIMIT 0,3

	rows, _ := queryBuilder.QueryAndGetMap()

	jsonString, _ := json.Marshal(&rows)
	fmt.Print(string(jsonString)) 
  // result like: {"0":{"flag":"1","price":"111.00","uid":"6","username":"johnny2"},"1":{"flag":"1","price":"111.00","uid":"5","username":"johnny2"},"2":{"flag":"0","price":"123.99","uid":"4","username":"joe"}}
}

```

## More examples

* [select](https://github.com/xujiajun/godbal/blob/master/examples/select/main.go)

* [insert](https://github.com/xujiajun/godbal/blob/master/examples/insert/main.go)

* [delete](https://github.com/xujiajun/godbal/blob/master/examples/delete/main.go)

* [update](https://github.com/xujiajun/godbal/blob/master/examples/update/main.go)

* [join](https://github.com/xujiajun/godbal/blob/master/examples/join/main.go)

* [transaction](https://github.com/xujiajun/godbal/blob/master/examples/transaction/main.go)

## Contributing

If you'd like to help out with the project. You can put up a Pull Request.

## Author

* [xujiajun](https://github.com/xujiajun)

## License

The godbal is open-sourced software licensed under the [MIT Licensed](http://www.opensource.org/licenses/MIT)


godbal

Fast and cheap SQL builder which does only one thing, SQL building

![image](https://github.com/user-attachments/assets/68470c45-ef4c-41a5-8129-4213429503f3)


# Obreron

Fast and cheap sql builder.

[![Go Reference](https://pkg.go.dev/badge/github.com/profe-ajedrez/obreron/v2.svg)](https://pkg.go.dev/github.com/profe-ajedrez/obreron/v2)
[![Go Report Card](https://goreportcard.com/badge/github.com/profe-ajedrez/obreron/v2)](https://goreportcard.com/report/github.com/profe-ajedrez/obreron/v2)
[![Coverage Status](https://coveralls.io/repos/github/profe-ajedrez/obreron/badge.svg?branch=v2)](https://coveralls.io/github/profe-ajedrez/obreron?branch=v2)
[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge-flat.svg)](https://github.com/avelino/awesome-go)

## Supported Dialects

- [x] Mysql
- [ ] Postgresql (Work in progress)


## Why?

Because writing SQL in Go is boring.

## Instalation

Use `go get` to install v2

```bash
$ go get github.com/profe-ajedrez/obreron/v2
```

## Use


Import package

```go
import (
	v2 "github.com/profe-ajedrez/obreron/v2"
)
```

### Select

* Simple select

```go
// Produces SELECT a1, a2, a3 FROM client
query, _ := v2.Select().Col("a1, a2, a3").From("client").Build()
r, error := db.Query(query)
```

* Select/join/where/shuffled

```go
// Produces SELECT a1, a2, ? AS diez, colIf1, colIf2, ? AS zero, a3, ? AS cien FROM client c JOIN addresses a ON a.id_cliente = a.id_cliente JOIN phones p ON p.id_cliente = c.id_cliente JOIN mailes m ON m.id_cliente = m.id_cliente AND c.estado_cliente = ? LEFT JOIN left_joined lj ON lj.a1 = c.a1 WHERE a1 = ? AND a2 = ? AND a3 = 10 AND a16 = ?
// with params = []any{10, 0, 100, 0, "'last name'", 1000.54, 75}
query, params := v2.Select().
    Where("a1 = ?", "'last name'").
    Col("a1, a2, ? AS diez", 10).
    Col(`colIf1, colIf2, ? AS zero`, 0).
    Col("a3, ? AS cien", 100).    
    Where("a2 = ?", 1000.54).
    And("a3 = 10").And("a16 = ?", 75).
    Join("addresses a ON a.id_cliente = a.id_cliente").
    Join("phones p").On("p.id_cliente = c.id_cliente").
    Join("mailes m").On("m.id_cliente = m.id_cliente").
    And("c.estado_cliente = ?", 0).    
    LeftJoin("left_joined lj").On("lj.a1 = c.a1").
    From("client c").
    Build()

r, error := db.Query(query, params...)
```

Note that in this example we purposely shuffled the order of the clauses and yet the query was built correctly

* Conditional elements

Sometimes we need to check for a condition to build dynamic sql

This example adds the column `name` to the query only if the variable `shouldAddName` is true.

```go
query, _ := v2.Select().
	Col("a1, a2, a3").
	ColIf(shouldAddName, "name")
	From("client").
	Build()

// Produces "SELECT a1, a2, a3 FROM client" when shouldAddName is false
// Produces "SELECT a1, a2, a3, name FROM client" when shouldAddName is true
```

This also can be applied to joins.

```go
query, _ := v2.Select().
	Col("*").
	From("client c").
	Join("addresses a").On("a.client_id = c.client_id").
    JoinIf(shouldGetPhones, "phones p ON p.client_id = c.client_id").
    Build()

// Produces "SELECT * FROM client c JOIN a.client_id = c.client_id" if shouldGetPhones is false
// Produces "SELECT * FROM client c JOIN a.client_id = c.client_id JOIN phones p ON p.client_id = c.client_id" " if shouldGetPhones is true
```

And boolean connectors

```go
query, _ := v2.Select().
	Col("*").
	From("client c").	
	Where("c.status = 0").AndIf(shouldFilterByCountry, "country = 'CL'").
    Build()

// Produces "SELECT * FROM client c WHERE c.status = 0" when shouldFilterByCountry is false
// Produces "SELECT * FROM client c WHERE c.status = 0 AND country = 'CL'" when shouldFilterByCountry is true
```

* Params

You can add params to almost any clause

```go
query, params := v2.Select().
	Col("name, mail, ? AS max_credit", 1000000).
	From("client c").	
	Where("c.status = 0").And("country = ?", "CL").
    Limit("?", "100").Build()
```


### Delete

* Simple delete

```go
query, _ := v2.Delete().From("client").Build()
// Produces "DELETE FROM client"
```

* Simple del where

```go
query, _ := v2.Delete().From("client").Where("client_id = 100").Build()
// Produces "DELETE FROM client WHERE client_id = 100"
```

* Like with Select you can use parameters and conditionals with Delete

```go
query, params := v2.Delete().From("client").Where("client_id = ?", 100).Build()
// Produces "DELETE FROM client WHERE client_id = ?"
```

```go
query, params := v2.Delete().From("client").Where("1=1").AndIf(filterByClient, "client_id = ?", 100).Build()
// Produces "DELETE FROM client WHERE 1=1" when filterByClient is false
// Produces "DELETE FROM client WHERE 1=1 AND client_id = ?" when filterByClient is true
```


### Update

* Simple update

```go
query, _ := v2.Update("client").Set("status = 0").Build()
// Produces UPDATE client SET status = 0
```

* Update/where/order/limit

```go
query, _ := v2.Update("client").
	Set("status = 0").
	Where("status = ?", 1).
	OrderBy("ciudad").
	Limit(10).
	Build()
```

* You can use obreron to build an update/join query

```go
query, _ := v2.Update("business AS b").
Join("business_geocode AS g").On("b.business_id = g.business_id").
Set("b.mapx = g.latitude, b.mapy = g.longitude").
Where("(b.mapx = '' or b.mapx = 0)").
And("g.latitude > 0").
Build()

// Produces "UPDATE business AS b JOIN business_geocode AS g ON b.business_id = g.business_id SET b.mapx = g.latitude, b.mapy = g.longitude WHERE (b.mapx = '' or b.mapx = 0) AND g.latitude > 0"
```

* You can use obreron to build an update/select query

```go
query, _ := v2.Update("items").
				ColSelect(Select().Col("id, retail / wholesale AS markup, quantity").From("items"), "discounted").
				Set("items.retail = items.retail * 0.9").
				Where("discounted.markup >= 1.3").
				And("discounted.quantity < 100").
				And("items.id = discounted.id").
	            Build()
// Produces UPDATE items ,( SELECT id, retail / wholesale AS markup, quantity FROM items ) discounted SET items.retail = items.retail * 0.9 WHERE discounted.markup >= 1.3 AND discounted.quantity < 100 AND items.id = discounted.id
```

## Insert

* Simple insert

```go
query, params := Iv2.nsert().
	Into("client").
	Col("name, value", "'some name'", "'somemail@mail.net'").
    Build()

// Produces "INSERT INTO client ( name, value ) VALUES ( ?, ? )"
```

* insert select

```go
query, params := v2.Insert().
    Into("courses").
    ColSelect("name, location, gid", 
		Select().
		Col("name, location, 1").
	    From("courses").
	    Where("cid = 2")
	).Build()

// Produces       "INSERT INTO courses ( name, location, gid ) SELECT name, location, 1 FROM courses WHERE cid = 2"
```

## Other clauses

You can add others clauses using the `Clause` method

```go
query, params := v2.Insert().Clause("IGNORE", "")
	Into("client").
	Col("name, value", "'some name'", "'somemail@mail.net'").
    Build()

// Produces "INSERT IGNORE INTO client ( name, value ) VALUES ( ?, ? )"
```

The `Clause` method always will inject the clause after the last uses building command


obreron

Powerful SQL Query builder that automatically generates SQL queries from structs

# Patcher

[![Go Reference](https://pkg.go.dev/badge/github.com/jacobbrewer1/patcher.svg)](https://pkg.go.dev/github.com/jacobbrewer1/patcher)
[![Go Report Card](https://goreportcard.com/badge/github.com/jacobbrewer1/patcher)](https://goreportcard.com/report/github.com/jacobbrewer1/patcher)

Patcher is a GO library that provides a simple way to generate and SQL patches from structs. The library was built out
of the need to generate patches for a database; when a new field is added to a struct, this would result in a bunch of
new `if` checks to be created in the codebase. This library aims to solve that problem by generating the SQL patches for
you.

## What is Patcher?

* **Automatic SQL Generation**: It automatically generates SQL UPDATE queries from structs, reducing the need for
  manually
  writing and maintaining SQL statements.
* **Code Simplification**: It reduces the amount of boilerplate code and if-else conditions required to handle different
  struct fields, making the codebase cleaner and easier to maintain.
* **Struct Diffs**: It allows injecting changes from one struct to another and generating update scripts based on
  differences, streamlining the process of synchronizing data changes.
* **Join Support**: It supports generating SQL joins by creating structs that implement the Joiner interface,
  simplifying the process of managing related data across multiple tables.

## Why Use Patcher?

* **Saves Time**: It saves time by automatically generating SQL queries from structs, reducing the need to write and
  maintain SQL statements manually.
* **Reduces Errors**: It reduces the risk of errors by automatically generating SQL queries based on struct fields,
  eliminating the need to manually update queries when struct fields change.
* **Simplifies Code**: It simplifies the codebase by reducing the amount of boilerplate code and if-else conditions
  required to handle different struct fields, making the code easier to read and maintain.
* **Streamlines Data Synchronization**: It streamlines the process of synchronizing data changes by allowing you to
  inject changes from one struct to another and generate update scripts based on differences.
* **Supports Joins**: It supports generating SQL joins by creating structs that implement the Joiner interface, making
  it easier to manage related data across multiple tables.
* **Flexible Configuration**: It provides flexible configuration options to customize the SQL generation process, such
  as including zero or nil values in the diff.
* **Easy Integration**: It is easy to integrate into existing projects and can be used with any Go project that needs to
  generate SQL queries from structs.
* **Open Source**: It is open-source and available under the Apache 2.0 license.

### Demonstration

Here is a simple example of how Patcher would compare to an example of not using Patcher.

#### Without Patcher

```go
package main

import (
	"database/sql"
	"encoding/json"
	"errors"
	"net/http"
	"strconv"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/gorilla/mux"
)

type User struct {
	ID    int     `json:"id"`
	Name  *string `json:"name"`
	Email *string `json:"email"`
}

func updateUser(db *sql.DB, user User) error {
	query := "UPDATE users SET"
	var updates []string
	var args []any

	if user.Name != nil {
		updates = append(updates, "name = ?")
		args = append(args, *user.Name)
	}
	if user.Email != nil {
		updates = append(updates, "email = ?")
		args = append(args, *user.Email)
	}

	if len(updates) == 0 {
		return errors.New("no fields to update")
	}

	query += " " + strings.Join(updates, ", ") + " WHERE id = ?"
	args = append(args, user.ID)

	_, err := db.Exec(query, args...)
	return err
}

func patchHandler(w http.ResponseWriter, r *http.Request) {
	db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer db.Close()

	vars := mux.Vars(r)
	userIDStr := vars["id"]
	userID, err := strconv.Atoi(userIDStr)
	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	user := User{ID: userID}
	if err := json.NewDecoder(r.Body).Decode(&user); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	if err := updateUser(db, user); err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	w.WriteHeader(http.StatusOK)
}

func main() {
	r := mux.NewRouter()
	r.HandleFunc("/users/{id}", patchHandler).Methods("PATCH")

	server := &http.Server{
		Addr:              ":8080",
		Handler:           r,
		ReadHeaderTimeout: 10 * time.Second,
	}

	if err := server.ListenAndServe(); err != nil {
		panic(err)
	}
}

```

As demonstrated above, without Patcher, you would need to manually write the SQL update query and handle each field. If
a new field is added to the struct, you would need to update the query manually, which can be error-prone (e.g., missing
or forgetting to update a field) and time-consuming.

#### With Patcher

```go
package main

import (
	"database/sql"
	"encoding/json"
	"net/http"
	"strconv"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/gorilla/mux"
	"github.com/jacobbrewer1/patcher"
)

type User struct {
	ID    *int    `db:"id" json:"id,omitempty"`
	Name  *string `db:"name" json:"name,omitempty"`
	Email *string `db:"email" json:"email,omitempty"`
}

type UserWhere struct {
	ID *int `db:"id"`
}

func NewUserWhere(id int) *UserWhere {
	return &UserWhere{ID: &id}
}

func (u *UserWhere) Where() (sqlStr string, sqlArgs []any) {
	return "id = ?", []any{*u.ID}
}

func patchHandler(w http.ResponseWriter, r *http.Request) {
	db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer db.Close()

	vars := mux.Vars(r)
	idStr := vars["id"]
	parsedID, err := strconv.Atoi(idStr)
	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	user := new(User)
	if err := json.NewDecoder(r.Body).Decode(user); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	user.ID = &parsedID
	condition := NewUserWhere(parsedID)

	sqlStr, args, err := patcher.GenerateSQL(
		user,
		patcher.WithTable("users"),
		patcher.WithWhere(condition),
	)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	_, execErr := db.Exec(sqlStr, args...)
	if execErr != nil {
		http.Error(w, execErr.Error(), http.StatusInternalServerError)
		return
	}

	w.WriteHeader(http.StatusOK)
}

func main() {
	r := mux.NewRouter()
	r.HandleFunc("/users/{id}", patchHandler).Methods("PATCH")

	server := &http.Server{
		Addr:              ":8080",
		Handler:           r,
		ReadHeaderTimeout: 5 * time.Second,
	}

	if err := server.ListenAndServe(); err != nil {
		panic(err)
	}
}

```

With Patcher, you can generate the SQL update query automatically from the struct fields, reducing the need to write and
maintain the query manually. If a new field is added to the struct, Patcher will automatically include it in the update
query, simplifying the code and reducing the risk of errors.

## Usage

### Configuration

#### LoadDiff Options

* `includeZeroValues`: Set to true to include zero values in the diff.
* `includeNilValues`: Set to true to include nil values in the diff.

#### GenerateSQL Options

* `WithTable(tableName string)`: Specify the table name for the SQL query.
* `WithWhere(whereClause Wherer)`: Provide a where clause for the SQL query.
    * You can pass a struct that implements the `WhereTyper` interface to use `OR` in the where clause. Patcher will
      default to `AND` if the `WhereTyper` interface is not implemented.
* `WithJoin(joinClause Joiner)`: Add join clauses to the SQL query.
* `WithDialect(dialect SQLDialect)`: Specify the SQL dialect for parameter placeholders.
    * `DialectMySQL` (default): Uses `?` parameter placeholders
    * `DialectSQLite`: Uses `?` parameter placeholders (same as MySQL)
    * `DialectPostgreSQL`: Uses `$1, $2, $3` parameter placeholders
* `includeZeroValues`: Set to true to include zero values in the Patch.
* `includeNilValues`: Set to true to include nil values in the Patch.

### Basic Examples

#### Basic

To use the library, you need to create a struct that represents the table you want to generate patches for. The struct
should have the following tags:

- `db:"column_name"`: This tag is used to specify the column name in the database.

Example:

```go
package main

import (
	"encoding/json"
	"fmt"

	"github.com/jacobbrewer1/patcher"
)

type Person struct {
	ID   *int    `db:"-"`
	Name *string `db:"name"`
}

type PersonWhere struct {
	ID *int `db:"id"`
}

func NewPersonWhere(id int) *PersonWhere {
	return &PersonWhere{
		ID: &id,
	}
}

func (p *PersonWhere) Where() (string, []any) {
	return "id = ?", []any{*p.ID}
}

func main() {
	const jsonStr = `{"id": 1, "name": "john"}`

	person := new(Person)
	if err := json.Unmarshal([]byte(jsonStr), person); err != nil {
		panic(err)
	}

	condition := NewPersonWhere(*person.ID)

	sqlStr, args, err := patcher.GenerateSQL(
		person,
		patcher.WithTable("people"),
		patcher.WithWhere(condition),
	)
	if err != nil {
		panic(err)
	}

	fmt.Println(sqlStr)

	fmt.Println(args)
}

```

This will output:

```sql
UPDATE people
SET name = ?
WHERE (1 = 1)
  AND (
    id = ?
    )
```

with the args:

```
["john", 1]
```

#### Struct diffs

The Patcher library has functionality where you are able to inject changes from one struct to another. This is
configurable to include Zero values and Nil values if requested. Please see the
example [here](./examples/loader_with_opts) for the detailed example. Below is an example on how you can utilize this
method with the default behaviour (Please see the comment attached to the `LoadDiff` [method](./loader.go) for the
default behaviour).

Example:

```go
package main

import (
	"fmt"

	"github.com/jacobbrewer1/patcher"
)

type Something struct {
	Number       int
	Text         string
	PrePopulated string
	NewText      string
}

func main() {
	s := Something{
		Number:       5,
		Text:         "Hello",
		PrePopulated: "PrePopulated",
	}

	n := Something{
		Number:  6,
		Text:    "Old Text",
		NewText: "New Text",
	}

	// The patcher.LoadDiff function will apply the changes from n to s.
	if err := patcher.LoadDiff(&s, &n); err != nil {
		panic(err)
	}

	fmt.Println(s.Number)
	fmt.Println(s.Text)
	fmt.Println(s.PrePopulated)
	fmt.Println(s.NewText)
}

```

This will output:

```
6
Hello
PrePopulated
New Text
```

If you would like to generate an update script from two structs, you can use the `NewDiffSQLPatch` function. This
function will generate an update script from the two structs.

Example:

```go
package main

import (
	"fmt"

	"github.com/jacobbrewer1/patcher"
)

type Something struct {
	Number       int
	Text         string
	PrePopulated string
	NewText      string
}

type SomeWhere struct {
	id int
}

func NewSomeWhere(id int) *SomeWhere {
	return &SomeWhere{id: id}
}

func (s *SomeWhere) Where() (string, []any) {
	return "id = ?", []any{s.id}
}

func main() {
	s := Something{
		Number:       5,
		Text:         "Old Text",
		PrePopulated: "PrePopulated",
		NewText:      "New Text",
	}

	n := Something{
		Number:       5,
		Text:         "Old Text",
		PrePopulated: "PrePopulatedDifferent",
		NewText:      "New Text",
	}

	wherer := NewSomeWhere(5)

	// The patcher.LoadDiff function will apply the changes from n to s.
	patch, err := patcher.NewDiffSQLPatch(
		&s,
		&n,
		patcher.WithTable("table_name"),
		patcher.WithWhere(wherer),
	)
	if err != nil {
		panic(err)
	}

	sqlStr, sqlArgs, err := patch.GenerateSQL()
	if err != nil {
		panic(err)
	}

	fmt.Println(sqlStr)
	fmt.Println(sqlArgs)
}

```

This will output:

```sql
UPDATE table_name
SET pre_populated = ?
WHERE (1 = 1)
  AND (
    id = ?
    )
```

with the args:

```
["PrePopulatedDifferent", 5]
```

You can also take a look at the Loader [examples](./examples) for more examples on how to use the library for this
approach.

#### Using `OR` in the where clause

If you would like to use `OR` in the where clause, you can apply the `patcher.WhereTyper` interface to your where
struct. Please take a look at the [example here](./examples/where_type).

#### PostgreSQL Support

Patcher supports PostgreSQL parameter placeholders (`$1, $2, $3`) by using the `WithDialect` option:

```go
package main

import (
	"encoding/json"
	"fmt"

	"github.com/jacobbrewer1/patcher"
)

type User struct {
	ID    *int    `db:"id" patcher:"-" json:"id,omitempty"`
	Name  *string `db:"name" json:"name,omitempty"`
	Email *string `db:"email" json:"email,omitempty"`
}

type UserWhere struct {
	ID *int `db:"id"`
}

func NewUserWhere(id int) *UserWhere {
	return &UserWhere{ID: &id}
}

func (u *UserWhere) Where() (sqlStr string, sqlArgs []any) {
	return "id = ?", []any{*u.ID}
}

func main() {
	const jsonStr = `{"id": 1, "name": "john", "email": "john@example.com"}`

	user := new(User)
	if err := json.Unmarshal([]byte(jsonStr), user); err != nil {
		panic(err)
	}

	condition := NewUserWhere(*user.ID)

	// Generate SQL for PostgreSQL
	sqlStr, args, err := patcher.GenerateSQL(
		user,
		patcher.WithTable("users"),
		patcher.WithWhere(condition),
		patcher.WithDialect(patcher.DialectPostgreSQL),
	)
	if err != nil {
		panic(err)
	}

	fmt.Println(sqlStr)
	fmt.Println(args)
}
```

This will output:

```sql
UPDATE users
SET name = $1, email = $2
WHERE (1=1)
AND (
id = $3
)
```

with the args:

```
["john", "john@example.com", 1]
```

### Joins

To generate a join, you need to create a struct that represents the join. This struct should implement
the [Joiner](./joiner.go) interface.

Once you have the join struct, you can pass it to the `GenerateSQL` function using the `WithJoin` option. You can add as
many of these as you would like.

## Installation

To install the Patcher library, use the following command:

```sh
go get github.com/jacobbrewer1/patcher
```

## Examples

You can find examples of how to use this library in the [examples](./examples) directory.

## Contributing

We welcome contributions! Please follow these steps to contribute:

1. Fork the repository.
2. Create a new branch for your feature or bugfix.
3. Write tests for your changes.
4. Run the tests to ensure everything works.
5. Submit a pull request.

To run tests, use the following command:

```sh
go test ./...
```


patcher

Type-safe database query builder with zero production dependencies, LRU statement cache, batch operations, and support for JOINs, subqueries, CTEs, and window functions

The Best Go Libraries For Database - SQL Query Builders (26)