slicebit/qb

The database toolkit for go

qb - the database toolkit for go

This project is currently pre 1.

Currently, it's not feature complete. It can have potential bugs. There are no tests covering concurrency race conditions. It can crash especially in concurrency. Before 1.x releases, each major release could break backwards compatibility.

About qb

qb is a database toolkit for easier db queries in go. It is inspired from python's best orm, namely sqlalchemy. qb is an orm(sqlx) as well as a query builder. It is quite modular in case of using just expression api and query building stuff.

Documentation

The documentation is hosted in readme.io which has great support for markdown docs. Currently, the docs are about 80% - 90% complete. The doc files will be added to this repo soon. Moreover, you can check the godoc from here. Contributions & Feedbacks in docs are welcome.

Features

• Support for postgres(9.5.+), mysql & sqlite3
• Powerful expression API for building queries & table ddls
• Struct to table ddl mapper where initial table migrations can happen
• Transactional session api that auto map structs to queries
• Foreign key definitions
• Single & Composite column indices
• Relationships (soon.. probably in 0.3 milestone)

Installation

go get -u github.com/slicebit/qb

If you want to install test dependencies then;

go get -u -t github.com/slicebit/qb

Quick Start

package main

import (
	"fmt"
	"github.com/slicebit/qb"
	_ "github.com/mattn/go-sqlite3"
    _ "github.com/slicebit/qb/dialects/sqlite"
)

type User struct {
	ID       string `db:"id"`
	Email    string `db:"email"`
	FullName string `db:"full_name"`
	Oscars   int    `db:"oscars"`
}

func main() {

	users := qb.Table(
		"users",
		qb.Column("id", qb.Varchar().Size(40)),
		qb.Column("email", qb.Varchar()).NotNull().Unique(),
		qb.Column("full_name", qb.Varchar()).NotNull(),
		qb.Column("oscars", qb.Int()).NotNull().Default(0),
		qb.PrimaryKey("id"),
	)

	db, err := qb.New("sqlite3", "./qb_test.db")
	if err != nil {
		panic(err)
	}

	defer db.Close()

	metadata := qb.MetaData()

	// add table to metadata
	metadata.AddTable(users)

	// create all tables registered to metadata
	metadata.CreateAll(db)
	defer metadata.DropAll(db) // drops all tables

	ins := qb.Insert(users).Values(map[string]interface{}{
		"id":        "b6f8bfe3-a830-441a-a097-1777e6bfae95",
		"email":     "[email protected]",
		"full_name": "Jack Nicholson",
	})

	_, err = db.Exec(ins)
	if err != nil {
		panic(err)
	}

	// find user
	var user User

	sel := qb.Select(users.C("id"), users.C("email"), users.C("full_name")).
		From(users).
		Where(users.C("id").Eq("b6f8bfe3-a830-441a-a097-1777e6bfae95"))

	err = db.Get(sel, &user)
	fmt.Printf("%+v\n", user)
}

Credits

• Aras Can Akın
• Christophe de Vienne
• Onur Şentüre
• Aaron O. Ellis
• Shawn Smith

qb's Issues

Making organization of tests generally

Tests should be organized. A common test suite can be made.

Table based Expression API implementation

It is required to have an expression api that can be called from Table object. Table object can start a new query using builder. For instance,

table := NewTable() // make full initialization of table
values := map[string]interface{}{
    "id": 5,
    "fullName": "Aras Can Akin"
}
table.Insert(values)

More examples can be found here

comparator, aggregator, functions should use Escape()

The following builder functions should escape;

Avg(column string) string
Count(column string) string
Sum(column string) string
Min(column string) string
Max(column string) string
NotIn(key string, values ...interface{}) string
In(key string, values ...interface{}) string
NotEq(key string, value interface{}) string
Eq(key string, value interface{}) string
Gt(key string, value interface{}) string
Gte(key string, value interface{}) string
St(key string, value interface{}) string
Ste(key string, value interface{}) string

Test suite implementation for postgres

