Description
When creating a record with associations, only the top level record is created if all the associated records use default values. Here is an example of a much larger policy database I'm creating. The real db has hundreds of settings (all with default values) across more than 15 tables. Each policy is initially created with just a name.
Actual Behavior
Only the record in the policy table is created. No records are created in any of the other tables.
Expected Behavior
A record should be created in all of the associated tables with the default values.
Below is the code to reproduce this issue.
package main
import (
"time"
"gorm.io/gorm"
)
var (
mainDBPath string = "test.db"
)
type DBModel struct {
ID uint `gorm:"primary_key"`
PolicyID uint
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt *time.Time `sql:"index"`
}
type Policy struct {
gorm.Model
Name string `gorm:"unique;unique_index"`
Settings PolicySettings `gorm:"embedded"`
SettingX bool `gorm:"default:false"`
DefaultInput bool `gorm:"default:true"`
Rulesets Ruleset
Files []PolicyFile
Sources []Source
}
type PolicySettings struct {
TestMode bool `gorm:"default:false"`
FileCleanUP bool `gorm:"default:true"`
FileAge time.Duration `default:"24h"`
}
type Source struct {
DBModel
URL string
SourceType string
}
type PolicyFile struct {
DBModel
Filename string
Filepath string
Filetype string
}
type Ruleset struct {
DBModel
RecommendedDisable bool `gorm:"default:true"`
RecommendedEnable bool `gorm:"default:true"`
RecommendedModify bool `gorm:"default:true"`
IPExtraction bool `gorm:"default:true"`
JA3Extraction bool `gorm:"default:true"`
TestRuleset bool `gorm:"default:true"`
FailOnRulesetError bool `gorm:"default:false"`
}
Here is the test code I used to create the table and create a policy.
package main
import (
"testing"
"github.com/stretchr/testify/assert"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"gorm.io/gorm/logger"
"gorm.io/gorm/schema"
)
func TestCreateTables(t *testing.T) {
t.Run("DB Test", func(t *testing.T) {
// Open the Database
db, err := gorm.Open(sqlite.Open(mainDBPath), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
NamingStrategy: schema.NamingStrategy{
SingularTable: true,
},
})
assert.Nil(t, err)
// Create the tables
err = db.AutoMigrate(&Policy{})
assert.Nil(t, err)
err = db.AutoMigrate(&Ruleset{})
assert.Nil(t, err)
err = db.AutoMigrate(&PolicyFile{})
assert.Nil(t, err)
err = db.AutoMigrate(&Source{})
assert.Nil(t, err)
})
}
func TestCreateDefaultPolicy(t *testing.T) {
t.Run("DB Test", func(t *testing.T) {
// Open the Database
db, err := gorm.Open(sqlite.Open(mainDBPath), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
NamingStrategy: schema.NamingStrategy{
SingularTable: true,
},
})
assert.Nil(t, err)
// Create a new policy with all default settings
policy := Policy{Name: "policy10"}
result := db.Create(&policy)
assert.Nil(t, result.Error)
db.Save(&policy)
})
}
Comment From: jlagermann
Here's the output from creating the tables.
=== RUN TestCreateTables
=== RUN TestCreateTables/DB_Test
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:33
[0.030ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="policy"
2022/07/22 12:02:40 /home/james/db-test/main_test.go:25
[10.688ms] [rows:0] CREATE TABLE `policy` (`id` integer,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`name` text UNIQUE,`test_mode` numeric DEFAULT false,`file_clean_up` numeric DEFAULT true,`file_age` integer,`setting_x` numeric DEFAULT false,`default_input` numeric DEFAULT true,PRIMARY KEY (`id`))
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:293
[23.991ms] [rows:0] CREATE INDEX `idx_policy_deleted_at` ON `policy`(`deleted_at`)
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:33
[0.032ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="policy"
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:111
[0.084ms] [rows:2] SELECT sql FROM sqlite_master WHERE type IN ("table","index") AND tbl_name = "policy" AND sql IS NOT NULL order by type = "table" desc
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:119
[0.026ms] [rows:-] SELECT * FROM `policy` LIMIT 1
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:310
[0.027ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type = "index" AND tbl_name = "policy" AND name = "idx_policy_deleted_at"
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:33
[0.027ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="ruleset"
2022/07/22 12:02:40 /home/james/db-test/main_test.go:27
[4.008ms] [rows:0] CREATE TABLE `ruleset` (`id` integer,`policy_id` integer,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`recommended_disable` numeric DEFAULT true,`recommended_enable` numeric DEFAULT true,`recommended_modify` numeric DEFAULT true,`ip_extraction` numeric DEFAULT true,`ja3_extraction` numeric DEFAULT true,`test_ruleset` numeric DEFAULT true,`fail_on_ruleset_error` numeric DEFAULT false,PRIMARY KEY (`id`),CONSTRAINT `fk_policy_rulesets` FOREIGN KEY (`policy_id`) REFERENCES `policy`(`id`))
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:33
[0.065ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="policy"
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:111
[0.155ms] [rows:2] SELECT sql FROM sqlite_master WHERE type IN ("table","index") AND tbl_name = "policy" AND sql IS NOT NULL order by type = "table" desc
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:119
[0.051ms] [rows:-] SELECT * FROM `policy` LIMIT 1
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:310
[0.052ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type = "index" AND tbl_name = "policy" AND name = "idx_policy_deleted_at"
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:33
[0.040ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="policy_file"
2022/07/22 12:02:40 /home/james/db-test/main_test.go:29
[8.194ms] [rows:0] CREATE TABLE `policy_file` (`id` integer,`policy_id` integer,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`filename` text,`filepath` text,`filetype` text,PRIMARY KEY (`id`),CONSTRAINT `fk_policy_files` FOREIGN KEY (`policy_id`) REFERENCES `policy`(`id`))
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:33
[0.028ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="policy"
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:111
[0.068ms] [rows:2] SELECT sql FROM sqlite_master WHERE type IN ("table","index") AND tbl_name = "policy" AND sql IS NOT NULL order by type = "table" desc
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:119
[0.024ms] [rows:-] SELECT * FROM `policy` LIMIT 1
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:310
[0.029ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type = "index" AND tbl_name = "policy" AND name = "idx_policy_deleted_at"
2022/07/22 12:02:40 /home/james/go/pkg/mod/gorm.io/driver/sqlite@v1.3.6/migrator.go:33
[0.020ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="source"
2022/07/22 12:02:40 /home/james/db-test/main_test.go:31
[5.066ms] [rows:0] CREATE TABLE `source` (`id` integer,`policy_id` integer,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`url` text,`source_type` text,PRIMARY KEY (`id`),CONSTRAINT `fk_policy_sources` FOREIGN KEY (`policy_id`) REFERENCES `policy`(`id`))
--- PASS: TestCreateTables (0.06s)
--- PASS: TestCreateTables/DB_Test (0.06s)
PASS
ok github.com/jlagermann/db-test 0.061s
> Test run finished at 7/22/2022, 12:03:00 PM <
Comment From: jlagermann
Here's the output from creating a policy.
```=== RUN TestCreateDefaultPolicy === RUN TestCreateDefaultPolicy/DB_Test
2022/07/22 12:03:55 /home/james/db-test/main_test.go:49
[5.898ms] [rows:1] INSERT INTO policy (created_at,updated_at,deleted_at,name,test_mode,file_clean_up,file_age,setting_x,default_input) VALUES ("2022-07-22 12:03:55.924","2022-07-22 12:03:55.924",NULL,"policy10",false,true,0,false,true) RETURNING id
2022/07/22 12:03:55 /home/james/db-test/main_test.go:51
[3.856ms] [rows:1] UPDATE policy SET created_at="2022-07-22 12:03:55.924",updated_at="2022-07-22 12:03:55.93",deleted_at=NULL,name="policy10",test_mode=false,file_clean_up=true,file_age=0,setting_x=false,default_input=true WHERE policy.deleted_at IS NULL AND id = 1
--- PASS: TestCreateDefaultPolicy (0.01s)
--- PASS: TestCreateDefaultPolicy/DB_Test (0.01s)
PASS
ok github.com/jlagermann/db-test 0.015s
Test run finished at 7/22/2022, 12:04:23 PM <
**Comment From: github-actions[bot]**
The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout [https://github.com/go-gorm/playground](https://github.com/go-gorm/playground) for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the `Question` template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ [Search Before Asking](https://stackoverflow.com/help/how-to-ask) ✨
**Comment From: a631807682**
We don't know if the user wants to create a associated table, what can explicitly specify this behavior is to refer to a non-null value.
```go
// insert into policies
DB.Create(&Policy{Name: "policy10"}).Error
// insert into rulesets and policies
DB.Create(&Policy{Name: "policy10", Rulesets: Ruleset{RecommendedDisable: true}}).Error
Comment From: jlagermann
What if the all the defaults for a table are null values? When I query the db for a policy, I need to get all the settings back, including null values. If an record does not exist for that policy in an associated table, none of those settings will be returned.
Comment From: a631807682
We won't insert the associated zero value, but you can still use a non-null pointer instead.
type Policy struct {
...
Rulesets *Ruleset
}
DB.Create(&Policy{Name: "policy10", Rulesets: &Ruleset{}})
Comment From: jlagermann
That works for tables that have at least one column with a non-null default, but it's not working for tables where all the defaults are null values.
Comment From: a631807682
I don't think inserting null values into an association table is a general requirement, you can implement it in https://gorm.io/docs/write_plugins.html#Plugin, that is, check the association relationship before creating it, and then fill it with the default value
Comment From: jlagermann
If I use a non-null pointer to create a policy, I have to have a record for that policy in each table, even if the record would be all null values. Without the null-value records, I cannot reference any value from that struct.
If I don't use a pointer to create a policy, I will have all the fields from all the tables with the Go defaults for each type. Then when I lookup the policy it will apply the values from the database over the Go struct defaults. The challenge with this option is none of the associated records are created unless I refer to a non-null value.
To solve this I created a constructor function to populate at least one non-null value from each table that has a non-null default.
func NewDefaultPolicy(name string) Policy {
policy := Policy{
Name: name,
Ruleset: Ruleset{RecommendedDisable: true},
Other: SomeOtherTable{SomeOtherBool: true},
}
return policy
}
I would have been a lot cleaner if db.Create() had an option to automatically create all associated records that have non-null values.