Skip to main content

Atlas Standalone Mode for SQLAlchemy

This guide shows how to use the Atlas SQLAlchemy provider for automatically loading SQLAlchemy models from a single folder into Atlas. If your project is mode complex and requires specific import logic, continue to the next guide about the Python Script Mode.

Installation​

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

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

Install the provider by running:

pip install atlas-provider-sqlalchemy

Setup​

  1. In your project directory, create a new file named atlas.hcl and populate it with the following configuration based on the desired driver:
atlas.hcl
data "external_schema" "sqlalchemy" {
    program = [
        "atlas-provider-sqlalchemy",
        "--path", "./models", // replace with the path to your SQLAlchemy models
        "--dialect", "postgresql"
    ]
}

env "sqlalchemy" {
    src = data.external_schema.sqlalchemy.url
    dev = "docker://postgres/16/dev?search_path=public"
    migration {
        dir = "file://migrations"
    }
    format {
        migrate {
            diff = "{{ sql . "  " }}"
        }
    }
}
  1. With the above configuration, you can run Atlas to automatically generate migration files based on the differences between your SQLAlchemy models and the current state of your database:
atlas migrate diff --env sqlalchemy

Running this command will generate files similar to this in the migrations directory:

migrations
|-- 20230918143104.sql
`-- atlas.sum

0 directories, 2 files

Examining the contents of 20230918143104.sql:

migrations/20230918143104.sql
-- Create "user_account" table
CREATE TABLE `user_account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `fullname` varchar(30) NULL,
  PRIMARY KEY (`id`)
) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Create "address" table
CREATE TABLE `address` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email_address` varchar(30) NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `user_id` (`user_id`),
  CONSTRAINT `address_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user_account` (`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 user_account and address tables in our database.

Next, alter the user_account entity to add a new age property:

    fullname: Mapped[Optional[str]] = mapped_column(String(30))
+   age: Mapped[Optional[int]] = mapped_column()
    addresses: Mapped[List["Address"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )

Re-run this command:

atlas migrate diff --env sqlalchemy

Observe a new migration file is generated in the migration directory:

-- Modify "user_account" table
ALTER TABLE `user_account` ADD COLUMN `age` int NULL;

Applying migrations​

You can now apply the new schema to your production database by running the following command:

atlas migrate apply --env sqlalchemy --url "YOUR_PRODUCTION_DB_URL"

Next Steps​

Now that your project is set up, start by choosing between the two workflows offered by Atlas for generating and planning migrations. Select the one you prefer that works best for you:

  • Declarative Migrations: Set up a Terraform-like workflow where each migration is calculated as the diff between your desired state and the current state of the database.

  • Versioned Migrations: Set up a migration directory for your project, creating a version-controlled source of truth of your database schema.