4 posts tagged with "erd"

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:

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:

//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:

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 ❤️.

Having a visual representation of your data model can be helpful as it allows for easier comprehension of complex data structures, and enables developers to better understand and collaborate on the data model of the application they are building.

Entity relationship diagrams (ERDs) are a common way to visualize data models, by showing how data is stored in the database. ERDs are graphical representations of the entities, their attributes, and the way these entities are related to each other.

Today we are happy to announce the release of DjangoViz, a new tool for automatically creating ERDs from Django data models.

Django is an open source Python framework for building web applications quickly and efficiently. In this blog post, I will introduce DjangoViz and demonstrate how to use it for generating Django schema visualizations using the Atlas playground.

Django ORM​

Django ORM is a built-in module in the Django web framework. It offers a high-level abstraction layer that enables developers to define complex application data models with ease. Unlike traditional ORM frameworks that rely on tables and foreign keys, Django models are defined using Python objects and relationships:

from django.db import models

class User(models.Model):
    username = models.CharField(max_length=255)
    email = models.EmailField(unique=True)
    password = models.CharField(max_length=255)

class Post(models.Model):
    title = models.CharField(max_length=255)
    content = models.TextField()
    author = models.ForeignKey(User, on_delete=models.CASCADE)

When the application runs, Django translates these Python models into database schemas, mapping each model to a corresponding database table and each field to a corresponding column in the table.
When working with schemas and making changes to them, being able to understand the full picture just through code can get complicated very quickly. To help developers better understand their schema, we have created DjangoViz.

Introducing DjangoViz​

For the purpose of this demo, we will follow the Django getting started tutorial, and showcase how you can use DjangoViz to visualize the default models included by Django's startproject command.

First, install Django and create a new project:

pip install Django
django-admin startproject atlas_demo
cd atlas_demo

Install the DjangoViz package:

pip install djangoviz

Add DjangoViz to your Django project's INSTALLED_APPS in atlas_demo/settings.py:

INSTALLED_APPS = [
    ...,
    'djangoviz',
    ...
]

DjangoViz supports either PostgreSQL or MySQL, in this example we will use PostgreSQL:

Install the PostgreSQL driver:

pip install psycopg2-binary

Configure the database to work with PostgreSQL in the settings.py file:

DATABASES = {
   "default": {
       "ENGINE": "django.db.backends.postgresql_psycopg2",
       "NAME": "postgres",
       "USER": "postgres",
       "PASSWORD": "pass",
       "HOST": "127.0.0.1",
       "PORT": "5432",
   }
}

Start a PostgreSQL container:

docker run --rm -p 5432:5432  -e POSTGRES_PASSWORD=pass -d postgres:15

Now, you can visualize your schema by running the djangoviz management command from your new project directory:

python manage.py djangoviz

You will get a public link to your visualization, which will present an ERD and the schema itself in SQL or HCL:

Here is a public link to your schema visualization:
  https://gh.atlasgo.cloud/explore/ac523fef

When clicking on the link you will see the ERD of your new project:

Wrapping up​

In this post, we discussed DjangoViz, a new tool that helps to quickly visualize Django schemas. With this tool, you can easily get an overview of the data model and visual of your schema. We would love to hear your thoughts and feedback if you decide to give it a go!

Have questions? Feedback? Find our team on our Discord server ❤️.

For a long time, one of the most common feature requests we've been getting from our users is the ability to manage their desired "schema state" using SQL. This is understandable, using Atlas DDL (HCL) can feel unfamiliar to some users, especially those who have never worked with Terraform before. For this reason, we're excited to announce the release of Atlas v0.9.0, which now fully supports SQL.

Schema as Code (SaC)​

Atlas applies the common IaC concept of declarative resource management to database schemas. With Atlas, users do not need to plan schema changes themselves. Instead of figuring out the correct SQL statements to update their database schemas, users provide to Atlas the schema definition that describe their desired state and Atlas generates a migration plan to move from the current state to the desired state defined by the schema.

Starting from v0.9.0, users can use SQL schema files (or a directory) containing CREATE and ALTER statements to describe their desired state. To demonstrate this, let's use this schema example with a single users table:

