While I understand why this is happening by default it makes a bunch of Postgresql queries impossible.

Examples:

• GroupBy function - GROUP BY count(x) will not work, because count(x) will be put in quotes. If I stick db.Raw in GroupBy I get the whole string in curly brackets instead of quotes - still fails
• Array search WHERE search_value = ANY(array_column) again, impossible, because either search value or function will be put in double quotes.

There seems to be something odd with prepared statements + MySQL which could mean we have another prepared statement leak.

This is a followup of: https://github.com/upper/db/issues/270#issuecomment-261646680

It would be ideal to find a query that gives the actual number of prepared statements on different databases, and add that query to our testing.

@xiam can you please finish this PR?

I started simple -- only for INSERTs -- and passing string instead of db.Raw or something. (We should support UPDATE too)

I really like the design of upper/db, it's super clean and minimal. However, I believe db.Open() should accept an array of settings objects. For example, it's common with mongodb to pass a bunch of hosts which are in a cluster and then the client will determine the master, etc. etc. I presume MySQL / Postgresl would have something similar in a master/slave arrangement. Thoughts?

Since uuids raw are byte arrays, the query for the inserted item will fail. This makes it impossible to save a record with a uuid (string) primary key

The argument passed will be []interface {}{(*db.InsertResult)(0xc00041c070)} and the error will be incorrect binary data format

I'm currently getting this message when running dep ensure:

✗ unable to deduce repository and source type for "upper.io/db.v3": unable to read metadata: unable to fetch raw metadata: failed HTTP request to URL "http://upper.io/db.v3?go-get=1": Get https://upper.io/db.v3?go-get=1: x509: certificate has expired or is not yet valid

Below is my Gopkg.toml:

[[constraint]]
  version = "0.3"
  name = "github.com/thoas/go-funk"

[[constraint]]
  name = "upper.io/db.v3"
  version = "v3.5.1"

[[constraint]]
  version = "v0.7.2"
  name = "github.com/hypnoglow/oas2"

[[constraint]]
  version = "v1.0.6"
  name = "github.com/sirupsen/logrus"

[[constraint]]
  version = "v3.2.0"
  name = "github.com/dgrijalva/jwt-go"

[[constraint]]
  version = "0.2"
  name = "github.com/google/uuid"

[[constraint]]
  version = "v2.3.0"
  name = "github.com/pressly/goose"

[[constraint]]
  name = "golang.org/x/crypto"
  revision = "614d502a4dac94afa3a6ce146bd1736da82514c6"

[[constraint]]
  name = "github.com/tebeka/selenium"
  revision = "a49cf4b98a36c2b21b1ccb012852bd142d5fc04a"

[[constraint]]
  name = "github.com/getsentry/raven-go"
  revision = "a9457d81ec91fa6d538567f14c6138e9ce5a37fb"

[[constraint]]
  name = "github.com/dnaeon/go-vcr"
  revision = "aafff18a5cc28fa0b2f26baf6a14472cda9b54c6"

[prune]
  go-tests = true
  unused-packages = true

It would be cool if the sqlgen was more like: https://github.com/lann/squirrel .. we could use it to build queries internally, but also expose it if someone wants to write their own crazy raw query

We should add support for https://github.com/cockroachdb/cockroach via our existing postgresql work. Cockroachdb uses the postgres wire protocol, works with lib/pq, but also adds some of its own sql extensions.

Pagination lets you split the results of a query into chunks containing a fixed number of items.

The PR here will add the same pagination tools for both db.Result and sqlbuilder.Selector.

Simple pagination

res = sess.Collection("posts").Paginate(20) // 20 results per page

err = res.All(&posts) // First 20 results of the query

err = res.Page(2).All(&posts) // Results from page 2 (limit 20, offset 40)

Or with the SQL builder:

q = sess.SelectFrom("posts").Paginate(20) 

// same as above

Cursor based pagination

res = sess.Collection("posts").
  Paginate(20). // 20 results per page
  Cursor("id") // using id as cursor

err = res.All(&posts) // First 20 results of the query

// Get the next 20 results starting from the last item of the previous query.
res = res.NextPage(posts[len(posts)-1].ID)
err = res.All(&posts) // Results from page 1, limit 20, offset 20