The test suite should be able to create sample tables, insert mock data, run queries on data and drop all tables at the end

dialect.Insert(table string, cols ...string).Values(vals ...interface{}) should be changed

This should be changed into the following example;

dialect.Insert(table string).Values(map [string]interface{})

This should change table's insert as the following;

table.Insert().Values(map[string]interface{})

implementation of optional escaping in adapters

Due to the feedback from google groups post, optional escaping can be achieved by a bool flag. The default option should be false.

Update() implementation of Table

Update(kv map[string]interface{}) should build an UPDATE ... SET ... statement inside table

Add FilterBy(map[string]interface{}) to session

Currently, it is needed a initialization of a struct pointer when querying with Find(). However, session should also be able to query with map[string]interface{}.

NewTable() key detection

NewTable() should detect primary & foreign keys and call AddPrimary() & AddRef() automatically

implementation of column escaping in dialects in table ddl

The table dll generators should be able to escape column names using dialect's Escape() function.

removing engine dependency from metadata

*Engine is only used in CreateAll() & DropAll() function calls. They should be CreateAll(engine *Engine), DropAll(engine *Engine). Session object should only keep the instance of *engine.

sqlx integration

Due to selective session function problems, it is way much easier to integrate sqlx. It has currently solid implementation especially StructScan() for select statements.

The integration should be the following;

• Engine should have db parameters as *sqlx.DB
• Table presumably won't have any changes
• NamedExec() can be used for dialect to be more generic just like sqlalchemy does
• The driver dependency of dialect will be removed.
• Sessions selective queries are much more easier

Removing types

It seems that keeping all types is not required. All distinct types should be removed. Only type struct definition and NewType() function should be left.

Engine pointer becomes nil after initialization

In tests, a test case makes engine pointer nil after initialization.

var engine *Engine

func TestSetup(t *testing.T) {
    engine := NewEngine(...)
}

func TestEngine(t *testing.T) {
    engine.Exec(query) // here engine is nil
}

This may be deriving from go 1.6 update

The right use of dialect abstraction, removal of driver dependency from builder

The current use of dialect is not efficient. It is only the definition of functions in builder. There could be separate dialect implementations with a common dialect. Structural composition can be used here. For instance;

func NewBuilder(driver string) *Dialect {
    // returns a new dialect given driver
}

type CommonDialect struct {
    // consists of current methods of builder
}

type PostgresDialect struct {
    CommonDialect
    // and extra methods
}

type MysqlDialect struct {
    CommonDialect
    // and extra methods
}

Index support in mapper

The mapper should be able to understand index tag. The index tag could be in the following format;

type User struct {
    id int `qb:"index"`
    email string `qb:"index"`
    qb.CompositeIndex `qb:"index:id, email"`
}

should build index on id, email, and a composite index id,email

Test suite implementation for mysql

The test suite should be able to create sample tables, insert mock data, run queries on data and drop all tables at the end

glide integration

refactor dialect -> adapter

All dialect naming should be renamed to adapter.

support for unsigned int mappings

Postgres doesn't support unsigned int mappings. It may not be needed but it could be useful to have it in mysql. This is also a potential bug when using uint types mapping into varchar(255)

potential race condition in session.Add()

Currently, if multiple goroutines calls session.Add(), it may cause to begin transactions without commiting and shows an error like transaction is already open. There should be a locking mechanism in Add() function that locks transaction opening

Adding optional tag to ignore struct field

The tags with "-" would not create a table column when it is mapped by mapper;

type User struct {
    ID string
    IgnoredColumn string `qb:"-"`
}

add Rollback() function to session api

separation of adapters

Each adapter should have its own file like the following;

- default_adapter.go
- mysql_adapter.go
- postgres_adapter.go
- sqlite_adapter.go

Test suite implementation for sqlite

The test suite should be able to create sample tables, insert mock data, run queries on data and drop all tables at the end

remove Sprintf's while detecting references in table

https://github.com/aacanakin/qb/blob/master/table.go#L109

Table should keep columns as a map[string]Column instead of []Column slice

