Query Builders

Package jet is a framework for writing type-safe SQL queries in Go, with ability to easily convert database query result into desired arbitrary object structure. Use the bellow command to install jet Install jet generator to GOPATH bin folder. This will allow generating jet files from the command line. *Make sure GOPATH bin folder is added to the PATH environment variable. Jet requires already defined database schema(with tables, enums etc), so that jet generator can generate SQL Builder and Model files. File generation is very fast, and can be added as every pre-build step. Sample command: Then next step is to import generated SQL Builder and Model files and write SQL queries in Go: To write SQL queries for PostgreSQL import: To write SQL queries for MySQL and MariaDB import: *Dot import is used so that Go code resemble as much as native SQL. Dot import is not mandatory. Write SQL: Store result into desired destination: Detail info about all features and use cases can be found at project wiki page - https://github.com/go-jet/jet/wiki.

Package dali wraps the sql.DB and provides convenient API for building database driven applications. Its main goal is to create a unified way of handling placeholders among all drivers and to simplify some common, repetive queries. There is no support for query builders (you have to write pure SQL queries). It focuses on the common queries (like writing INSERTs or UPDATEs) and on loading of results into structs, for which it provides easy-to-write alternatives. The following is the complete list of possible placeholders that can be used when writing a query using Query method. dali has also a support for prepared statements. However, it doesn't support certain placeholders. Only ?ident, ?ident..., and ?sql placeholders are allowed in the phase of the query building (befored the statement is prepared). The ? placeholder is the only one left for parameter binding. So working with prepared statements can look like this:

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

Provides SQL table metadata, enabling select field lists, easy getters, relations when using a query builder.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/lucidstats-dev/goqu for an introduction to goqu.

Package jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automatic query result data mapping. Jet currently supports PostgreSQL, MySQL, MariaDB and SQLite. Future releases will add support for additional databases. Use the command bellow to add jet as a dependency into go.mod project: Jet generator can be installed in one of the following ways: (Go1.16+) Install jet generator using go install: go install github.com/go-jet/jet/v2/cmd/jet@latest Install jet generator to GOPATH/bin folder: cd $GOPATH/src/ && GO111MODULE=off go get -u github.com/go-jet/jet/cmd/jet Install jet generator into specific folder: git clone https://github.com/go-jet/jet.git cd jet && go build -o dir_path ./cmd/jet Make sure that the destination folder is added to the PATH environment variable. Jet requires already defined database schema(with tables, enums etc), so that jet generator can generate SQL Builder and Model files. File generation is very fast, and can be added as every pre-build step. Sample command: Before we can write SQL queries in Go, we need to import generated SQL builder and model types: To write postgres SQL queries we import: Then we can write the SQL query: Now we can run the statement and store the result into desired destination: We can print a statement to see SQL query and arguments sent to postgres server: Output: If we print destination as json, we'll get: Detail info about all statements, features and use cases can be found at project wiki page - https://github.com/go-jet/jet/wiki.

Package sqlz implements an SQL query builder based on github.com/jmoiron/sqlx.

Package dbx provides a set of DB-agnostic and easy-to-use query building methods for relational databases. This example shows how to do CRUD operations. This example shows how to populate DB data in different ways. This example shows how to use query builder to build DB queries. This example shows how to use query builder in transactions.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/kovetskiy/goqu for an introduction to goqu.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

Package jet is a framework for writing type-safe SQL queries in Go, with ability to easily convert database query result into desired arbitrary object structure. Use the bellow command to install jet Install jet generator to GOPATH bin folder. This will allow generating jet files from the command line. *Make sure GOPATH bin folder is added to the PATH environment variable. Jet requires already defined database schema(with tables, enums etc), so that jet generator can generate SQL Builder and Model files. File generation is very fast, and can be added as every pre-build step. Sample command: Then next step is to import generated SQL Builder and Model files and write SQL queries in Go: To write SQL queries for PostgreSQL import: To write SQL queries for MySQL and MariaDB import: *Dot import is used so that Go code resemble as much as native SQL. Dot import is not mandatory. Write SQL: Store result into desired destination: Detail info about all features and use cases can be found at project wiki page - https://github.com/go-jet/jet/wiki.

Package dotsql provides a way to separate your code from SQL queries. 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. For more usage examples see https://github.com/gchaincl/dotsql

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

package osquery provides a non-obtrusive, idiomatic and easy-to-use query and aggregation builder for the official Go client (https://github.com/elastic/go-elasticsearch) for the ElasticSearch database (https://www.elastic.co/products/elasticsearch). osquery alleviates the need to use extremely nested maps (map[string]interface{}) and serializing queries to JSON manually. It also helps eliminating common mistakes such as misspelling query types, as everything is statically typed. Using `osquery` can make your code much easier to write, read and maintain, and significantly reduce the amount of code you write. osquery provides a method chaining-style API for building and executing queries and aggregations. It does not wrap the official Go client nor does it require you to change your existing code in order to integrate the library. Queries can be directly built with `osquery`, and executed by passing an `*opensearch.Client` instance (with optional search parameters). Results are returned as-is from the official client (e.g. `*opensearchapi.Response` objects). Getting started is extremely simple: osquery currently supports version 7 of the ElasticSearch Go client. The library cannot currently generate "short queries". For example, whereas ElasticSearch can accept this: { "query": { "term": { "user": "Kimchy" } } } The library will always generate this: This is also true for queries such as "bool", where fields like "must" can either receive one query object, or an array of query objects. `osquery` will generate an array even if there's only one query object.

Package dbx provides a set of DB-agnostic and easy-to-use query building methods for relational databases. This example shows how to do CRUD operations. This example shows how to populate DB data in different ways. This example shows how to use query builder to build DB queries. This example shows how to use query builder in transactions.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

Package dotsql provides a way to separate your code from SQL queries. 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. For more usage examples see https://github.com/gchaincl/dotsql

Package rds provides the client and types for making API requests to Amazon Relational Database Service. Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks, freeing up developers to focus on what makes their applications and businesses unique. Amazon RDS gives you access to the capabilities of a MySQL, MariaDB, PostgreSQL, Microsoft SQL Server, Oracle, or Amazon Aurora database server. These capabilities mean that the code, applications, and tools you already use today with your existing databases work with Amazon RDS without modification. Amazon RDS automatically backs up your database and maintains the database software that powers your DB instance. Amazon RDS is flexible: you can scale your database instance's compute resources and storage capacity to meet your application's demand. As with all Amazon Web Services, there are no up-front investments, and you pay only for the resources you use. This interface reference for Amazon RDS contains documentation for a programming or command line interface you can use to manage Amazon RDS. Note that Amazon RDS is asynchronous, which means that some interfaces might require techniques such as polling or callback functions to determine when a command has been applied. In this reference, the parameter descriptions indicate whether a command is applied immediately, on the next instance reboot, or during the maintenance window. The reference structure is as follows, and we list following some related topics from the user guide. Amazon RDS API Reference For the alphabetical list of API actions, see API Actions (http://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_Operations.html). For the alphabetical list of data types, see Data Types (http://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_Types.html). For a list of common query parameters, see Common Parameters (http://docs.aws.amazon.com/AmazonRDS/latest/APIReference/CommonParameters.html). For descriptions of the error codes, see Common Errors (http://docs.aws.amazon.com/AmazonRDS/latest/APIReference/CommonErrors.html). Amazon RDS User Guide For a summary of the Amazon RDS interfaces, see Available RDS Interfaces (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Welcome.html#Welcome.Interfaces). For more information about how to use the Query API, see Using the Query API (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Using_the_Query_API.html). See https://docs.aws.amazon.com/goto/WebAPI/rds-2014-10-31 for more information on this service. See rds package documentation for more information. https://docs.aws.amazon.com/sdk-for-go/api/service/rds/ To Amazon Relational Database Service with the SDK use the New function to create a new service client. With that client you can make API requests to the service. These clients are safe to use concurrently. See the SDK's documentation for more information on how to use the SDK. https://docs.aws.amazon.com/sdk-for-go/api/ See aws.Config documentation for more information on configuring SDK clients. https://docs.aws.amazon.com/sdk-for-go/api/aws/#Config See the Amazon Relational Database Service client RDS for more information on creating client for this service. https://docs.aws.amazon.com/sdk-for-go/api/service/rds/#New The rdsutil package's BuildAuthToken function provides a connection authentication token builder. Given an endpoint of the RDS database, AWS region, DB user, and AWS credentials the function will create an presigned URL to use as the authentication token for the database's connection. The following example shows how to use BuildAuthToken to create an authentication token for connecting to a MySQL database in RDS. See rdsutil package for more information. http://docs.aws.amazon.com/sdk-for-go/api/service/rds/rdsutils/

Package sqlz implements an SQL query builder based on github.com/jmoiron/sqlx.

Package dotsql provides a way to separate your code from SQL queries. 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. For more usage examples see https://github.com/gchaincl/dotsql

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

Package jet is a framework for writing type-safe SQL queries in Go, with ability to easily convert database query result into desired arbitrary object structure. Use the bellow command to install jet Install jet generator to GOPATH bin folder. This will allow generating jet files from the command line. *Make sure GOPATH bin folder is added to the PATH environment variable. Jet requires already defined database schema(with tables, enums etc), so that jet generator can generate SQL Builder and Model files. File generation is very fast, and can be added as every pre-build step. Sample command: Then next step is to import generated SQL Builder and Model files and write SQL queries in Go: To write SQL queries for PostgreSQL import: To write SQL queries for MySQL and MariaDB import: *Dot import is used so that Go code resemble as much as native SQL. Dot import is not mandatory. Write SQL: Store result into desired destination: Detail info about all features and use cases can be found at project wiki page - https://github.com/go-jet/jet/wiki.

Package dotsql provides a way to separate your code from SQL queries. 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. For more usage examples see https://github.com/gchaincl/dotsql

Package sqldimel provides a SQL DML query builder. Package sqldimel provides a SQL DML query builder.

Package gocqlx is an idiomatic extension to gocql that provides usability features. With gocqlx you can bind the query parameters from maps and structs, use named query parameters (:identifier) and scan the query results into structs and slices. It comes with a fluent and flexible CQL query builder and a database migrations module.

Package godb is query builder and struct mapper. godb does not manage relationships like Active Record or Entity Framework, it's not a full-featured ORM. Its goal is to be more productive than manually doing mapping between Go structs and databases tables. godb needs adapters to use databases, some are packaged with godb for : Start with an adapter, and the Open method which returns a godb.DB pointer : There are three ways to executes SQL with godb : Using raw queries you can execute any SQL queries and get the results into a slice of structs (or single struct) using the automatic mapping. Structs tools looks more 'orm-ish' as they're take instances of objects or slices to run select, insert, update and delete. Statements tools stand between raw queries and structs tools. It's easier to use than raw queries, but are limited to simpler cases. The statements tools are based on types : Example : The SelectStatement type could also build a query using columns from a structs. It facilitates the build of queries returning values from multiple table (or views). See struct mapping explanations, in particular the `rel` part. Example : The structs tools are based on types : Examples : Raw queries are executed using the RawSQL type. The query could be a simple hand-written string, or something complex builded using SQLBuffer and Conditions. Example : Stucts contents are mapped to databases columns with tags, like in previous example with the Book struct. The tag is 'db' and its content is : For autoincrement identifier simple use both 'key' and 'auto'. Example : More than one field could have the 'key' keyword, but with most databases drivers none of them could have the 'auto' keyword, because executing an insert query only returns one value : the last inserted id : https://golang.org/pkg/database/sql/driver/#RowsAffected.LastInsertId . With PostgreSQL you cas have multiple fields with 'key' and 'auto' options. Structs could be nested. A nested struct is mapped only if has the 'db' tag. The tag value is a columns prefix applied to all fields columns of the struct. The prefix is not mandatory, a blank string is allowed (no prefix). A nested struct could also have an optionnal `rel` attribute of the form `rel=relationname`. It's useful to build a select query using multiples relations (table, view, ...). See the example using the BooksWithInventories type. Example Databases columns are : The mapping is managed by the 'dbreflect' subpackage. Normally its direct use is not necessary, except in one case : some structs are scannable and have to be considered like fields, and mapped to databases columns. Common case are time.Time, or sql.NullString, ... You can register a custom struct with the `RegisterScannableStruct` and a struct instance, for example the time.Time is registered like this : The structs statements use the struct name as table name. But you can override this simply by simplementing a TableName method : Statements and structs tools manage 'where' and 'group by' sql clauses. These conditional clauses are build either with raw sql code, or build with the Condition struct like this : WhereQ methods take a Condition instance build by godb.Q . Where mathods take raw SQL, but is just a syntactic sugar. These calls are equivalents : Multiple calls to Where or WhereQ are allowed, these calls are equivalents : Slices are managed in a particular way : a single placeholder is replaced with multiple ones. This allows code like : The SQLBuffer exists to ease the build of complex raw queries. It's also used internaly by godb. Its use and purpose are simple : concatenate sql parts (accompagned by their arguments) in an efficient way. Example : For all databases, structs updates and deletes manage optimistic locking when a dedicated integer row is present. Simply tags it with `oplock` : When an update or delete operation fails, Do() returns the `ErrOpLock` error. With PostgreSQL and SQL Server, godb manages optimistic locking with automatic fields. Just add a dedicated field in the struct and tag it with `auto,oplock`. With PostgreSQL you can use the `xmin` system column like this : For more informations about `xmin` see https://www.postgresql.org/docs/10/static/ddl-system-columns.html With SQL Server you can use a `rowversion` field with the `mssql.Rowversion` type like this : For more informations about the `rowversion` data type see https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql godb keep track of time consumed while executing queries. You can reset it and get the time consumed since Open or the previous reset : You can log all executed queried and details of condumed time. Simply add a logger : godb takes advantage of PostgreSQL RETURNING clause, and SQL Server OUTPUT clause. With statements tools you have to add a RETURNING clause with the Suffix method and call DoWithReturning method instead of Do(). It's optionnal. With StructInsert it's transparent, the RETURNING or OUTPUT clause is added for all 'auto' columns and it's managed for you. One of the big advantage is with BulkInsert : for others databases the rows are inserted but the new keys are unkonwns. With PostgreSQL and SQL Server the slice is updated for all inserted rows. It also enables optimistic locking with *automatic* columns. godb has two prepared statements caches, one to use during transactions, and one to use outside of a transaction. Both use a LRU algorithm. The transaction cache is enabled by default, but not the other. A transaction (sql.Tx) isn't shared between goroutines, using prepared statement with it has a predictable behavious. But without transaction a prepared statement could have to be reprepared on a different connection if needed, leading to unpredictable performances in high concurrency scenario. Enabling the non transaction cache could improve performances with single goroutine batch. With multiple goroutines accessing the same database : it depends ! A benchmark would be wise. Using statements tools and structs tools you can execute select queries and get an iterator instead of filling a slice of struct instances. This could be useful if the request's result is big and you don't want to allocate too much memory. On the other side you will write almost as much code as with the `sql` package, but with an automatic struct mapping, and a request builder. Iterators are also available with raw queries. In this cas you cas executes any kind of sql code, not just select queries. To get an interator simply use the `DoWithIterator` method instead of `Do`. The iterator usage is similar to the standard `sql.Rows` type. Don't forget to check that there are no errors with the `Err` method, and don't forget to call `Close` when the iterator is no longer useful, especially if you don't scan all the resultset. To avoid performance cost godb.DB does not implement synchronization. So a given instance of godb.DB should not be used by multiple goroutines. But a godb.DB instance can be created and used as a blueprint and cloned for each goroutine. See Clone and Clear methods. A typical use case is a web server. When the application starts a godb.DB is created, and cloned in each http handler with Clone, and ressources are to be freed calling Clear (use defer statement).

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

Package esquery provides a non-obtrusive, idiomatic and easy-to-use query and aggregation builder for the official Go client (https://github.com/elastic/go-elasticsearch) for the ElasticSearch database (https://www.elastic.co/products/elasticsearch). esquery alleviates the need to use extremely nested maps (map[string]interface{}) and serializing queries to JSON manually. It also helps eliminating common mistakes such as misspelling query types, as everything is statically typed. Using `esquery` can make your code much easier to write, read and maintain, and significantly reduce the amount of code you write. esquery provides a method chaining-style API for building and executing queries and aggregations. It does not wrap the official Go client nor does it require you to change your existing code in order to integrate the library. Queries can be directly built with `esquery`, and executed by passing an `*elasticsearch.Client` instance (with optional search parameters). Results are returned as-is from the official client (e.g. `*esapi.Response` objects). Getting started is extremely simple: esquery currently supports version 7 of the ElasticSearch Go client. The library cannot currently generate "short queries". For example, whereas ElasticSearch can accept this: { "query": { "term": { "user": "Kimchy" } } } The library will always generate this: This is also true for queries such as "bool", where fields like "must" can either receive one query object, or an array of query objects. `esquery` will generate an array even if there's only one query object.

Package dotsql provides a way to separate your code from SQL queries. 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. For more usage examples see https://github.com/gchaincl/dotsql

Package jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automatic query result data mapping. Jet currently supports PostgreSQL, MySQL, MariaDB and SQLite. Future releases will add support for additional databases. Use the command bellow to add jet as a dependency into go.mod project: Jet generator can be installed in one of the following ways: (Go1.16+) Install jet generator using go install: go install github.com/go-jet/jet/v2/cmd/jet@latest Install jet generator to GOPATH/bin folder: cd $GOPATH/src/ && GO111MODULE=off go get -u github.com/go-jet/jet/cmd/jet Install jet generator into specific folder: git clone https://github.com/go-jet/jet.git cd jet && go build -o dir_path ./cmd/jet Make sure that the destination folder is added to the PATH environment variable. Jet requires already defined database schema(with tables, enums etc), so that jet generator can generate SQL Builder and Model files. File generation is very fast, and can be added as every pre-build step. Sample command: Before we can write SQL queries in Go, we need to import generated SQL builder and model types: To write postgres SQL queries we import: Then we can write the SQL query: Now we can run the statement and store the result into desired destination: We can print a statement to see SQL query and arguments sent to postgres server: Output: If we print destination as json, we'll get: Detail info about all statements, features and use cases can be found at project wiki page - https://github.com/go-jet/jet/wiki.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/doug-martin/goqu for an introduction to goqu.

goqu an idiomatch SQL builder, and query package. Please see https://github.com/slessard/goqu for an introduction to goqu.

Package query provides an extensible SQL query builder for PostgreSQL. It works by using first class functions to allow for queries to be built up. This does not support the entire dialect for PostgreSQL, only a subset that would be necessary for CRUD operations. Let's look at how a simple SELECT query can be built up using this library, we can then use the above to pass a query to our database driver, along with the arguments passed to the query, Calling Build on the query will build up the query string and correctly set the parameter arguments in the query. Args will return an interface slice containing the arguments given to the query via query.Arg. We can do even more complex SELECT queries too, This library makes use of the type Option which is a first class function, which takes the current Query an Option is being applied to and returns it. With this we can define our own Option functions to clean up some of the queries we want to build.

Bob is an SQL builder library initially made as an extension for Squirrel with functionality like Knex (from the Node.js world). Squirrel itself doesn't provide other types of queries for creating a table, upsert, and some other things. Bob is meant to fill those gaps. The different between Bob and Squirrel is that Bob is solely a query builder. The users have to execute and manage the SQL connection themself. Meaning there are no ExecWith() function implemented on Bob, as you can find it on Squirrel. The purpose of an SQL query builder is to prevent any typo or mistypes on the SQL queries. Although also with that reason, Bob might not always have the right query for you, depending on what you are doing with the SQL query. It might sometimes be better for you to write the SQL query yourself, if your problem is specific and needs some micro-tweaks. With that being said, I hope you enjoy using Bob and consider starring or reporting any issues regarding the usage of Bob in your projects. Copyright (c) 2021-present Reinaldy Rafli and Bob collaborators

Package sqls focuses only on bulding SQL queries by free combination of segments. Thus, it works naturally with all sql dialects without having to deal with the differences between them. Unlike any other sql builder or ORMs, Segment is the only concept you need to learn. Segment is the builder for a part of or even the full query, it allows you to write and combine segments with freedom. With the help of Segment, we pay attention only to the reference relationships inside the segment, for example, use "$1" to refer the first element of s.Args. The syntax of the segment is exactly the same as the syntax of the "database/sql", plus preprocessing functions support: Note: