A Go SQL query builder and struct mapper.

Overview

godb - a Go query builder and struct mapper

Build Status GoDoc

godb is a simple Go query builder and struct mapper, not a full-featured ORM. godb does not manage relationships.

Initially, godb was a learning project. The goal was to improve my Go skills by doing some useful things. But more and more features have been added and godb has become a serious project that can be used by others.

godb is a project that is still young and evolving. The API is almost stable, but it can still change slightly from one version to another. Each new version is associated with a tag, so it is possible to target a particular one if necessary.

Features

  • Queries builder.
  • Mapping between structs and tables (or views).
  • Mapping with nested structs.
  • Execution of custom SELECT, INSERT, UPDATE and DELETE queries with structs and slices.
  • Optional execution of SELECT queries with an iterator to limit memory consumption if needed (e.g. batches).
  • Execution of raw queries, mapping rows to structs.
  • Optimistic Locking
  • SQL queries and durations logs.
  • Two adjustable prepared statements caches (with/without transaction).
  • RETURNING support for PostgreSQL.
  • OUTPUT support for SQL Server.
  • Optional common db errors handling for backend databases.(db.UseErrorParser())
  • Define your own logger (should have Println(...) method)
  • Define model struct name to db table naming with db.SetDefaultTableNamer(yourFn). Supported types are: Plural,Snake,SnakePlural. You can also define TableName() string method to for your struct and return whatever table name will be.
  • BlackListing or WhiteListing columns for struct based inserts and updates.
  • Could by used with
    • SQLite
    • PostgreSQL
    • MySQL / MariaDB
    • MS SQL Server
    • other compatible database if you write an adapter.

I made tests of godb on differents architectures and operating systems : OSX, Windows, Linux, ARM (Cortex A7) and Intel x64.

The current version of godb is compatible from Go 1.13 to 1.16. Older versions through 1.10 to 1.12 are supported by the v1.0.14 tag .

Documentation

There are three forms of documentation :

  • This README with the example presented below, which gives an overview of what godb allows.
  • The tests in dbtests/common, which are run on the different databases supported.
  • Detailed documentation on GoDoc: https://godoc.org/github.com/samonzeweb/godb

Install

go get github.com/samonzeweb/godb

Install the required driver (see tests). You cas use multiple databases if needed.

Of course you can also use a dependency management tool like dep.

Running Tests

godb tests use GoConvey and at least SQLite :

go get github.com/smartystreets/goconvey
go get github.com/mattn/go-sqlite3

To run tests, go into the godb directory and executes go test ./...

SQLite tests are done with in memory database, it's fast. You can run tests with others databases, see below.

With the exception of SQLite, all drivers are pure Go code, and does not require external dependencies.

Test with PostgreSQL

Install the driver and set the GODB_POSTGRESQL environment variable with the PostgreSQL connection string.

go get github.com/lib/pq
GODB_POSTGRESQL="your connection string" go test ./...

Test with MySQL / MariaDB

Install the driver and set the GODB_MYSQL environment variable with the MySQL connection string.

go get github.com/go-sql-driver/mysql
GODB_MYSQL="your connection string" go test ./...

Test with MS SQL Server

Install the driver and set the GODB_MSSQL environment variable with the SQL Server connection string.

go get github.com/denisenkom/go-mssqldb
GODB_MSSQL="your connection string" go test ./...

Test all with Docker

Using Docker you can test with SQLite, PostgreSQL, MariaDB and SQL Server with the testallwithdocker.sh shell script.

SQL Server is greedy, on OSX allow at least 4Go to Docker.

Example

The example below illustrates the main features of godb.

You can copy the code into an example.go file and run it. You need to create the database and the books table as explained in the code.

package main

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

	"github.com/samonzeweb/godb"
	"github.com/samonzeweb/godb/adapters/sqlite"
	"log"
	"os"
)

/*
  To run this example, initialize a SQLite3 DB called 'library.db' and add
  a 'books' table like this :

  create table books (
  	id        integer not null primary key autoincrement,
  	title     text    not null,
  	author    text    not null,
  	published date    not null);
*/

// Struct and its mapping
type Book struct {
	Id        int       `db:"id,key,auto"`
	Title     string    `db:"title"`
	Author    string    `db:"author"`
	Published time.Time `db:"published"`
}

// Optional, default if the struct name (Book)
func (*Book) TableName() string {
	return "books"
}

// See "group by" example
type CountByAuthor struct {
	Author string `db:"author"`
	Count  int    `db:"count"`
}

func main() {
	// Examples fixtures
	var authorTolkien = "J.R.R. tolkien"

	var bookTheHobbit = Book{
		Title:     "The Hobbit",
		Author:    authorTolkien,
		Published: time.Date(1937, 9, 21, 0, 0, 0, 0, time.UTC),
	}

	var bookTheFellowshipOfTheRing = Book{
		Title:     "The Fellowship of the Ring",
		Author:    authorTolkien,
		Published: time.Date(1954, 7, 29, 0, 0, 0, 0, time.UTC),
	}

	var bookTheTwoTowers = Book{
		Title:     "The Two Towers",
		Author:    authorTolkien,
		Published: time.Date(1954, 11, 11, 0, 0, 0, 0, time.UTC),
	}

	var bookTheReturnOfTheKing = Book{
		Title:     "The Return of the King",
		Author:    authorTolkien,
		Published: time.Date(1955, 10, 20, 0, 0, 0, 0, time.UTC),
	}

	var setTheLordOfTheRing = []Book{
		bookTheFellowshipOfTheRing,
		bookTheTwoTowers,
		bookTheReturnOfTheKing,
	}

	// Connect to the DB
	db, err := godb.Open(sqlite.Adapter, "./library.db")
	panicIfErr(err)
	// OPTIONAL: Set logger to show SQL execution logs
	db.SetLogger(log.New(os.Stderr, "", 0))
	// OPTIONAL: Set default table name building style from struct's name(if active struct doesn't have TableName() method)
	db.SetDefaultTableNamer(tablenamer.Plural())
	// Single insert (id will be updated)
	err = db.Insert(&bookTheHobbit).Do()
	panicIfErr(err)

	// Multiple insert
	// Warning : BulkInsert only update ids with PostgreSQL and SQL Server!
	err = db.BulkInsert(&setTheLordOfTheRing).Do()
	panicIfErr(err)

	// Count
	count, err := db.SelectFrom("books").Count()
	panicIfErr(err)
	fmt.Println("Books count : ", count)

	// Custom select
	countByAuthor := make([]CountByAuthor, 0, 0)
	err = db.SelectFrom("books").
		Columns("author", "count(*) as count").
		GroupBy("author").
		Having("count(*) > 3").
		Do(&countByAuthor)
	fmt.Println("Count by authors : ", countByAuthor)

	// Select single object
	singleBook := Book{}
	err = db.Select(&singleBook).
		Where("title = ?", bookTheHobbit.Title).
		Do()
	if err == sql.ErrNoRows {
		// sql.ErrNoRows is only returned when the target is a single instance
		fmt.Println("Book not found !")
	} else {
		panicIfErr(err)
	}

	// Select single record values
	authorName := ""
	title := ""
	err = db.SelectFrom("books").
		Where("title = ?", bookTheHobbit.Title).
		Columns("author", "title").
		Scanx(&authorName, &title)
	if err == sql.ErrNoRows {
		// sql.ErrNoRows is only returned when the target is a single instance
		fmt.Println("Book not found !")
	} else {
		panicIfErr(err)
	}

	// Select multiple objects
	multipleBooks := make([]Book, 0, 0)
	err = db.Select(&multipleBooks).Do()
	panicIfErr(err)
	fmt.Println("Books found : ", len(multipleBooks))

	// Iterator
	iter, err := db.SelectFrom("books").
		Columns("id", "title", "author", "published").
		DoWithIterator()
	panicIfErr(err)
	for iter.Next() {
		book := Book{}
		err := iter.Scan(&book)
		panicIfErr(err)
		fmt.Println(book)
	}
	panicIfErr(iter.Err())
	panicIfErr(iter.Close())

	// Raw query
	subQuery := godb.NewSQLBuffer(0, 0). // sizes are indicative
						Write("select author ").
						Write("from books ").
						WriteCondition(godb.Q("where title = ?", bookTheHobbit.Title))

	queryBuffer := godb.NewSQLBuffer(64, 0).
		Write("select * ").
		Write("from books ").
		Write("where author in (").
		Append(subQuery).
		Write(")")

	panicIfErr(queryBuffer.Err())

	books := make([]Book, 0, 0)
	err = db.RawSQL(queryBuffer.SQL(), queryBuffer.Arguments()...).Do(&books)
	panicIfErr(err)
	fmt.Printf("Raw query found %d books\n", len(books))

	// Update and transactions
	err = db.Begin()
	panicIfErr(err)

	updated, err := db.UpdateTable("books").Set("author", "Tolkien").Do()
	panicIfErr(err)
	fmt.Println("Books updated : ", updated)

	bookTheHobbit.Author = "Tolkien"
	err = db.Update(&bookTheHobbit).Do()
	panicIfErr(err)
	fmt.Println("Books updated : ", updated)

	err = db.Rollback()
	panicIfErr(err)

	// Delete
	deleted, err := db.Delete(&bookTheHobbit).Do()
	panicIfErr(err)
	fmt.Println("Books deleted : ", deleted)

	deleted, err = db.DeleteFrom("books").
		WhereQ(godb.Or(
			godb.Q("author = ?", authorTolkien),
			godb.Q("author = ?", "Georged Orwell"),
		)).
		Do()
	panicIfErr(err)
	fmt.Println("Books deleted : ", deleted)

	// Bye
	err = db.Close()
	panicIfErr(err)
}

