Gorm Autoincrement is not enabled in SQLite when used with AutoMigrate

Description

Very simple use case:

package main

import (
    "log"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type User struct {
    ID   int64  `gorm:"primaryKey;autoIncrement;not null"`
    Name string `gorm:"unique;not null"`
    Mail string `gorm:"unique;not null"`
    Pass string `gorm:"unique;not null"`
}

func main() {
    con, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    if err != nil {
        log.Panic(err)
    }
    con.AutoMigrate(User{})
}

The database and table is created. Everything else is fine. Except AUTOINCREMENT is not enabled on primary key. What am I doing wrong here? (~~I have not tried with other drivers~~) (just tried on mysql and works fine there)

This is the DDL of the generated table:

CREATE TABLE users (
    id   INTEGER NOT NULL,
    name TEXT    NOT NULL
                 UNIQUE,
    mail TEXT    NOT NULL
                 UNIQUE,
    pass TEXT    NOT NULL
                 UNIQUE,
    PRIMARY KEY (
        id
    )
);

OS: Windows x64 GO: 1.17.2 (fresh installation. just began learning go)

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 for details. it will be closed in 2 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 ✨

Comment From: ghost

@iSLC hello, a field declared as INTEGER PRIMARY KEY will automatically add in sqite doc

look here and it explain this issue. https://github.com/go-gorm/sqlite/blob/2a8115147a2b4d95eb4455a253de09bb19a11fc0/sqlite.go#L146

Comment From: github-actions[bot]

Comment From: iSLC

@longlihale ~~So basically i only need to specify autoIncrement without primaryKey? At least that's what I can infer from the code at first glance.~~ nvm I just read it wrong.


case schema.Int, schema.Uint:
        if field.AutoIncrement && !field.PrimaryKey {
            // https://www.sqlite.org/autoinc.html
            return "integer PRIMARY KEY AUTOINCREMENT"
        } else {
            return "integer"
        }

I'll have to look more into it when I get home. Thanks for the reply.

Comment From: ghost

@longlihale ~So basically i only need to specify autoIncrement without primaryKey? At least that's what I can infer from the code at first glance.~ nvm I just read it wrong.

go case schema.Int, schema.Uint: if field.AutoIncrement && !field.PrimaryKey { // https://www.sqlite.org/autoinc.html return "integer PRIMARY KEY AUTOINCREMENT" } else { return "integer" }

I'll have to look more into it when I get home. Thanks for the reply.

Comment From: yqchilde

If you want to set the auto-increment on the ID field, using the autoIncrement tag alone does not take effect, because the primaryKey is added to the ID by default, which makes it impossible to set the auto-increment on the ID field anyway.

Comment From: Binary-Eater

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

The above is from the SQLite docs. I think, given the docs, the logic in go-gorm/sqlite3 should be field.AutoIncrement && field.PrimaryKey instead of what it is currently.

Comment From: Binary-Eater

Actually, one can use autoIncrement -> INTEGER PRIMARY KEY AUTOINCREMENT with sqlite3, so the current behavior makes sense to me.

Comment From: onlyshow

How was it finally resolved?