sqlc

README

sqlc-go-builder

sqlc implements a Dynamic Query Builder for SQLC and more specifically MySQL queries.

It implements a parser using vitess-go-sqlparser to parse and rewrite Complex MySQL queries on the fly using their AST and Rewrite functionality provided by the sqlparser package.

Features

1. Allows Where, Order, In, Offset, Limit, Group dynamically.
2. Supports complex SELECT queries.
3. Safe from SQLi by using Parameterized Arguments for everything dynamic.
4. Sanitizes code by using Vitess SQL parser.

Example

Using as standalone

package main

import (
	"fmt"

	sqlc "github.com/badgeek/sqlc-go-builder"
)

func main() {
	builder := sqlc.New().
		In("id", 1, 2, 3).
		Where("name = ?", "John").
		Where("age > ?", 18).
        	Group("name, age").
		Order("name ASC, age DESC").
		Offset(10).
		Limit(5)

	query, args, err := builder.Build("select id from user_items")
	if err != nil {
		panic(err)
	}
	fmt.Printf("query= %s\n args= %v\n", query, args)
}

// Output:
// query= select id from user_items where id in (?, ?, ?) and `name` = ? and age > ? group by `name`, age order by `name` asc, age desc limit 10, 5
// args= [1 2 3 John 18]

Wrapping with SQLC

package background

import (
	"context"
	"fmt"
	"testing"

	v2 "github.com/test-repo/pkg/db/v2"
	"github.com/test-repo/pkg/db/v2/dbsql"
	sqlc "github.com/badgeek/sqlc-go-builder"
)

func TestPaginationDynamic(t *testing.T) {
	db, err := v2.New("<db-url>")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	querier := dbsql.New(sqlc.Wrap(db.Pool))
	//querier := db.Queries()
	data, err := querier.GetData(
		sqlc.Build(context.Background(), func(builder *sqlc.Builder) {
			builder.Limit(10)
		}),
	)
	if err != nil {
		t.Fatal(err)
	}

	for _, item := range data {
		item := item
		fmt.Printf("%v\n", item)
	}
}

TODO

• Better error handling
• More tests

Credits

1. https://github.com/yiplee/sqlc - Original inspiration for this library. The concepts have been extended to support AST rewrite instead of string formatting and things have been made safer (No SQLi).

Documentation

Index

• func Build(ctx context.Context, f func(builder *Builder)) context.Context
• func WithBuilder(ctx context.Context, b *Builder) context.Context
• type Builder
  • func BuilderFrom(ctx context.Context) (*Builder, bool)
  • func New() *Builder
  • func (b *Builder) Build(query string, args ...interface{}) (string, []interface{}, error)
  • func (b *Builder) Group(cols string) *Builder
  • func (b *Builder) In(column string, args ...interface{}) *Builder
  • func (b *Builder) Limit(x int) *Builder
  • func (b *Builder) Offset(x int) *Builder
  • func (b *Builder) Order(cols string) *Builder
  • func (b *Builder) Where(query string, args ...interface{}) *Builder
  • func (b *Builder) WhereCompare(column string, operator string, args ...interface{}) *Builder
  • func (b *Builder) WhereCondition(query string, condition FilterCondition, args ...interface{}) *Builder
• type DBTX
  • func Wrap(db DBTX) DBTX
• type FilterCondition

Functions

func Build

func Build(ctx context.Context, f func(builder *Builder)) context.Context

func WithBuilder

func WithBuilder(ctx context.Context, b *Builder) context.Context

Types

type Builder

type Builder struct {
	RowCount int
	// contains filtered or unexported fields
}

Builder is a SQL query builder.

It supports dynamic WHERE conditions, ORDER BY, LIMIT, and OFFSET. For dynamic WHERE and ORDER BY clauses, only the parent table rows can be used. It uses vitess sqlparser to parse the query and rewrite it.

func BuilderFrom

func BuilderFrom(ctx context.Context) (*Builder, bool)

func New

func New() *Builder

New creates a new Builder.

func (*Builder) Build

func (b *Builder) Build(query string, args ...interface{}) (string, []interface{}, error)

Build generates a query and args from the builder.

func (*Builder) Group

func (b *Builder) Group(cols string) *Builder

Group sets columns of GROUP BY in SELECT. Group("name")

func (*Builder) In

func (b *Builder) In(column string, args ...interface{}) *Builder

In is an equivalent of Where("column IN (?,?,?)", args...). In("id", 1, 2, 3)

func (*Builder) Limit

func (b *Builder) Limit(x int) *Builder

Limit sets the limit in SELECT.

func (*Builder) Offset

func (b *Builder) Offset(x int) *Builder

Offset sets the offset in SELECT.

func (*Builder) Order

func (b *Builder) Order(cols string) *Builder

Order sets columns of ORDER BY in SELECT. Order("name, age DESC")

func (*Builder) Where

func (b *Builder) Where(query string, args ...interface{}) *Builder

Where set conditions of where in SELECT Where("user = ?","tom") Where("a = ? OR b = ?",1,2)

Requires 3 arguments, first is key, second is operator and third is a argument to be replaced with a placeholder.

Requires spaces between conditions to work.

func (*Builder) WhereCompare

func (b *Builder) WhereCompare(column string, operator string, args ...interface{}) *Builder

Perform tuple based comparision like ("a", "b") > (@value_of_a, @value_of_b)

func (*Builder) WhereCondition

func (b *Builder) WhereCondition(query string, condition FilterCondition, args ...interface{}) *Builder

WhereCondition set conditions of where in SELECT with specified AND or OR

type DBTX

type DBTX interface {
	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
	QueryRow(context.Context, string, ...interface{}) pgx.Row
}

func Wrap

func Wrap(db DBTX) DBTX

type FilterCondition

type FilterCondition int

const (
	FilterConditionAnd FilterCondition = iota
	FilterConditionOr
)