loukoum

package module
v1.1.0 Latest Latest
Warning

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

 Go to latest Published: Mar 9, 2018 License: MIT Imports: 3 Imported by: 2

README

Loukoum

CircleCI Documentation License

A simple SQL Query Builder.

Loukoum

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 CONFLICT clause for resource's owner
  • And so on...

Installation

Using dep

dep ensure -add github.com/ulule/loukoum@master

or go get

go get -u github.com/ulule/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/ulule/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, args := builder.Prepare()
	// query: INSERT INTO comments (created_at, email, message, status) VALUES (NOW(), :arg_1, :arg_2, :arg_3) RETURNING id
	// args: (map[string]interface {}) (len=3) {
	// (string) (len=5) "arg_1": (string) comment.Email,
	// (string) (len=5) "arg_2": (string) comment.Message,
	// (string) (len=5) "arg_3": (string) "waiting"
	// }

	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/ulule/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("created_at", lk.Raw("NOW()")),
		).
		OnConflict("email", lk.DoUpdate(
			lk.Pair("message", comment.Message),
			lk.Pair("status", "waiting"),
			lk.Pair("created_at", lk.Raw("NOW()")),
			lk.Pair("deleted_at", nil),
		)).
		Returning("id")

	query, args := builder.Prepare()
	// query: INSERT INTO comments (created_at, email, message, status) VALUES (
	//		NOW(), :arg_1, :arg_2, :arg_3
	// ) ON CONFLICT (email) DO UPDATE SET created_at = NOW(), deleted_at = NULL, message = :arg_4, status = :arg_5 RETURNING id
	// args: (map[string]interface {}) (len=5) {
	// (string) (len=5) "arg_1": (string) comment.Email,
	// (string) (len=5) "arg_2": (string) comment.Message,
	// (string) (len=5) "arg_3": (string) "waiting",
	// (string) (len=5) "arg_4": (string) comment.Message,
	// (string) (len=5) "arg_5": (string) "waiting"
	// }

	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, args := builder.Prepare()
	// 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 {}) (len=2) {
	//  (string) (len=5) "arg_1": (string) (len=9) "published",
	//  (string) (len=5) "arg_2": (int) news.ID
	// }

	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/ulule/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))

	users := []User{}

	// query: SELECT id, first_name, last_name, email FROM users WHERE (deleted_at IS NULL)
	// args: map[string]interface{}{}
	query, args := builder.Prepare()

	stmt, err := db.PrepareNamed(query)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	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").Is(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 IS :arg_1))))
	// args: (map[string]interface {}) (len=1) {
	// (string) (len=5) "arg_1": (bool) true
	// }
	query, args := builder.Prepare()

	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 comments, 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("id", "email", "status", "user_id", "message", "created_at").
		From("comments").
		Join(lk.Table("users"), lk.On("comments.user_id", "users.id")).
		Where(lk.Condition("deleted_at").IsNull(true))

	// query: SELECT id, email, status, user_id, message, created_at FROM comments INNER JOIN users ON comments.user_id = users.id WHERE (deleted_at IS NULL)
	// args: (map[string]interface {}) {
	// }
	query, args := builder.Prepare()

	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 comments, 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, args := builder.Prepare()
	// query: DELETE FROM users WHERE (id = :arg_1)
	// args: (map[string]interface {}) (len=1) {
	//  (string) (len=5) "arg_1": (int) user.ID
	// }

	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.

Inspiration

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

Index

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
	// 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 Column 

func Column(name string) stmt.Column

Column is a wrapper to create a new Column statement.

func Condition 

func Condition(column string) stmt.Identifier

Condition is a wrapper to create a new Identifier statement.

func Delete 

func Delete(from interface{}) builder.Delete

Delete starts a DeleteBuilder using the given table as from clause.

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 Insert 

func Insert(into interface{}) builder.Insert

Insert starts an InsertBuilder using the given table as into clause.

func On 

func On(left string, right string) stmt.On

On is a wrapper to create a new On statement.

func Or 

func Or(left stmt.Expression, right stmt.Expression) stmt.InfixExpression

Or is a wrapper to create a new InfixExpression statement.

func Order 

func Order(column string, option ...types.OrderType) stmt.Order

Order is a wrapper to create a new Order statement.

func Pair 

func Pair(key, value interface{}) types.Pair

Pair takes a key and its related value and returns a Pair.

func Raw 

func Raw(value string) stmt.Raw

Raw is a wrapper to create a new Raw expression.

func Select 

func Select(columns ...interface{}) builder.Select

Select starts a SelectBuilder using the given columns.

func Table 

func Table(name string) stmt.Table

Table is a wrapper to create a new Table statement.

func Update 

func Update(table interface{}) builder.Update

Update starts an Update builder using the given table.

Types

type Map 

type Map = types.Map

Map is a key/value map.

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.