CRUD Operations — Create

In computer programming, create, read, update, and delete (CRUD) are the four essential functions of persistent storage. When developing applications with GORM, we want our models to provide these four functions, so our application can perform operations on the data stored in the database.

You already know how to create GORM models and migrate a database schema. Now it is time to learn how to fill the database with some data. In this topic, we will learn how to use GORM's CRUD Interface to create and insert records into the database.

Why use GORM for CRUD operations?

Abstraction — Working with an ORM provides the ability to abstract the underlying database SQL dialect, allowing you to write database-agnostic code; this means the GORM methods used with SQLite will execute similar CRUD operations in MySQL, PostgreSQL, or Microsoft SQL Server, adding a layer of adaptability to your codebase.

Ease of use — GORM simplifies database interaction with its straightforward CRUD methods. Instead of writing raw SQL queries, you leverage GORM methods that encapsulate common database operations such as creating, reading, updating, or deleting data.

Security — GORM helps safeguard your application against SQL injection attacks. It does this by using prepared statements for queries, which separates the query structure from the data. This way, even if an attacker tries to inject malicious code into the data, it won't affect the structure of the SQL command.

Maintainability — Since GORM's preferred convention is to work with model structs and methods rather than raw SQL queries, your code becomes more uniform, maintainable, and easier to comprehend and test.

Creating a single record

First, let's take a look at creating a single record. To do this, GORM provides the db.Create() method — it allows you to create and insert one or multiple records into a specific table.

After you've downloaded the example-library project files, you can go ahead and create the first record for the authors table:

func main() {
    // ... Connect to the `library` database using gorm.Open()

    // Create a new `Author` record:
    author := Author{Name: "J.R.R. Tolkien"}
    result := db.Create(&author)
    if result.Error != nil {
        log.Fatalf("cannot create Author: %v\n", result.Error)
    }

    fmt.Printf("Rows created: %d\n", result.RowsAffected) // Print the number of rows created
    fmt.Printf("Created Author with ID: %d, Name: %s", author.ID, author.Name)
}

// Output:
// Rows created: 1
// Created Author with ID: 1, Name: J.R.R. Tolkien

The db.Create() method takes a pointer to a model struct (e.g., &author) as an argument and then creates a new record in the table referenced by the model; it also returns a pointer to a *gorm.DB struct.

If you want to check for errors in creating the record or get the number of rows created, you can use the Error and RowsAffected fields from the returned gorm.DB struct. And since the Author model contains the gorm.Model field, you can use its ID field to check the inserted record's primary key.

Notice: when creating a record from a model struct with the gorm.Model field using the db.Create() method; GORM will automatically generate the time of creation values for the created_at and updated_at columns before executing the INSERT record query in the database:

Finally, after db.Create() inserts the record into the authors table; if the database engine supports a RETURNING statement, GORM scans the returning inserted values into the author struct, and it is updated:

fmt.Println(author.CreatedAt) // 2023-07-04 11:22:19.3810126-05:00
fmt.Println(author.UpdatedAt) // 2023-07-04 11:22:19.3810126-05:00
fmt.Println(author.DeletedAt) // {0001-01-01 00:00:00 +0000 UTC false}
fmt.Println(author.Name)      // J.R.R. Tolkien

Creating records with associations

Now that you've created the first record for the authors table, it is time to create records for the publishers, books, and most importantly, the author_books join table.

Since the author_books table is a special join table that sets up a many-to-many relationship between the authors and books tables, we need to set up GORM's association mode before we can insert data into it:

func main() {
    // ... Connect to the `library` database using gorm.Open()

    // Create a new `Publisher` record:
    publisher := Publisher{Name: "George Allen & Unwin"}
    result := db.Create(&publisher)
    if result.Error != nil {
        log.Fatalf("cannot create Publisher: %v\n", result.Error)
    }
    fmt.Printf("Created Publisher with ID: %d, Name: %s\n", publisher.ID, publisher.Name)

    // Declare and assign values to the new `Book` record:
    book := Book{
        Title:         "The Hobbit",
        DatePublished: time.Date(1937, time.January, 21, 0, 0, 0, 0, time.UTC),
        PublisherID:   publisher.ID,
    }

    // Retrieve the previously created `Author` by its `name`:
    author := Author{}
    result = db.Where("name = ?", "J.R.R. Tolkien").First(&author)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Author: %v\n", result.Error)
    }

    // Set up GORM's association mode between the `authors` and `books` tables
    // And chain Append() to create a new record in the `books` and `author_books` join table:
    err = db.Model(&author).Association("Books").Append(&book)
    if err != nil {
        log.Fatalf("cannot create Book and/or author_books record (or both): %v\n", err)
    }
    fmt.Printf("Created Book with ID: %d, Title: %s, DatePublished: %s, PublisherID: %d\n",
        book.ID, book.Title, book.DatePublished, book.PublisherID)
    fmt.Printf("Created author_books record with Author ID: %d, Book ID: %d",
        author.ID, book.ID)
}

// Output:
// Created Publisher with ID: 1, Name: George Allen & Unwin
// Created Book with ID: 1, Title: The Hobbit, DatePublished: 1937-01-21 00:00:00 +0000 UTC, PublisherID: 1
// Created author_books record with Author ID: 1, Book ID: 1

Creating a record for the author_books join table involves several steps, each building on the previous one:

1. Declare the new Book record and assign values to its fields. At this point, this record only exists in your application's memory.
2. Retrieve the previously created Author record via the db.Where("name = ?", "J.R.R. Tolkien").First(&author) syntax; this will scan all the values of a single record with name = "J.R.R Tolkien" into the author struct.
3. Set up GORM's association mode, linking the authors and books tables via the db.Model(&author).Association("Books") syntax.
4. Lastly, chain the Append() method to Association(), with a pointer to the book record you previously declared.

