Additions to Go's database/sql for super fast performance and convenience. (fork of gocraft/dbr)

When I'll try to get latest stable version of mailru/dbr:

module some/project

require (
	...
	github.com/mailru/dbr v0.0.0-20180825184916-bee39f330c3b
	...
)

Problem is in release-tag naming:

vX.Y with vX.Y.Z

Could you please create a new GitHub release or just version tag, because dependency managers like the official dep (https://github.com/golang/dep) use those versions by default.

Hello guys, I don't understand what I'm doing wrong, this IS a client program after all. With sqlx I'm using "tcp://host:9000" and it works correctly, we're evaluating using dbr instead but it complains: unsupported protocol scheme "tcp"

I've tried a bunch of things already, setting only host/default it complains until I call Load: panic: runtime error: index out of range [0] with length 0 Actually, it doesn't matter what I put as host in Open(), I even tried with foo.var as host and it ran all the way to the call of query.Load() xD

I tried with and without user, different ports, https scheme, I don't know what else to try, I haven't found any different examples here or otherwise.

Any suggestions?

package main

import (
	"log"

	"github.com/mailru/dbr"
	_ "github.com/mailru/go-clickhouse"
)

func main() {
	connect, err := dbr.Open("clickhouse", "http://default@host:9000/default", nil)
	if err != nil {
		log.Fatalln(err)
	}
	sess := connect.NewSession(nil)
	query := sess.Select("start").From("sometable").Where(dbr.Eq("id", 3))

	var result []struct {
		Start string `db:"start"`
	}
	count, err := query.Load(&result)
	if err != nil {
		log.Fatalln(err)
	}

	log.Printf("count %d  select %v\n", count, result)
}

Sometimes when using transactions I want to enforce behavior by setting read-only or isolation level. The current Session.Begin doesn't take any options and when it eventually calls BeginTx to get the underlying sql.Tx it just uses nil for the options.

This change is backward compatible and allows passing sql.TxOptions to BeginWithOpts while Begin retains it's original functionality.

Usage:

tx, err := sess.BeginWithOpts(&sql.TxOptions{
    Isolation: sql.LevelRepeatableRead,
})
defer tx.RollbackUnlessCommitted()

I'm trying to run the following sql query, but want to use the query builder:

SELECT * FROM project_user WHERE project_id IN (SELECT project_id FROM project_user WHERE user_id = 1)

What I have so far:

innerBuilder := sess.Select("project_id").From("project_user").Where("user_id = ?", userID)
builder := sess.Select("*").From("project_user").Where("project_id IN ???")

But I don't know how to put the innerBuilder into the outer statement, how can I do that?

Nice work! I was just playing with this and couldn't work out how to unmarshal joined tables. For example, in your example:

sess.Select("*").From("suggestions").
  Join("subdomains", "suggestions.subdomain_id = subdomains.id")

How would you store the query result into a struct using .Load(&suggestions) that contains []subdomain field?

During iteration with rows.Next() some errors can appear. It will be saved to Rows.lasterr, Next() will return false and iteration will be stopped.

Normal case is Rows.lasterr == io.EOF, then all data from db has been read. But there are other cases, for example - some transfer problem, then only part of data will be read and Load will not return this error.

So, it is required to check the latest error with Rows.Err(), it returns any err != nil && err != io.EOF.

Is there any way to use the select builder to do something like:

SELECT *
FROM "mytable"
WHERE "state" = ? AND "queue" = ?
ORDER BY "created_on"
LIMIT 1
FOR UPDATE SKIP LOCKED

The closest I get is:

dbr.From("mytable").
	Where("state = ? and queue = ?", statusQueued, queue).
	OrderBy("created_on").
	Limit(1).
	ForUpdate()

But that misses the last (essential) part SKIP LOCKED…

I think this lib should be Clickhouse first. So I have added key feature of Clickhouse: PREWHERE statement. Example: https://clickhouse.yandex/docs/en/query_language/select/#prewhere-clause

Prepare db:

create table test
(
    date  DateTime,
    event String
) engine MergeTree() order by date;

Run go code:

func TestIssue2(t *testing.T) {
	conn, _ := dbr.Open("clickhouse", "http://user:pass@localhost:8123/db", nil)

	conn.Exec("truncate table test")

	// 4 rows
	query := `insert into test (date, event) values 
	   ('2021-01-01 00:00:00', 'click'),
       ('2021-01-01 01:00:00', '"" '),
       ('2021-01-01 02:00:00', '"with quotes" 1'),
       ('2021-01-01 02:00:00', '"with quotes" 2')`

	conn.Exec(query)

	sess := conn.NewSession(nil)

	res, err := sess.Select("event").From("test").ReturnStrings()
	fmt.Println(err)
	fmt.Println(len(res))
	// 2 rows
}

I expect that it will be 4 rows in result.

Here's the case.

Prepare some db and test if it's working:

create table test (
     date DateTime,
     event String
) engine MergeTree() order by date;

insert into test format CSV
2021-01-01 00:00:00,click
2021-01-01 00:00:00,?

select * from test;

Run go code:

func TestIssue(t *testing.T) {
	conn, _ := dbr.Open("clickhouse", "http://user:pass@localhost:8123/db", nil)

	query := `insert into test (date, event) format CSV
2021-01-01 00:00:00,click
2021-01-01 00:00:00,?`

	_, err := conn.Exec(query)
	fmt.Println(err) //clickhouse: wrong placeholder count

	query = `insert into test (date, event) format CSV
"2021-01-01 00:00:00","click"
"2021-01-01 00:00:00","?"`

	_, err = conn.Exec(query)
	fmt.Println(err) //clickhouse: wrong placeholder count
}

I expect to get no errors here, because every query I run is perfectly valid.

По аналогии со структурой clickhouse добавил в остальные диалекты метод String(), возвращающий название драйвера.

Существующий пример из диалекта clickhouse

func (d clickhouse) String() string {
	return "clickhouse"
}