Skip to main content

4 posts tagged with "gorm"

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.

· 9 min read
Rotem Tamir

Hi everyone,

It's been a few weeks since the release of v0.22, and we're excited to be back with the next version of Atlas, packed with some long awaited features and improvements.

  • Redshift Support - Amazon Redshift, a fully managed, petabyte-scale data warehouse service in the cloud. Starting today, you can use Atlas to manage your Redshift Schema-as-Code.
  • CircleCI Integration - Following some recent requests from our Enterprise customers, we have added a CircleCI orb to make it easier to integrate Atlas into your CircleCI pipelines.
  • Kubernetes Operator Down Migrations - The Kubernetes Operator now detects when you are moving to a previous version and will attempt to apply a down migration if configured to do so.
  • GORM View Support - We have added support for defining SQL Views in your GORM models.
  • SQLAlchemy Provider Improvements - We have added support for defining models using SQLAlchemy Core Tables in the SQLAlchemy provider.
  • ERD v2 - We have added a new navigation sidebar to the ERD to make it easier to navigate within large schemas.
  • PostgreSQL Improvements - We have added support for PostgreSQL Event Triggers, Aggregate Functions, and Function Security.

Let's dive in!

Redshift Beta Support

Atlas's "Database Schema-as-Code" is useful even for managing small schemas with a few tables, but it really shines when you have a large schema with many tables, views, and other objects. This is the common case instead of the exception when you are dealing with Data Warehouses like Redshift that aggregate data from multiple sources.

Data warehouses typically store complex and diverse datasets consisting of hundreds of tables with thousands of columns and relationships. Managing these schemas manually can be a nightmare, and that's where Atlas comes in.

Today we are happy to announce the beta support for Amazon Redshift in Atlas. You can now use Atlas to manage your Redshift schema, generate ERDs, plan and apply changes, and more.

To get started, first install the latest version of the Atlas CLI:

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

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

Next, login to your Atlas account to activate the Redshift beta feature:

atlas login

To verify Atlas is able to connect to your Redshift database, run the following command:

atlas schema inspect --url "redshift://<username>:<password>@<host>:<port>/<database>?search_path=<schema>"

If everything is working correctly, you should see the Atlas DDL representation of your Redshift schema.

To learn more about the Redshift support in Atlas, check out the documentation.

CircleCI Integration

CircleCI is a popular CI/CD platform that allows you to automate your software development process. With this version we have added a CircleCI orb to make it easier to integrate Atlas into your CircleCI pipeline. CircleCI orbs are reusable packages of YAML configuration that condense repeated pieces of config into a single line of code.

As an example, suppose you wanted to create a CircleCI pipeline that pushes your migration directory to your Atlas Cloud Schema Registry. You can use the atlas-orb to simplify the configuration:

version: '2.1'
orbs:
atlas-orb: ariga/atlas-orb@0.0.3
workflows:
postgres-example:
jobs:
- push-dir:
context: the-context-has-ATLAS_TOKEN
docker:
- image: cimg/base:current
- environment:
POSTGRES_DB: postgres
POSTGRES_PASSWORD: pass
POSTGRES_USER: postgres
image: cimg/postgres:16.2
steps:
- checkout
- atlas-orb/setup:
cloud_token_env: ATLAS_TOKEN
version: latest
- atlas-orb/migrate_push:
dev_url: >-
postgres://postgres:pass@localhost:5432/postgres?sslmode=disable
dir_name: my-cool-project

Let's break down the configuration:

  • The push-dir job uses the cimg/postgres:16.2 Docker image to run a PostgreSQL database. This database will be used as the Dev Database for different operations performed by Atlas.
  • The atlas-orb/setup step initializes the Atlas CLI with the provided ATLAS_TOKEN environment variable.
  • The atlas-orb/migrate_push step pushes the migration directory my-cool-project to the Atlas Cloud Schema Registry.

To learn more about the CircleCI integration, check out the documentation.

