Skip to main content

Atlas v0.35: Oracle, Bootstrap Projects, and more

· 9 min read
Rotem Tamir
Rotem Tamir
Building Atlas

Hey everyone!

It's been just over a week since our last release, and we are back with another batch of exciting features and improvements. Here's what's in store for you in Atlas v0.35:

  • Bootstrap Projects - You can now bootstrap SQL projects with one command, making it easier to get started with Atlas. Using the new split and write template functions, you can now create a code representation of your database schema in SQL or HCL format to turn your database into code in no time.
  • Atlas for Oracle in Beta - We are excited to announce that Atlas is now in beta for Oracle databases.

Announcing Project Bootstrapping​

Atlas has always been about managing your database schema as code. Most teams start with a single file, but as projects grow, you want structure. Like in any serious programming language, splitting code into logical parts becomes critical. That's why Atlas supports HCL, a declarative, graph-like format where order doesn't matter and dependencies are explicit:

table "users" {
  schema = schema.public
  // table definition goes here
}

We added SQL support in v0.9 (Jan '23) due to popular demand, but SQL is imperative, meaning large projects quickly get messy. You need to carefully manage statement order, so splitting files without breaking things becomes difficult. To make things easier, we introduced SQL Imports, to let you define the dependencies between SQL files and delegate the topological sorting to Atlas.

Still, if you were working with a large, complex database, bootstrapping a clean project structure was tedious. You'd either end up with a giant monolithic file or spend time manually organizing schema objects. Today, we're introducing the building blocks that will let you project bootstrap with ease.

With the new split and write template functions, available via the schema inspect command, Atlas can inspect any existing database - no matter how complex - and generate a clean, modular project. Tables, views, schemas, and relationships are split into structured files you can immediately work with. We believe this will make scaling schema management much easier and more approachable.

The command looks something like this:

atlas schema inspect --url '<DB URL>' --format "{{ sql . | split | write }}"

The split function automatically organizes your schema into logical files, while the write function writes the output to the specified directory. This way, you can quickly turn your existing database into a well-structured project without the hassle of manually splitting files or managing dependencies. The split function intelligently handles both SQL and HCL formats, so you can use it regardless of your preferred format.

Here's an example of how it works:

To learn more about the new split and write template functions, check out the documentation.

Atlas for Oracle in Beta​

note

The Oracle Driver is currently in beta and only supports creating and modifying tables and columns.

One of our main goals for Atlas is to create a unified experience for managing database schemas across different databases. To date we have supported many databases, including MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and ClickHouse. Today, we are happy to announce that we are adding support for Oracle databases to the list.

It is worth mentioning that Atlas uses a fork of the open-source go-ora driver to connect to Oracle databases. Oracle is a trademark of Oracle Corporation. Atlas is not affiliated with or endorsed by Oracle.

Oracle support is an Atlas Pro feature and is currently in beta.

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

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

Oracle support is an Atlas Pro feature. You can try it out for free by signing up or running:

atlas login

To spin up a local Oracle Database instance using docker, run:

docker run --rm -e 'ORACLE_PWD=Pssw0rd0995' -p 1521:1521 --name atlas-demo -d \
  container-registry.oracle.com/database/free:latest-lite

Note that the Oracle Database image is a bit larger than other database images, so it may take a while to download.

atlas schema inspect --url='oracle://PDBADMIN:Pssw0rd0995@localhost:1521/FREEPDB1'

After the container is up and running, you need connect to it and update the permissions of the PDBADMIN user to allow Atlas to create and modify tables and columns:

docker exec -i atlas-demo sqlplus \
  -S 'sys/Pssw0rd0995@127.0.0.1:1521/FREEPDB1' AS SYSDBA <<SQL
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO PDBADMIN;
-- Grant privileges to manage schemas in the PDB
GRANT CREATE USER, CREATE ANY TABLE, CREATE VIEW TO PDBADMIN;
GRANT ALTER USER, ALTER ANY TABLE TO PDBADMIN;
GRANT DROP USER, DROP ANY TABLE TO PDBADMIN;
-- Grant execute on DBMS_LOCK to create advisory locks
GRANT EXECUTE ON SYS.DBMS_LOCK TO PDBADMIN;
SQL

If you don't setup the permissions correctly, you will get an error when trying to apply a schema.

Error: acquiring database lock: sql/oracle: allocate lock: ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 error occur at position: 6

Managing your database schema as code​

Atlas supports a workflow called declarative schema migrations. In this workflow, you first define the desired state of your database schema (in one of many supported formats and languages). Then, you let Atlas calculate the diff between the desired state and the actual state of your database. Atlas then generates the SQL commands that will bring your database to the desired state.

Let's see this in action. First, create a new file name schema.sql. This file will contain the desired state of our database in plain SQL.

schema.sql
-- Create the users table
CREATE TABLE users (
  id NUMBER CONSTRAINT PK_users PRIMARY KEY,
  email VARCHAR2(255),
  display_name VARCHAR2(255)
);

-- Create the posts table with a custom name for the FK constraint
CREATE TABLE posts (
  id NUMBER CONSTRAINT PK_posts PRIMARY KEY,
  title VARCHAR2(255),
  body CLOB,
  author_id NUMBER,
  CONSTRAINT author_fk FOREIGN KEY (author_id) REFERENCES users(id)
);
note

Resource names in Oracle are case-sensitive and are converted to uppercase by default. To use lowercase names, you need to wrap them in double quotes. For example, CREATE TABLE "users" will create a table named users, while CREATE TABLE users will create a table named USERS. Atlas always quotes the resource names in the generated SQL, to ensure that they are created with the correct case.

Applying our schema​

Next, let's apply this schema to our database using the atlas schema apply command.

atlas schema apply -u "oracle://PDBADMIN:Pssw0rd0995@localhost:1521/FREEPDB1" \
  --to file://schema.sql \
  --dev-url "docker://oracle/free:latest-lite?t=10m"

Atlas will connect to our target database to inspect its current state. Next, it will use the dev-database to normalize our schema and generate the SQL commands that will bring our database to the desired state:

note
  • Since the Oracle Container is slow to start, we add t=10m to the dev-url to allow it to run for 10 minutes. If you run into trouble with the dev container, you can add v=1 to see more verbose logs.
  • In Oracle, the schema name is the name of the user in the connection string, so the tables will be created in the PDBADMIN schema.
Planning migration statements (2 in total):

  -- add new table named "users":
    -> CREATE TABLE "USERS" (
         "ID" NUMBER NOT NULL,
         "EMAIL" VARCHAR2(255),
         "DISPLAY_NAME" VARCHAR2(255),
         CONSTRAINT "PK_USERS" PRIMARY KEY ("ID")
       )
  -- add new table named "posts":
    -> CREATE TABLE "POSTS" (
         "ID" NUMBER NOT NULL,
         "TITLE" VARCHAR2(255),
         "BODY" CLOB,
         "AUTHOR_ID" NUMBER,
         CONSTRAINT "PK_POSTS" PRIMARY KEY ("ID"),
         CONSTRAINT "AUTHOR_FK" FOREIGN KEY ("AUTHOR_ID") REFERENCES "USERS" ("ID")
       )

-------------------------------------------

Analyzing planned statements (2 in total):

  -- no diagnostics found

  -------------------------
  -- 4.424169042s
  -- 2 schema changes

-------------------------------------------

? Approve or abort the plan:
  ▸ Approve and apply
    Abort

After applying the schema, Atlas confirms that the changes were applied:

-------------------------------------------

Applying approved migration (2 statements in total):

  -- add new table named "users"
    -> CREATE TABLE "USERS" (
         "ID" NUMBER NOT NULL,
         "EMAIL" VARCHAR2(255),
         "DISPLAY_NAME" VARCHAR2(255),
         CONSTRAINT "PK_USERS" PRIMARY KEY ("ID")
       )
  -- ok (88.693875ms)

  -- add new table named "posts"
    -> CREATE TABLE "POSTS" (
         "ID" NUMBER NOT NULL,
         "TITLE" VARCHAR2(255),
         "BODY" CLOB,
         "AUTHOR_ID" NUMBER,
         CONSTRAINT "PK_POSTS" PRIMARY KEY ("ID"),
         CONSTRAINT "AUTHOR_FK" FOREIGN KEY ("AUTHOR_ID") REFERENCES "USERS" ("ID")
       )
  -- ok (18.533542ms)

  -------------------------
  -- 107.422708ms
  -- 1 migration
  -- 2 sql statements

Next, let's re-run the atlas schema apply command. This time, Atlas will detect that the database is already in the desired state and will not generate any changes:

Schema is synced, no changes to be made

Altering our schema​

Now, let's make some changes to our schema. Open the schema.sql file and add a new column to the users table:

schema.sql
CREATE TABLE users (
  id NUMBER CONSTRAINT PK_users PRIMARY KEY,
  email VARCHAR2(255),
  display_name VARCHAR2(255),
+ bio VARCHAR2(1024) -- New column added
);

Next, let's re-run the atlas schema apply command. This time, Atlas will detect that the schema has changed and will generate the needed SQL commands to bring the database to the desired state:

Planning migration statements (1 in total):

  -- add columns to table "users":
    -> ALTER TABLE "USERS" ADD "BIO" VARCHAR2(1024)

-------------------------------------------

Analyzing planned statements (1 in total):

  -- no diagnostics found

  -------------------------
  -- 1.582271459s
  -- 1 schema change

-------------------------------------------

? Approve or abort the plan:
  ▸ Approve and apply
    Abort

After applying the changes, Atlas confirms once again that the changes were applied:

-------------------------------------------

Applying approved migration (1 statement in total):

  -- add columns to table "users"
    -> ALTER TABLE "USERS" ADD "BIO" VARCHAR2(1024)
  -- ok (111.057416ms)

  -------------------------
  -- 111.109958ms
  -- 1 migration
  -- 1 sql statement

As we announced in a previous release, we have kicked off a beta feedback program to help us improve the driver and the overall user experience. If you are interested in taking part, please fill out this short form:

Beta Feedback Program

Wrapping Up​

We hope you enjoy the new features and improvements. As always, we would love to hear your feedback and suggestions on our Discord server.