The table struct currently has a slice of Column struct. It should be map[string]Column instead of slice of Column for performance improvements.

Logging module and log levels implementation

It is required to have a leveled logging solution when executing transactions, queries, etc. Glog can be used

adapter based auto increments

Currently, auto increment sql code is generated by mapper. It should be adapter based that should implement AutoIncrement() function.

Reorganization of all struct dependencies

Currently, it seems odd that structs have both driver string & dialect *Dialect dependency. The struct dependencies like this should be minimized as possible as it can be.

the spacing is inconsistent through the project

Fix these type of inconsistency; https://play.golang.org/p/EP1paAudS4

A possible solution to removing driver dependency from dialect

Currently, almost any structs, especially dialect depends on driver string. However, it seems it doesn't have to be that way.

Query struct would keep the placeholders in named statements. Only SQL() function should have driver dependency. Moreover, SQL() function should convert the named placeholders into native placeholders.

Composite foreign key generation doesn't generate accurate syntax

Composite foreign keys generate wrong syntax. For instance,

type User struct {
    id string `qb:"constraints:ref(profile.id)"`
    name string `qb:"constraints:ref(profile.name)"`
}

generates

CREATE TABLE user(
    id VARCHAR(255),
    name VARCHAR(255),
    FOREIGN KEY(id, `name`) REFERENCES profile(id, `name`)
)

All the key columns should be encapsulated by ``

quick start code not working

The quick start code doesn't seem to be working.

Race condition in map key value access

There exists a race condition in builder test such that update statements column order can be changed randomly. https://github.com/aacanakin/qb/blob/master/builder_test.go#L196-L210

Batch query support

The builder api should be able to build query templates, which means queries without bindings. This should be done like a Query() function call, namely Template(). Then engine functions should have something like BatchExec() which can take bindings as an array. There should also be an option to have transactions in batch exec. Maybe session api would do the trick.

Add support for entering inline table primary & foreign key constraints

Currently, generic struct mapping into table doesn't allow inline primary key, foreign key, unique & index constraints. The rule should be the following;

For primary key constraints;

• if multiple definition of primary_key constraint tag is present, then the mapper should merge them into table level primary key constraint.

For foreign key constraint;

• same with primary_key

For index and unique;

• there should be a type called CompositeIndex & CompositeUnique which is defined in struct as the following;

type User struct {
    Email string `qbit:"constraints:index,unique"`
    Password string `qbit:"constraints:index"`
    CompositeIndex `qbit:"email,password"`
}

,the indices would be the following;

• email index
• email unique
• password index
• email,password index

The mysql test suite is failing to create table

The mapper is skipping bio column and prints an empty line instead of column sql.

The engine pointer should be owned by session, not metadata

Mapper test structs should be inline defined

remove table api's query builders

This makes confusing to choose whether use the builder api or table api. Table api doesn't require query builders. The builder does the job.

table.Insert().Values() not adding bindings to the query

Improvements of session.Find()

Currently, Find() function implementation is really messy. There are ugly hacks that is made for just to retrieve result set as an array and map it into a struct. The following library can be used in order to map the struct from a map[string]interface{};

• https://github.com/mitchellh/mapstructure

Engine should be composed by *sqlx.DB instead of having a db field

docs

An official docs page is required. All documentation would be there. it is qb.readme.io
Quick start should also have query building api

Delete() implementation of Table

Delete(kv map[string]interface{}) should build an DELETE FROM ... WHERE ... statement inside table

LastInsertId is failing when postgres is the driver

lib/pg is not supporting LastInsertId. Therefore, Returning() clause should be added to query builder.
lib/pq#24

Adding PrimaryKey() & ForeignKey() functions in constraints

Currently, It is required to have Add* calls to table after generating a primary key. However, It could just be done defining table constraints in NewTable().

add Engine function to session

For easier query builder api, the Engine() function should be present in session.

Improvements of Session

The Session should be improved. It is currently doing nothing. The session should be a container of queries and active transaction. More info could be found here