How to retrieve last inserted ID in golang go orm when using Raw query

When using Go ORM frameworks such as GORM, handling native SQL queries is a common operation. Especially when you need to retrieve the ID of the newly inserted record after an insert operation, this feature becomes particularly important. Below are the steps and examples for executing native SQL inserts and retrieving the latest ID using GORM in Go.

Step 1: Configure GORM and Database Connection

First, import the GORM package and configure the database connection. Assuming we are using a MySQL database:

go
package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

func main() {
    // Connection string "username:password@tcp(address:port)/database?charset=utf8mb4&parseTime=True&loc=Local"
    dsn := "your_user:your_password@tcp(127.0.0.1:3306)/your_db?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }

    // You can then use the db object for database operations
}

Step 2: Insert Data Using Native SQL and Retrieve the ID

In GORM, you can use the Exec method to execute native SQL commands. If you want to retrieve the ID after inserting data, you can use the LAST_INSERT_ID() function in SQL (specifically for MySQL). Here is an example:

go
package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

func main() {
    // ... (database connection code)

    // Assuming we have a table named `users` with fields id, name, age
    sql := "INSERT INTO users (name, age) VALUES (?, ?)"
    result := db.Exec(sql, "John Doe", 30)
    if result.Error != nil {
        panic(result.Error)
    }

    // Retrieve the last inserted ID
    var lastInsertId int
    db.Raw("SELECT LAST_INSERT_ID()").Scan(&lastInsertId)
    fmt.Printf("Last inserted ID: %d\n", lastInsertId)
}

Note:

• Ensure that the SQL syntax matches your database type. Different database systems (such as PostgreSQL, SQLite, etc.) may have different functions to retrieve the last inserted ID.
• In production environments, ensure that all inputs are properly validated and sanitized to prevent security issues such as SQL injection.

By following these steps, you can effectively execute native SQL inserts and retrieve the ID of the newly inserted record when using GORM for ORM operations.