Managing Multiple Database Schemas with Gorm

4 mins to read golang gorm database

Introduction #

Gorm is an exceptional Object-Relational Mapping (ORM) library for Golang, equipped with a wide range of features.

Identifying the Issue #

Although the Gorm documentation is comprehensive, it does not always include all the minor details that a developer might need. I encountered a specific problem where I needed to manage two schemas with one database, as illustrated below. The aim was to maintain a schema of APP and another schema of AUTH within the same database. This separation can be useful for breaking down the application into smaller services or for managing the app database from a different viewpoint.


Upon reading the Gorm documentation, I did not find a direct solution to my problem. However, by exploring the Gorm source code, I was able to devise a solution.

The Solution #

To enable Gorm to manage multiple schemas within one database, we can use table naming for each table model we create.

type TablerWithNamer interface { TableName(Namer) string}

Here, TableName is an interface used to modify the given name for a table. To employ this function, we need to define a custom struct that implements this interface.

// CustomTabler is a custom struct that implements TablerWithNamertype CustomTabler struct { gorm.Tabler}
// TableName returns the custom table name based on the struct namefunc (ct *CustomTabler) TableName() string { // Generate and return the custom table name based on your logic // Example: return "custom_table_name" return "custom_table_name"}

Now let’s implement our database schemas as per the diagram above, using a full code example that utilizes Gorm’s table renaming, scopes, and clauses.

package main import ( "fmt" "" "" "") const ( AppSchema = "app" AuthSchema = "auth") // User is a model representing the users tabletype User struct { ID uint Name string} // TableName returns the custom table name for the User modelfunc (User) TableName() string { return AuthSchema + ".user"} // Order is a model representing the orders tabletype Order struct { ID uint Name string UserID uint} // TableName returns the custom table name for the Order modelfunc (Order) TableName() string { return AppSchema + ".order"} func main() { // Connect to the database db, err := gorm.Open(postgres.Open("host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable TimeZone=Asia/Jakarta"), &gorm.Config{}) if err != nil { panic(err) }  // Create schema if not exist db.Exec("CREATE SCHEMA IF NOT EXISTS " + AppSchema) db.Exec("CREATE SCHEMA IF NOT EXISTS " + AuthSchema)  // Auto-migrate the models err = db.AutoMigrate(&User{}, &Order{}) if err != nil { panic(err) }  // Create a new user in the auth schema user := User{Name: "John"} db.Create(&user)  // Create a new order associated with the user in the app schema order := Order{Name: "Order 1", UserID: user.ID} db.Create(&order)  // Fetch all users from the auth schema var authUsers []User db.Find(&authUsers) fmt.Println("Auth Users:", authUsers)  // Fetch all orders from the app schema var appOrders []Order db.Find(&appOrders) fmt.Println("App Orders:", appOrders)  // Fetch all orders from the app schema with the associated user var appOrdersWithUser []Order db.Preload(clause.Associations).Find(&appOrdersWithUser) fmt.Println("App Orders with User:", appOrdersWithUser)}

We begin by creating our models, such as the user and order models. We establish a database connection and ensure our database includes these schemas, creating them if necessary. clause.Associations are used to specify associations to be preloaded or eager-loaded when querying the database. It allows you to load related associations in a single query to minimize the number of database round-trips and improve performance.

That’s it! Thank you for reading this blog, and stay tuned for future posts.

source code #