Autoincrement is not enabled in SQLite when used with AutoMigrate

[iSLC]

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)

[github-actions]

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 ✨

[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

[github-actions]

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 ✨

[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.

[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.

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.

OK

[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.

[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.

[Binary-Eater]

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

[onlyshow]

How was it finally resolved?