Your Question

I have been leveraging the gorm.DB struct as a generic database provider. In my unit tests I've been running with SQLite and switch to Postgres for integration tests. SQLite doesn't have a truncate statement, but postgres does (which we use in production for performance reasons). For my unit tests, I'd like to register a callback to modify the callback to conform to SQLite's syntax.

I tried to do this, and I can actually debug with DLV and see my code getting executed but it doesn't look like the actual raw statement is replaced:

Test Code

// ...
gormDB.Callback().Raw().Register("sqlite-truncate-mapping", sqliteTruncateMapping)
// ...


// SQLite does NOT have a truncate statement. It has a truncate
// optimization that occurs with an unqualified delete statement.
// https://www.sqlite.org/lang_delete.html
func sqliteTruncateMapping(db *gorm.DB) {
    if rawSQL := db.Statement.SQL.String(); strings.HasPrefix(rawSQL, `TRUNCATE`) {
        updatedSQL := strings.Replace(rawSQL, `TRUNCATE`, `DELETE FROM`, 1)
        db.Statement.SQL.Reset()
        db.Statement.SQL.WriteString(updatedSQL)
    }
}

// ...
if err := tx.Exec(`TRUNCATE projects`).Error; err != nil {
    return err
}

The document you expected this should be explained

  • https://gorm.io/docs/hooks.html#Modify-current-operation (Probably most likely here)
  • https://gorm.io/docs/sql_builder.html

Expected answer

I expected the raw statement to be changed from:

TRUNCATE projects

to:

DELETE FROM projects

However I still get the SQLite syntax error showing the command hasn't been changed:

--- FAIL: TestProjectRepository_Set (45.23s)
    repository_test.go:49: 
            Error Trace:    repository_test.go:49
            Error:          Received unexpected error:
                            could not set projects: near "TRUNCATE": syntax error
            Test:           TestProjectRepository_Set
            Messages:       should not recieve an error, but did: could not set projects: near "TRUNCATE": syntax error
FAIL

Comment From: niko-dunixi

Actually I have this working. After doing some debugging and seeing how the callbacks were handled, my callback was being called AFTER the database was being hit. I found gorm:raw was defined as the callback for execution, so I placed mine before it and it is executed in order now.

My question is now, is this the most correct way to implement this or have I overlooked anything in my ignorance?

gormDB.Callback().Raw().Before("gorm:raw").Register("sqlite:truncate-mapping", sqliteTruncateMapping)
// ...

// SQLite does NOT have a truncate statement. It has a truncate
// optimization that occurs with an unqualified delete statement.
// https://www.sqlite.org/lang_delete.html
func sqliteTruncateMapping(db *gorm.DB) {
    truncate := `TRUNCATE `
    deleteFrom := `DELETE FROM `
    if rawSQL := db.Statement.SQL.String(); strings.HasPrefix(strings.ToUpper(rawSQL), truncate) {
        log.Infof("raw-sql: `%s` mapping to `%s` for SQLite compatibility", truncate, deleteFrom)
        updatedSQL := deleteFrom + rawSQL[len(truncate):]
        db.Statement.SQL.Reset()
        db.Statement.SQL.WriteString(updatedSQL)
    }
}

Comment From: li-jin-gou

hello @paul-nelson-baker, Is this question whether Execute different SQL for different databases ? if you can know gorm 's DB is sqlite or pg,you can solve it. use DB.Statement.Dialector.Name() to get db type ,sqlite or pg.

Comment From: niko-dunixi

Thanks for answering! Fortunately, I don't need to go that in-depth. I'd like to keep this test centric code separate from the final binary. That way I won't need to validate that it isn't accidentally being run on my execution environment.

Thanks for your feedback, it's much appreciated!!