Kubernetes Operator Down Migrations

The Atlas Operator is a Kubernetes operator that enables you to manage your database schemas using Kubernetes Custom Resources. In one of our recent releases, we added support for the migrate down command to the CLI. Using this command, you can roll back applied migrations in a safe and controlled way, without using pre-planned down migration scripts or manual intervention.

Starting with v0.5.0, the Atlas Operator supports down migrations as well. When you change the desired version of your database for a given AtlasMigration resource, the operator will detect whether you are moving to a previous version and will attempt to apply a down migration if you configured it to do so.

Down migrations are controlled via the new protectedFlows field in the AtlasMigration resource. This field allows you to specify the policy for down migrations. The following policy, for example, allows down migrations and auto-approves them:

apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasMigration
metadata:
name: atlasmig-mysql
spec:
protectedFlows:
migrateDown:
allow: true
autoApprove: true
# ... redacted for brevity

Alternatively, Atlas Cloud users may set the autoApprove field to false to require manual approval for down migrations. In this case, the operator will pause the migration and wait for the user to approve the down migration before proceeding:

ERD v2

When you push your migration directory to the Atlas Cloud Schema Registry, Atlas generates an ERD for your schema. The ERD is a visual representation of your schema that shows the different database objects in your schema and the relationships between them.

To make it easier to navigate within large schemas we have recently added a fresh new navigation sidebar to the ERD:

GORM View Support

GORM is a popular ORM library for Go that provides a simple way to interact with databases. The Atlas GORM provider provides a seamless integration between Atlas and GORM, allowing you to generate migrations from your GORM models and apply them to your database.

SQL Views are a powerful feature in relational databases that allow you to create virtual tables based on the result of a query. Managing views with GORM (and ORMs in general) is a notoriously clunky process, as they are normally not first-class citizens in the ORM world.

With v0.4.0, we have added a new API to the GORM provider that allows you to define views in your GORM models.

Here's a glimpse of how you can define a view in GORM:

// User is a regular gorm.Model stored in the "users" table.
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")
}),
}
}

By implementing the ViewDefiner interface, GORM users can now include views in their GORM models and have Atlas automatically generate the necessary SQL to create the view in the database.

To learn more about the GORM view support, check out the documentation.

Thanks to luantranminh for contributing this feature!

SQLAlchemy Provider Improvements

The Atlas SQLAlchemy provider allows you to generate migrations from your SQLAlchemy models and apply them to your database.

With v0.2.2, we have added support for defining models using SQLAlchemy Core Tables in addition to the existing support for ORM Models.

In addition, we have decoupled the provider from using a specific SQLAlchemy release, allowing users to use any version of SQLAlchemy they prefer. This should provide more flexibility and make it easier to integrate the provider into your existing projects.

Huge thanks to vshender for contributing these improvements!

Other Improvements

On our quest to support the long tail of lesser known database features we have recently added support for the following:

PostgreSQL Event Triggers

PostgreSQL Event Triggers are a special kind of trigger. Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events.

Here are some examples of how you can use event triggers in Atlas:

# Block table rewrites.
event_trigger "block_table_rewrite" {
on = table_rewrite
execute = function.no_rewrite_allowed
}

# Filter specific events.
event_trigger "record_table_creation" {
on = ddl_command_start
tags = ["CREATE TABLE"]
execute = function.record_table_creation
}

Aggregate Functions

Aggregate functions are functions that operate on a set of values and return a single value. They are commonly used in SQL queries to perform calculations on groups of rows. PostgreSQL allows users to define custom aggregate functions using the CREATE AGGREGATE statement.

Atlas now supports defining custom aggregate functions in your schema. Here's an example of how you can define an aggregate function in Atlas:

aggregate "sum_of_squares" {
schema = schema.public
arg {
type = double_precision
}
state_type = double_precision
state_func = function.sum_squares_sfunc
}