-- create table "users
CREATE TABLE users(
    id int NOT NULL,
    name varchar(100) NULL,
    PRIMARY KEY(id)
);

Given this schema file, Atlas offers two workflows to update databases:

• Declarative: Similar to Terraform, Atlas compares the current state of the database schema with the desired state defined by the SQL schema, and generates a migration plan to reach that state.
• Versioned: Atlas compares the current state defined by the migrations directory to the desired state defined by the SQL schema, and writes a new migration script to the directory to update the database schema to the desired state.

In this blog post, we'll focus on explaining how SQL schemas can be used with the declarative workflow. For the sake of simplicity, let's assume we have an empty database that we want to apply the schema above to:

atlas schema apply \
  --url "mysql://root:pass@localhost:3306/example" \
  --to "file://schema.sql" \
  --dev-url "docker://mysql/8/example"

atlas schema apply \
  --url "maria://root:pass@:3306/example" \
  --to "file://schema.sql" \
  --dev-url "docker://maria/latest/example"

atlas schema apply \
  --url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/15"

atlas schema apply \
  --url "sqlite://file.db" \
  --to "file://schema.sql" \
  --dev-url "sqlite://file?mode=memory"

Running the command above with the --auto-approve flag will apply the following changes:

-- Planned Changes:
-- Create "users" table
CREATE TABLE `users` (`id` int NOT NULL, `name` varchar NULL, PRIMARY KEY (`id`));

Hooray! We have successfully created the users table defined in our schema file. Let's inspect our database and ensure its schema was actually updated by the command above:

atlas schema inspect \
  --url "mysql://root:pass@localhost:3306/example" \
  --format "{{ sql . }}"

atlas schema inspect \
  --url "maria://root:pass@:3306/example" \
  --format "{{ sql . }}"

atlas schema inspect \
  --url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
  --format "{{ sql . }}"

atlas schema inspect \
  --url "sqlite://file.db" \
  --format "{{ sql . }}"

Excellent! As you can see, our database schema has been updated:

-- create "users" table
CREATE TABLE `users` (`id` int NOT NULL, `name` varchar NULL, PRIMARY KEY (`id`));

Now let's make our schema more interesting by adding a column to the users table and creating a blog_posts table with a foreign key that references users:

-- create table "users
CREATE TABLE users(
    id int NOT NULL,
    name varchar(100) NULL,
    email varchar(50) NULL,
    PRIMARY KEY(id)
);

-- create table "blog_posts"
CREATE TABLE blog_posts(
    id int NOT NULL,
    title varchar(100) NULL,
    body text NULL,
    author_id int NULL,
    PRIMARY KEY(id),
    CONSTRAINT author_fk FOREIGN KEY(author_id) REFERENCES users(id)
);

Next, executing atlas schema apply again will update the database schema with the following changes:

-- Planned Changes:
-- Add column "email" to table: "users"
ALTER TABLE `users` ADD COLUMN `email` varchar NULL;
-- Create "blog_posts" table
CREATE TABLE `blog_posts` (`title` varchar NULL, `body` text NULL, `author_id` int NULL, `id` int NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `author_fk` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION);

Boom! Atlas automatically calculates the difference between the current state of our database and the desired state defined by our schema file, and generates the necessary changes to migrate the database to the new state. We don't need to specify each individual migration – we simply tell Atlas what state we want the database to be in, and it handles the rest.

To see a full description of this generated migration plan, check out this diagram example in Atlas public playground:

Diff ERD​

Diff SQL​

Atlas Playground​

As part of this version, we have released the Atlas playground where users can visualize their database schemas in an interactive way. Simply provide an SQL or HCL schema, or import one from an existing database, and in return get an ERD visualizing their entire data model.

Users can also compare between two schemas with the Schema Diff button, and get the SQL statements necessary to migrate from one schema to the other - give it try!

A big thanks to @solomonme, @ronenlu and @masseelch for contributing this feature to Atlas!

Schema Loaders​

