gosqle

package module
v0.0.1 Latest Latest
Warning

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

Go to latest
Published: Sep 12, 2023 License: MIT Imports: 17 Imported by: 0

README

gosqle

gosqle is a golang package that can generate sql queries.

Table of Contents:

Examples

Examples shown here are generated into this README.md file from the examples folder. See README.tmpl.md for more information. To generate the examples into this README.md file, run the following command:

./run_readme.sh

To run examples from the examples folder, run the following command:

docker-compose up -d
# if needed you can seed the database with some data
./run_seed.sh
./run_examples.sh
Select

Create a select statement with the following syntax:

gosqle.NewSelect(...columns)
Generate a select query:
package main

import (
	"database/sql"
	"strings"

	"github.com/dwethmar/gosqle"
	"github.com/dwethmar/gosqle/clauses"
	"github.com/dwethmar/gosqle/clauses/from"
	"github.com/dwethmar/gosqle/expressions"
	"github.com/dwethmar/gosqle/postgres"
)

type User struct {
	ID    int64
	Name  string
	Email string
}

// SelectUsers selects users.
func SelectUsers(db *sql.DB) ([]User, string, error) {
	sb := new(strings.Builder)
	args := postgres.NewArguments()
	// SELECT id, name, email FROM users LIMIT 10;
	err := gosqle.NewSelect(
		clauses.Selectable{Expr: expressions.Column{Name: "id"}},
		clauses.Selectable{Expr: expressions.Column{Name: "name"}},
		clauses.Selectable{Expr: expressions.Column{Name: "email"}},
	).From(from.Table{
		Name: "users",
	}).Limit(args.NewArgument(10)).WriteTo(sb)
	if err != nil {
		return nil, "", err
	}
	rows, err := db.Query(sb.String(), args.Args...)
	if err != nil {
		return nil, "", err
	}
	var users []User
	for rows.Next() {
		var user User
		err = rows.Scan(&user.ID, &user.Name, &user.Email)
		if err != nil {
			return nil, "", err
		}
		users = append(users, user)
	}
	return users, sb.String(), nil
}

Generate select query using group by and aggregate functions:
package main

import (
	"database/sql"
	"strings"

	"github.com/dwethmar/gosqle"
	"github.com/dwethmar/gosqle/clauses"
	"github.com/dwethmar/gosqle/clauses/from"
	"github.com/dwethmar/gosqle/clauses/groupby"
	"github.com/dwethmar/gosqle/clauses/orderby"
	"github.com/dwethmar/gosqle/expressions"
	"github.com/dwethmar/gosqle/postgres"
)

type AmountOfAddressesPerCountry struct {
	Country string
	Count   int64
}

// SelectAmountOfAddressesPerCountry select amount of addresses per country
func SelectAmountOfAddressesPerCountry(db *sql.DB) ([]AmountOfAddressesPerCountry, string, error) {
	sb := new(strings.Builder)
	args := postgres.NewArguments()
	/**
	SELECT country, COUNT(id) AS address_count
	FROM addresses
	GROUP BY country
	ORDER BY address_count DESC;
	**/
	err := gosqle.NewSelect(
		clauses.Selectable{
			Expr: &expressions.Column{Name: "country"},
		},
		clauses.Selectable{
			Expr: expressions.NewCount(&expressions.Column{Name: "id"}),
			As:   "address_count",
		},
	).From(from.Table{
		Name: "addresses",
	}).GroupBy(groupby.ColumnGrouping{
		&expressions.Column{Name: "country"},
	}).OrderBy(orderby.Sort{
		Column:    &expressions.Column{Name: "address_count"},
		Direction: orderby.DESC,
	}).WriteTo(sb)
	if err != nil {
		return nil, "", err
	}

	rows, err := db.Query(sb.String(), args.Args...)
	if err != nil {
		return nil, "", err
	}

	var r []AmountOfAddressesPerCountry
	for rows.Next() {
		var a AmountOfAddressesPerCountry
		err = rows.Scan(&a.Country, &a.Count)
		if err != nil {
			return nil, "", err
		}
		r = append(r, a)
	}

	return r, sb.String(), nil
}

Insert
gosqle.NewInsert(table, ...columns)
Generate an insert query:
package main

import (
	"database/sql"
	"fmt"
	"strings"
	"time"

	"github.com/dwethmar/gosqle"
	"github.com/dwethmar/gosqle/postgres"
)

// InsertUser inserts a user.
func InsertUser(db *sql.DB) (string, error) {
	sb := new(strings.Builder)
	args := postgres.NewArguments()

	// INSERT INTO users (name, email) VALUES ($1, $2)
	err := gosqle.NewInsert("users", "name", "email").Values(
		args.NewArgument("John"),
		args.NewArgument(fmt.Sprintf("john%d@%s", time.Now().Unix(), "example.com")),
	).WriteTo(sb)

	if err != nil {
		return "", err
	}

	if _, err = db.Exec(sb.String(), args.Args...); err != nil {
		return "", err
	}

	return sb.String(), nil
}

Delete
Generate a delete query:
package main

import (
	"database/sql"
	"strings"

	"github.com/dwethmar/gosqle"
	"github.com/dwethmar/gosqle/expressions"
	"github.com/dwethmar/gosqle/postgres"
	"github.com/dwethmar/gosqle/predicates"
)

