How to use raw SQL in GORM?

GORM provides various methods to handle raw SQL queries. When ORM functionality cannot meet requirements, you can use raw SQL.

Executing Raw SQL

Exec() - Execute SQL that doesn't return data

go
// Create table
db.Exec("CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100))")

// Insert data
db.Exec("INSERT INTO users (name, email) VALUES (?, ?)", "John", "john@example.com")

// Update data
db.Exec("UPDATE users SET name = ? WHERE id = ?", "Jane", 1)

// Delete data
db.Exec("DELETE FROM users WHERE id = ?", 1)

Raw() - Execute SQL that returns data

go
// Query single record
var user User
db.Raw("SELECT * FROM users WHERE id = ?", 1).Scan(&user)

// Query multiple records
var users []User
db.Raw("SELECT * FROM users WHERE age > ?", 18).Scan(&users)

// Query specific fields
var results []struct {
    Name  string
    Email string
}
db.Raw("SELECT name, email FROM users").Scan(&results)

Mixing Raw SQL with ORM

Use raw SQL in queries

go
// Use raw SQL as subquery
var users []User
db.Where("age > (?)", db.Raw("SELECT AVG(age) FROM users")).Find(&users)

// Use raw SQL condition
db.Where(db.Raw("DATE(created_at) = ?", "2024-01-01")).Find(&users)

Use Joins to execute raw SQL

go
var users []User
db.Joins("LEFT JOIN profiles ON users.id = profiles.user_id").
   Where("profiles.status = ?", "active").
   Find(&users)

Advanced Raw SQL Queries

Complex Aggregation Queries

go
type Result struct {
    UserName string
    PostCount int
}

var results []Result
db.Raw(`
    SELECT u.name as user_name, COUNT(p.id) as post_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    WHERE u.age > ?
    GROUP BY u.id
    HAVING COUNT(p.id) > ?
    ORDER BY post_count DESC
    LIMIT ?
`, 18, 5, 10).Scan(&results)

Using CTE (Common Table Expression)

go
var results []struct {
    UserName string
    TotalAmount float64
}

db.Raw(`
    WITH user_orders AS (
        SELECT user_id, SUM(amount) as total
        FROM orders
        WHERE created_at > ?
        GROUP BY user_id
    )
    SELECT u.name as user_name, o.total as total_amount
    FROM users u
    JOIN user_orders o ON u.id = o.user_id
`, time.Now().AddDate(0, -1, 0)).Scan(&results)

Using Window Functions

go
var results []struct {
    UserName string
    Amount   float64
    Rank     int
}

db.Raw(`
    SELECT 
        u.name as user_name,
        o.amount,
        RANK() OVER (PARTITION BY o.user_id ORDER BY o.amount DESC) as rank
    FROM orders o
    JOIN users u ON o.user_id = u.id
`).Scan(&results)

Raw SQL Transactions

Using raw SQL in transactions

go
err := db.Transaction(func(tx *gorm.DB) error {
    // Insert using raw SQL
    if err := tx.Exec("INSERT INTO users (name) VALUES (?)", "John").Error; err != nil {
        return err
    }

    // Update using raw SQL
    if err := tx.Exec("UPDATE users SET email = ? WHERE name = ?", "john@example.com", "John").Error; err != nil {
        return err
    }

    return nil
})

Named Parameters

Using named parameters

go
// MySQL
db.NamedExec("INSERT INTO users (name, email) VALUES (:name, :email)", 
    map[string]interface{}{"name": "John", "email": "john@example.com"})

// PostgreSQL
db.NamedExec("INSERT INTO users (name, email) VALUES ($name, $email)", 
    map[string]interface{}{"name": "John", "email": "john@example.com"})

Using struct as parameters

go
type UserParams struct {
    Name  string `db:"name"`
    Email string `db:"email"`
}

params := UserParams{Name: "John", Email: "john@example.com"}
db.NamedExec("INSERT INTO users (name, email) VALUES (:name, :email)", params)

Raw SQL Best Practices

1. Use parameterized queries to prevent SQL injection

go
// Unsafe
db.Raw(fmt.Sprintf("SELECT * FROM users WHERE name = '%s'", userInput))

// Safe
db.Raw("SELECT * FROM users WHERE name = ?", userInput)

2. Use Scan to map results

go
type UserSummary struct {
    Name  string
    Count int
}

var summaries []UserSummary
db.Raw(`
    SELECT name, COUNT(*) as count
    FROM users
    GROUP BY name
`).Scan(&summaries)

3. Use Rows to handle large amounts of data

go
rows, err := db.Raw("SELECT * FROM users").Rows()
if err != nil {
    panic(err)
}
defer rows.Close()

for rows.Next() {
    var user User
    if err := db.ScanRows(rows, &user); err != nil {
        panic(err)
    }
    // Process user data
}

4. Use Row to handle single record

go
var name string
row := db.Raw("SELECT name FROM users WHERE id = ?", 1).Row()
if err := row.Scan(&name); err != nil {
    panic(err)
}

Performance Optimization

1. Use index hints

go
// MySQL index hints
db.Raw("SELECT * FROM users USE INDEX (idx_name) WHERE name = ?", "John").Scan(&users)

// PostgreSQL index hints
db.Raw("SELECT * FROM users WHERE name = ?", "John").Scan(&users)

2. Batch operations

go
// Batch insert
values := []interface{}{"John", "john@example.com"}, {"Jane", "jane@example.com"}
query := "INSERT INTO users (name, email) VALUES "
placeholders := make([]string, len(values))
for i := range values {
    placeholders[i] = "(?, ?)"
}
query += strings.Join(placeholders, ", ")

args := make([]interface{}, 0, len(values)*2)
for _, v := range values {
    args = append(args, v.([]interface{})...)
}
db.Exec(query, args...)

Notes

1. SQL Injection: Always use parameterized queries, don't concatenate SQL strings
2. Database Compatibility: SQL syntax may vary across different databases
3. Error Handling: Properly handle errors from raw SQL execution
4. Performance Considerations: Complex raw SQL may affect performance, needs optimization
5. Maintainability: Raw SQL code is harder to maintain, try to use ORM
6. Transaction Consistency: Pay attention to transaction consistency when using raw SQL in transactions

Common Questions

Q: When should I use raw SQL?

A: When ORM cannot meet requirements, such as complex aggregation queries, window functions, performance optimization, etc.

Q: How to prevent SQL injection?

A: Always use parameterized queries (? or named parameters), don't directly concatenate SQL strings.

Q: Will mixing raw SQL and ORM affect performance?

A: No, GORM will correctly handle mixed queries, but pay attention to query complexity.

Q: How to handle raw SQL errors?

A: Check db.Error or use Error() method to get error information.