The Append() method works similarly to Create() but it performs additional tasks:

First, it checks whether the book record you provided as an argument exists in the books table. If it does, Append() proceeds with the association process using the existing book record's primary key.

However, if the provided book record does NOT exist in the books table, Append() creates a new record, generating the created_at and updated_at values, and inserts it into the books table:

After confirming the book record (either by identifying an existing one or creating a new one), Append() retrieves the necessary values, including the primary key, updates the book struct in your application with these values, and finally inserts the primary keys of the author ID: 1 in the author_id column and book.ID: 1 in the book_id column of the author_books join table:

Batch insertions

GORM's CRUD interface allows you to insert multiple records at once into a table by passing a pointer to a slice of a model struct to the db.Create() method:

func main() {
    // ... Connect to the `library` database using gorm.Open()

    // Retrieve the previously created `Publisher` by its `name`:
    publisher := Publisher{}
    result := db.Where("name = ?", "George Allen & Unwin").First(&publisher)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Publisher: %v\n", result.Error)
    }

    books := []Book{
        {
            Title:         "The Fellowship of the Ring",
            DatePublished: time.Date(1954, 7, 29, 0, 0, 0, 0, time.UTC),
            PublisherID:   publisher.ID,
        },
        {
            Title:         "The Two Towers",
            DatePublished: time.Date(1954, 11, 11, 0, 0, 0, 0, time.UTC),
            PublisherID:   publisher.ID,
        },
        {
            Title:         "The Return of the King",
            DatePublished: time.Date(1955, 10, 20, 0, 0, 0, 0, time.UTC),
            PublisherID:   publisher.ID,
        },
        // ... Additional books go here
    }
    result = db.Create(&books) // Insert the []Book slice records into the `books` table
    if result.Error != nil {
        log.Fatalf("cannot create Books: %v", result.Error)
    }
}

When passing a slice of records (e.g., &books) as an argument to db.Create(), GORM prepares a single SQL INSERT INTO statement with multiple VALUES clauses, each representing one record to be inserted into the books table:

Now imagine you're dealing with a substantial amount of data — perhaps millions of records. Trying to insert all these records in a single operation could lead to a few potential issues:

• Preparing a single SQL INSERT INTO statement with millions of records could consume significant memory in your application.
• Databases usually have a limit on the size of the SQL statement that can be executed. The operation will fail if the combined size of all your records exceeds this limit.
• A single operation that takes a long time to complete could lead to transaction timeouts or lock contention in the database.

For slices with a substantial amount of records, you can use the db.CreateInBatches() method — it allows you to break down the total amount of records in the slice into smaller, manageable batches for insertion:

func main() {
    // ... Connect to the `library` database using gorm.Open()

    books := []Book{{Title: "Book1"}, {Title: "Book2"}, ..., {Title: "Book1000000"}}
    result := db.CreateInBatches(books, 10000) // Create 1,000,000 books in batches of 10,000
    if result.Error != nil {
        log.Fatalf("cannot create Books in batches: %v\n", result.Error)
    }
}

The db.CreateInBatches(books, 10000) call would perform 100 insert operations — each one would create and insert 10,000 records to the books table at once.

Creating records from a map

Finally, GORM's CRUD interface also allows you to create records from a map or a slice of maps; however, the map must have keys of the string type, and values of the empty interface{} or any type:

func main() {
    // ... Connect to the `library` database using gorm.Open()

    // Retrieve the previously created `Publisher` by its `name`:
    publisher := Publisher{}
    result := db.Where("name = ?", "George Allen & Unwin").First(&publisher)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Publisher: %v\n", result.Error)
    }

    // Creating a single record from a map:
    book := map[string]interface{}{
        "Title":         "The Silmarillion",
        "DatePublished": time.Date(1977, time.September, 15, 0, 0, 0, 0, time.UTC),
        "PublisherID":   publisher.ID,
    }
    result = db.Model(&Book{}).Create(&book)
    if result.Error != nil {
        log.Fatalf("cannot create Book: %v\n", result.Error)
    }

    // Creating multiple records from a slice of maps:
    books := []map[string]any{
        {
            "Title":         "Unfinished Tales",
            "DatePublished": time.Date(1980, time.October, 2, 0, 0, 0, 0, time.UTC),
            "PublisherID":   publisher.ID,
        },
        {
            "Title":         "Beren and Lúthien",
            "DatePublished": time.Date(2017, time.June, 1, 0, 0, 0, 0, time.UTC),
            "PublisherID":   publisher.ID,
        },
    }
    result = db.Model(&Book{}).Create(&books)
    if result.Error != nil {
        log.Fatalf("cannot create Books: %v\n", result.Error)
    }
}

To create records from a map, we must first call db.Model() with a pointer to a model struct. Then we chain to it the Create() method with a pointer to the map with the values we want to create.

book := map[string]any{
    "CreatedAt":     time.Now(), // 2023-07-04 11:41:15.8407099-05:00
    "UpdatedAt":     time.Now(), // 2023-07-04 11:41:15.8407099-05:00
    "Title":         "The Silmarillion",
    "DatePublished": time.Date(1977, time.September, 15, 0, 0, 0, 0, time.UTC),
    "PublisherID":   publisher.ID,
}

Conclusion

Good going! You have learned the basics of using GORM's CRUD interface to create and insert records into tables without writing any SQL code in your Go program.

You also learned how to insert records in batches and even how to create and insert records using a map instead of a model struct.

For additional learning, don't hesitate to explore GORM's official documentation, where you can dive deeper into the Create methods.

Now it's time to test your knowledge about creating and inserting records using GORM's CRUD interface with a few theory and coding tasks; let's go!