Other tools

res = res.Paginate(23)

totalNumberOfEntries, err = res.TotalEntries()

totalNumberOfPages, err = res.TotalPages()

The first page from a paginator is always 0.

I'm using v3.

Example: db.Select("foo", "bar").From("table").Where("foo = 1").Where("bar = 2") will produce SELECT foo, bar FROM table WHERE bar = 2

To get primary key set on save you just leave it empty and tag it with pk,omitempty - this works just fine.

What you can't do is get the same thing to happen for fields that are not primary key:

Let's say I've got a struct:

type data struct{
    Id          int64 `db:"id,pk,omitempty"`
    CreatedAt   *time.Time `db:"created_at,omitempty"`
}

that gets saved to a table

CREATE SEQUENCE data_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE data (
    id bigint DEFAULT nextval('data_id_seq'::regclass) NOT NULL,
    created_at timestamp DEFAULT now() NOT NULL
);

after saving data struct will have Id set to the next value of db sequence, but CreatedAt will remain nil despite the fact it too has been set to a default value defined in the db.

I think we need one more tag: dbprovided, dbdefault or something along those lines that will add those fields to RETURNING part of SQL query and make the update happen.

The example is for creation, but it can easily apply to update as well.

Adds a call to sqlTx.Rollback() when an error is returned from compat.ExecContext in the SQLite adapter. This addresses #669 SQLite Adapter doesn't roll back transaction on error, which I ran into while writing a SQLite-backed web app.

This seems to have also addressed https://github.com/upper/db/issues/636 - I was able to remove the case that skipped the test for SQLite.

Hi,

We're building an API using net/http (server) and github.com/gorilla/mux (router) and have some difficulties understanding how to best manage open connections in an efficient way using upper/db for mysql.

I believe the underlying sql/database module manages a pool of connections that each goroutine can use when needed and then release it back to the pool for others to use. However, when running the following code in a stress test, we see some weird behavior.

https://go.dev/doc/database/manage-connections

According to your documentation, we should start a session (open an actual connection) and then specify pool configuration details like max open connections, max idle connection, and connection max idle time which seems a bit weird to do that on an open/active session (connection).

After the session (connection) has been established, it's assigned to the global DB variable and used throughout the application including goroutines.

The weird behavior is that under stress, upper sometimes log slow queries - which is fine. However, each log statement includes information about the session in use. All of our logs state that a given query was slow for the session with id 00001. Given that our max open connections is set to 10, I would expect to see different session id's between 00001-00010. It seems like the same session is always being used and not balanced between a pool of 10-15 connections.

I've tried opening the connection (session) in each router handler (goroutine) but we don't want to open too many connections to the database.

I know that something is wrong with my code and my understanding, but I haven't been able to find any good answers, so... I hope someone can help!

import (
	"fmt"
	"log"
	"net/http"
	"time"

	"github.com/gorilla/mux"
	"github.com/upper/db/v4"
	"github.com/upper/db/v4/adapter/mysql"
)

var DB db.Session

func init() {
	databaseSettings := mysql.ConnectionURL{
		Host:     "host",
		Database: "database",
		User:     "user",
		Password: "password",
	}

	session, err := mysql.Open(databaseSettings) // this actually opens a connection!

	if err != nil {
		log.Fatalln("Cant connect to database")
	}

	session.SetMaxOpenConns(10)
	session.SetMaxIdleConns(5)                   
	session.SetConnMaxIdleTime(time.Minute * 10)

	DB = session
}

func main() {
	router := mux.NewRouter()
	router.HandleFunc("/path", Index).Methods("GET")

	server := &http.Server{
		Handler:      router,
		Addr:         ":8080",
		ReadTimeout:  time.Second * 5,
		WriteTimeout: time.Second * 60 * 2,
	}

	if err := server.ListenAndServe(); err != nil {
		log.Fatalln(err)
	}
}

// This function is called within it's own goroutine
func Index(w http.ResponseWriter, r *http.Request) {
	w.WriteHeader(http.StatusOK)

	// Dont mind the actual query, it's just to show the connection in use
	iterator := DB.SQL().Select("table.column").From("table").Iterator()

	defer iterator.Close()

	// Scan and more etc.

	fmt.Fprintf(w, "OK")
}

