Generic PRAGMA URI parameters about go-sqlite3 HOT 9 OPEN

Here is a primitive version of how to do it in a driver-agnostic way:

type connector struct {
	d   driver.Driver
	dsn string
}

func newConnector(driverName string, dsn string) (driver.Connector, error) {
	db, err := sql.Open(driverName, "")
	if err != nil {
		return nil, err
	}
	defer db.Close()

	return &connector{
		d:   db.Driver(),
		dsn: dsn,
	}, nil
}

func (c *connector) Connect(context.Context) (driver.Conn, error) {
	conn, err := c.d.Open(c.dsn)
	if err != nil {
		return nil, err
	}

	if err := exec(conn, `PRAGMA foreign_keys=ON`); err != nil {
		conn.Close()
		return nil, err
	}

	return conn, nil
}

func (c *connector) Driver() driver.Driver {
	return c.d
}

func exec(conn driver.Conn, query string) error {
	stmt, err := conn.Prepare(query)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(nil)
	return err
}

Then your code would do something like:

c, err := newConnector("sqlite3", "file:...")
if err != nil {
   ...
}

return sql.OpenDB(c)

from go-sqlite3.

Can you clarify the workflow here? Where are these query parameters coming from in the case of libraries like GORM? Inside the library itself, or from the user of the library?

from go-sqlite3.

From inside the library.

See this for a good example.

The default pragmas used are things like:

"journal_mode": "wal",
"synchronous":  "normal",
"temp_store":   "memory",
"mmap_size":    "268435456",
"foreign_keys": "on",

from go-sqlite3.

I don't really understand how that works. Presumably the user of the library still has to provide the URI containing the database path. But then, if they've also included the non-standard query parameters for this or any other driver, then the library's own defaults would counteract the user's intent.

And in the specific case of the library you linked, it takes a pre-created sql.DB instance. So I don't understand how the library being able to generically provide pragmas in the query string would help.

from go-sqlite3.

I don't really understand how that works. Presumably the user of the library still has to provide the URI containing the database path. But then, if they've also included the non-standard query parameters for this or any other driver, then the library's own defaults would counteract the user's intent.

Yes, that happens. But if a library that's managing its own schema in the database really needs foreign key support, what else do you expect them to do, besides try and force them on? Etc.

And in the specific case of the library you linked, it takes a pre-created sql.DB instance.

You can also provide a "path", which is what happens in most of the example code, and I guess, the way most people use it: https://github.com/nalgeon/redka/blob/main/redka.go#L94

I think it would be instructive for contributors of this driver to search GitHub for "PRAGMA" and "mattn" and see just how many people erroneously set pragmas on the connection pool, and never realize they need to register a custom driver with a ConnectHook.

from go-sqlite3.

If another library wishes to set pragmas in a driver-agnostic way, I think it would make more sense for it to do so via driver.Connector + sql.OpenDB. Then it will truly be agnostic, instead of relying on all possible drivers supporting some de facto query parameter format.

from go-sqlite3.

I'd be very interested in seeing how you could do that.

That is: implement a library that can work with both mattn and modernc (ignore my driver, it's not popular enough to matter), enable foreign keys on all connections it makes to a database, and does so without importing either driver, letting users of the library pick the driver, without build tags, without getting both drivers statically linked into the binary (which defeats the entire purpose, and is actually dangerous because of SQLite locking issues).

from go-sqlite3.

A workaround till decided maybe,

	sql.Register("sqlite3_with_hook_example",
		&sqlite3.SQLiteDriver{
			ConnectHook: func(conn *sqlite3.SQLiteConn) error {
				conn.Exec(`PRAGMA ...`)
				return nil
			},
		})

This would ensure that every new connection gets the pragma. I think. I want to verify, but time. 😆

from go-sqlite3.

That's a good trick: open and close a connection to get the driver.

Someone should make this a Gist; many libraries needlessly hard code a driver, or are buggy in terms of setting their needed PRAGMAs/etc.

from go-sqlite3.