// It's just an example, what did you expect ? (never do that in real code)
func panicIfErr(err error) {
	if err != nil {
		panic(err)
	}
}

Licence

Released under the MIT License, see LICENSE.txt for more informations.

Comments
  • Tablenamer

    Tablenamer

    • Define table name for structs. (You can call one of SetTableNamer... functions at your main function to use a namer.) For example, if struct's name is BookAuthor:
      • Default tablename will be same as struct's name(SetTableNamerSame()). Example table name will be BookAuthor.
      • Call SetTableNamerPlural() to use plural name of struct. Example table name will be BookAuthors.
      • Call SetTableNamerSnake() to get struct name as sname format. Example table name will be book_author.
      • Call SetTableNamerSnakePlural() to get struct name as plural snake format. Example table name will be book_authors.
      • If none of options is good for you add TableName() string method to for your struct and return whatever table name will be.
    opened by derkan 14
  • added JSONStr and CompactJSONStr

    added JSONStr and CompactJSONStr

    JSONStr makes easy to handle JSON data at database's text fields(like VARCHAR,CHAR,TEXT) and blob fields(like BLOB, BYTEA, JSONB). NullJSONStr is Nullable version of JSONStr.

    CompactJSONStr is same as JSONStr except, whitespaces are remove from JSON formatted data while saving to database. It is useless if data is hold in database specific json typed field(like PostgreSQL's JSONB field). NullCompactJSONStr is nullable version of CompactJSONStr.

    Tested on PosgreSQL 9.6, CockroachDB 2 and SqlLite, but should run on others, as these new types depend on database's TEXT or BLOB fields.

    opened by derkan 6
  • Suporting cursors(get one row at a time)

    Suporting cursors(get one row at a time)

    Hi Sam,

    It will be helpful to expose a function from SelectStatement to get rows one by one. It will be useful if user doesn't want to fetch all data from DB.

    Of course we can use LIMIT and OFFSET but, if size of row is big(for example: rows with columns containing file contents) it will be helpful to get one by one and break if necessary.

    derkan.

    opened by derkan 6
  • Need better UUID/GUID support

    Need better UUID/GUID support

    We use uuid as primary keys in Postgres, and this probably applies to GUID types in MS SQL Server. Things like hard-coded int64 primary keys (both Do and DoWithReturning on *InsertStatement) make it kind of a pain to work with. So, humbly requesting this feature.

    opened by finsterdexter 5
  • Added NullTime, NullBytes & Null* helpers

    Added NullTime, NullBytes & Null* helpers

    Null helpers&types

    • Types: NullTime, NullBytes
    • Helpers for initializing Null* values: NullBoolFrom, NullStringFrom, NullFloat64From, NullInt64From, NullTimeFrom, NullBytesFrom
    opened by derkan 5
  • Fetching performance

    Fetching performance

    Hi Samuel,

    I did some benchmarking&profiling. Fetching is spending time&memory on calling (smd *structMappingDetails) traverseTree method. It is critical especially while scanning high number of rows from results(for reporting).

    Full results of benchmarking is at this repo. As for every fetching of rows(single or multiple, after inserts and updates also) godb is calling traverseTree and that puts godb into middle of report. I think it will get better if a caching mechanism is added instead of running reflections for each time. Caching should be implemented specific to db session instance.

    For example pg orm make use of this kind of caching.

    I've checked if some kind of caching for column mapping is possible, but unfortunately couldn't find a place in code to implement. Can you also check this?

    Here is profiling output for scanning rows of SQL with LIMIT 10000:

    Showing top 10 nodes out of 81
          flat  flat%   sum%        cum   cum%
      274.51MB 29.92% 29.92%   457.53MB 49.87%  github.com/samonzeweb/godb/dbreflect.(*structMappingDetails).traverseTree /data/Development/MyOpenSource/GODB/src/github.com/samonzeweb/godb/dbreflect/dbreflect.go
       95.01MB 10.36% 40.28%    95.01MB 10.36%  github.com/samonzeweb/godb/dbreflect.(*StructMapping).GetAllFieldsPointers.func1 /data/Development/MyOpenSource/GODB/src/github.com/samonzeweb/godb/dbreflect/dbreflect.go
       58.50MB  6.38% 46.66%    58.50MB  6.38%  github.com/lib/pq.textDecode /data/Development/MyOpenSource/GODB/src/github.com/lib/pq/encode.go
       50.88MB  5.55% 52.20%    50.88MB  5.55%  github.com/lib/pq.(*stmt).exec /data/Development/MyOpenSource/GODB/src/github.com/lib/pq/buf.go
          44MB  4.80% 57.00%       44MB  4.80%  reflect.unsafe_New /usr/local/src/go/src/runtime/malloc.go
          43MB  4.69% 61.69%       43MB  4.69%  reflect.(*structType).Field /usr/local/src/go/src/reflect/type.go
       36.37MB  3.96% 65.65%    42.39MB  4.62%  github.com/samonzeweb/godb.(*SQLBuffer).WriteBytes /data/Development/MyOpenSource/GODB/src/github.com/samonzeweb/godb/sqlbuffer.go
       33.27MB  3.63% 69.28%    33.77MB  3.68%  database/sql.driverArgs /usr/local/src/go/src/database/sql/convert.go
       27.50MB  3.00% 72.28%       38MB  4.14%  github.com/samonzeweb/godb/dbreflect.(*StructMapping).GetNonAutoFieldsValues.func1 /data/Development/MyOpenSource/GODB/src/github.com/samonzeweb/godb/dbreflect/dbreflect.go
       20.22MB  2.20% 74.48%   123.64MB 13.48%  github.com/lib/pq.(*conn).QueryContext /data/Development/MyOpenSource/GODB/src/github.com/lib/pq/conn_go18.go
    (pprof) 
    

    Sample SQL is:

    type GDModel struct {
    	Id      int           `db:"id,key,auto"`
    	Name    string        `db:"name"`
    	Title   string        `db:"title"`
    	Fax     string        `db:"fax"`
    	Web     string        `db:"web"`
    	Age     int           `db:"age"`
    	Right   bool          `db:"right"`
    	Counter int64         `db:"counter"`
    }
    
    func (*GDModel) TableName() string {
    	return "models"
    }
    
    var models []*GDModel
    if err := db.Select(&models).Where("id > ?", 0).Limit(10000).Do(); err != nil {
    	fmt.Printf("slice err: %v\n", err)
    }
    
    opened by derkan 5
  • Upsert support

    Upsert support

    I think this would be a good feature because it avoids one query.

    Currently I've to do it this way:

    err := db.Insert(&obj).Do()
    if err != nil {
       db.Update(&obj).Do()
    }
    

    Eric

    opened by lozaeric 4
  • Request Feature - ToSQL on struct_insert.go

    Request Feature - ToSQL on struct_insert.go

    Gi. Can I request that the following is added to struct_insert.go

    // ToSQL returns a string with the SQL statement (containing placeholders),
    // the arguments slices, and an error.
    func (si *StructInsert) ToSQL() (string, []interface{}, error) {
    	a, b, c := si.insertStatement.ToSQL()
    	return a, b, c
    }
    
    
    opened by raxisau 3
  • postgresql schema

    postgresql schema

    i use schema to group tables in my db. i usually write my query like this

    SELECT * FROM auth.user
    

    so i write a TableName() like this

    func (*User) TableName() string { return "auth.user" }
    

    but postgresql complains that relation "auth.user" does not exists. initial digging suggest that the Quote() function in godb simply apply quote around identifier, resulting in "auth.schema". while proper postgresql schema identifier should be "auth"."schema"

    if i want to contribute a fix, should i modify the adapter's Quote() function or somewhere else?

    opened by blackpawninja 3
  • If no columns defined for selection, get all columns

    If no columns defined for selection, get all columns

    If no columns defined for selection, get all columns (SELECT * FROM)

    booksWithInventories := make([]BooksWithInventories, 0, 0)
    db.SelectFrom("books").
        LeftJoin("inventories", "inventories", godb.Q("inventories.book_id = books.id")).
        Do(&booksWithInventories)
    

    No need to call ColumnsFromStruct(&booksWithInventories), if we want all columns in destination interface.

    opened by derkan 3
  • White/Black list support for struct Update/Insert

    White/Black list support for struct Update/Insert

    Hi Sam,

    Without w/b listing struct insert/update becomes useless and forces developer to use InsertInto/UpdateInto methods which results in lots of boilerplate code. So #21 is to resolve this.

    Can you review #21?

    Erkan.

    opened by derkan 3
  • removed driver dependencies

    removed driver dependencies

    Hi Samuel, I hope you are doing well. I removed db driver dependencies in this PR.

    PR provides using pgx/stdlib driver instead of pg(or modernc.org/sqlite instead of mattn/go-sqlite3...). Also this makes exe size smaller when user uses alternate drivers.

    Only drawback: user should import his/her driver before using it(not auto imported when imported relevant adapter. And I think mostly users do this beforehand.

    opened by derkan 1
  • Context support

    Context support

    Hi Sam,

    What do you think about adding Context support? My suggestions are: 1- Setting it connection-wide like db.WithContext(context.Context) and each runner interface check if ctx is nil or not and call suitable sql func. 2- For each runner interface add context param with naming suffix ...Ctx like db.SelectFrom("books").DoCtx(&books, ctx). This one will need every runner and Transaction methods to be duplicated for Context parameter with Ctx suffix but more Go-like. 3- Without changing any interface, altering current runners with contex'ed ones internally, and get/set context with a function. For ex: db.SelectFrom("books").Do() internally will run ExecTx with ctx.Background.

    If you can check and give directions I can add a PR for this according to your suggestions or better you can add this to godb.

    Erkan.

    opened by derkan 2
Owner
Samuel GAY
Samuel GAY
BQB is a lightweight and easy to use query builder that works with sqlite, mysql, mariadb, postgres, and others.

Basic Query Builder Why Simple, lightweight, and fast Supports any and all syntax by the nature of how it works Doesn't require learning special synta

Aaron M 48 Sep 26, 2022
A flexible and powerful SQL string builder library plus a zero-config ORM.

SQL builder for Go Install Usage Basic usage Pre-defined SQL builders Build SQL for MySQL, PostgreSQL or SQLite Using Struct as a light weight ORM Nes

Huan Du 844 Sep 27, 2022
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

DiDi 1.4k Sep 21, 2022
A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management

ploto A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management It's not an ORM. wo

solar 2 Mar 16, 2022
OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.

OctoSQL OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases, streaming sources and file formats using

Jacob Martin 4.1k Sep 25, 2022
auto generate sql from gorm model struct

gorm2sql: auto generate sql from gorm model struct A Swiss Army Knife helps you generate sql from gorm model struct. Installation go get github.com/li

Danieliu 116 Aug 22, 2022
ddl-maker generate ddl (SQL file) from Go struct.

[日本語] What is ddl-maker ddl-maker generate ddl (SQL file) from golang struct. It's only supported MySQL only now. The original code is kayac/ddl-maker

CHIKAMATSU Naohiro 3 Jun 16, 2022
Use SQL to query host, DNS and exploit information using Shodan. Open source CLI. No DB required.

Shodan Plugin for Steampipe Query Shodan with SQL Use SQL to query host, DNS and exploit information using Shodan. For example: select * from shod

Turbot 23 May 10, 2022
Query and Provision Cloud Infrastructure using an extensible SQL based grammar

Deploy, Manage and Query Cloud Infrastructure using SQL [Documentation] [Developer Guide] Cloud infrastructure coding using SQL InfraQL allows you to

InfraQL 22 Apr 5, 2022
Use SQL to instantly query file, domain, URL and IP scanning results from VirusTotal.

VirusTotal Plugin for Steampipe Use SQL to query file, domain, URL and IP scanning results from VirusTotal. Get started → Documentation: Table definit

Turbot 24 Aug 14, 2022
Use SQL to instantly query users, groups, applications and more from Okta. Open source CLI. No DB required.

Okta Plugin for Steampipe Use SQL to query infrastructure including users, groups, applications and more from Okta. Get started → Documentation: Table

Turbot 5 Sep 11, 2022
Use SQL to instantly query instances, networks, databases, and more from Scaleway. Open source CLI. No DB required.

Scaleway Plugin for Steampipe Use SQL to query infrastructure servers, networks, databases and more from your Scaleway project. Get started → Document

Turbot 6 Jul 16, 2022
Use SQL to query information including Users, Groups, Clients, Roles and more from Keycloak.

Keycloak Plugin for Steampipe [WIP] THIS IS NOT ACTIVE NOR WORKING YET - DO NOT USE Use SQL to query information including Users, Groups, Clients, Rol

APS Group 2 Jul 1, 2022
Use SQL to query instances, domains and more from Prometheus.

Use SQL to instantly query Prometheus metrics, alerts, labels and more. Open source CLI. No DB required.

Turbot 15 Jul 31, 2022
Use SQL to instantly query resources, data sources and more from Terraform code. Open source CLI. No DB required.

Terraform Plugin for Steampipe Use SQL to query data from Terraform configuration files. Get started → Documentation: Table definitions & examples Com

Turbot 12 Sep 26, 2022
Use SQL to instantly query Algolia indexes and configuration. Open source CLI. No DB required

Use SQL to instantly query Algolia indexes and configuration. Open source CLI. No DB required

Turbot 1 Feb 12, 2022
Use SQL to query databases, logs and more from PlanetScale

Use SQL to instantly query PlanetScale databases, branches and more. Open source CLI. No DB required.

Turbot 1 Feb 13, 2022
Query redis with SQL

reqlite reqlite makes it possible to query data in Redis with SQL. Queries are executed client-side with SQLite (not on the redis server). This projec

Augmentable 45 Aug 31, 2022
Use SQL to instantly query Datadog resources across accounts. Open source CLI. No DB required.

steampipe-plugin-datadog Datadog Plugin for Steampipe Use SQL to query dashboards, users, roles and more from Datadog. Get started → Documentation: Ta

Turbot 7 Aug 26, 2022