I have created an SQL file that loads into sqlite3 and creates a bunch of tables for me. In that sql file, I attempt to enforce foreign_keys with the pragma:
PRAGMA foreign_keys = on; -- also 1, true
When I load the sql file using -init
it looks good:
$ sqlite3 -init sqlite3-empty.sql
-- Loading resources from sqlite3-empty.sql
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> pragma foreign_keys;
1
sqlite> .quit
However, if I load the database file created by the sql file above, it doesn't stick:
$ sqlite3 unit_test.db
-- Loading resources from /home/me/.sqliterc
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> pragma foreign_keys;
foreign_keys
------------
0
sqlite> .quit
$ cat ~/.sqliterc
.headers on
.mode column
Is there a away to set this pragma in my .sqliterc
file? Or from an environment variable?
Note that I am loading the sql creation script as part of a go unit test apparatus setup:
sqlite3, err := sql.Open("sqlite3", "unit_test.db")
if err != nil {
err = fmt.Errorf("NewSQLite3() error creating db connection: %w", err)
return nil, err
}
[...]
file, err := ioutil.ReadFile("sqlite3-empty.sql")
if err != nil {
err = fmt.Errorf("NewSQLite3() error opening seed file: %w", err)
return nil, err
}
requests := strings.Split(string(file), ";")
for _, request := range requests {
_, err := db.Client.Exec(request)
if err != nil {
err = fmt.Errorf("NewSQLite3() error executing seed file sql line: %v\n", err)
return nil, err
}
}
The schema gets setup correctly. Everything but the foreign_keys pragma works. I have also tried the URI based approach, found elsewhere:
sqlite3, err := sql.Open("sqlite3", "file:unit_test.db?foreign_keys=on")
None of it works or has any effect. How do I set the foreign_keys pragma and make it stick?
Edit: riffing off the above, and leaving golang out of it:
$ sqlite3 -init sqlite3-empty.sql
-- Loading resources from sqlite3-empty.sql
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> pragma foreign_keys;
1
sqlite> .backup backup.db
sqlite> .quit
$ sqlite3 backup.db
-- Loading resources from /home/johnnyb/.sqliterc
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> pragma foreign_keys;
foreign_keys
------------
0
sqlite>
Edit #2: I was able to get it to "stick" by adding the pragma to my .sqliterc
file but it's a PITA to ask users to do that. Is there no other way? Also not quite sure how that would work with CI...
The answer is listed on mattn's sqlite3 driver page:
sqlite3, err := sql.Open("sqlite3", "file:unit_test.db?_foreign_keys=on")
I was close above, just didn't have the underscore... Thanks @shawn...