Skip to main content

2 posts tagged with "views"

View All Tags

· 10 min read
TL;DR

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.

See an example

Introduction

Making liberal use of views is a key aspect of good SQL database design.

Postgres documentation

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:

models/models.go
// 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:

main.go
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

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.

models/models.go
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.).

models/models.go
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
`),
}
}

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:

atlas.hcl
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 . \" \" }}"
}
}
}
Using docker://

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:

tools.go
//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:

migrations/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.

models/models.go
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
migrations/20240525153152.sql
-- 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.

· 12 min read
Rotem Tamir

It's been just over two months since our last version announcement and today I'm super excited to share with you our latest release, v0.13, which includes some long-anticipated additions to Atlas:

  • Atlas and Atlas Community Editions - we are making a change in the way Atlas is distributed. Starting today, we are making many of the features previously available only in the commercial version of Atlas available to everyone for free. More on this below.

  • Support for SQL Views - SQL Views are virtual tables in a database created by a query and stored in the database. Starting today, you can manage views in your database using the same Atlas workflows you use for tables, indexes, foreign keys, etc.

  • Microsoft SQL Server Driver Beta - Over the last few months, we have been working on a driver for MS-SQL with some of our enterprise design partners and are happy to make it available to you as a beta.

  • Built-in schema visualization - Over the past year we released some tools to help developers visualize their database schemas using Entity Relationship Diagrams (ERDs). Starting today, you can use the atlas schema inspect --visualize command to create database ERDs directly from the command line.

  • GitHub Action for Deployments - We recently received a request from the community to add a GitHub Action to streamline applying migrations directly from GitHub workflows. We thought this was a great idea and are happy to introduce ariga/atlas-deploy-action which you can use to deploy your migrations today.

  • Type checking for schema files - As part of our continuous effort to make the experience of working with Atlas as smooth as possible, we are excited to ship a new engine for running type checks on your schema files.

This release is extra-packed with new features and improvements, so let's dive in!

Atlas and Atlas Community Editions

In recent months, based on strategic discussions with our advisors, we have come to realize that in order to keep fueling Atlas's rapid growth and adoption, we should give free access to some features previously available only in the commercial version. For this reason, we are making a change in the way Atlas is distributed. Here is a summary of how this is going to work:

The Atlas codebase will continue to be actively developed in our open-source repository (ariga/atlas) under the Apache 2 License. This version of Atlas will continue to be actively maintained and supported by us in community channels (GitHub, Discord, etc). We will continue to build and ship versions of this purely open-source version under the name "Atlas Community". For more information about obtaining Atlas Community binaries, see the docs.

In addition, we are starting to distribute for free (under the Atlas EULA) the binaries for our commercial product which will include features previously unavailable to the public. We plan to release many of these features to the open-source repositories over time to keep supporting more open-source innovation in the database space. To keep things short and sweet, this distribution of the project will simply be called "Atlas".

Starting today, our existing distribution channels (the atlasgo.sh installation script, Docker image, brew tap, etc.) will point to this edition, and we are adding parallel installation options for users that prefer to use the community edition.

Trying out the new features

Before we dive into the new features, let's make sure you have the latest version of Atlas installed. To do so, run the following command:

curl -sSf https://atlasgo.sh | sh

For more installation options, see the docs.

In addition, two of the following features (MS-SQL driver, schema inspect --visualize) require you to log in your CLI to Atlas Cloud. To do so, run the following command:

atlas login

The CLI will ask your permission to open a browser window to complete the login process.

After completing the login process, you should see a message similar to the following:

You are now connected to "acme-corp" on Atlas Cloud.

Support for Views

"Views" are virtual tables created by a query and stored in the database. Many teams use them to simplify complex queries, encapsulate logic, and present a consistent interface to users, abstracting the underlying data structures. Starting today, you can start managing views in your database using the same Atlas workflows you use for tables, indexes, foreign keys, etc. Views are currently supported for MySQL, MariaDB, PostgreSQL, MS-SQL, and SQLite.

To demonstrate how this works, let's start by creating a new database. For this example, let's run a local MySQL database using Docker:

docker run --rm -d --name atlas-demo -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pass -e MYSQL_DATABASE=default mysql:8

Next, create a new file named schema.hcl with the following content:

schema "default" {}

table "users" {
schema = schema.default
column "id" {
type = int
}
column "nickname" {
type = varchar(32)
}
column "phone_number" {
type = varchar(32)
}
}

view "users_clean" {
schema = schema.default
column "id" {
type = int
}
column "nickname" {
type = varchar(32)
}
as = <<-SQL
SELECT id, nickname
FROM ${table.users.name}
SQL
depends_on = [table.users]
}

In this example, we first defined a table named users that contains some PII (the user's phone number). Next, we defined a view named users_clean that exposes only the user's id and nickname. This view can be used by other applications to access the user's data without exposing any personally identifiable information.

Next, run the following command to apply the schema to the database:

atlas schema apply -u mysql://root:pass@localhost:3306/default -f schema.hcl

Atlas will connect to the database, inspect its current schema (which is empty) and produce the following output:

-- Planned Changes:
-- Create "users" table
CREATE TABLE `default`.`users` (`id` int NOT NULL, `nickname` varchar(32) NOT NULL, `phone_number` varchar(32) NOT NULL);
-- Create "users_clean" view
CREATE VIEW `default`.`users_clean` (`id`, `nickname`) AS SELECT id, nickname
FROM users;
Use the arrow keys to navigate: ↓ ↑ → ←
? Are you sure?:
▸ Apply
Abort

After reviewing the changes, press enter to apply them to the database. Atlas will connect to the database and apply the changes.

Next, run the following command to inspect the database schema:

atlas schema inspect -u mysql://root:pass@/default --format "{{ sql . \" \" }}"

Notice that we used the --format flag to instruct Atlas to output the schema as SQL statements.

Atlas will connect to the database and produce the following output:

-- Create "users" table
CREATE TABLE `users` (
`id` int NOT NULL,
`nickname` varchar(32) NOT NULL,
`phone_number` varchar(32) NOT NULL
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Create "users_clean" view
CREATE VIEW `users_clean` (
`id`,
`nickname`
) AS select `users`.`id` AS `id`,`users`.`nickname` AS `nickname` from `users`;

As you can see, Atlas created the users table and the users_clean view in the database!

Microsoft SQL Server Driver

Microsoft SQL Server, one of the longest-standing database engines in our business, was first released by Microsoft in 1989. MS-SQL is the go-to database for Windows environments in many industries. Over the last few months, we have been working on a driver for MS-SQL with some of our enterprise design partners and are happy to make it available to you as a beta feature.

To get started with MS-SQL, you must first opt-in to its beta testing program. To do so:

  1. Login to your Atlas Cloud account using the instructions above.
  2. Head over to your Atlas Cloud account.
  3. Click on your profile picture in the bottom left corner to open the user menu and select the "Personal Settings" option.
  4. Select the "Microsoft SQL Server" checkbox from the driver list and click on the "Save" button to save your changes.

Next, run the following command to start a local MS-SQL server using Docker:

docker run --rm -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=P@ssw0rd0995' -p 1433:1433 -d mcr.microsoft.com/mssql/server:latest

Notice that by providing the ACCEPT_EULA environment variable you confirm your acceptance of the Microsoft's End-User Licensing Agreement.

Create a new file named schema.hcl with the following content:

schema "dbo" {
}
table "users" {
schema = schema.dbo
column "id" {
type = int
}
column "nickname" {
type = varchar(32)
}
}

With this file ready, run the following command to apply the schema to the database:

atlas schema apply \
--url "sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master" \
--to "file://schema.hcl"

Atlas will connect to the database, inspect its current schema (which is empty) and produce the following output:

-- Planned Changes:
-- Create "users" table
CREATE TABLE [dbo].[users] ([id] int NOT NULL, [nickname] varchar(32) NOT NULL);
Use the arrow keys to navigate: ↓ ↑ → ←
? Are you sure?:
▸ Apply
Abort

After reviewing the changes, press enter to apply them to the database. Atlas will connect to the database and apply the changes.

Next, run the following command to inspect the database:

atlas schema inspect \
--url "sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master"

Atlas will connect to the database and produce the following output:

table "users" {
schema = schema.dbo
column "id" {
null = false
type = int
}
column "nickname" {
null = false
type = varchar(32)
}
}
schema "dbo" {
}

Great! Atlas created the users table in the database!

Do you use MS-SQL? We would love to hear feedback about your experience using this driver. Feel free to give it a try and reach out on our Discord channel to let us know what you think.

Built-in Schema Visualization

Over the past year we have released some tools to help developers visualize their database schemas using Entity Relationship Diagrams (ERDs). The success of these experiments (especially for Ent and Django) convinced us that we should provide this functionality natively in Atlas. So starting today, you can use the atlas schema inspect --visualize and atlas schema diff --visualize to create database ERDs directly from the command line.

To get started, create a new schema file named schema.hcl:

schema "default" {}

table "t1" {
schema = schema.default
column "id" {
type = int
}
primary_key {
columns = [column.id]
}
}

table "t2" {
schema = schema.default
column "id" {
type = int
}
column "t1_id" {
type = int
}
foreign_key "t1_id" {
columns = [column.t1_id]
ref_columns = [table.t1.column.id]
}
}

Next, run the following command to generate the ERD:

atlas schema inspect --dev-url docker://mysql/8/d --url file://schema.hcl --visualize

Atlas will prompt you to check where you would like to share the ERD:

Use the arrow keys to navigate: ↓ ↑ → ←
? Where would you like to share your schema visualization?:
▸ Publicly (gh.atlasgo.cloud)
Privately (acme-corp.atlasgo.cloud)

After selecting the desired option, Atlas will generate the ERD and print out the URL where you can access it:

https://gh.atlasgo.cloud/explore/cdd7bee5

ariga/atlas-deploy GitHub Action

GitHub Actions is a powerful tool that allows developers to automate workflows around GitHub repositories. We have previously published GitHub Actions for running CI for schema changes and for syncing your migration directory with Atlas Cloud.

Today, we are happy to announce the release of the ariga/atlas-deploy-action to the GitHub Actions Marketplace. This action allows you to run migrations directly from your GitHub Actions workflow. To get started, add the following step to your workflow:

name: Deploy Database Migrations
on:
push:
branches:
- master
jobs:
deploy:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- name: Deploy Atlas Migrations
uses: ariga/atlas-deploy-action@v0
with:
url: ${{ secrets.DATABASE_URL }}
dir: path/to/migrations

Type checking for schema files

I firmly believe that great developer experiences aren't created by any one single design decision but are rather a result of a relentless commitment to improving things little by little over long periods of time. As we work with Atlas as users every day, we constantly look for points of friction and frustration and try to find ways to make them better.

Sometimes, these changes are small and simple, like improving the help text of a command or updating a documentation page. Other times, they are more complex, and require building more fundamental infrastructure.

I give all of this as an intro to a feature that I am very excited about: type checking for the HCL schema. Starting with this release, we are shipping a type checker engine that will give you more precise and actionable feedback when you make mistakes in your schema files.

We have come as an industry to appreciate that a fast feedback loop is one of the most prominent factors in developer productivity, and it all starts in the local development environment.

Type checking validates the contents of your schema files and provides you with actionable feedback in case you make a mistake. For example, suppose you define a table like this (try spotting the errors yourself before reading on):

schema.hcl
table "users" {
column "id" {
type = int
}
column "name" {
type = varchar(255)
}
column "manager_id" {
type = int
}
primray_key {
columns = [
column.id
]
}
index "idx_name" {
coulmns = [
column.name
]
unique = true
}
foreign_key {
columns = [column.manager_id]
ref_columns = [column.id]
}
}

Trying to apply this schema will produce the following error:

Error: failed parsing mysql schema files: schema.hcl
schema.hcl:1,1-6: missing required attribute schema in block table
schema.hcl:11,3-14: unknown block type primray_key in block of type table (Did you mean primary_key?)
schema.hcl:17,5-19,6: unknown attribute coulmns in block of type index (Did you mean columns?)
schema.hcl:22,3-14: foreign_key block must have exactly 1 label

This type checking engine is available on the Atlas CLI and will be integrated into our editor plugins for VSCode and (the upcoming) JetBrains IDEs.

Wrapping up

This is it! I know this was a long post, but we are very excited about all of these new features and we hope you will find them useful. As always, we would love to hear your feedback and suggestions on our Discord server.