[Bug] SQLite3 not honoring Foreign key constraints leading to successful insertion of record with invalid foreign key

[rams3sh]

Sample Reproduction Code

I havent handled errors in the below program. I have written it with objective of reproducing the issue.

package main

import (
    "fmt"
    "log"

    "gorm.io/driver/postgres"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

// Defining Models
type Environment struct {
    gorm.Model
    Name        string `gorm:"not null;default:null"`
    Description string `gorm:"not null;default:null"`
}

type Node struct {
    gorm.Model
    Name          string       `gorm:"not null;default:null"`
    Description   string       `gorm:"not null;default:null"`
    Environment   *Environment `gorm:"foreignKey:EnvironmentID;not null;"`
    EnvironmentID uint
}

// Function handling migrations and connection to DB instance
func ConnectDatabase(dbType string, dbName string, dbHost string, dbPort uint64, dbSSLMode string, dbTimeZone string, dbUser string, dbPass string, moduleModels []interface{}) gorm.DB {

    var database *gorm.DB
    var err error
    if dbType == "sqlite3" {
        database, err = gorm.Open(sqlite.Open(fmt.Sprint(dbName+".db")), &gorm.Config{})
    } else if dbType == "postgres" {
        dsn := fmt.Sprintf("host=%v user=%v password=%v dbname=%v port=%v sslmode=%v TimeZone=%v",
            dbHost, dbUser, dbPass, dbName, dbPort, dbSSLMode, dbTimeZone)
        database, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})
    }

    if err != nil {
        panic("Failed to connect to database!")
    }
    database.AutoMigrate(moduleModels...)

    DB := *database
    return DB
}

// Function to create node
func CreateNode(db *gorm.DB, nodeName string, nodeDescription string, environmentID uint) {
    node := Node{Name: nodeName, Description: nodeDescription, EnvironmentID: environmentID}
    (*db).Debug().Create(&node)
}

func main() {

    models := make([]interface{}, 0)
    models = append(models,
        Environment{},
        Node{})

        // Run a docker based postgresql container before executing the program
    postgresDB := ConnectDatabase("postgres", "test_db", "localhost", 5432, "disable", "Asia/Kolkata", "root", "root", models)
    log.Println("Testing with Postgresql !!")
    CreateNode(&postgresDB, "testpostgres", "testpostgres", 2)

    sqlite3DB := ConnectDatabase("sqlite3", "test_db", "", 0, "", "", "", "", models)
    log.Println("\n\nTesting with SQLite3 !!")
    CreateNode(&sqlite3DB, "testsqlite3", "testsqlite3", 2)
}

Output

test@lab:~/Garage/gorm-test $ go run main.go 
2021/12/31 12:04:56 Testing with Postgresql !!

2021/12/31 12:04:56 /home/test/Garage/gorm-test/main.go:52 ERROR: insert or update on table "nodes" violates foreign key constraint "fk_nodes_environment" (SQLSTATE 23503)
[1.363ms] [rows:0] INSERT INTO "nodes" ("created_at","updated_at","deleted_at","environment_id","name","description") VALUES ('2021-12-31 12:04:56.886','2021-12-31 12:04:56.886',NULL,2,'testpostgres','testpostgres') RETURNING "id","name","description"
2021/12/31 12:04:56 

Testing with SQLite3 !!

2021/12/31 12:04:56 /home/test/Garage/gorm-test/main.go:52
[8.966ms] [rows:1] INSERT INTO `nodes` (`created_at`,`updated_at`,`deleted_at`,`environment_id`,`name`,`description`) VALUES ("2021-12-31 12:04:56.923","2021-12-31 12:04:56.923",NULL,2,"testsqlite3","testsqlite3") RETURNING `id`,`name`,`description`

Expectation

SQLITE3 should raise foreign key violation error just as postgresql.

Description

SQLite3 doesnt seem to honor foreign key constraint when provided with non existing foreign primary key id through gorm. However , postgres honors it. The above code tries to create a Node record with invalid Environment Id in sqlite3 and postgres database.

When the same is tried through the sqlite browser, it gives the error. Screenshot below :- :

The PRAGMA Foreign Keys is set to true. Screenshot from SQLITE3 browser below :-

Raising the issue here since there is not much documentation that I could find pertaining to the above issue.

I have referred articles related to sqlite3 foreign key handling in gorm. Most of them pertained to old version and there was'nt much pertaining to v2 gorm. Hence raising the issue here.

[rittneje]

(Note: I do not use gorm, and am only familiar with SQLite and the mattn wrapper.)

In SQLite, for historical reasons whether foreign keys are enabled is a property of an individual database connection, NOT the database itself. Consequently the fact that your DB Browser shows foreign keys enabled is irrelevant.

In order to enable foreign keys on your application's database connections, you will likely have to set a parameter in the DSN. Should be sqlite.Open("file:" + dbName+".db?_fk=1"). See the mattn docs for more information.

[rams3sh]

@rittneje Thanks. Sure.

I understand that foreign key in sqlite is connection specific. The screenshot was shared just to show that it worked from the browser and not from gorm. This issue is very specific to gorm and not sqlite I feel.

sqlite.Open("file:" + dbName+".db?_fk=1") according to my understanding is to be used only if you are dealing with sqlite package directly without an intermediate ORM interface. This connection string is expected to be handled by the gorm instead based on the tag provided in the struct field which is being done alrite in case of postgresql but no sqlite.

Hence this issue.

[rittneje]

I don't see any logic in the source code to manipulate the connection string. It just passes it verbatim. For Postgres it works because I don't think you can disable foreign keys anyway.

It would be nice if gorm just always enabled foreign keys, regardless of whether your schema references them. But that might be a breaking change, for the same reason that SQLite itself considered it a breaking change.

BTW, another workaround for now is to compile with the sqlite_foreign_keys tag, as that will enable foreign keys by default.

[rams3sh]

It would be nice if gorm just always enabled foreign keys, regardless of whether your schema references them. But that might be a breaking change, for the same reason that SQLite itself considered it a breaking change.

I think gorm can have foreign key enforce option applicable / considered only for sqlite with some default value handling for backward compatibility, and user can explicitly set that flag during initialisation.

Anyway I am just speculating and throwing of ideas from my head.

[BinaryTENSHi]

After updating gorm and gorm/sqlite I also stumbled upon this problem. Not including '?_fk=1' is only part of the problem as adding it to the example code makes no difference: create still does not return an error.

I think the other part of the problem is closely related to https://github.com/mattn/go-sqlite3/issues/986#issuecomment-983727930. When using a version of gorm/sqlite with RETURNING support (and only inserting a single struct), gorm itself calls rows.Next() once, scans the result but does not call rows.Next() again, resulting in the missing error. The fix in sqlite3 itself (https://sqlite.org/src/info/a818ba2ed635b91e) may resolve this issue, but it's not released yet. I'll try to patch it by hand and reverify the problem.

EDIT: Manually updating mattn/go-sqlite3 with the newest prerelease amalgamation code fixes this problem. Guess we have to wait for sqlite 3.38.0. (:

Otherwise, downgrading to 1.1.6 would also be an option: this is the latest Version without RETURNING support.

[jinzhu]

https://sqlite.org/foreignkeys.html

If it still doesn't work, try to run PRAGMA foreign_keys = ON, the case should be tested in github.com/go-gorm/gorm