CODEWITHSUNDEEP
databasegogo-gorm
Octaviotastico
Octaviotastico

Reputation: 134

Gorm delete but keep ID increasing

I want have unique and autoincremental ids on my gorm database, even from previously created and deleted entries.

I have this model in my database:

type Books struct {
    ID            uint `json:"-" gorm:"autoIncrement"`
    Name          string `json:"name"`
    Author        uint `json:"author"`
}

Lets say I create 3 books, if I run db.Find(&Books{}) the output will be something like this:

[
  {
    id: 1,
    name: "name1",
    author: 123,
  },
  {
    id: 2,
    name: "name2",
    author: 123,
  },
  {
    id: 3,
    name: "name3",
    author: 123,
  },
]

If I delete Book 3 by ID, doing this: db.Delete(&Books{}, 3), as the documentation says: https://gorm.io/docs/delete.html#Delete-with-primary-key, the book 3 is deleted.

So, if I make another Find, I will get this:

[
  {
    id: 1,
    name: "name1",
    author: 123,
  },
  {
    id: 2,
    name: "name2",
    author: 123,
  },
]

The problem comes out when I create one more book, because it will have 3 as ID.

I would like that the new created book would have ID 4, instead of 3, so I will have ids 1, 2, and 4.

The output from Find I would like to have is:

[
  {
    id: 1,
    name: "name1",
    author: 123,
  },
  {
    id: 2,
    name: "name2",
    author: 123,
  },
  {
    id: 4,
    name: "name4",
    author: 123,
  },
]

So that's basically my problem.

I also thought of doing something like:

type Books struct {
    ID            uint `json:"-" gorm:"autoIncrement"`
    Name          string `json:"name"`
    Author        uint `json:"author"`
    Deleted       bool `json:"-"`
}

And then, when I make a Find, i would filter the deleted=true ones... But that would take too much space on my database file, so I would like another solution.

Upvotes: 0

Views: 1096

Answers (2)

Niraj Kumar
Niraj Kumar

Reputation: 241

Considering your gorm struct is:

type Books struct {
    gorm.Model
    Name          string `json:"name"`
    Author        uint `json:"author"`
}

Where gorm.Model gives you fields: ID, CreatedAt, UpdatedAt, DeletedAt

When you do a db.Delete(&Books, 3) it will do a soft-delete, which means the DeletedAt will be updated from null to a timestamp. This does not physically removes the record from the DB. You can still fetch this record by db.Unscoped().Find(&Books)

In order to permanently delete the record: db.Unscoped().Delete(&Books,3)

I guess (I haven't tried this yet!) after permanently deleting the record, your ID sequence should fall in place.

Upvotes: 1

Uday Yadav
Uday Yadav

Reputation: 121

This is because the database generates a new sequence generator on id in book table and whenever you add a book, it will take the current value from sequencer , add it to the row and increment it for next value.

There is no way to go back and fill the id deleted from row. And this is something not to worry about. when presenting it to the user, dont show the misordered numbers just display 1,2,3 and for performing operations like update, delete use this id.

Or you can use UUID instead of integer

Upvotes: 2

Related Questions