SqlBldr

A lightweight sql generator

Overview

SqlBldr's purpose is to be a lightweight sql generator and as a result certain features found in similar libraries are absent.

It is my opinion that once you require a join in a query you are better served by taking advantage of sql language (features such as views).

Because SqlBldr uses jmoiron's sqlx (https://github.com/jmoiron/sqlx) to communicate with the database, when you need to you can write more complex queries and process it with sqlx

Usage

package main

import (
	"fmt"
	"log"

	"github.com/mayowa/sqlbldr"

	_ "github.com/mattn/go-sqlite3"
	"github.com/jmoiron/sqlx"
)

type Person struct {
	ID        int
	FirstName string
	LastName  string
	Email     string
}

func main() {
    //
    // connect to db
	db, err := sqlx.Connect("sqlite3", "file:/tmp/test.db")
	if err != nil {
		log.Fatal(err)
	}


    //
    // insert single record with a struct
	person := Person{
		FirstName: "chidinma",
		LastName:  "akinyemi",
		Email:     "chidi@gmail.com",
	}
	_aff_, err := sqlbldr.Insert("person").
		Values(person).
		Exec(db, &person.ID)
	if err != nil {
		log.Fatal(err)
	}

	// insert single record with a map
	another := map[string]interface{}{
		"first_name": "babe",
		"last_name":  "akinyemi",
		"email":      "chidi@gmail.com",
	}
	anonID := 0

	_, err = sqlbldr.Insert("person").
		Values(another).
		Exec(db, &anonID)
	if err != nil {
		log.Fatal(err)
	}



	//
	// insert multiple records with []struct
	persons := []Person{
		{
			ID:        2,
			FirstName: "bosman",
			LastName:  "akinyemi",
			Email:     "bosman@gmail.com",
		},
		{
			ID:        3,
			FirstName: "nwa",
			LastName:  "akinyemi",
			Email:     "nwa@gmail.com",
		},
	}

	_, err = sqlbldr.Insert("person").
		Values(persons).
		Exec(db, nil)
	if err != nil {
		log.Fatal(err)
	}

	// insert multiple records with []map
	cousins := []map[string]interface{}{
		{
			"first_name": "bude",
			"last_name":  "akinyemi",
			"email":      "bude@gmail.com",
		},
		{
			"first_name": "aya",
			"last_name":  "akinyemi",
			"email":      "aya@gmail.com",
		},
	}

	_, err = sqlbldr.Insert("person").
		Values(cousins).
		Exec(db, nil)
	if err != nil {
		log.Fatal(err)
	}



	//
	// delete record
	aff, err = sqlbldr.Delete("person").
		Where("id = ?", 2).
		Exec(db)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("deleted", aff)



	//
	// update record
	// note this will generate:
	// update person
	// 	set first_name='dayo', last_name='', email=''
	// where
	// 	id = 0
	p1 := Person{
		FirstName: "dayo",
	}
	_, err = sqlbldr.Update("person").
		Set(p1).
		Where("id = ?", 0).
		Exec(db)
	if err != nil {
		log.Fatal(err)
	}


	_, err = sqlbldr.Update("person").
		Set(map[string]interface{}{
			"last_name": "akinyemi",
			"email":     "dayo@gmail.com",
		}).
		Where("id = ?", 0).
		Exec(db)
	if err != nil {
		log.Fatal(err)
	}

}