function "sum_squares_sfunc" {
schema = schema.public
lang = PLpgSQL
arg "state" {
type = double_precision
}
arg "value" {
type = double_precision
}
return = double_precision
as = <<-SQL
BEGIN
RETURN state + value * value;
END;
SQL
}

Function Security

PostgreSQL allows you to define the security level of a function using the SECURITY clause. The SECURITY clause can be set to DEFINER or INVOKER. When set to DEFINER, the function is executed with the privileges of the user that defined the function. When set to INVOKER, the function is executed with the privileges of the user that invoked the function. This is useful when you want to create functions that execute with elevated privileges.

Atlas now supports defining the security level of functions in your schema. Here's an example of how you can define a function with SECURITY DEFINER in Atlas:

function "positive" {
schema = schema.public
lang = SQL
arg "v" {
type = integer
}
return = boolean
as = "SELECT v > 0"
security = DEFINER
}

Wrapping Up

That's all for this release! We hope you try out (and enjoy) all of these new features and find them useful. As always, we would love to hear your feedback and suggestions on our Discord server.

· 5 min read
Rotem Tamir

We recently shipped a new feature in Atlas that enables developers to quickly create ERD (Entity Relationship Diagram) visualizations of their database schemas. Today, I want to show how this feature can be used in tandem with one of Atlas's core capabilities - "Schema Loaders" - to produce visualizations of GORM models.

But first, let's introduce the different characters in this story:

  • Atlas - an open-source tool for managing database schemas.
  • GORM - one of the most popular ORMs for Go.
  • ERD - a diagram that shows the relationships between entities in a database. (we'll see an example in a minute)
  • Schema Loaders - a feature in Atlas that allows users to load their database schemas from different sources.

In this blog post, we will create a toy GORM application, and show how you can use Atlas to visualize the database schema that GORM generates for you.

Step 1: Bootstrap the GORM application

Start by creating a new directory for our project. Then, initialize a new Go module:

mkdir gormviz
cd gormviz
go mod init example.io/gormviz

Next, we will install the GORM package and the SQLite driver:

go get -u gorm.io/gorm
go get -u gorm.io/driver/sqlite

Now, let's create a file that will contain our database models. We will call it models/models.go:

models/models.go
package models

import "gorm.io/gorm"

type User struct {
gorm.Model
Name string
Pets []Pet
}

type Pet struct {
gorm.Model
Name string
User User
UserID uint
}

Step 2: Setup Atlas

Now that we have a GORM application, let's set up Atlas. First, we will install the Atlas CLI:

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

For more installation options, see these instructions.

Next, let's install the GORM Atlas provider which will allow us to load our GORM models into Atlas:

go get ariga.io/atlas-provider-gorm@v0.1.0

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

After the provider is installed, let's create an Atlas project file to glue everything together.

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",
"--dialect", "sqlite",
]
}

env "gorm" {
src = data.external_schema.gorm.url
dev = "sqlite://file?mode=memory&_fk=1"
}

This file defines two things:

  • An External Schema Loader named gorm that will load our GORM models into Atlas by executing the ariga.io/atlas-provider-gorm program on our models package.
  • An Environment named gorm that will use the gorm schema loader to load the models into a SQLite database in memory.

Step 3: Visualize the GORM models

Now that we have everything set up, let's run Atlas and see what we get:

atlas schema inspect -w --env gorm --url 'env://src'

Let's break down this command:

  • The schema inspect command is used to inspect a database schema and produce a representation of it.
  • The -w flag tells Atlas to open a web browser and display the visualization.
  • The --env gorm flag tells Atlas to use the gorm environment we defined in the atlas.hcl file.
  • The --url 'env://src' flag tells Atlas to use schema defined in the src attribute of the gorm env.

When we run this command,

Atlas will prompt us to ask if we want this visualization to be shared on the Public Atlas Playground or if we want to keep it private:

? Where would you like to share your schema visualization?:
▸ Publicly (gh.atlasgo.cloud)
Your personal workspace (requires 'atlas login')

