ququery

package module
v1.1.1 Latest Latest
Warning

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

 Go to latest Published: Sep 18, 2024 License: MIT Imports: 3 Imported by: 0

README

Go Report Card codecov

QuQuery: Golang SQL Builder

 ██████╗ ██╗   ██╗ ██████╗ ██╗   ██╗███████╗██████╗ ██╗   ██╗
██╔═══██╗██║   ██║██╔═══██╗██║   ██║██╔════╝██╔══██╗╚██╗ ██╔╝
██║   ██║██║   ██║██║   ██║██║   ██║█████╗  ██████╔╝ ╚████╔╝
██║▄▄ ██║██║   ██║██║▄▄ ██║██║   ██║██╔══╝  ██╔══██╗  ╚██╔╝
╚██████╔╝╚██████╔╝╚██████╔╝╚██████╔╝███████╗██║  ██║   ██║
 ╚══▀▀═╝  ╚═════╝  ╚══▀▀═╝  ╚═════╝ ╚══════╝╚═╝  ╚═╝   ╚═╝

About

QuQuery is simple and efficient SQL databases query builder that provide zero dependency and zero type reflection in your code base to make repositories more readable for first look.

Why i make Ququery package

When I was learning Golang, I noticed that many people coding in Golang for the first time were using GORM. However, after a short period of using GORM, I found myself dealing with convoluted code that I couldn't understand, and it often didn't work as expected.

I searched for other solutions and discovered that in many large companies and projects, programmers prefer to use pure SQL queries with the standard database/sql package. This approach avoids several issues commonly encountered with GORM:

  1. Performance Overhead: GORM introduces an additional layer of abstraction which can result in performance overhead compared to writing raw SQL queries. This can be significant in high-performance applications.

  2. Complex Queries: For complex queries involving multiple joins, subqueries, and custom SQL, GORM's abstraction can become cumbersome and harder to manage, often requiring raw SQL anyway.

  3. Debugging Difficulties: Debugging GORM issues can be challenging because it abstracts away the SQL, making it harder to understand what exact queries are being generated and executed.

  4. Code Complexity : Over time, as projects grow, the GORM code can become complex and harder to maintain, especially if it's not used consistently across the codebase.

After a long time of writing SQL queries, my coworkers and I grew tired of writing repetitive queries. To solve this issue, I decided to create ququery—a query builder for Golang. This tool aims to simplify the process of building SQL queries, making your code more readable and maintainable, while avoiding the aforementioned problems with GORM.

Installation And Usage

For installing ququery in your project should run below command in root of project.

go get github.com/adel-hadadi/ququery@latest

Every database operation such as (UPDATE, INSERT, DELETE, SELECT) in ququery have specific methods and they can be different from other one so let's explain each operation methods one by one.

Select Statements

Specifying a Select Clause

You may not always want to select all columns from database table. Using the Columns method you can specify each column that you want to fetch from database.

query := ququery.Select("table_name").Columns("id", "name", "email").Query()

log.Println(query) // query => SELECT id, name, email FROM table_name

For situations that you want to fetch all columns you can call Select method without Columns.

Joins

The query builder also be used to add join clauses to your queries. To perform a basic inner join, you may use the Join method on a query builder instance. The first arguments passed to Join method is the name of the table you need to join to, while the second argument specify the column constraints for the join. You may even join multiple tables in a single query:

query := ququery.Select("users").
    Join("posts", "posts.user_id = users.id").
    Query()

log.Println(query) // query => SELECT * FROM users INNER JOIN posts ON posts.user_id = users.id
Left join / Right join

if you would like to perform left join or right join instead of an inner join, use LeftJoin or RightJoin methods. This methods have the same signature as the Join method:

leftJoin := ququery.Select("users").LeftJoin("posts", "posts.user_id = users.id").Query()
log.Println(leftJoin) // query => SELECT * FROM users LEFT JOIN posts ON posts.user_id = users.id

rightJoin := ququery.Select("users").RightJoin("posts", "posts.user_id = users.id").Query()
log.Println(rightJoin) // query => SELECT * FROM users RIGHT JOIN posts ON posts.user_id = users.id
With

Also if you want to load a simple belongs to relations you can use With method. This method take a list of entities and then automatically load relations:

query := ququery.Select("users").With("role", "wallet").Query()
log.Println(query) // query => SELECT * FROM users LEFT JOIN roles ON roles.id = user.role_id LEFT JOIN wallets ON wallets.id = users.wallet_id

Basic Where Clauses

Where Clauses

You may use the query builder's Where method to add "where" clauses to the query. The most basic call to the Where method requires two 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.