// Delete deletes a user.
func DeleteAddress(db *sql.DB) (string, error) {
	sb := new(strings.Builder)
	args := postgres.NewArguments()

	// DELETE FROM addresses WHERE user_id = $1
	err := gosqle.NewDelete("addresses").Where(
		predicates.EQ{
			Col:  expressions.Column{Name: "user_id"},
			Expr: args.NewArgument(111),
		},
	).WriteTo(sb)
	if err != nil {
		return "", err
	}
	if _, err = db.Exec(sb.String(), args.Args...); err != nil {
		return "", err
	}

	return sb.String(), nil
}

Update
Generate an update query:
package main

import (
	"database/sql"
	"fmt"
	"strings"
	"time"

	"github.com/dwethmar/gosqle"
	"github.com/dwethmar/gosqle/clauses/set"
	"github.com/dwethmar/gosqle/expressions"
	"github.com/dwethmar/gosqle/postgres"
	"github.com/dwethmar/gosqle/predicates"
)

// UpdateUser updates a user.
func UpdateUser(db *sql.DB) (string, error) {
	sb := new(strings.Builder)
	args := postgres.NewArguments()

	// UPDATE users SET name = $1 WHERE id = $2
	err := gosqle.NewUpdate("users").Set(set.Change{
		Col:  "name",
		Expr: args.NewArgument(fmt.Sprintf("new name %d", time.Now().Unix())),
	}).Where(predicates.EQ{
		Col:  expressions.Column{Name: "id"},
		Expr: args.NewArgument(1),
	}).WriteTo(sb)
	if err != nil {
		return "", err
	}
	if _, err = db.Exec(sb.String(), args.Args...); err != nil {
		return "", err
	}
	return sb.String(), nil
}

Syntax used

image

Documentation

Index

Constants

This section is empty.

Variables

View Source
var GoExampleFiles embed.FS
View Source
var ReadMeTemplate []byte

Functions

This section is empty.

Types

type Delete

type Delete struct {
	statement.Statement
}

Delete is a wrapper for a delete query statement.

func NewDelete

func NewDelete(
	table string,
) *Delete

NewDelete creates a new delete query.

func (*Delete) SetClause

func (d *Delete) SetClause(c clauses.Clause) *Delete

SetClause sets the clause for the query.

func (*Delete) Where

func (d *Delete) Where(predicates ...predicates.Predicate) *Delete

Where adds a where clause to the query.

func (*Delete) WriteTo

func (d *Delete) WriteTo(sw io.StringWriter) error

WriteTo writes the delete statement to the given string writer.

type Insert

type Insert struct {
	statement.Statement
}

Insert is a wrapper for a insert query statement.

func NewInsert

func NewInsert(
	into string,
	columns ...string,
) *Insert

NewInsert creates a new insert query.

func (*Insert) SetClause

func (i *Insert) SetClause(c clauses.Clause) *Insert

SetClause sets the clause for the query.

func (*Insert) Values

func (i *Insert) Values(arguments ...expressions.Expression) *Insert

Values adds values to the insert query.

func (*Insert) WriteTo

func (i *Insert) WriteTo(sw io.StringWriter) error

Write writes the insert query to the given writer. It also adds a semicolon to the end of the query.

type Select

type Select struct {
	statement.Statement
}

Select is a wrapper for a select query statement.

func NewSelect

func NewSelect(
	selectables ...clauses.Selectable,
) *Select

NewSelect creates a new select query.

func (*Select) From

func (s *Select) From(table from.Table) *Select

func (*Select) GroupBy

func (s *Select) GroupBy(grouping groupby.Grouping) *Select

GroupBy adds a group by clause to the select statement.

Grouping options: - groupby.ColumnGrouping - <add more>

func (*Select) Having

func (s *Select) Having(p ...predicates.Predicate) *Select

Having adds a having clause to the select statement.

func (*Select) Join

func (s *Select) Join(j ...join.Options) *Select

Join adds a join clause to the select statement. If no join options are given, the join clause will be ignored.

func (*Select) Limit

func (s *Select) Limit(argument expressions.Expression) *Select

Limit adds a limit clause to the select statement.

func (*Select) Offset

func (s *Select) Offset(argument expressions.Expression) *Select

Offset adds a offset clause to the select statement.

func (*Select) OrderBy

func (s *Select) OrderBy(sorting ...orderby.Sort) *Select

OrderBy adds a order by clause to the select statement. If no sorting options are given, the order by clause will be ignored.

func (*Select) SetClause

func (s *Select) SetClause(c clauses.Clause) *Select

SetClause sets the clause for the select statement.

func (*Select) Where

func (s *Select) Where(predicates ...predicates.Predicate) *Select

Where adds a where clause to the select statement. If no predicates are given, the where clause will be ignored.

func (*Select) WriteTo

func (s *Select) WriteTo(sw io.StringWriter) error

Write writes the select query to the given writer. It also adds a semicolon to the end of the query.

type Update

type Update struct {
	statement.Statement
}

Update is a wrapper for a update query statement.

func NewUpdate

func NewUpdate(
	table string,
) *Update

NewUpdate creates a new update query.

func (*Update) Join

func (u *Update) Join(j ...join.Options) *Update

From adds a from clause to the select statement.

func (*Update) OrderBy

func (u *Update) OrderBy(sorting ...orderby.Sort) *Update

OrderBy adds a order by clause to the select statement.

func (*Update) Set

func (u *Update) Set(changes ...set.Change) *Update

Set adds a set clause to the select statement.

func (*Update) SetClause

func (u *Update) SetClause(c clauses.Clause) *Update

SetClause sets the clause for the select statement.

func (*Update) Where

func (u *Update) Where(predicates ...predicates.Predicate) *Update

Where adds a where clause to the select statement.

func (*Update) WriteTo

func (u *Update) WriteTo(sw io.StringWriter) error

WriteTo writes the select statement to the given writer.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL