sqlitegogo-sqlite3

sqlite3 in golang, foreign_keys pragma doesn't enforce keys


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...


Solution

  • 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...