How can GORM's FirstOrCreate() method (or Django's get_or_create) ensure that just one row is created?

Question

I'm considering using GORM for an application and was looking into how FirstOrCreate works, and it seems that it uses two database operations. Consider this example script:

package main

import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
    "github.com/sirupsen/logrus"
)

type User struct {
    gorm.Model
    Name string
    Age  uint
}

func main() {
    db, err := gorm.Open("sqlite3", "examplegorm.db")
    if err != nil {
        logrus.Fatalf("open db: %v", err)
    }
    defer db.Close()

    db.LogMode(true)
    db.AutoMigrate(&User{})

    var user User
    db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
}

Upon running this and inspecting the logs, I see that (aside from the auto-migration) it uses two queries, one SELECT and one INSERT:

kurt@Kurts-MacBook-Pro-13 ~/D/Scratch> go run gorm_example.go

(/Users/kurt/Documents/Scratch/gorm_example.go:23) 
[2020-01-05 09:09:10]  [1.03ms]  CREATE TABLE "users" ("id" integer primary key autoincrement,"created_at" datetime,"updated_at" datetime,"deleted_at" datetime,"name" varchar(255),"age" integer )  
[0 rows affected or returned ] 

(/Users/kurt/Documents/Scratch/gorm_example.go:23) 
[2020-01-05 09:09:10]  [0.86ms]  CREATE INDEX idx_users_deleted_at ON "users"(deleted_at)   
[0 rows affected or returned ] 

(/Users/kurt/Documents/Scratch/gorm_example.go:26) 
[2020-01-05 09:09:10]  [0.28ms]  SELECT * FROM "users"  WHERE "users"."deleted_at" IS NULL AND (("users"."name" = 'non_existing')) ORDER BY "users"."id" ASC LIMIT 1  
[0 rows affected or returned ] 

(/Users/kurt/Documents/Scratch/gorm_example.go:26) 
[2020-01-05 09:09:10]  [0.31ms]  INSERT  INTO "users" ("created_at","updated_at","deleted_at","name","age") VALUES ('2020-01-05 09:09:10','2020-01-05 09:09:10',NULL,'non_existing',20)  
[1 rows affected or returned ] 

As I understand from https://stackoverflow.com/a/16128088/995862, however,

In a SQL DBMS, the select-test-insert approach is a mistake: nothing prevents another process from inserting the "missing" row between your select and insert statements.

It seems that Django's get_or_create() works in a similar fashion. Given this model,

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=255)
    age = models.PositiveIntegerField() 

if I enable database logging and run a get_or_create() query I see

In [1]: from djangoapp.models import *                                                                                           

In [2]: User.objects.get_or_create(name="jinzhu", age=20)                                                                        
(0.000) SELECT "djangoapp_user"."id", "djangoapp_user"."name", "djangoapp_user"."age" FROM "djangoapp_user" WHERE ("djangoapp_user"."age" = 20 AND "djangoapp_user"."name" = 'jinzhu') LIMIT 21; args=(20, 'jinzhu')
(0.000) BEGIN; args=None
(0.000) INSERT INTO "djangoapp_user" ("name", "age") VALUES ('jinzhu', 20); args=['jinzhu', 20]
Out[2]: (<User: User object (1)>, True)

In short, if I want to be sure that only one record gets created, it seems that I should refrain from using an ORM such as GORM or the Django ORM and write my own query?

A second question I have is how to get the equivalent of Django's created Boolean in GORM. Should I determine whether the RowsAffected of the resulting gorm.DB is 1 to determine whether a row was actually created or not?

Answer 1

You should just add UNIQUE constraint on the query model fields and that would be enough to keep it consistent in db

for Django that would be adding meta class to model

class Meta:
    unique_together = ['name', 'age']

for GORM

Name string `gorm:"unique_index:idx_name_age"`
Age  uint   `gorm:"unique_index:idx_name_age"`