I have a query in this form

SELECT ...
FROM  ...
JOIN (SELECT ...
          FROM ...
          WHERE ... IN ? )
....

I've tried running it with Query(argSlice) or even with a prepared statement but the ? is never replaced with the contents of argSlice. I also tried bulding subqueries with chained methods with no success.

Is there a better way of doing this?

Thanks

In the SQLite adapter's StatementExec method, the opened transaction doesn't get rolled back if compat.ExecContext returns an error. This came up in a personal project after hitting a constraint violation - all further write calls to the DB would return database is locked errors until I restarted the application.

I was able to reproduce the behavior using this test:

package db_test

import (
	"path/filepath"
	"testing"

	"github.com/mattn/go-sqlite3"
	"github.com/upper/db/v4"
	"github.com/upper/db/v4/adapter/sqlite"
)

type A struct {
	A int64 `db:"a"`
}

type B struct {
	B int64 `db:"b"`
}

func checkErr(t *testing.T, err error) {
	if err != nil {
		t.Fatal(err)
	}
}

func TestDatabaseIsLocked(t *testing.T) {
	dbFile := filepath.Join(t.TempDir(), "test.db")

	// Open DB
	sess, err := sqlite.Open(sqlite.ConnectionURL{
		Database: dbFile,
		Options: map[string]string{
			"foreign_keys": "on",
		},
	})
	checkErr(t, err)
	defer sess.Close()

	// Set up scenario
	_, err = sess.SQL().Exec("CREATE TABLE IF NOT EXISTS table1 (a INTEGER NOT NULL PRIMARY KEY)")
	checkErr(t, err)
	_, err = sess.Collection("table1").Insert(&A{1})
	checkErr(t, err)
	_, err = sess.SQL().Exec("CREATE TABLE IF NOT EXISTS table2 (b INTEGER NOT NULL PRIMARY KEY)")
	checkErr(t, err)
	_, err = sess.Collection("table2").Insert(&B{2})
	checkErr(t, err)

	// Trigger constraint violation on table 1
	_, err = sess.Collection("table1").Insert(&A{1})
	if err.(sqlite3.Error).Code != sqlite3.ErrConstraint {
		panic(err)
	}

	// Run tests
	t.Run("Read from table 1", testReadTable1(sess))
	t.Run("Read from table 2", testReadTable2(sess))
	t.Run("Insert into table 1", testInsertTable1(sess))
	t.Run("Insert into table 2", testInsertTable2(sess))
}

func testReadTable1(sess db.Session) func(t *testing.T) {
	return func(t *testing.T) {
		err := sess.Collection("table1").Find().One(new(A))
		if err != nil {
			t.Error(err)
		}
	}
}

func testReadTable2(sess db.Session) func(t *testing.T) {
	return func(t *testing.T) {
		err := sess.Collection("table2").Find().One(new(B))
		if err != nil {
			t.Error(err)
		}
	}
}

func testInsertTable1(sess db.Session) func(t *testing.T) {
	return func(t *testing.T) {
		_, err := sess.Collection("table1").Insert(&A{3})
		if err != nil {
			t.Error(err)
		}
	}
}

func testInsertTable2(sess db.Session) func(t *testing.T) {
	return func(t *testing.T) {
		_, err := sess.Collection("table2").Insert(&B{3})
		if err != nil {
			t.Error(err)
		}
	}
}

with the following output:

~/src/emm035/playground $ go test -v -count 1 .
--- FAIL: TestActions (20.81s)
    --- FAIL: TestActions/Insert_into_table_1 (10.39s)
        db_test.go:85: database is locked
    --- FAIL: TestActions/Insert_into_table_2 (10.41s)
        db_test.go:94: database is locked
FAIL
FAIL    github.com/emm035/playground  23.884s
FAIL

Adding a rollback statement to the error handling branch here seemed to stop the locking in my application and the test is passing:

~/src/emm035/playground $ go test -v -count 1 .
ok      github.com/emm035/playground  0.114s

I'd be more than happy to submit a PR with the change, but wanted to make sure this is fixing the actual problem and not just obscuring another issue 🙂