README
¶
Loukoum
A simple SQL Query Builder.
Introduction
Loukoum is a simple SQL Query Builder, only PostgreSQL is supported at the moment.
If you have to generate complex queries, which rely on various contexts, loukoum is the right tool for you.
Afraid to slip a tiny SQL injection manipulating fmt to append conditions? Fear no more, loukoum is here to protect you against yourself.
Just a few examples when and where loukoum can become handy:
- Remove user anonymity if user is an admin
- Display news draft for an author
- Add filters in query based on request parameters
- Add a
ON CONFLICTclause for resource's owner - And so on...
Installation
Using dep
dep ensure -add github.com/DzananGanic/loukoum@master
or go get
go get -u github.com/DzananGanic/loukoum
Usage
Loukoum helps you generate SQL queries from composable parts.
However, keep in mind it's not an ORM or a Mapper so you have to use a SQL connector (database/sql, sqlx, etc.) to execute queries.
INSERT
Insert a new Comment and retrieve its id.
import lk "github.com/DzananGanic/loukoum"
// Comment model
type Comment struct {
ID int64
Email string `db:"email"`
Status string `db:"status"`
Message string `db:"message"`
UserID int64 `db:"user_id"`
CreatedAt pq.NullTime `db:"created_at"`
DeletedAt pq.NullTime `db:"deleted_at"`
}
// CreateComment creates a comment.
func CreateComment(db *sqlx.DB, comment Comment) (Comment, error) {
builder := lk.Insert("comments").
Set(
lk.Pair("email", comment.Email),
lk.Pair("status", "waiting"),
lk.Pair("message", comment.Message),
lk.Pair("created_at", lk.Raw("NOW()")),
).
Returning("id")
// query: INSERT INTO comments (created_at, email, message, status, user_id)
// VALUES (NOW(), :arg_1, :arg_2, :arg_3, :arg_4) RETURNING id
// args: map[string]interface{}{
// "arg_1": string(comment.Email),
// "arg_2": string(comment.Message),
// "arg_3": string("waiting"),
// "arg_4": string(comment.UserID),
// }
query, args := builder.NamedQuery()
stmt, err := db.PrepareNamed(query)
if err != nil {
return comment, err
}
defer stmt.Close()
err = stmt.Get(&comment, args)
if err != nil {
return comment, err
}
return comment, nil
}
INSERT on conflict (UPSERT)
import lk "github.com/DzananGanic/loukoum"
// UpsertComment inserts or updates a comment based on the email attribute.
func UpsertComment(db *sqlx.DB, comment Comment) (Comment, error) {
builder := lk.Insert("comments").
Set(
lk.Pair("email", comment.Email),
lk.Pair("status", "waiting"),
lk.Pair("message", comment.Message),
lk.Pair("user_id", comment.UserID),
lk.Pair("created_at", lk.Raw("NOW()")),
).
OnConflict("email", lk.DoUpdate(
lk.Pair("message", comment.Message),
lk.Pair("user_id", comment.UserID),
lk.Pair("status", "waiting"),
lk.Pair("created_at", lk.Raw("NOW()")),
lk.Pair("deleted_at", nil),
)).
Returning("id, created_at")
// query: INSERT INTO comments (created_at, email, message, status, user_id)
// VALUES (NOW(), :arg_1, :arg_2, :arg_3, :arg_4)
// ON CONFLICT (email) DO UPDATE SET created_at = NOW(), deleted_at = NULL, message = :arg_5,
// status = :arg_6, user_id = :arg_7 RETURNING id, created_at
// args: map[string]interface{}{
// "arg_1": string(comment.Email),
// "arg_2": string(comment.Message),
// "arg_3": string("waiting"),
// "arg_4": string(comment.UserID),
// "arg_5": string(comment.Message),
// "arg_6": string("waiting"),
// "arg_7": string(comment.UserID),
// }
query, args := builder.NamedQuery()
stmt, err := db.PrepareNamed(query)
if err != nil {
return comment, err
}
defer stmt.Close()
err = stmt.Get(&comment, args)
if err != nil {
return comment, err
}
return comment, nil
}
UPDATE
Publish a News by updating its status and publication date.
// News model
type News struct {
ID int64
Status string `db:"status"`
PublishedAt pq.NullTime `db:"published_at"`
DeletedAt pq.NullTime `db:"deleted_at"`
}
// PublishNews publishes a news.
func PublishNews(db *sqlx.DB, news News) (News, error) {
builder := lk.Update("news").
Set(
lk.Pair("published_at", lk.Raw("NOW()")),
lk.Pair("status", "published"),
).
Where(lk.Condition("id").Equal(news.ID)).
And(lk.Condition("deleted_at").IsNull(true)).
Returning("published_at")
// query: UPDATE news SET published_at = NOW(), status = :arg_1 WHERE ((id = :arg_2) AND (deleted_at IS NULL))
// RETURNING published_at
// args: map[string]interface{}{
// "arg_1": string("published"),
// "arg_2": int64(news.ID),
// }
query, args := builder.NamedQuery()
stmt, err := db.PrepareNamed(query)
if err != nil {
return news, err
}
defer stmt.Close()
err = stmt.Get(&news, args)
if err != nil {
return news, err
}
return news, nil
}
SELECT
Basic SELECT with an unique condition
Retrieve non-deleted users.
import lk "github.com/DzananGanic/loukoum"
// User model
type User struct {
ID int64
FirstName string `db:"first_name"`
LastName string `db:"last_name"`
Email string
IsStaff bool `db:"is_staff"`
DeletedAt pq.NullTime `db:"deleted_at"`
}
// FindUsers retrieves non-deleted users
func FindUsers(db *sqlx.DB) ([]User, error) {
builder := lk.Select("id", "first_name", "last_name", "email").
From("users").
Where(lk.Condition("deleted_at").IsNull(true))
// query: SELECT id, first_name, last_name, email FROM users WHERE (deleted_at IS NULL)
// args: map[string]interface{}{
//
// }
query, args := builder.NamedQuery()
stmt, err := db.PrepareNamed(query)
if err != nil {
return nil, err
}
defer stmt.Close()
users := []User{}
err = stmt.Select(&users, args)
if err != nil {
return nil, err
}
return users, nil
}
SELECT IN with subquery
Retrieve comments only sent by staff users, the staff users query will be a subquery as we don't want to use any JOIN operations.
// FindStaffComments retrieves comments by staff users.
func FindStaffComments(db *sqlx.DB, comment Comment) ([]Comment, error) {
builder := lk.Select("id", "email", "status", "user_id", "message", "created_at").
From("comments").
Where(lk.Condition("deleted_at").IsNull(true)).
Where(
lk.Condition("user_id").In(
lk.Select("id").
From("users").
Where(lk.Condition("is_staff").Equal(true)),
),
)
// query: SELECT id, email, status, user_id, message, created_at
// FROM comments WHERE ((deleted_at IS NULL) AND
// (user_id IN (SELECT id FROM users WHERE (is_staff = :arg_1))))
// args: map[string]interface{}{
// "arg_1": bool(true),
// }
query, args := builder.NamedQuery()
stmt, err := db.PrepareNamed(query)
if err != nil {
return nil, err
}
defer stmt.Close()
comments := []Comment{}
err = stmt.Select(&comments, args)
if err != nil {
return nil, err
}
return comments, nil
}
SELECT with JOIN
Retrieve non-deleted comments sent by a user with embedded user in results.
First, we need to update the Comment struct to embed User.
// Comment model
type Comment struct {
ID int64
Email string `db:"email"`
Status string `db:"status"`
Message string `db:"message"`
UserID int64 `db:"user_id"`
User *User `db:"users"`
CreatedAt pq.NullTime `db:"created_at"`
DeletedAt pq.NullTime `db:"deleted_at"`
}
Let's create a FindComments method to retrieve these comments.
In this scenario we will use an INNER JOIN but loukoum also supports LEFT JOIN and RIGHT JOIN.
// FindComments retrieves comments by users.
func FindComments(db *sqlx.DB, comment Comment) ([]Comment, error) {
builder := lk.
Select(
"comments.id", "comments.email", "comments.status",
"comments.user_id", "comments.message", "comments.created_at",
).
From("comments").
Join(lk.Table("users"), lk.On("comments.user_id", "users.id")).
Where(lk.Condition("comments.deleted_at").IsNull(true))
// query: SELECT comments.id, comments.email, comments.status, comments.user_id, comments.message,
// comments.created_at FROM comments INNER JOIN users ON comments.user_id = users.id
// WHERE (comments.deleted_at IS NULL)
// args: map[string]interface{}{
//
// }
query, args := builder.NamedQuery()
stmt, err := db.PrepareNamed(query)
if err != nil {
return nil, err
}
defer stmt.Close()
comments := []Comment{}
err = stmt.Select(&comments, args)
if err != nil {
return nil, err
}
return comments, nil
}
DELETE
Delete a user based on ID.
// DeleteUser deletes a user.
func DeleteUser(db *sqlx.DB, user User) error {
builder := lk.Delete("users").
Where(lk.Condition("id").Equal(user.ID))
// query: DELETE FROM users WHERE (id = :arg_1)
// args: map[string]interface{}{
// "arg_1": int64(user.ID),
// }
query, args := builder.NamedQuery()
stmt, err := db.PrepareNamed(query)
if err != nil {
return err
}
defer stmt.Close()
_, err = stmt.Exec(args)
return err
}
See examples directory for more information.
NOTE: For
database/sql, see standard.
Migration
Migrating from v1.x.x
- Change
Prepare()toNamedQuery()for builder.Builder interface.
Inspiration
Thanks
License
This is Free Software, released under the MIT License.
Loukoum artworks are released under the Creative Commons BY-SA License.
Contributing
Don't hesitate ;)
Documentation
¶
Overview ¶
Package loukoum provides a simple SQL Query Builder. At the moment, only PostgreSQL is supported.
If you have to generate complex queries, which rely on various contexts, loukoum is the right tool for you. It helps you generate SQL queries from composable parts. However, keep in mind it's not an ORM or a Mapper so you have to use a SQL connector (like "database/sql" or "sqlx", for example) to execute queries.
If you're afraid to slip a tiny SQL injection manipulating fmt (or a byte buffer...) when you append conditions, loukoum is here to protect you against yourself.
For further informations, you can read this documentation: https://github.com/DzananGanic/loukoum/blob/master/README.md
Or you can discover loukoum with these examples. An "insert" can be generated like that:
builder := loukoum.Insert("comments").
Set(
loukoum.Pair("email", comment.Email),
loukoum.Pair("status", "waiting"),
loukoum.Pair("message", comment.Message),
loukoum.Pair("created_at", loukoum.Raw("NOW()")),
).
Returning("id")
Also, if you need an upsert, you can define a "on conflict" clause:
builder := loukoum.Insert("comments").
Set(
loukoum.Pair("email", comment.Email),
loukoum.Pair("status", "waiting"),
loukoum.Pair("message", comment.Message),
loukoum.Pair("created_at", loukoum.Raw("NOW()")),
).
OnConflict("email", loukoum.DoUpdate(
loukoum.Pair("message", comment.Message),
loukoum.Pair("status", "waiting"),
loukoum.Pair("created_at", loukoum.Raw("NOW()")),
loukoum.Pair("deleted_at", nil),
)).
Returning("id")
Updating a news is also simple:
builder := loukoum.Update("news").
Set(
loukoum.Pair("published_at", loukoum.Raw("NOW()")),
loukoum.Pair("status", "published"),
).
Where(loukoum.Condition("id").Equal(news.ID)).
And(loukoum.Condition("deleted_at").IsNull(true)).
Returning("published_at")
You can remove a specific user:
builder := loukoum.Delete("users").
Where(loukoum.Condition("id").Equal(user.ID))
Or select a list of users...
builder := loukoum.Select("id", "first_name", "last_name", "email").
From("users").
Where(loukoum.Condition("deleted_at").IsNull(true))
Index ¶
- Constants
- func And(left stmt.Expression, right stmt.Expression) stmt.InfixExpression
- func AndOn(left stmt.OnExpression, right stmt.OnExpression) stmt.OnExpression
- func Column(name string) stmt.Column
- func Condition(column string) stmt.Identifier
- func Count(value string) stmt.Count
- func Delete(from interface{}) builder.Delete
- func DoNothing() stmt.ConflictNoAction
- func DoUpdate(args ...interface{}) stmt.ConflictUpdateAction
- func Exists(value interface{}) stmt.Exists
- func Insert(into interface{}) builder.Insert
- func Max(value string) stmt.Max
- func Min(value string) stmt.Min
- func NotExists(value interface{}) stmt.NotExists
- func On(left string, right string) stmt.OnClause
- func Or(left stmt.Expression, right stmt.Expression) stmt.InfixExpression
- func OrOn(left stmt.OnExpression, right stmt.OnExpression) stmt.OnExpression
- func Order(column string, option ...types.OrderType) stmt.Order
- func Pair(key, value interface{}) types.Pair
- func Raw(value string) stmt.Raw
- func Select(columns ...interface{}) builder.Select
- func Sum(value string) stmt.Sum
- func Table(name string) stmt.Table
- func Update(table interface{}) builder.Update
- func With(name string, value interface{}) stmt.WithQuery
- type Map
Constants ¶
View Source
const ( // InnerJoin is used for "INNER JOIN" in join statement. InnerJoin = types.InnerJoin // LeftJoin is used for "LEFT JOIN" in join statement. LeftJoin = types.LeftJoin // RightJoin is used for "RIGHT JOIN" in join statement. RightJoin = types.RightJoin // LeftOuterJoin is used for "LEFT OUTER JOIN" in join statement. LeftOuterJoin = types.LeftOuterJoin // RightOuterJoin is used for "RIGHT OUTER JOIN" in join statement. RightOuterJoin = types.RightOuterJoin // Asc is used for "ORDER BY" statement. Asc = types.Asc // Desc is used for "ORDER BY" statement. Desc = types.Desc )
Variables ¶
This section is empty.
Functions ¶
func And ¶
func And(left stmt.Expression, right stmt.Expression) stmt.InfixExpression
And is a wrapper to create a new InfixExpression statement.
func AndOn ¶
func AndOn(left stmt.OnExpression, right stmt.OnExpression) stmt.OnExpression
AndOn is a wrapper to create a new On statement using an infix expression.
func Condition ¶
func Condition(column string) stmt.Identifier
Condition is a wrapper to create a new Identifier statement.
func DoNothing ¶
func DoNothing() stmt.ConflictNoAction
DoNothing is a wrapper to create a new ConflictNoAction statement.
func DoUpdate ¶
func DoUpdate(args ...interface{}) stmt.ConflictUpdateAction
DoUpdate is a wrapper to create a new ConflictUpdateAction statement.
func Or ¶
func Or(left stmt.Expression, right stmt.Expression) stmt.InfixExpression
Or is a wrapper to create a new InfixExpression statement.
func OrOn ¶
func OrOn(left stmt.OnExpression, right stmt.OnExpression) stmt.OnExpression
OrOn is a wrapper to create a new On statement using an infix expression.
Types ¶
Source Files
Directories
¶
| Path | Synopsis |
|---|---|
|
Package builder receives user input and generates an AST using "stmt" package.
|
Package builder receives user input and generates an AST using "stmt" package. |
|
examples
|
|
|
Package format escape various types for types.RawContext.
|
Package format escape various types for types.RawContext. |
|
Package stmt defines various statements and clauses that are used to generate queries.
|
Package stmt defines various statements and clauses that are used to generate queries. |
|
Package types defines some internal types that are handled by the "builder" and "stmt" package.
|
Package types defines some internal types that are handled by the "builder" and "stmt" package. |
Click to show internal directories.
Click to hide internal directories.