For example, the following query retrieves users where the value of the votes column is equal to $1 and the value of the age column is greater than $2:

query := ququery.Select("users").
    Where("votes", "=").
    Where("age", ">").
    Query()

log.Println(query) // query => SELECT * FROM users WHERE votes = $1 AND age > $2

For convenience, if you want to verify that a column is = to a given value, you may call Where method with just column name. Ququery will assume you would like to use the = operator:

query := ququery.Select("users").
    Where("votes").
    Query()

log.Pritln(query) // query => SELECT * FROM users WHERE votes = $1

Or Where Clauses

When chaining together calls to the query builder's Where method, the "where" clauses will be joined together using the AND operator. However, you may use the OrWhere method to join a clause to the query using the OR operator. The OrWhere method accepts the same arguments as the Where method:

query := ququery.Select("users").
    Where("votes").
    OrWhere("name")
    Query()

log.Pritln(query) // query => SELECT * FROM users WHERE votes = $1 OR name = $2

If you need to group multiple where clauses together you can use WhereGroup method:

query := ququery.Select("users").
    Where("votes").
    WhereGroup(func(subQuery MultiWhere) string {
        return subQuery.Where("name").
            OrWhere("votes", ">").
            Query()
    }).
    Query()

log.Pritln(query) // query => SELECT * FROM users WHERE votes = $1 OR name = $2

Additional Where Clause

WhereLike / OrWhereLike

The WhereLike method allows you to add "LIKE" clauses to query for pattern matching. These methods provide a database-agnostic way performing string matching queries, with the ability to toggle case-sensitivity. By default, string matching is case-insensitive:

query := ququery.Select("users").WhereLike("name").Query()
log.Println(query) // query => SELECT * FROM users WHERE name LIKE $1

The OrWhereLike method allows you to add an "or"A clause with a LIKE condition:

query := ququer.Select("users").
    Where("votes", ">").
    OrWhereLike("name").
    Query()

log.Println(query) // query => SELECT * FROM users WHERE votes > $1 OR WHERE name LIKE $2
WhereNull / WhereNotNull / OrWhereNull / OrWhereNotNull

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

query := ququery.Select("users").WhereNull("updated_at").Query()
log.Println(query) // query => SELECT * FROM users WHERE updated_at IS NULL

The WhereNotNull method verifies that the column's value is not NULL:

query := ququery.Select("users").WhereNotNull("updated_at").Query()
log.Println(query) // query => SELECT * FROM users WHERE updated_at IS NOT NULL

Ordering, Grouping, Limit and offset

Ordering

The OrderBy Method

The OrderBy method allows you to sort the results of the query by a given column. The First argument accepted by the OrderBy method should be the column you wish to sort by, while the second argument determines the direction of the sort and may not either asc or desc:

query := ququery.Select("users").OrderBy("name", "desc").Query()
log.Println(query) // query => SELECT * FROM users ORDER BY name DESC

Limit and Offset

You may use the Limit and Offset methods to limit the number of results returned from the query or to skip a given number of results in the query:

query := ququery.Select("users").Limit().Offset().Query()
log.Println(query) // query => SELECT * FROM users LIMIT $1 OFFSET $2

Insert Statements

The query builder also provides an Insert method that may be used to insert records into database table. The Insert method accepts a list of column names.

query := ququery.Insert("users").Into("email", "votes").Query()
log.Println(query) // query => INSERT INTO users (email, votes) VALUES ($1, $2)

Update Statements

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 list of columns that should be updated:

query := ququery.Update("users").Where("id").Set("email", "email_verified").Query()
log.Println(query) // query => UPDATE users SET email = $1, email_verified = $2 WHERE id = $3

Delete Statements

The query builder's Delete method may be used to delete records from the table:

query := ququery.Delete("users").Where("votes", ">").Query()
log.Println(query) // query => DELETE FROM users WHERE votes > $1

Documentation

Index

Constants

View Source 
const (

	// DESC sort direction for descending
	DESC string = "DESC"

	// ASC sort direction for ascending
	ASC string = "ASC"
)

Variables

This section is empty.

Functions

func CountOver 

func CountOver() string

Types

type DeleteQuery 

type DeleteQuery struct {
	WhereContainer[*DeleteQuery]
	// contains filtered or unexported fields
}

func Delete 

func Delete(table string) *DeleteQuery

func (*DeleteQuery) Query 

func (q *DeleteQuery) Query() string

type ExistsQuery 

type ExistsQuery struct {
	WhereContainer[*ExistsQuery]
	// contains filtered or unexported fields
}

func Exists 

func Exists(table string) *ExistsQuery

func (*ExistsQuery) Query 

func (q *ExistsQuery) Query() string

