Foreign keys in pool?

[delaneyj]

I have a write pool of 1 via

	uri := fmt.Sprintf("file:%s?_journal_mode=WAL&_synchronous=NORMAL&_foreign_keys=1", dbFilename)
	writePool, err := sqlitex.Open(uri, 0, 1)
	if err != nil {
		return nil, fmt.Errorf("could not open write pool: %w", err)
	}
	conn := writePool.Get(ctx)
	defer writePool.Put(conn)

        // It's off if I don't set it here
	fkOnStmt := conn.Prep("PRAGMA foreign_keys = on;")
	defer fkOnStmt.Finalize()
	if _, err := fkOnStmt.Step(); err != nil {
		return nil, fmt.Errorf("migrate database: %w", err)
	}

	fkCheckStmt := conn.Prep("PRAGMA foreign_keys;")
	defer fkCheckStmt.Finalize()
	if _, err := fkCheckStmt.Step(); err != nil {
		return nil, fmt.Errorf("migrate database: %w", err)
	}
	foreignKeysEnabled := fkCheckStmt.ColumnBool(0)
	if !foreignKeysEnabled {
		return nil, fmt.Errorf("foreign keys are not enabled")
	}

	if err := sqlitemigration.Migrate(ctx, conn, sqlitemigration.Schema{
		Migrations: migrations,
		MigrationOptions: []*sqlitemigration.MigrationOptions{
			{
				DisableForeignKeys: true,
			},
		},
	}); err != nil {

		return nil, fmt.Errorf("failed to migrate database: %w", err)
	}

	fkCheckStmt2 := conn.Prep("PRAGMA foreign_keys;")
	defer fkCheckStmt2.Finalize()
	if _, err := fkCheckStmt2.Step(); err != nil {
		return nil, fmt.Errorf("migrate database: %w", err)
	}
	foreignKeysEnabled2 := fkCheckStmt2.ColumnBool(0)
	if !foreignKeysEnabled2 {
		return nil, fmt.Errorf("foreign keys are not enabled")
	}

This shows the connect does have foreign_keys set, however it errors on the last line. I think this is an error in migration. However it brings up another note, is there a way to set per connection pragmas when using the pool?

The docs say
| Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

PS) _foreign_keys=1 doesn't have an effect and I noticed it in mattn/go-sqlite3#407 but had to explicitly set it to see the effect. I would think this get used per connection but its not really documented anywhere.

[delaneyj]

More info,

1. it passes if its a new database (ie no migrations); but not on next run.
2. If I comment out the MigrationOptions it always shows foreign keys as off.

[delaneyj]

https://github.com/zombiezen/go-sqlite3/blob/foreignkeys/sqlite3.go#L546

Ah, go-sqlite3 !- go-sqlite.... looks like the crawshaw based driver doesn't have the same support for setting pragmas in opening. Would be great if it did :p

[delaneyj]

@zombiezen bump?

[zombiezen]

Apologies, I've had a lot going on the last few months.

PrepareConn is what you're looking for.

[zombiezen]

I'm not sure whether this is what you were originally encountering, but I hit a related issue with the sqlitemigration package and pushed out 0.13.1 to fix it.

[delaneyj]

Yeah it's was from sqlmigration thx

[benbarbour]

I'm not using migrations, just sqlitex.Pool, but I think I'm hitting the same issue where need to manually run the pragma for each connection. Can PrepareConn, or something similar, be added to sqlitex.Pool?

[zombiezen]

@benbarbour The feature you're requesting is #65, which will be available in the next release.