Gorm Bug: gorm:"default:null" Tag Causes AutoMigrate to Drop and Recreate Table

Your Question

This is a bug, but I do not know how to write a test case for it. It is only observed through the logs. The use of the gorm:"default:null" tag is causing the AutoMigrate function to drop and recreate tables using that tag. Here is a minimal example:

package main

import (
    "github.com/glebarez/sqlite"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
    "log"
    "os"
    "time"
)

type CreditCard struct {
    gorm.Model
    Number uint `gorm:"default:null"`
}

func main() {
    newLogger := logger.New(
        log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
        logger.Config{
            SlowThreshold:             time.Second, // Slow SQL threshold
            LogLevel:                  logger.Info, // Log level
            IgnoreRecordNotFoundError: true,        // Ignore ErrRecordNotFound error for logger
            Colorful:                  false,       // Disable color
        },
    )

    db, _ := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{Logger: newLogger})
    db.AutoMigrate(&CreditCard{})
}

The document you expected this should be explained

When the gorm:"default:null" tag is present in the model, the table is created as expected the first time the application runs. The problem is observed in the logs the next time(s) the program runs. You can see that the table is dropped and the data is migrated to a new table:

[0.371ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="credit_cards"
[0.105ms] [rows:2] SELECT sql FROM sqlite_master WHERE type IN ("table","index") AND tbl_name = "credit_cards" AND sql IS NOT NULL order by type = "table" desc
[0.043ms] [rows:-] SELECT * FROM `credit_cards` LIMIT 1
[0.023ms] [rows:1] PRAGMA foreign_keys
[0.021ms] [rows:-] SELECT sql FROM sqlite_master WHERE type = "table" AND tbl_name = "credit_cards" AND name = "credit_cards"
[0.472ms] [rows:0] CREATE TABLE `credit_cards__temp` (`id` integer,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`number` integer DEFAULT null,PRIMARY KEY (`id`))
[0.045ms] [rows:0] INSERT INTO `credit_cards__temp`(`id`,`created_at`,`updated_at`,`deleted_at`,`number`) SELECT `id`,`created_at`,`updated_at`,`deleted_at`,`number` FROM `credit_cards`
[0.175ms] [rows:0] DROP TABLE `credit_cards`
[1.258ms] [rows:0] ALTER TABLE `credit_cards__temp` RENAME TO `credit_cards`
[0.033ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type = "index" AND tbl_name = "credit_cards" AND name = "idx_credit_cards_deleted_at"
[0.701ms] [rows:0] CREATE INDEX `idx_credit_cards_deleted_at` ON `credit_cards`(`deleted_at`)

I don't think this should be happening.

Expected answer

I expect the following behavior when the application is run the first time, as seen in the logs:

[0.343ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="credit_cards"
[1.081ms] [rows:0] CREATE TABLE `credit_cards` (`id` integer,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`number` integer DEFAULT null,PRIMARY KEY (`id`))
[0.695ms] [rows:0] CREATE INDEX `idx_credit_cards_deleted_at` ON `credit_cards`(`deleted_at`)

I expect the following behavior when the application is run the next time, as seen in the logs:

[0.386ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="credit_cards"
[0.144ms] [rows:2] SELECT sql FROM sqlite_master WHERE type IN ("table","index") AND tbl_name = "credit_cards" AND sql IS NOT NULL order by type = "table" desc
[0.036ms] [rows:-] SELECT * FROM `credit_cards` LIMIT 1
[0.022ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type = "index" AND tbl_name = "credit_cards" AND name = "idx_credit_cards_deleted_at"

AutoMigrate detects the table exists.

Comment From: black-06

update gorm.io/driver/sqlite to v1.4.4

Comment From: dthomas-sensonix

@black-06 Thank you so much. Unfortunately I'm using https://github.com/glebarez/sqlite so I won't be able to pick up the fix immediately. Still, I'm glad you were able to find an issue and fix it.