Using Database Views in GORM
Database views are virtual tables based on the result of a query. Views are helpful when you want to simplify complex queries, strengthen security by only selecting necessary data, or encapsulate the details of your table structures.
From a querying perspective, views and tables are identical, which means GORM can natively query any views that exist on the database. However, defining and managing views has previously had only partial support in GORM.
The Atlas GORM Provider provides an API that allows you to define database views in the form of GORM models, and with the help of Atlas, migration files can be automatically generated for them.
Atlas support for Views used in this guide is available exclusively to Pro users. To use this feature, run:
atlas login
Getting Started with Atlas and GORM
To get set up, follow our Getting Started guide for GORM and Atlas, and ensure you have the Atlas GORM Provider installed on your GORM project.
Defining Views in GORM
To define a Go struct as a database VIEW, implement the ViewDefiner
interface. The ViewDef() method takes the dialect argument to determine the SQL dialect for generating the view.
The gormschema package provides two styles for defining a view's base query:
BuildStmt Approach
The BuildStmt function allows you to define a query using the GORM API. This is useful when you need to leverage GORM's query building
capabilities and maintain consistency with your existing GORM code.
Let's start with a simple example. Suppose we have a User model and we want to create a view that shows only working-aged users:
package models
import (
"gorm.io/gorm"
"ariga.io/atlas-provider-gorm/gormschema"
)
type User struct {
gorm.Model
Name string
Age int
}
// WorkingAgedUsers is mapped to the VIEW definition below.
type WorkingAgedUsers struct {
Name string
Age int
}
func (WorkingAgedUsers) ViewDef(dialect string) []gormschema.ViewOption {
return []gormschema.ViewOption{
gormschema.BuildStmt(func(db *gorm.DB) *gorm.DB {
return db.Model(&User{}).Where("age BETWEEN 18 AND 65").Select("name, age")
}),
}
}
CreateStmt Approach
The CreateStmt function allows you to define a query using raw SQL. This is useful when you need to use SQL features that GORM does
not support or when you want more direct control over the generated SQL.
func (WorkingAgedUsers) ViewDef(dialect string) []gormschema.ViewOption {
var stmt string
switch dialect {
case "mysql":
stmt = "CREATE VIEW working_aged_users AS SELECT name, age FROM users WHERE age BETWEEN 18 AND 65"
case "postgres":
stmt = "CREATE VIEW working_aged_users AS SELECT name, age FROM users WHERE age BETWEEN 18 AND 65"
case "sqlserver":
stmt = "CREATE VIEW [working_aged_users] ([name], [age]) AS SELECT [name], [age] FROM [users] WHERE [age] >= 18 AND [age] <= 65 WITH CHECK OPTION"
}
return []gormschema.ViewOption{
gormschema.CreateStmt(stmt),
}
}
Using Views with Atlas
The View feature works in both Standalone and Go Program mode.
Standalone Mode
For Standalone mode, if you place the view definition in the same package as your models, the provider will automatically detect and create migration files for them.
Your atlas.hcl configuration remains the same:
data "external_schema" "gorm" {
program = [
"go",
"run",
"-mod=mod",
"ariga.io/atlas-provider-gorm",
"load",
"--path", "./path/to/models",
"--dialect", "mysql" // | postgres | sqlite | sqlserver
]
}
env "gorm" {
src = data.external_schema.gorm.url
dev = "docker://mysql/8/dev"
migration {
dir = "file://migrations"
}
format {
migrate {
diff = "{{ sql . \" \" }}"
}
}
}
Go Program Mode
For Go Program mode, you can load the VIEW definition in the same way as a model:
package main
import (
"fmt"
"io"
"os"
"ariga.io/atlas-provider-gorm/gormschema"
"github.com/yourorg/yourrepo/models"
)
func main() {
stmts, err := gormschema.New("mysql").Load(
&models.User{}, // Table-based model.
&models.WorkingAgedUsers{}, // View-based model.
)
if err != nil {
fmt.Fprintf(os.Stderr, "failed to load gorm schema: %v\n", err)
os.Exit(1)
}
io.WriteString(os.Stdout, stmts)
}
In your project directory, create a new file named atlas.hcl with the following contents:
data "external_schema" "gorm" {
program = [
"go",
"run",
"-mod=mod",
"./loader",
]
}
env "gorm" {
src = data.external_schema.gorm.url
dev = "docker://mysql/8/dev"
migration {
dir = "file://migrations"
}
format {
migrate {
diff = "{{ sql . \" \" }}"
}
}
}
Generating Migrations
With the atlas.hcl configuration file set up, you can now run the following command to generate migration files for your views:
atlas migrate diff --env gorm
This will generate a migration file that includes both your tables and views. For example:
-- Create "users" table
CREATE TABLE `users` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`created_at` datetime(3) NULL,
`updated_at` datetime(3) NULL,
`deleted_at` datetime(3) NULL,
`name` longtext NULL,
`age` bigint NULL,
PRIMARY KEY (`id`),
INDEX `idx_users_deleted_at` (`deleted_at`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Create "working_aged_users" view
CREATE VIEW `working_aged_users` (
`name`,
`age`
) AS select `users`.`name` AS `name`,`users`.`age` AS `age` from `users` where (`users`.`age` between 18 and 65);
Apply the migration file to your database to create the view by running:
atlas migrate apply --env gorm
Using Views in Your Application
Once the view is created, you can query it just like any other GORM model:
// Query the view
var workingUsers []WorkingAgedUsers
db.Find(&workingUsers)
// Use WHERE clauses
var experienced []WorkingAgedUsers
db.Where("age > ?", 30).Find(&experienced)
// Count records
var count int64
db.Model(&WorkingAgedUsers{}).Count(&count)
Conclusion
Database views provide a powerful way to encapsulate complex queries and create reusable data access patterns. By defining views as GORM models and managing them through Atlas migrations, you can maintain a clean, version-controlled database schema that evolves with your application.
For more information and advanced usage of views, refer to the Atlas GORM Provider documentation.
Need Help?
You can reach us by clicking the Intercom bubble on our site or by scheduling a demo with our team.
For questions or feedback, join the conversation on our Discord server.