howto make pragma statements persistent, e.g. for foreign_keys checks

[bernhardreiter]

Hello!

Writing a small RESTful webserver application, we rely on constrains in the sqlite db.
In our app the foreign_key checks were working once every second time. We figured that
go-sqlite3 will open several connections, probably recycling some, and some of the pool did not have
the "PRAGMA foreign_keys = ON" set.

Our solution was to use a connect_hook like

import (
        "database/sql"

        sqlite3 "github.com/mattn/go-sqlite3"
)

func init() {
        sql.Register("sqlite3_with_fk",
                &sqlite3.SQLiteDriver{
                        ConnectHook: func(conn *sqlite3.SQLiteConn) error {
                                _, err := conn.Exec("PRAGMA foreign_keys = ON", nil)
                                return err
                        },
                })
}

and later calling

sql.Open("sqlite3_with_fk", dataSourceName)

Is this the best way of doing this?

Because this is a common use case, people wanting to set some sqlite PRAGMAs
and use them everytims, we suggest to add a hint to the documentation.

Best Regards and thanks for creating go-sqlite3 as Free Software!
Bernhard

[gwenn]

http://sqlite.org/compile.html#default_foreign_keys
Regards.

[bernhardreiter]

On Thursday 19 November 2015 at 18:23:30, gwenn wrote:

http://sqlite.org/compile.html#default_foreign_keys

Hi gwenn,
I'm assuming that you are are pointing me to the possibilty to change
the default for sqlite at build time. Thanks for the hint!

Yes, I knew about this possibility, that it sometimes is not a good
option because an already build sqlite version is uses. Like if you
are on a Debian GNU/Linux you really would want to use the system sqlite
builds. One argument for this are security updates.

Therefore I think this will be common use case which should be solved on
go-sqlite3 level. And with the connect hook, there is a reasonable solution.
The behaviour of a go application with go-sqlite3 can be surprising, at least
it took us several hours to figure out what was going on and find a good
solution. A hint in the go-sqlite3 would have been very helpful. Thus we are
suggesting it.

Regards,
Bernhard

[gwenn]

There is another alternative:
http://sqlite.org/c3ref/open.html#urifilenamesinsqlite3open

db, err := sql.Open("sqlite3", "file:my.db?foreign_keys=on")

And somewhere in SQLiteDriver.Open

url, err := url.Parse(name)
// TODO error handling
params := url.Query()
if params["foreign_keys"] != nil {
  _, err := conn.Exec("PRAGMA foreign_keys = ON", nil)
  // TODO error handling
}

Regards

[mattn]

This fixes your problem? #407

[bernhardreiter]

#407 seems to solve the one of the pragma use cases by adding an explicit flag. It would improve our technical situation with foreign_keys. It does not offer a more general solution for other PRAGMA settings which this is issues is also about.

[blackheart01]

I currently use the connection hook method for all of the pragmas I'm using. As an alternative, you could use the advice from https://medium.com/@benbjohnson/structuring-applications-in-go-3b04be4ff091 on wrapping the DB and TX types. You could then add a method on the TX type that calls your pragmas on the TX before returning it. You could also add it directly to the TX returned by your wrapped DB.Begin() method. For example (edited example from the link I provided to suite our case):

type DB struct {
    *sql.DB
}
type Tx struct {
    *sql.Tx
}
func Open(dataSourceName string) (*DB, error) {
    db, err := sql.Open("sqlite", dataSourceName)
    if err != nil {
        return nil, err
    }
    return &DB{db}, nil
}
func (db *DB) Begin() (*Tx, error) {
    tx, err := db.DB.Begin()
    if err != nil {
        return nil, err
    }
    if _, err := tx.Exec("PRAGMA foreign_keys = ON", nil), err != nil {
        return nil, err
    }
    return &Tx{tx}, nil
}