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.

Source Files

View all Source files

Directories

Path Synopsis
from
groupby
having
join
limit
offset
orderby
returning
set
values
where
scripts
readme
seed

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.