type InsertQuery 

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

func Insert 

func Insert(table string) InsertQuery

func (InsertQuery) Into 

func (q InsertQuery) Into(columns ...string) InsertQuery

func (InsertQuery) Query 

func (q InsertQuery) Query() string

func (InsertQuery) Returning 

func (q InsertQuery) Returning(columns ...string) InsertQuery

type MultiWhere 

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

func (MultiWhere) OrWhere 

func (w MultiWhere) OrWhere(condition ...string) MultiWhere

func (MultiWhere) Query 

func (w MultiWhere) Query() string

func (MultiWhere) Where 

func (w MultiWhere) Where(condition ...string) MultiWhere

type Query 

type Query interface {
	Query() string
}

type SelectQuery 

type SelectQuery struct {
	WhereContainer[*SelectQuery]
	// contains filtered or unexported fields
}

func Select 

func Select(table string) *SelectQuery

func (*SelectQuery) Columns 

func (q *SelectQuery) Columns(columns ...string) *SelectQuery

func (*SelectQuery) Join 

func (q *SelectQuery) Join(table, constraints string) *SelectQuery

Join method used to add inner join to your queries

Example: 

query := ququery.Select("users").Join("posts", "posts.user_id = users.id").Query()
log.Println(query) => SELECT * FROM users INNER JOIN posts ON posts.user_id = users.id

func (*SelectQuery) LeftJoin added in v1.1.0 

func (q *SelectQuery) LeftJoin(table, constraints string) *SelectQuery

LeftJoin method used to add left join to your queries

Example: 

query := ququery.Select("users").LeftJoin("posts", "posts.user_id = users.id").Query()
log.Println(query) => SELECT * FROM users LEFT JOIN posts ON posts.user_id = users.id

func (*SelectQuery) Limit 

func (q *SelectQuery) Limit() *SelectQuery

func (*SelectQuery) Offset 

func (q *SelectQuery) Offset() *SelectQuery

func (*SelectQuery) OrderBy 

func (q *SelectQuery) OrderBy(column, direction string) *SelectQuery

func (*SelectQuery) Query 

func (q *SelectQuery) Query() string

func (*SelectQuery) RightJoin added in v1.1.0 

func (q *SelectQuery) RightJoin(table, constraints string) *SelectQuery

RightJoin method used to add right join to your queries

Example: 

query := ququery.Select("users").RightJoin("posts", "posts.user_id = users.id").Query()
log.Println(query) => SELECT * FROM users RIGHT JOIN posts ON posts.user_id = users.id

func (*SelectQuery) Table 

func (q *SelectQuery) Table(table string) *SelectQuery

func (*SelectQuery) With 

func (q *SelectQuery) With(entities ...string) *SelectQuery

With can load one-to-many relations without need to pass join column

type UpdateQuery 

type UpdateQuery struct {
	WhereContainer[*UpdateQuery]
	// contains filtered or unexported fields
}

func Update 

func Update(table string) *UpdateQuery

func (*UpdateQuery) Query 

func (q *UpdateQuery) Query() string

func (*UpdateQuery) Set 

func (q *UpdateQuery) Set(columns ...string) *UpdateQuery

type WhereContainer 

type WhereContainer[T whereable] struct {
	// contains filtered or unexported fields
}

WhereContainer is holder that contain every condinal clauses and methods.

func (*WhereContainer[T]) OrStrpos added in v1.0.0 

func (c *WhereContainer[T]) OrStrpos(column string) T

OrStrpos method allows you to add an "or" clause to Strpos condition.

Example: 

query := ququery.Select("users").Where("id").Strpos("name").Query()
log.Println(query) => SELECT * FROM users WHERE id = $1 OR (STRPOS(name, $2) > 0 or $3 = '')

func (*WhereContainer[T]) OrWhere 

func (c *WhereContainer[T]) OrWhere(column ...string) T

OrWhere allows you to add an "or" clause to Where condition.

Example: 

query := ququery.Delete("users").Where("id").OrWhere("email").Query()
log.Println(query) => DELETE FROM users WHERE id = $1 OR email = $2

func (*WhereContainer[T]) OrWhereInSubquery added in v1.1.0 

func (c *WhereContainer[T]) OrWhereInSubquery(column string, subQuery func(q SelectQuery) string) T

OrWhereInSubquery method allows you to add an "or" clause to WhereInSubquery condition.

Example: 

     query := ququery.Select("users").Where("age", ">=").WhereInSubquery("users.id", func(q ququery.SelectQuery) string {
	    return q.Table("orders").
		    Columns("user_id").
		    OrderBy("total_price", ququery.DESC).
		    Limit().
		    Query()
        }).
        Query()

    log.Println(query) => SELECT * FROM users WHERE age >= OR users.id IN (SELECT user_id FROM orders ORDER BY total_price DESC LIMIT $1)

func (*WhereContainer[T]) OrWhereLike added in v1.0.0 

func (c *WhereContainer[T]) OrWhereLike(column string) T

OrWhereLike method allows you to add an "or" clause with a LIKE condition

Example: 

query := ququery.Select("users").Where("id").OrWhereLike("name").Query()
log.Println(query) => SELECT * FROM users WHERE id = $1 OR name LIKE $2

func (*WhereContainer[T]) OrWhereNotNull added in v1.0.0 

func (c *WhereContainer[T]) OrWhereNotNull(column string) T

OrWhereNotNull method allows you to add an "or" clause to WhereNotNull codition

func (*WhereContainer[T]) OrWhereNull added in v1.0.0 

func (c *WhereContainer[T]) OrWhereNull(column string) T

OrWhereNull method allows you to add an "or" clause to WhereNull condition

Example: 

query := ququery.Select("users").Where("status").OrWhereNull("deleted_at").Query
log.Println(query) => SELECT * FROM users WHERE status = $1 OR deleted_at IS NULL

func (*WhereContainer[T]) Strpos added in v1.0.0 

func (c *WhereContainer[T]) Strpos(column string) T

Strpos method is more like whereLike method, but the difference is that strpos method is used by postgresql users for full text search.

Example: 

query := ququery.Select("users").Strpos("name").Query()
log.Println(query) => SELECT * FROM users WHERE (STRPOS(name, $1) > 0 or $2 = '')

func (*WhereContainer[T]) Where 

func (c *WhereContainer[T]) Where(column ...string) T

Where You may use the query builder's Where method to add "where" clauses to the query.

Example: 

query := ququery.Select("users").Where("id").Query()
log.Println(query) => SELECT * FROM users WHERE id = $1

query = ququery.Select("users").Where("age", ">=").Query()
log.Println(query) => SELECT * FROM users WHERE age >= $1

func (*WhereContainer[T]) WhereGroup added in v1.0.0 

func (c *WhereContainer[T]) WhereGroup(f func(subQuery MultiWhere) string) T

WhereGroup Sometimes you may need to group several "where" clauses within parentheses in order to achieve your query's desired logical grouping. In fact, you should generally always group calls to the orWhere method in parentheses in order to avoid unexpected query behavior. To accomplish this, you may user this method:

Example: 

	     query := ququery.Select("users").WhereGroup(func(subQuery ququery.MultiWhere) string {
		    return subQuery.Where("email").
			    Where("role_id").
			    OrWhere("type").
			    Query()
	    }).Query(),
        log.Println(query) => SELECT * FROM users WHERE ( email = $1 AND role_id = $2 OR type = $3)

func (*WhereContainer[T]) WhereInSubquery added in v1.0.0 

func (c *WhereContainer[T]) WhereInSubquery(column string, subQuery func(q SelectQuery) string) T

WhereInSubquery Sometimes you may need to construct a "where" clause that compares the results of a subquery to a given value. You may accomplish this by passing a closure and a value to the where method.

Example: 

     query := ququery.Select("users").WhereInSubquery("users.id", func(q ququery.SelectQuery) string {
	    return q.Table("orders").
		    Columns("user_id").
		    OrderBy("total_price", ququery.DESC).
		    Limit().
		    Query()
        }).
        Query()

    log.Println(query) => SELECT * FROM users WHERE users.id IN (SELECT user_id FROM orders ORDER BY total_price DESC LIMIT $1)

func (*WhereContainer[T]) WhereLike added in v1.0.0 

func (c *WhereContainer[T]) WhereLike(column string) T

WhereLike method allows you to add "LIKE" clauses to your query from pattern matchinga.

Example: 

query := ququery.Select("users").WhereLike("name").Query()
log.Println(query) => SELECT * FROM users WHERE name LIKE $1

func (*WhereContainer[T]) WhereNotNull added in v1.0.0 

func (c *WhereContainer[T]) WhereNotNull(column string) T

WhereNotNull method verifies that the column's value is not NULL:

Example: 

query := ququery.Delete("users").WhereNotNull("deleted_at").Query()
log.Println(query) => DELETE FROM users WHERE deleted_at IS NOT NULL

func (*WhereContainer[T]) WhereNull added in v1.0.0 

func (c *WhereContainer[T]) WhereNull(column string) T

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

Example: 

query := ququery.Select("users").WhereNull("deleted_at").Query()
log.Println(query) => SELECT * FROM users WHERE deleted_at IS NULL

Source Files

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.