Manage and Query SQL Views with GORM and Atlas
GORM, a popular ORM for Go can easily query SQL views, but managing them has traditionally been an issue. With the latest release of Atlas GORM Provider, you can now easily manage views for your GORM application.
Introduction
Making liberal use of views is a key aspect of good SQL database design.
Views are a powerful database feature: they are virtual table representing the result of a query. Many teams use them to simplify complex queries, encapsulate logic, and present a consistent interface to users, abstracting the underlying data structures.
Using Views with ORMs
Despite their numerous benefits, views are often underutilized in many applications. Specifically, many ORMs provide partial support for views.
This is also the case with GORM, one of the most popular ORMs in Go. Let's see how GORM users can integrate views into their applications today:
First, we need to define the query that will back our view, and then use the GORM Migrator interface to create the view:
query := db.Model(&User{}).Select("id, name, age").Where("age BETWEEN 18 AND 60")
db.Migrator().CreateView("working_aged_users", gorm.ViewOption{Query: query})
// CREATE VIEW working_aged_users AS SELECT id, name, age FROM users WHERE age BETWEEN 18 AND 60
In order to be able to use GORM to query our view, we need to define an additional struct:
type WorkingAgedUser struct {
ID uint
Name string
Age int
}
Finally, we can use GORM to query records from our view:
var johnFamilies []WorkingAgedUser
db.Where("name LIKE ?", "John%").Find(&johnFamilies)
// SELECT * FROM `working_aged_users` WHERE name LIKE "John%"
Notice that this works by convention, GORM uses reflection and transforms the struct type name WorkingAgedUser to working_aged_users.
I have always felt that working with views in GORM isn't the smoothest experience. Here's why:
The "GORM way" of doing things is defining struct types and using them for everything. They serve as the foundation for modeling, querying data, and migrations. However, in my eyes, the current way of using views in GORM doesn't align with this principle. Views are defined in multiple places: the backing query, the migration step, and finally the runtime query struct.
As a GORM user, I have always wished that everything would just work from the same struct definition.
To address this challenge, our team working on the Atlas GORM provider (an Atlas plugin that enhances GORM's migration and capabilities) came up with a neat solution. Here's what it looks like:
// 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 60").Select("id, name, age")
}),
}
}
The migration step is now as simple as:
gormschema.New("mysql").Load(
&models.User{}, // Table-based model.
&models.WorkingAgedUsers{}, // View-based model.
)
It is also worth mentioning that querying the view is still the same:
var johnFamilies []WorkingAgedUser
db.Where("name LIKE ?", "John%").Find(&johnFamilies)
// SELECT * FROM `working_aged_users` WHERE name LIKE "John%"
The key benefits of this approach are:
- Alignment with GORM Philosophy: It follows the GORM (and generally ORM) principle that structs model database objects, both for schema definition and querying.
- Unified Source of Truth: It consolidates the schema source of truth for migrations and the DB Query API in a single location - the view definition structs.
This seamless integration of views with GORM's core principles results in a more organic and holistic workflow when working with database views. In the end, it's easy to think of views as read-only tables backed by a query, and this is precisely what this API is designed for.
Demo Time!
Let's walk through a step-by-step example of using GORM Atlas Provider to automatically plan schema migrations for tables and views in a GORM project.
Installation
If you haven't already, install Atlas from macOS or Linux by running:
curl -sSf https://atlasgo.sh | sh
See atlasgo.io for more installation options.
In addition, the view feature is only available for logged-in users, run the following command to login:
atlas login
Install the provider by running:
go get -u ariga.io/atlas-provider-gorm
Step 1: Create a GORM Application
Models are defined using normal structs. For views, we define a struct and
implement the ViewDefiner interface. The ViewDef(dialect string) method receives the
dialect argument to determine the SQL dialect to generate the view. It is helpful for generating the view definition for different SQL dialects if needed.
Let's create a file that will contain our database models. We will call it models/models.go
package models
import (
"ariga.io/atlas-provider-gorm/gormschema"
"gorm.io/gorm"
)
// User is a regular gorm.Model stored in the "users" table.
type User struct {
gorm.Model
Name string
Age int
Gender string
}
// WorkingAgedUser is mapped to the VIEW definition below.
type WorkingAgedUser struct {
Name string
Age int
}
For views, our provider provides two options for defining the view:
- BuildStmt: allows you to define a query using the GORM API. This is useful when you need to use GORM's query building capabilities.
- CreateStmt: allows you to define a query using raw SQL. This is useful when you need to define a complex query that GORM cannot handle.
BuildStmt
This option allows you to define the view using the GORM API. The dialect is handled automatically by GORM.
func (WorkingAgedUser) ViewDef(dialect string) []gormschema.ViewOption {
return []gormschema.ViewOption{
// view name will adhere to GORM's convention for table name
// which is "working_aged_users" in this case
gormschema.BuildStmt(func(db *gorm.DB) *gorm.DB {
return db.Table("users").Select("name, age").Where("age BETWEEN 18 AND 60")
}),
}
}
CreateStmt
This option gives you more flexibility to define the view using raw SQL. However, it also involves a trade-off, as you need to handle the SQL dialects yourself if you want it to work across multiple databases (e.g. switching databases, writing integration tests, etc.).
- Default dialect
- Multiple dialects
func (WorkingAgedUser) ViewDef(dialect string) []gormschema.ViewOption {
return []gormschema.ViewOption{
gormschema.CreateStmt(`
CREATE VIEW working_aged_users AS
SELECT
name,
age
FROM
users
WHERE
age BETWEEN 18 AND 60
`),
}
}
func (WorkingAgedUser) 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 60"
case "sqlserver":
stmt = "CREATE VIEW [working_aged_users] ([name], [age]) AS SELECT [name], [age] FROM [users] WHERE [age] >= 18 AND [age] <= 60 WITH CHECK OPTION"
}
return []gormschema.ViewOption{
gormschema.CreateStmt(stmt),
}
}
For demonstration purposes, we will use the CreateStmt option with the default dialect.
Step 2: Setup Atlas GORM Provider
Standalone vs Go Program mode
This feature works in both Standalone and Go Program modes:
- Standalone: If your views and models are in the same package, you can use the provider directly to load your GORM schema into Atlas.
- Go Program: If you have them defined in different packages, you can use the provider as a library in your Go program to load your GORM schema into Atlas.
Since all of our models are in the same package, it's pretty handy to use the Standalone mode. But if you're curious, you can also try the Go Program mode with more detail in the GORM Guide.
In your project directory, create a new file named atlas.hcl with the following contents:
data "external_schema" "gorm" {
program = [
"go",
"run",
"-mod=mod",
"ariga.io/atlas-provider-gorm",
"load",
"--path", "./models" // path to your models
"--dialect", "mysql", // | postgres | sqlite | sqlserver
]
}
env "gorm" {
src = data.external_schema.gorm.url
dev = "docker://mysql/8/dev" // the dev-database needs to be mapped to the same dialect above
migration {
dir = "file://migrations"
}
format {
migrate {
diff = "{{ sql . \" \" }}"
}
}
}
If you use the docker:// driver for spinning up your Dev Database
be sure that Docker is running locally on your machine first.
Next, to prevent the Go Modules system from dropping this dependency from our go.mod file, let's
follow the Go Module's official recommendation
for tracking dependencies of tools and add a file named tools.go with the following contents:
//go:build tools
package main
import _ "ariga.io/atlas-provider-gorm/gormschema"
Alternatively, you can simply add a blank import to the models.go file we created above.
Finally, to tidy things up, run:
go mod tidy
Step 3: Generate Migrations
We can now generate a migration file by running this command:
atlas migrate diff --env gorm
Observe that files similar to this were created in the migrations directory:
migrations
├── 20240525153051.sql
└── atlas.sum
1 directory, 2 files
Examining the contents of 20240525153051.sql:
-- 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,
`gender` longtext 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 60);
Amazing! Atlas automatically generated a migration file that will create the users table and working_aged_users view in our database!
Step 4: Update the View
Next, as business requirements change, the age range is now different for each gender. Let's update the WorkingAgedUser struct and its view definition.
type WorkingAgedUser struct {
Name string
Age int
+ Gender string
}
func (WorkingAgedUser) ViewDef(dialect string) []gormschema.ViewOption {
return []gormschema.ViewOption{
gormschema.CreateStmt(`
CREATE VIEW working_aged_users AS
SELECT
name,
age,
+ gender
FROM
users
WHERE
- age BETWEEN 18 AND 60
+ (gender = 'male' AND age BETWEEN 18 AND 65) OR
+ (gender = 'female' AND age BETWEEN 18 AND 60)
`),
}
}
Re-run this command:
atlas migrate diff --env gorm
Observe a new migration file is generated 🎉:
migrations
├── 20240525153051.sql
├── 20240525153152.sql
└── atlas.sum
1 directory, 3 files
-- Modify "working_aged_users" view
CREATE OR REPLACE VIEW `working_aged_users` (
`name`,
`age`,
`gender`
) AS select `users`.`name` AS `name`,`users`.`age` AS `age`,`users`.`gender` AS `gender` from `users` where (((`users`.`gender` = 'male') and (`users`.`age` between 18 and 65)) or ((`users`.`gender` = 'female') and (`users`.`age` between 18 and 60)));
Wrapping up
In this post, we have shown how to use Atlas to manage database schema migrations for tables and views in a GORM project. This is just one of the many features that Atlas provides for working with your database schema. Checkout the Atlas documentation for more information.
Have questions? Feedback? Find our team on our Discord server.