What's next? In the near future, we plan to add an infrastructure for loading schemas from external sources. This will enable ORM maintainers to integrate with Atlas and provide their schema definitions as Atlas schemas. As as result, they can utilize the Atlas engine to diff schemas, plan and lint migrations, execute them on the databases, and more.

The first ORM to integrate with Atlas will be Ent. Using this integration, Ent users will be able to generate Atlas schemas or migrations for their Ent projects with a single command:

atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "ent://path/to/schema" \
  --dev-url "docker://<driver-name>"

Would you like to see other ORMs integrated with Atlas? Please, join our Discord server and let me know.

What next?​

Have questions or feedback? Feel free to reach out on our Discord server.

Atlas is most commonly used for managing and applying schema changes to databases, but it can also be used for something else: exploring and understanding database schemas.

With inspection, Atlas connects to your database, analyzes its structure from the metadata tables, and creates a graph data structure that maps all the entities and relations within the database. Atlas can then take this graph and represent it in various formats for users to consume. In this post, I will present two such forms of representation: Entity Relationship Diagrams (ERDs) and JSON documents.

Schemas as ERDs​

One of the most useful ways to represent a database schema is using an Entity Relationship Diagram (ERD). This allows developers to see the schema in a visual and intuitive way, making it easy to understand the relationships between different elements of the database. When using ERDs, because the data is presented in a graph format, you can easily navigate through the schema and see how different entities are connected. This can be especially useful when working with complex or large databases, as it allows you to quickly identify patterns and connections that might not be immediately obvious when looking at the raw data.

To automatically generate an ERD from your database, you can use the Explore feature of Atlas Cloud. To visualize a schema using the Explore feature, you need to provide your database schema in one of two ways:

1. Provide a connection string to your database. This will allow Atlas Cloud to connect to your database and automatically generate a schema from the metadata tables. Note: this method only works for databases that are publicly accessible via the internet.

2. Provide the schema as an Atlas HCL file. If you have an existing Atlas project, you can use the atlas schema inspect command to generate the HCL file from your database.

   After installing Atlas, you can run the following command to generate the HCL representation of your database schema:

   # MySQL
   atlas schema inspect -u mysql://root:pass@localhost:3306/db_name
   
   # PostgreSQL
   atlas schema inspect postgres://postgres:pass@localhost:5432/db_name?sslmode=disable
   

Schemas as JSON documents​

In addition to producing ERDs, Atlas can also produce a JSON document that represents the database schema. One of the key benefits of representing the database schema as a JSON document is that it allows you to use standard tools like jq to analyze the schema programmatically. jq is a popular command-line tool for working with JSON data, and it can be especially useful for exploring and manipulating the schema data generated by Atlas.

With jq, you can easily extract specific information from the schema, such as the names of all the tables in the database or the foreign key relationships between different entities. This makes it easy to write scripts or programs that can automatically analyze the schema and identify potential issues or opportunities for optimization.

To get the JSON representation of your database schema, you can use the atlas schema inspect command with a custom logging format:

atlas schema inspect -u '<url>' --format '{{ json . }}'

This will output the schema as a JSON document:

{
  "schemas": [
    {
      "name": "test",
      "tables": [
        {
          "name": "blog_posts",
          "columns": [
            {
              "name": "id",
              "type": "int"
            },
            {
              "name": "title",
              "type": "varchar(100)",
              "null": true
            },
            // .. Truncated for brevity ..
        ]
    }
  ]
}

Once your schema is represented as a JSON document, you can use jq to analyze it. For example, to get a list of all the tables that contain a foreign key, run:

atlas schema inspect -u '<url>' --format '{{ json . }}' | jq '.schemas[].tables[] | select(.foreign_keys | length > 0) | .name'

This will output:

"blog_posts"

Wrapping up​

In this blog post, we demonstrated how Atlas can be used as a schema inspection and visualization tool, in addition to its more commonly known use as a schema migration tool. We showed how to use the Explore feature to create an ERD from your database schema, and how to use the atlas schema inspect command to generate a JSON document that can be analyzed using jq and other tools.

Have questions? Feedback? Feel free to reach out on our Discord server.