As this is just a toy example, let's publish it to the public playground by pressing Enter.

Once we do this, Atlas will extract the database schema from our GORM models, and publish it to the playground, printing the URL of the visualization:

https://gh.atlasgo.cloud/explore/60d94de7

Privately sharing visualizations

By publishing visualizations to the public playground, you can easily share them with others by simply sending them the URL. However, if you want to keep your visualizations private, such that they are only accessible to you and your team, you can do so by first logging in to Atlas Cloud:

atlas login

Your browser should open and prompt you to either login or create a free account if you don't already have one. Upon completion, you should see a message in your terminal that looks like this:

You are now connected to rotemtam85 on Atlas Cloud.

Once you are logged in, you can re-run the atlas schema inspect --env gorm --web command and Atlas will prompt you to ask if you want to share the visualization in your personal workspace:

? Where would you like to share your schema visualization?:
Publicly (gh.atlasgo.cloud)
▸ Privately (rotemtam85.atlasgo.cloud)

Select the Privately option and press Enter. Atlas will then publish the visualization to your personal workspace and print the URL of the visualization:

https://rotemtam85.atlasgo.cloud/explore/2da80ffx

Wrapping up

In this post, we have shown how to use Atlas to visualize the database schema that GORM generates for your application. ERD visualizations are just one of the many features that Atlas provides for working with your database schema. To learn more about what you can do with the Atlas GORM provider, check out the relevant guide.

How can we make Atlas better?

We would love to hear from you on our Discord server ❤️.

· 7 min read
Rotem Tamir
TL;DR

You can now import the desired database schema from any ORM or other tool into Atlas, and use it to automatically plan migrations for you.

See an example

Introduction

Today, I'm happy to share with you one of the most exciting features we've added to Atlas since its inception: "External Schemas".

Atlas is a modern tool for managing your database schema. It allows you to inspect, plan, lint and execute schema changes to your database. It is designed to be used by developers, DBAs and DevOps engineers alike.

Schema-as-Code

Atlas is built around the concept of database "Schema-as-Code", which means that you define the desired schema of your database in a declarative way, and Atlas takes care of planning and executing the necessary migrations to get your database to the desired state. The goal of this approach is to let organizations build a single source of truth for complex data topologies, and to make it easy to collaborate on schema changes.

Schema Loaders

To achieve this goal, Atlas provides support for "Schema Loaders" which are different mechanisms for loading the desired state of your database schema into Atlas. Until today, Atlas supported a few ways to load your schema:

  • Using Atlas DDL - an HCL based configuration language for defining database schemas.
  • Using Plain SQL - a simple way to define your schema using plain SQL files (CREATE TABLE statements, etc.)
  • From an existing database - Atlas can connect to your database and load the schema from it.
  • The Ent ORM - Atlas can load the schema of your Ent project.

Today, we are adding support for "External Schemas", which means that you can now import the desired database schema from any ORM or other tool into Atlas, and use it to automatically plan migrations and execute them for you.

How do External Schemas work?

External Schemas are implemented using a new type of Datasource called external_schema. The external_schema data source enables the import of an SQL schema from an external program into Atlas' desired state. With this data source, users have the flexibility to represent the desired state of the database schema in any language.

To use an external_schema, create a file named atlas.hcl with the following content:

data "external_schema" "example" {
program = [
"echo",
"create table users (name text)",
]
}

env "local" {
src = data.external_schema.example.url
dev = "sqlite://file?mode=memory&_fk=1"
}

In this dummy example, we use the echo command to generate a simple SQL schema. In a real-world scenario, you would use a program that understands your ORM or tool of choice to generate the desired schema. Some ORMs support this out-of-the-box, such as Laravel's Eloquent's schema:dump command, while others require some simple integrations work to extract the schema from.

In the next section we will present the GORM Atlas Provider and how it can be used to seamlessly integrate a GORM based project with Atlas.

Demo Time

