go-gorm / sqlite Goto Github PK
View Code? Open in Web Editor NEWGORM sqlite driver
License: MIT License
GORM sqlite driver
License: MIT License
I wanna use this extension:
https://github.com/wangfenjin/simple/blob/master/examples/go/main.go
But It can not be loaded by my code:
sql.Register("sqlite3_simple",
&sqlite3.SQLiteDriver{
Extensions: []string{
"lib/libsimple",
},
})
db, err = gorm.Open(sqlite.Open(dsn), &gorm.Config{})
output:
query error: no such function: simple_query
sqlite file cannot be closed
sqlDB, _ = db.DB()
fmt.Println(sqlDB.Ping())
fmt.Println(sqlDB.Close())
fmt.Println(sqlDB.Ping())
output:
<nil>
<nil>
sql: database is closed
but when I use the lsof -p
command, I can still see that many database files are opened
type Product struct {
Price float64 `gorm:"type:decimal(10,2);not null"`
}
...
DB.AutoMigrate(Product{})
The database is initialized normally for the first time, but it crashes after restarting the application, returning the error invaild DDL, unbalanced brackets
According to my repeated debugging, the problem appears in migrate.go
At line 89, it would replace decimal (10,2)
to ?, 2)
Please bump this dependency version
Line 6 in e64f7a5
This fixes mattn/go-sqlite3#1164 - can't build on Alpine 3.19 (?)
For anyone else hitting this, a temporary workaround is to set CGO_CFLAGS="-D_LARGEFILE64_SOURCE"
CVE-2023-7104 has been reported for github.com/mattn/go-sqlite3 package, versions <1.14.18
as reported by Snyk
https://security.snyk.io/vuln/SNYK-GOLANG-GITHUBCOMMATTNGOSQLITE3-6139875
This dependency update has an already existing PR created by dependabot
#175
func Open(dsn string) gorm.Dialector {
return &Dialector{DSN: dsn}
}
Open
is the factory func of sqlite
driver to generate a gorm.Dialector
, but not open a db connection in actual. Maybe named New
is more reasonable.
The same as other drivers.
Sample code below
type Message struct {
ID int
}
I need set the initial auto increment value of message's id, and the sqlite's doc described the sqlite_sequence table. But when i try to manipulate it, i got an error, the table doesnt exists.
So i read the migrator's code, the DataTypeOf()
method checks if the field is auto increment and not primary key field, migrator will generate proper statement to support auto increment.
But the ID field defaults handled as primary key, the problem seems can't fixed.
Gorm docs Conventions section say, we can use tag primaryKey
to set other field as primary key, but migrator reports an error table device has more than one primary key
.
How to fix it? Thanks for reply.
Attempting to use a net.IP
field in a gorm model causes a sqlite row value misued
error on INSERT.
I did not create a playground PR because this is so simple to reproduce:
package main
import (
"net"
"gorm.io/gorm"
"gorm.io/driver/sqlite"
)
type Client struct {
gorm.Model
SourceIP net.IP
}
func main() {
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
if err != nil {
panic("Failed to connect to db")
}
db.AutoMigrate(&Client{})
db.Create(&Client{
SourceIP: net.ParseIP("192.168.1.1"),
})
}
Output:
2021/03/21 00:33:35 gorm-sqlite-netIP/main.go:22 row value misused
[0.134ms] [rows:0] INSERT INTO `clients` (`created_at`,`updated_at`,`deleted_at`,`source_ip`) VALUES ("2021-03-21 00:33:35.378","2021-03-21 00:33:35.378",NULL,(0,0,0,0,0,0,0,0,0,0,255,255,192,168,1,1))
This happens in the sqlite3
CLI also:
sqlite> INSERT INTO `clients` (`created_at`,`updated_at`,`deleted_at`,`source_ip`) VALUES ("2021-03-21 00:33:35.378","2021-03-21 00:33:35.378",NULL,(0,0,0,0,0,0,0,0,0,0,255,255,192,168,1,1));
Error: row value misused
It seems that the nested parenthesis syntax for a BLOB is not correct.
github.com/mattn/go-sqlite3
this lib using c, cgo is not support CROSS_COMPILE
SQLite driver does not return foreign key violation error since introducing support for RETURNING clause.
The problem is due to specific behavior of SQLite and underlying driver, as described here: mattn/go-sqlite3#986 (comment)
SQLite does not understand OFFSET without a LIMIT clause.
Look onto the following code:
[0.000ms] [rows:0] SELECT `queue_records`.`queue_id`,`queue_records`.`id`,`queue_records`.`journal_id` FROM `queue_records` WHERE (status = "put" AND queue_id = "a08a10b7-16ee-11ec-a4bb-482ae31ffe87") AND `queue
_records`.`deleted_at` IS NULL ORDER BY rec_time DESC OFFSET 1
?[1mSTEP?[0m: creating 2 queues
?[1mSTEP?[0m: creating queue 1
?[1mSTEP?[0m: creating queue 2
?[1mSTEP?[0m: inserting a bulk of queue records
?[1mSTEP?[0m: updating queue
+ Failure [0.004 seconds]
queues tests
qservice/mcstorage/sqlite/queues_test.go:19
Queue Records
qservice/mcstorage/sqlite/queues_test.go:338
various delete funcs
qservice/mcstorage/sqlite/queues_test.go:711
QueueDeletePutRecords
qservice/mcstorage/sqlite/queues_test.go:727
checks if queue.Max = 1 [It]
qservice/mcstorage/sqlite/queues_test.go:742
Unexpected error:
<sqlite3.Error>: {
Code: 1,
ExtendedCode: 1,
SystemErrno: 0x0,
err: "near \"OFFSET\": syntax error",
}
near "OFFSET": syntax error
occurred
qservice/mcstorage/sqlite/queues_test.go:751
Consider also looking the diagrams: https://www.sqlite.org/syntaxdiagrams.html
Workaround is to rewrite ClauseBuilders["LIMIT"]
with valid implementation inside *gorm.DB
entity.
SqLite Error during build.
Tested on docker alpine 3.17, RHEL Linux 8 and Suse Linux 12.5
Cannot replicate in playground as it fails in build.
[builder 7/7] RUN go build -o main:
#0 11.77 # gorm.io/driver/sqlite
#0 11.77 /go/pkg/mod/gorm.io/driver/[email protected]/error_translator.go:9:35: undefined: sqlite3.ErrNoExtended
#0 11.77 /go/pkg/mod/gorm.io/driver/[email protected]/error_translator.go:14:36: undefined: sqlite3.Error
最近在使用该模块,发现一个问题:
func (dialector Dialector) DataTypeOf(field *schema.Field) string { switch field.DataType { case schema.Bool: return "numeric" case schema.Int, schema.Uint: if field.AutoIncrement && !field.PrimaryKey { // https://www.sqlite.org/autoinc.html return "integer PRIMARY KEY AUTOINCREMENT" } else { return "integer" }
why not field.AutoIncrement && field.PrimaryKey => integer PRIMARY KEY AUTOINCREMENT
Sqlite coumns that are neither NOT NULL
or NULL
are nullable by default. However, .Nullable()
defaults to false and is only set true
if the CREATE TABLE
sql explicitly denotes the column as NULL
Line 122 in 397ec6f
columnType := migrator.ColumnType{
<snip>
NullableValue: sql.NullBool{Valid: true}, //<--- .Bool implicitly false, i.e. NOT NULL
<snip>
}
matchUpper := strings.ToUpper(matches[3])
if strings.Contains(matchUpper, " NOT NULL") {
columnType.NullableValue = sql.NullBool{Bool: false, Valid: true}
} else if strings.Contains(matchUpper, " NULL") {
columnType.NullableValue = sql.NullBool{Bool: true, Valid: true}
}
...
With the version 1.4.1 I have the following error:
C:\Users\***\go\pkg\mod\gorm.io\driver\[email protected]\sqlite.go:101:23: invalid operation: limit.Limit != nil (mismatched types int and untyped nil)
C:\Users\***\go\pkg\mod\gorm.io\driver\[email protected]\sqlite.go:101:31: invalid operation: cannot indirect limit.Limit (variable of type int)
C:\Users\***\go\pkg\mod\gorm.io\driver\[email protected]\sqlite.go:102:13: invalid operation: cannot indirect limit.Limit (variable of type int)
Probably due to this commit: ea59bcf
Revert to1.3.6 temporary fix the problem.
Please check https://github.com/go-gorm/sqlite/blob/master/ddlmod.go#L29 at line 120. If a column is defined without "NOT NULL", it should be nullable by default. So line 120 needs to be modified as:
NullableValue: sql.NullBool{Bool: true, Valid: true}
r如题,请问大家之前是否遇到过? 当文件一直增大的情况下 执行程序占用内存也会变大
The feature which I am happy to do a PR for is to remove the use of the existing CGO based SQLite implementation with a pure Golang based implementation here.
The CGO libraries are a HUGE pain with Windows machines and even other machines without the GCC binaries and specific settings. It does not compile well in many scenarios. This conversion appears to be solid and is a direct conversion of the C libraries of the original implementation.
I did not provide a playground link, because I was able to pinpoint the issue and will provide a PR to fix it.
This report rather serves as a reference and documentation.
AutoMigrate with gorm did not work for me on my sqlite db. It tried to create a table that already existed. The reason were two regexes in the sqlite connector that did not cover a specific edge case.
I recently upgraded gorm to the latest version in my project and found that it displayed an error message to me:
SQL logic error: table "users" already exists (1)
It tried to create a new table which already existed.
This was weird because of two reasons. First, it worked with an older version of gorm and second, even if the table exists, gorm should just update its fields by creating a temporary table and copying over the contents.
So I did some investigations and found out that the actual breaking commit was go-gorm/gorm@93986de. Every version released before this commit worked, every version after did not. The problematic lines in the commit were these ones:
https://github.com/go-gorm/gorm/blob/93986de8e43bc9af6864621c9a4855f0f860cde2/migrator/migrator.go#L456-L459
When using AutoMigrate with my table, line 458 got executed. That shows that gorm did not manage to read the default value from the table schema and thought it needs to create a default value for a column in my table. But instead the database already had a default value set.
I assumed that it was due to a parsing issue, so I checked the schema and the parsing code. The schema was pretty weirdly formatted but valid sql.
CREATE TABLE "users"
(
id integer
primary key
unique,
created_at datetime,
updated_at datetime,
username text,
first_name text,
last_name text,
lang_code text
, `dark_mode` numeric DEFAULT true);
There is indeed a parsing issue right here:
Lines 31 to 34 in 502ed63
tableRegex = (?is)(CREATE TABLE [`|\"|'|\t]?[\\w\\d-]+[`|\"|'|\t]?)(?: \\((.*)\\))?
The tableRegex
wasn't able to parse the above sql statement, because of the line break right after "users"
. That's the reason why the returned sqlDDL did not contain any fields/columns. The part of the tableRegex (?: \\((.*)\\))?
expected a space (0x20) after the quotes around the table name. In my case the SQL did not contain a space but a newline.
Side note: The regex isn't very good overall, because it also matches things like CREATE TABLE |users|
because |
is being used in the character set for "separators".
That regex issue leads to the columnTypes
return value being filled with only the rawColumnTypes
(SELECTed from the actual table) and hence contain no information about e.g. autoincrement, default values, etc.
https://github.com/go-gorm/gorm/blob/b444011d094db7444f87f442c33860365f55770a/migrator/migrator.go#L116-L118
Then the MigrateColumn code gets executed and for any column that has additional properties (like uniqueness, nullability, default values) we will at some point set the alterColumn
variable to True
. In my case the dark_mode
column caused the issue due to a default value being used.
https://github.com/go-gorm/gorm/blob/b444011d094db7444f87f442c33860365f55770a/migrator/migrator.go#L503-L506
Since alterColumn was set to true, gorm now executed the AlterColumn
method of the sqlite module, where we finally call m.recreateTable()
.
https://github.com/go-gorm/gorm/blob/b444011d094db7444f87f442c33860365f55770a/migrator/migrator.go#L524-L526
AlterColumn Method:
Lines 79 to 100 in 502ed63
In the recreateTable method, we want to create a temporary table name (e.g. users__temp
) in order to copy over all existing fields from the old table to a newly created one, to migrate to a new db schema.
Lines 383 to 397 in 502ed63
But again we have some regex issue here: " ('|`|\"| )" + table + "('|`|\"| ) "
- at least one space before and after the table's name is expected. This does not work when there is a newline right after the table name (which is valid sqlite syntax). That finally lead to the case where the actual createSQL
was CREATE TABLE users [...]
instead of CREATE TABLE users__temp
.
I'll create a PR to fix these two regex parsing issues. While debugging I also came across yet another issue, so stay tuned for another (much shorter) report :)
Best regards,
Rico
JSON Operations
HasColumn 的判断规则是:
"SELECT count(*) FROM sqlite_master WHERE type = ? AND tbl_name = ? AND (sql LIKE ? OR sql LIKE ? OR sql LIKE ?)",
"table", stmt.Table, `%"`+name+`" %`, `%`+name+` %`, "%`"+name+"`%",
但 Sqlite 的工具创建或修改表结构后的 sqlite_master.sql
格式并不一致, 造成 hasColumn
无法正确判断.
我遇到其中两个例外情况:
column
后使用 tab
而非 space
的.CREATE TABLE posts (
id integer PRIMARY KEY,
created_at datetime);
这个是在 mac 上的一个 sqlite gui 工具修改后的结果.
[]
作为分隔符.CREATE TABLE [posts](
[id] integer PRIMARY KEY,
[created_at] datetime);
这是 win 上 Sqlite Expert 修改后的结果.
Here is the explanation of the mechanism - https://www.sqlite.org/fts5.html
But somehow the FTS5 module should be installed.
How to do it for the gorm SQLite driver?
It would be nice to have an encrypted database like this lib:
https://github.com/jackfr0st13/gorm-sqlite-cipher
Thanks,
...
It would be awesome if this package could be independent of go-sqlite3 and instead, use go-sqlite3
(sqlite3
) when CGO_ENABLED=1
and use modernc.org/sqlite
when not.
This allows the user to choose between either go-sqlite3
(sqlite3
) or modernc.org/sqlite
(sqlite
) pure-go implementation. go-sqlite3
is excellent, but CGO is a lot to deal with.
The https://github.com/glebarez/sqlite driver is behind on many commits, it would be much simpler to maintain one codebase for multiple drivers rather than two, especially since they are almost the same.
According to
https://www.sqlitetutorial.net/sqlite-autoincrement/
https://www.sqlite.org/autoinc.html
AUTOINCREMENT
should be used in sqlite if one want's ID values not to be reused (i.e. after row deletion).
With table definition
ID uint64 `gorm:"column:id;not null;primaryKey;autoIncrement"`
GORM creates column without autoincrement:
CREATE TABLE `[...]` (`id` integer NOT NULL,[...],PRIMARY KEY (`id`));
but should add autoincrement also.
According to #126 one can create primary key + autoincrement with autoIncrement
tag only but this looks like dirty hack and error
failed to create database schema: table "[...]" has more than one primary key
is thrown when trying to create table with
IDH uint64 `gorm:"column:idh;not null;autoIncrement"` // Tried to create as PK+autoincrement.
ID uint64 `gorm:"column:id;not null"` // Tried to create as foreign key with ID name (makes sense in history table for us).
because GORM tries ID
as PK by default (because of col name) and adds PK also to IDH
(because of wrong handling of tags like above).
Column defined with gorm:"[...];primaryKey;autoIncrement"
should be created as PK with autoincrement in sqlite db.
.....
if err := db.DB.AutoMigrate(&sys.RoleMenu{}); err != nil {
log.Errorf("%s", err.Error())
}
......
log
[0.455ms] [rows:0] CREATE TABLE `tb_sys_role_menu__temp` (
"id" integer PRIMARY KEY AUTOINCREMENT,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`created_by` integer NOT NULL DEFAULT 0,
`updated_by` integer NOT NULL DEFAULT 0,
`role_id` integer NOT NULL,
"menu_id" bigint NOT NULL
)
pkg/mod/gorm.io/driver/[email protected]/migrator.go:411 near ")": syntax error
[0.011ms] [rows:0] INSERT INTO `tb_sys_role_menu__temp`() SELECT FROM `tb_sys_role_menu`
2022-06-01T09:45:25.751+0800 ERROR models/base.go:54 创建RoleMenu表异常:near ")": syntax error
Support CGO-free SQLite driver: https://pkg.go.dev/modernc.org/sqlite
CGO-free
I'm using the GORM ORM framework for Golang and have encountered an issue with composite unique indexes. When using the following structure that includes a composite unique index, I noticed that the recreateTable process (which includes creating a temporary table, migrating data, deleting the old table, and renaming the table) is executed every time AutoMigrate is called, even if the model has not changed.
type Gateway struct {
IP string `gorm:"index:uniq_vip,unique"`
UIN string `gorm:"index:uniq_vip,unique"`
}
However, there is no issue when using a single-field unique index like the following model:
type Gateway struct {
IP string `gorm:"index:uniq_vip,unique"`
UIN string
}
After investigating, I found that the alterColumn = true is set in the code at https://github.com/go-gorm/gorm/blob/v1.25.5/migrator/migrator.go#L500, because field.Unique is false while unique is true.
// check unique
if unique, ok := columnType.Unique(); ok && unique != field.Unique {
// not primary key
if !field.PrimaryKey {
alterColumn = true
}
}
Further investigation revealed that the problematic code for the sqlite-driver is at https://github.com/go-gorm/sqlite/blob/v1.5.4/ddlmod.go#L165:
for _, column := range getAllColumns(matches[1]) {
for idx, c := range result.columns {
if c.NameValue.String == column {
c.UniqueValue = sql.NullBool{Bool: strings.ToUpper(strings.Fields(str)[1]) == "UNIQUE", Valid: true}
result.columns[idx] = c
}
}
}
Since it is a composite unique index, getAllColumns(matches[1]) returns a length of 2, containing the column names IP and UIN. Neither of these individual fields is unique, but they form a composite unique index. The condition strings.Fields(str)[1]) == "UNIQUE" is not strict enough, causing each field to be set as unique.
I expect the check should be something like strings.Fields(str)[1]) == "UNIQUE" && len(getAllColumns(matches[1])) == 1, so that the unique attribute is only set for fields when there is only one field in the unique index, rather than setting unique for each field in the composite unique index. After modifying the condition and testing, the behavior is as expected.
github.com/mattn/go-sqlite3
cc1.exe: sorry, unimplemented: 64-bit mode not compiled in
As we can see in the playground,AutoMigrate()
fails with error invalid DDL
if a table with -
in name exists.
I try to run the demo code. After go build, I got this error!
../../../../go/pkg/mod/gorm.io/driver/[email protected]/sqlite.go:100:22: cannot convert 0 (untyped int constant) to *int
../../../../go/pkg/mod/gorm.io/driver/[email protected]/sqlite.go:101:24: cannot convert 0 (untyped int constant) to *int
../../../../go/pkg/mod/gorm.io/driver/[email protected]/sqlite.go:102:21: cannot use -1 (untyped int constant) as *int value in assignment
../../../../go/pkg/mod/gorm.io/driver/[email protected]/sqlite.go:104:50: cannot use limit.Limit (variable of type *int) as type int in argument to strconv.Itoa
OS: macos 12.5.1 / arm64
Pkg Vesion: gorm.io/driver/[email protected]
ld: warning: -no_pie is deprecated when targeting new OS versions
ld: warning: non-standard -pagezero_size is deprecated when targeting macOS 13.0 or later
gorm.io/driver/sqlite v1.4.4
Mac OS 13.3.1
Sorry for skipping the playground PR, but I think the below snippet should explain the issue well enough.
It took me a couple of hours to figure out the following issue. When parsing the DDL as part of AutoMigrate
, an error will occur if the original SQL statement wasn't fully uppercase.
package main
import (
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
type Foo struct {
ID uint64 `gorm:"primary_key"`
Field1 string
Field2 string
}
func main() {
db, err := gorm.Open(sqlite.Open("gorm_db"), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
})
if err != nil {
panic(err)
}
if err := db.AutoMigrate(&Foo{}); err != nil {
panic(err)
}
// note "on" vs "ON" here
if err := db.Exec("CREATE INDEX idx_field1_field2 on foos (field1, field2)").Error; err != nil {
panic(err)
}
if err := db.AutoMigrate(&Foo{}); err != nil {
panic(err)
}
}
Here's the output:
2022/04/03 17:07:23 /home/ferdinand/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:33
[0.025ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="foos"
2022/04/03 17:07:23 /home/ferdinand/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:106
[0.062ms] [rows:2] SELECT sql FROM sqlite_master WHERE type IN ("table","index") AND tbl_name = "foos" AND sql IS NOT NULL order by type = "table" desc
2022/04/03 17:07:23 /tmp/gorm-bug/main.go:23 duplicate column name: id
[0.012ms] [rows:0] ALTER TABLE `foos` ADD `id` integer
panic: duplicate column name: id
goroutine 1 [running]:
main.main()
/tmp/gorm-bug/main.go:24 +0x23a
Process finished with the exit code 2
So when manually running a DDL statement that is valid, yet not correctly formatted, auto migrations for that table will break forever until you explicitly fix it.
Would be very helpful if the regexes were case-insensitive and only check for correct syntax instead.
go.mod
module gorm-bug
go 1.18
require (
gorm.io/driver/sqlite v1.3.1
gorm.io/gorm v1.23.4
)
require (
github.com/jinzhu/inflection v1.0.0 // indirect
github.com/jinzhu/now v1.1.4 // indirect
github.com/mattn/go-sqlite3 v2.0.3+incompatible // indirect
)
The following code yields a different result when used with the mysql instead of the sqlite driver.
Instead of using ON CONFLICT DO NOTHING RETURNING `id`,`id`;
, this driver should do a ON CONFLICT DO UPDATE SET `id`=`id` RETURNING `id`,`id`;
just as the mysql driver does.
Output when using sqlite:
2023/01/25 00:34:39 User #0: Peter1, country: New Zealand
2023/01/25 00:34:39 User #1: Peter2, country: [EMPTY]
(Correct) output when using mysql:
2023/01/25 00:34:39 User #0: Peter1, country: New Zealand
2023/01/25 00:34:39 User #1: Peter2, country: Germany
package main
import (
"fmt"
"log"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"gorm.io/gorm/clause"
)
type User struct {
gorm.Model
ID uint `gorm:"column:id; primaryKey; unique; not null"`
Name string `gorm:"column:name; unique; not null"`
Country *Country
CountryID *uint `gorm:"column:country_id"`
}
type Country struct {
gorm.Model
ID uint `gorm:"column:id; primaryKey; unique; not null"`
Name string `gorm:"column:name; unique; not null"`
}
func run() error {
const (
// dbFile = "db.sqlite"
dbFile = "file:memdb?mode=memory"
)
db, err := gorm.Open(
sqlite.Open(dbFile),
&gorm.Config{},
)
if err != nil {
return fmt.Errorf("failed to open GORM: %w", err)
}
if err := db.Debug().AutoMigrate(
&User{},
&Country{},
); err != nil {
return fmt.Errorf("failed to auto migrate: %w", err)
}
// Create countries
{
tx := db.Debug().
Create(&Country{Name: "Germany"})
if err := tx.Error; err != nil {
return fmt.Errorf("failed to create country: %w", err)
}
}
// Create users
{
tx := db.Debug().
Create(&User{Name: "Peter1", Country: &Country{Name: "New Zealand"}})
if err := tx.Error; err != nil {
return fmt.Errorf("failed to create user: %w", err)
}
}
{
tx := db.Debug().
Create(&User{Name: "Peter2", Country: &Country{Name: "Germany"}})
if err := tx.Error; err != nil {
return fmt.Errorf("failed to create user: %w", err)
}
}
// Retrieve users
{
var ms []*User
tx := db.Debug().Preload(clause.Associations).
Find(&ms)
if err := tx.Error; err != nil {
return fmt.Errorf("failed to find: %w", err)
}
for i, m := range ms {
country := "[EMPTY]"
if m.Country != nil {
country = m.Country.Name
}
log.Printf("User #%d: %s, country: %s\n", i, m.Name, country)
}
}
return nil
}
func main() {
if err := run(); err != nil {
log.Fatal(err)
}
}
Add a way to override the return value of Dialector.DefaultValueOf()
For our project we use sqlite driver for the tests.
When we insert a default value with gorm:default:null
it inserts DEFAULT instead of NULL. The field is a primary key and has AutoIncrement disabled because we are using snowflake.
We couldn't find any solution except by modifying the driver code which is not satisfying
As demonstrated in the playground, dropping a column fails when foreign keys are enabled
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)
}
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`
SQLITE3 should raise foreign key violation error just as postgresql.
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.