Heavy duplication when upserting with GORM generics in SQLite

Ask Question

Asked 4 days ago

Modified 3 days ago

Viewed 78 times

I have several models in my application, which have associations between each other. I'm trying to recursively upsert them, but haven't been able to find a good solution that doesn't extensively rely on raw SQL or the non-generics API. These are my models:

EDIT 2025-11-26 Added Enclosure.

// Feed represents an RSS channel
type Feed struct {
    gorm.Model
    Title       string `gorm:"not null;"`
    Description string `gorm:"not null;"`
    // The link to the HTML representation of the feed. Different from FetchFrom
    Link string `gorm:"not null;"`
    // The URL that this feed can be retrieved from. Different from Link
    FetchFrom string `gorm:"not null;unique"`
    Language  string
    // The time-to-live of the feed. Time in minutes that the reader should wait between each refresh
    TTL          int `gorm:"default:60"`
    ETag         string
    LastModified time.Time
    Items        []Item `gorm:"constraint:OnDelete:CASCADE;"`
}

// Item represents an RSS item/post
type Item struct {
    gorm.Model
    // The feed this item came from.
    Feed Feed
    // ID of the feed. Automatically creates a belongs-to relationship with Feed -> Item.
    FeedID      uint   `gorm:"index;not null;"`
    GUID        string `gorm:"not null;unique"`
    Title       string
    Description string
    Link        *string
    Author      string
    PubDate     time.Time  `gorm:"not null;"`
    Read        bool       `gorm:"not null;"`
    Starred     bool       `gorm:"not null;"`
    Enclosure   *Enclosure `gorm:"constraint:OnDelete:CASCADE;"`
}

// Enclosure represents an RSS enclosure, usually media associated with an item
// See https://www.rssboard.org/rss-specification
type Enclosure struct {
    gorm.Model
    ItemID   uint `gorm:"index"`
    Item     Item
    URL      string `gorm:"not null;"`
    MimeType string `gorm:"not null;"`
    FilePath string `gorm:"not null;"`
}

And this is my upsert solution:

func upsert[T any](db *gorm.DB, m T, where string, args ...any) error {
    if where == "" {
        return fmt.Errorf("expected non-empty where")
    }

    ctx := context.Background()

    w := gorm.G[T](db).Where(where, args...)
    rows, err := w.Updates(ctx, m)
    if err != nil {
        return fmt.Errorf("failed to update object: %w", err)
    }

    if rows == 0 {
        err := gorm.G[T](db).Create(ctx, &m)
        if err != nil {
            return fmt.Errorf("failed to create object: %w", err)
        }
        return nil
    }
    return nil
}

When using the upsert on a Feed, it gets stored twice in the feeds table, one having fetch_from as provided and the other having an empty one. The items also appear to be stored twice each. When adding the UNIQUE constraint to Item.GUID, it throws several extremely long errors in this format:

upsert.go:27 constraint failed: UNIQUE constraint failed: items.guid (2067)
[50.595ms] [rows:0] sql statement

At the end, there appear to be 2 relatively short errors:

2025/11/25 19:13:01 .../server/database/upsert.go:27 constraint failed: UNIQUE constraint failed: items.guid (2067)
[47.266ms] [rows:1] INSERT INTO `feeds` (`created_at`,`updated_at`,`deleted_at`,`title`,`description`,`link`,`fetch_from`,`language`,`ttl`,`e_tag`,`last_modified`) VALUES ("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS 
Advisory Board","RSS Advisory Board announcements and Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","
RSS Advisory Board announcements and Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Boar
d announcements and Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Board announcements a
nd Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Board announcements and Really Simple 
Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Board announcements and Really Simple Syndication news"
,"https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Board announcements and Really Simple Syndication news","https://www.rss
board.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Board announcements and Really Simple Syndication news","https://www.rssboard.org/","","e
n-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Board announcements and Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000
-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Board announcements and Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00")
,("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Board announcements and Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:
13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Board announcements and Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-
11-25 19:13:01.123",NULL,"RSS Advisory Board","RSS Advisory Board announcements and Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.12
3",NULL,"RSS Advisory Board","RSS Advisory Board announcements and Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00"),("2025-11-25 19:13:01.123","2025-11-25 19:13:01.123",NULL,"RSS Advi
sory Board","RSS Advisory Board announcements and Really Simple Syndication news","https://www.rssboard.org/","","en-us",10,"","0000-00-00 00:00:00") ON CONFLICT DO NOTHING RETURNING `id`

2025/11/25 19:13:01 .../server/database/upsert.go:27 constraint failed: UNIQUE constraint failed: items.guid (2067)
[50.595ms] [rows:0] UPDATE `feeds` SET `updated_at`="2025-11-25 19:13:01.123",`title`="RSS Advisory Board",`description`="RSS Advisory Board announcements and Really Simple Syndication news",`link`="https://www.rssboard.org/",`fetch_f
rom`="http://feeds.rssboard.org/rssboard",`language`="en-us",`ttl`=10 WHERE (id = 0 OR fetch_from = "http://feeds.rssboard.org/rssboard") AND `feeds`.`deleted_at` IS NULL
> github.com/its-mrarsikk/fedup/server/database.upsert[go.shape.struct { gorm.io/gorm.Model; Title string "gorm:\"not null;\""; Description string "gorm:\"not null;\""; Link string "gorm:\"not null;\""; FetchFrom string "gorm:\"not nu
ll;unique\""; Language string; TTL int "gorm:\"default:60\""; ETag string; LastModified time.Time; Items []github.com/its-mrarsikk/fedup/shared/rss.Item "gorm:\"constraint:OnDelete:CASCADE;\"" }]() ./server/database/upsert.go:27 (PC: 
0xde9642)

The one(s) above appear to contain all of the items it's about to store, spanning thousands of lines.

This all happens in one upsert call. Any help would be appreciated.

EDIT 2025-11-26 I'm using github.com/glebarez/sqlite for a driver.

Here's a complete reproducible example:

main.go - https://bpa.st/7ZMA

go.mod - https://bpa.st/4Z2A

I tested them and they replicate the issue.

edited Nov 26 at 7:49

asked Nov 25 at 13:04

MrArsikk

492 silver badges10 bronze badges

I couldn't reproduce your result although I duplicated your code. Share more code, especially regarding how you invocate upsert method.

John Galt
– John Galt

2025-11-25 16:39:56 +00:00
Commented Nov 25 at 16:39
@JohnGalt I have added working repro files to the question.

MrArsikk
– MrArsikk

2025-11-26 07:50:21 +00:00
Commented Nov 26 at 7:50

Add a comment |

0 Your Answer

Sign up or log in

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.

Collectives™ on Stack Overflow

Heavy duplication when upserting with GORM generics in SQLite

0

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

0

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Sign up or log in

Post as a guest