Obreron

Fast and cheap sql builder.

Supported Dialects

Mysql
Postgresql (Work in progress)

Why?

Because writing SQL in Go is boring.

Instalation

Use go get to install v2

$ go get github.com/profe-ajedrez/obreron/v2

Use

Import package

import (
	v2 "github.com/profe-ajedrez/obreron/v2"
)

Select

Simple select

// Produces SELECT a1, a2, a3 FROM client
query, _ := v2.Select().Col("a1, a2, a3").From("client").Build()
r, error := db.Query(query)

Select/join/where/shuffled

// Produces SELECT a1, a2, ? AS diez, colIf1, colIf2, ? AS zero, a3, ? AS cien FROM client c JOIN addresses a ON a.id_cliente = a.id_cliente JOIN phones p ON p.id_cliente = c.id_cliente JOIN mailes m ON m.id_cliente = m.id_cliente AND c.estado_cliente = ? LEFT JOIN left_joined lj ON lj.a1 = c.a1 WHERE a1 = ? AND a2 = ? AND a3 = 10 AND a16 = ?
// with params = []any{10, 0, 100, 0, "'last name'", 1000.54, 75}
query, params := v2.Select().
    Where("a1 = ?", "'last name'").
    Col("a1, a2, ? AS diez", 10).
    Col(`colIf1, colIf2, ? AS zero`, 0).
    Col("a3, ? AS cien", 100).    
    Where("a2 = ?", 1000.54).
    And("a3 = 10").And("a16 = ?", 75).
    Join("addresses a ON a.id_cliente = a.id_cliente").
    Join("phones p").On("p.id_cliente = c.id_cliente").
    Join("mailes m").On("m.id_cliente = m.id_cliente").
    And("c.estado_cliente = ?", 0).    
    LeftJoin("left_joined lj").On("lj.a1 = c.a1").
    From("client c").
    Build()

r, error := db.Query(query, params...)

Note that in this example we purposely shuffled the order of the clauses and yet the query was built correctly

Conditional elements

Sometimes we need to check for a condition to build dynamic sql

This example adds the column name to the query only if the variable shouldAddName is true.

query, _ := v2.Select().
	Col("a1, a2, a3").
	ColIf(shouldAddName, "name")
	From("client").
	Build()

// Produces "SELECT a1, a2, a3 FROM client" when shouldAddName is false
// Produces "SELECT a1, a2, a3, name FROM client" when shouldAddName is true

This also can be applied to joins.

query, _ := v2.Select().
	Col("*").
	From("client c").
	Join("addresses a").On("a.client_id = c.client_id").
    JoinIf(shouldGetPhones, "phones p ON p.client_id = c.client_id").
    Build()

// Produces "SELECT * FROM client c JOIN a.client_id = c.client_id" if shouldGetPhones is false
// Produces "SELECT * FROM client c JOIN a.client_id = c.client_id JOIN phones p ON p.client_id = c.client_id" " if shouldGetPhones is true

And boolean connectors

query, _ := v2.Select().
	Col("*").
	From("client c").	
	Where("c.status = 0").AndIf(shouldFilterByCountry, "country = 'CL'").
    Build()

// Produces "SELECT * FROM client c WHERE c.status = 0" when shouldFilterByCountry is false
// Produces "SELECT * FROM client c WHERE c.status = 0 AND country = 'CL'" when shouldFilterByCountry is true

Params

You can add params to almost any clause

query, params := v2.Select().
	Col("name, mail, ? AS max_credit", 1000000).
	From("client c").	
	Where("c.status = 0").And("country = ?", "CL").
    Limit("?", "100").Build()

Delete

Simple delete

query, _ := v2.Delete().From("client").Build()
// Produces "DELETE FROM client"

Simple del where

query, _ := v2.Delete().From("client").Where("client_id = 100").Build()
// Produces "DELETE FROM client WHERE client_id = 100"

Like with Select you can use parameters and conditionals with Delete

query, params := v2.Delete().From("client").Where("client_id = ?", 100).Build()
// Produces "DELETE FROM client WHERE client_id = ?"

query, params := v2.Delete().From("client").Where("1=1").AndIf(filterByClient, "client_id = ?", 100).Build()
// Produces "DELETE FROM client WHERE 1=1" when filterByClient is false
// Produces "DELETE FROM client WHERE 1=1 AND client_id = ?" when filterByClient is true

Update

Simple update

query, _ := v2.Update("client").Set("status = 0").Build()
// Produces UPDATE client SET status = 0

Update/where/order/limit

query, _ := v2.Update("client").
	Set("status = 0").
	Where("status = ?", 1).
	OrderBy("ciudad").
	Limit(10).
	Build()

You can use obreron to build an update/join query

query, _ := v2.Update("business AS b").
Join("business_geocode AS g").On("b.business_id = g.business_id").
Set("b.mapx = g.latitude, b.mapy = g.longitude").
Where("(b.mapx = '' or b.mapx = 0)").
And("g.latitude > 0").
Build()

// Produces "UPDATE business AS b JOIN business_geocode AS g ON b.business_id = g.business_id SET b.mapx = g.latitude, b.mapy = g.longitude WHERE (b.mapx = '' or b.mapx = 0) AND g.latitude > 0"

You can use obreron to build an update/select query

query, _ := v2.Update("items").
				ColSelect(Select().Col("id, retail / wholesale AS markup, quantity").From("items"), "discounted").
				Set("items.retail = items.retail * 0.9").
				Where("discounted.markup >= 1.3").
				And("discounted.quantity < 100").
				And("items.id = discounted.id").
	            Build()
// Produces UPDATE items ,( SELECT id, retail / wholesale AS markup, quantity FROM items ) discounted SET items.retail = items.retail * 0.9 WHERE discounted.markup >= 1.3 AND discounted.quantity < 100 AND items.id = discounted.id

Insert

Simple insert

query, params := Iv2.nsert().
	Into("client").
	Col("name, value", "'some name'", "'[email protected]'").
    Build()

// Produces "INSERT INTO client ( name, value ) VALUES ( ?, ? )"

insert select

query, params := v2.Insert().
    Into("courses").
    ColSelect("name, location, gid", 
		Select().
		Col("name, location, 1").
	    From("courses").
	    Where("cid = 2")
	).Build()

// Produces       "INSERT INTO courses ( name, location, gid ) SELECT name, location, 1 FROM courses WHERE cid = 2"

Other clauses

You can add others clauses using the Clause method

query, params := v2.Insert().Clause("IGNORE", "")
	Into("client").
	Col("name, value", "'some name'", "'[email protected]'").
    Build()

// Produces "INSERT IGNORE INTO client ( name, value ) VALUES ( ?, ? )"

The Clause method always will inject the clause after the last uses building command

📚 obreron - Awesome Go Library for Database

Detailed Description of obreron