GORM is a popular ORM widely used in the Go community. GORM allows users to manage their database schemas using its AutoMigrate feature, which is usually sufficient during development and in many simple cases.

However, at some point, teams need more control and decide to employ the versioned migrations methodology. Once this happens, the responsibility for planning migration scripts and making sure they are in line with what GORM expects at runtime is moved to developers.

Atlas can automatically plan database schema migrations for developers using GORM. Atlas plans migrations by calculating the diff between the current state of the database, and its desired state.

In the context of versioned migrations, the current state can be thought of as the database schema that would have been created by applying all previous migration scripts.

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.

Install the provider by running:

go get -u ariga.io/atlas-provider-gorm

Standalone vs Go Program mode

The Atlas GORM Provider can be used in two modes:

  • Standalone - If all of your GORM models exist in a single package, and either embed gorm.Model or contain gorm struct tags, you can use the provider directly to load your GORM schema into Atlas.
  • Go Program - If your GORM models are spread across multiple packages, or do not embed gorm.Model or contain gorm struct tags, you can use the provider as a library in your Go program to load your GORM schema into Atlas.

Standalone mode

If all of your GORM models exist in a single package, and either embed gorm.Model or contain gorm struct tags, you can use the provider directly to load your GORM schema into Atlas.

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", "./path/to/models",
"--dialect", "mysql", // | postgres | sqlite
]
}

env "gorm" {
src = data.external_schema.gorm.url
dev = "docker://mysql/8/dev"
migration {
dir = "file://migrations"
}
format {
migrate {
diff = "{{ sql . \" \" }}"
}
}
}

In this example, we use the go run command to run the atlas-provider-gorm program and load the schema from the ./path/to/models directory. The atlas-provider-gorm program will scan the directory for GORM models and generate the desired schema for them. The --dialect flag is used to specify the database dialect that the schema should be generated for. The atlas-provider-gorm program supports the following dialects: mysql, postgres, and sqlite.

For the sake of brevity, we will not review the Go program mode in this post, but you can find more information about it in the GORM Guide.

External schemas in action

Atlas supports a versioned migrations workflow, where each change to the database is versioned and recorded in a migration file. You can use the atlas migrate diff command to automatically generate a migration file that will migrate the database from its latest revision to the current GORM schema.

Suppose we have the following GORM models in our models package:

package models

import "gorm.io/gorm"

type User struct {
gorm.Model
Name string
Pets []Pet
}

type Pet struct {
gorm.Model
Name string
User User
UserID uint
}

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
|-- 20230627123246.sql
`-- atlas.sum

0 directories, 2 files

Examining the contents of 20230625161420.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,
PRIMARY KEY (`id`),
INDEX `idx_users_deleted_at` (`deleted_at`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Create "pets" table
CREATE TABLE `pets` (
`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,
`user_id` bigint unsigned NULL,
PRIMARY KEY (`id`),
INDEX `fk_users_pets` (`user_id`),
INDEX `idx_pets_deleted_at` (`deleted_at`),
CONSTRAINT `fk_users_pets` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Amazing! Atlas automatically generated a migration file that will create the pets and users tables in our database!

Next, alter the models.Pet struct to add a Nickname field:

type Pet struct {
gorm.Model
Name string
+ Nickname string
User User
UserID uint
}

Re-run this command:

atlas migrate diff --env gorm 

Observe a new migration file is generated:

-- Modify "pets" table
ALTER TABLE `pets` ADD COLUMN `nickname` longtext NULL;

Conclusion

In this post, we have presented External Schemas and how they can be used to automatically generate database schema directly from your ORM models. We have also demonstrated how to use the GORM Atlas Provider to automatically plan migrations for your GORM models.

We believe that this is a huge step forward in making Atlas more accessible to developers who are already using ORMs in their projects. We hope that you will find this feature useful and we look forward to hearing your feedback.

How can we make Atlas better?

We would love to hear from you on our Discord server ❤️.