Repository: ariga/atlas
Pull request #2411 (2023-12-31 13:06:46)

doc/md: add sqlalchemy provider guide


Patch:
diff --git a/cmd/atlas/internal/cmdapi/migrate.go b/cmd/atlas/internal/cmdapi/migrate.go
index c2d99c18..bdfae0cf 100644
--- a/cmd/atlas/internal/cmdapi/migrate.go
+++ b/cmd/atlas/internal/cmdapi/migrate.go
@@ -570,7 +570,7 @@ func migrateDiffCmd() *cobra.Command {
 by executing its files. It then compares its state to the desired state and create a new migration file containing
 SQL statements for moving from the current to the desired state. The desired state can be another another database,
 an HCL, SQL, or ORM schema. See: https://atlasgo.io/versioned/diff`,
-			Example: `  atlas migrate diff "--dev-url docker://mysql/8/dev" --to "file://schema.hcl"
+			Example: `  atlas migrate diff --dev-url "docker://mysql/8/dev" --to "file://schema.hcl"
   atlas migrate diff --dev-url "docker://postgres/15/dev?search_path=public" --to "file://atlas.hcl" add_users_table
   atlas migrate diff --dev-url "mysql://user:pass@localhost:3306/dev" --to "mysql://user:pass@localhost:3306/dbname"
   atlas migrate diff --env dev --format '{{ sql . "  " }}'`,
diff --git a/cmd/atlas/internal/sqlparse/myparse/myparse_oss.go b/cmd/atlas/internal/sqlparse/myparse/myparse_oss.go
index 6c026676..3843fad0 100644
--- a/cmd/atlas/internal/sqlparse/myparse/myparse_oss.go
+++ b/cmd/atlas/internal/sqlparse/myparse/myparse_oss.go
@@ -14,19 +14,19 @@ import (
 )
 
 // Parser for fixing linting changes.
-type Parser struct{}
+type FileParser struct{}
 
 // FixChange fixes the changes according to the given statement.
-func (*Parser) FixChange(migrate.Driver, string, schema.Changes) (schema.Changes, error) {
+func (*FileParser) FixChange(migrate.Driver, string, schema.Changes) (schema.Changes, error) {
 	return nil, errors.New("unimplemented")
 }
 
 // ColumnFilledBefore checks if the column was filled with values before the given position in the file.
-func (*Parser) ColumnFilledBefore([]*migrate.Stmt, *schema.Table, *schema.Column, int) (bool, error) {
+func (*FileParser) ColumnFilledBefore([]*migrate.Stmt, *schema.Table, *schema.Column, int) (bool, error) {
 	return false, errors.New("unimplemented")
 }
 
 // CreateViewAfter checks if a view was created after the position with the given name to a table.
-func (*Parser) CreateViewAfter([]*migrate.Stmt, string, string, int) (bool, error) {
+func (*FileParser) CreateViewAfter([]*migrate.Stmt, string, string, int) (bool, error) {
 	return false, errors.New("unimplemented")
 }
diff --git a/cmd/atlas/internal/sqlparse/sqlparse.go b/cmd/atlas/internal/sqlparse/sqlparse.go
index 710a9e30..5a2609fe 100644
--- a/cmd/atlas/internal/sqlparse/sqlparse.go
+++ b/cmd/atlas/internal/sqlparse/sqlparse.go
@@ -57,7 +57,7 @@ func ParserFor(name string) Parser {
 }
 
 func init() {
-	Register(mysql.DriverName, &myparse.Parser{})
+	Register(mysql.DriverName, &myparse.FileParser{})
 	Register(postgres.DriverName, &pgparse.Parser{})
 	Register(sqlite.DriverName, &sqliteparse.FileParser{})
 }
diff --git a/doc/md/guides/guides.mdx b/doc/md/guides/guides.mdx
index bea00817..b882934d 100644
--- a/doc/md/guides/guides.mdx
+++ b/doc/md/guides/guides.mdx
@@ -263,6 +263,12 @@ import { Card, CardImage } from "../components/card";
     url="/guides/orms/hibernate"
     image={CardImage.Tools}
   />
+   <Card
+    name={"SQLAlchemy"}
+    description={"This guide is for automatic migration planning for SQLAlchemy with Atlas"}
+    url={"/guides/orms/sqlalchemy"}
+    image={CardImage.Tools}
+  />
 </div>
 
 ## Terraform
diff --git a/doc/md/guides/orms/sqlalchemy.md b/doc/md/guides/orms/sqlalchemy.md
new file mode 100644
index 00000000..a08dc1da
--- /dev/null
+++ b/doc/md/guides/orms/sqlalchemy.md
@@ -0,0 +1,271 @@
+---
+id: sqlalchemy
+title: Automatic migration planning for SQLAlchemy
+slug: /guides/orms/sqlalchemy
+---
+
+## TL;DR
+* [SQLAlchemy](https://www.sqlalchemy.org) is a Python SQL toolkit and Object Relational Mapper.
+* [Atlas](https://atlasgo.io) is an open-source tool for inspecting, planning, linting and
+  executing schema changes to your database.
+* Developers using SQLAlchemy can use Atlas to automatically plan schema migrations
+  for them, based on the desired state of their schema instead of crafting them by hand.
+
+## Automatic migration planning for SQLAlchemy
+
+SQLAlchemy is a popular ORM toolkit widely used in the Python community. SQLAlchemy allows users to
+describe their data model using its [declarative-mapping](https://docs.sqlalchemy.org/en/20/orm/declarative_tables.html)
+feature. To actually create the underlying tables, users can use the `Base.metadata.create_all` method
+which may be sufficient during development where tables can be routinely dropped and re-created.
+
+However, at some point, teams need more control and decide to employ
+the [versioned migrations](/concepts/declarative-vs-versioned#versioned-migrations) methodology,
+which is a more robust way to manage a database schema.
+
+The native way to manage migrations with SQLAlchemy is to use the [Alembic](https://alembic.sqlalchemy.org/en/latest/) migration tool.
+Alembic can [automatically generate](https://alembic.sqlalchemy.org/en/latest/autogenerate.html#auto-generating-migrations)
+migration scripts from the difference between the current state of the database and the desired state of the application.
+
+A downside of this approach is that in order for it to work, a pre-existing database with the current version of the schema must be connected to.
+In many production environments, databases should generally not be reachable from developer workstations,
+which means this comparison is normally done against a local copy of the database which may have
+undergone some changes that aren't reflected in the existing migrations.
+In addition, Alembic auto-generation is [fails to detect many kinds of changes](https://alembic.sqlalchemy.org/en/latest/autogenerate.html#what-does-autogenerate-detect-and-what-does-it-not-detect)
+and cannot be relied upon to generate production-ready migration scripts without routine manual intervention.
+
+Atlas, on the other hand, can automatically plan database schema migrations for SQLAlchemy
+without requiring a connection to such a database and can detect almost any kind of schema change.
+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.
+
+The desired schema of your application can be provided to Atlas via an [External Schema Datasource](/atlas-schema/projects#data-source-external_schema),
+which is any program that can output a SQL schema definition to stdout.
+
+To use Atlas with SQLAlchemy, users can utilize the [SQLAlchemy Atlas Provider](https://github.com/ariga/atlas-provider-sqlalchemy),
+which is a small program that can be used to load the schema of a SQLAlchemy project into Atlas.
+
+In this guide, we will show how Atlas can be used to automatically plan schema migrations for
+SQLAlchemy users.
+
+## Prerequisites
+
+* A local [SQLAlchemy](https://www.sqlalchemy.org) project.
+
+If you don't have a SQLAlchemy project handy, check out the [SQLAlchemy quick start page](https://docs.sqlalchemy.org/en/20/orm/quickstart.html).
+
+## Using the Atlas SQLAlchemy Provider
+
+In this guide, we will use the [SQLAlchemy Atlas Provider](https://github.com/ariga/atlas-provider-sqlalchemy)
+to automatically plan schema migrations for a SQLAlchemy project.
+
+### Installation
+
+Install Atlas from macOS or Linux by running:
+```bash
+curl -sSf https://atlasgo.sh | sh
+```
+See [atlasgo.io](https://atlasgo.io/getting-started#installation) for more installation options.
+
+Start Python virtual environment if you haven't already:
+```bash
+python3 -m venv venv
+source venv/bin/activate
+```
+
+Install the provider by running:
+```bash
+pip install atlas-provider-sqlalchemy
+``` 
+
+:::info TL;DR
+
+The SQLAlchemy Atlas Provider works by importing your SQLAlchemy models and extracting the schema from them.
+
+Therefore, you will need to run the provider from within your project's Python environment.
+
+:::
+
+### Standalone vs Script mode
+
+The Atlas SQLAlchemy Provider can be used in two modes:
+
+* **Standalone** - If all of your SQLAlchemy models exist in a single module,
+ you can use the provider directly to load your SQLAlchemy schema into Atlas.
+* **Script** - In other cases, you can use the provider as a python script to load your SQLAlchemy schema into Atlas.
+
+### Standalone mode
+
+In your project directory, create a new file named `atlas.hcl` with the following contents:
+
+```hcl
+data "external_schema" "sqlalchemy" {
+  program = [
+    "atlas-provider-sqlalchemy",
+    "--path", "./db",
+    "--dialect", "mysql" // mariadb | postgresql | sqlite | mssql
+  ]
+}
+
+env "sqlalchemy" {
+  src = data.external_schema.sqlalchemy.url
+  dev = "docker://mysql/8/dev"
+  migration {
+    dir = "file://migrations"
+  }
+  format {
+    migrate {
+      diff = "{{ sql . \"  \" }}"
+    }
+  }
+}
+```
+
+#### As Python Script 
+
+If you want to use the provider as a python script, you can use the provider as follows:
+Create a new file named `load_models.py` with the following contents:
+
+```python
+# import one of the models
+from models import User
+from atlas_provider_sqlalchemy.ddl import print_ddl
+
+print_ddl("mysql", [User])
+```
+
+Next, in your project directory, create a new file named `atlas.hcl` with the following contents:
+
+```hcl
+data "external_schema" "sqlalchemy" {
+    program = [
+        "python3",
+        "load_models.py"
+    ]
+}
+
+env "sqlalchemy" {
+  src = data.external_schema.sqlalchemy.url
+  dev = "docker://mysql/8/dev"
+  migration {
+    dir = "file://migrations"
+  }
+  format {
+    migrate {
+      diff = "{{ sql . \"  \" }}"
+    }
+  }
+}
+```
+
+## Usage
+
+Atlas supports a [versioned migrations](/concepts/declarative-vs-versioned#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 SQLAlchemy schema.
+
+Suppose we have the following SQLAlchemy models in our `db/models.py` file:
+
+```python
+from typing import List, Optional
+from sqlalchemy import ForeignKey, String
+from sqlalchemy.orm import Mapped, mapped_column, relationship, DeclarativeBase
+
+
+class Base(DeclarativeBase):
+    pass
+
+
+class User(Base):
+    __tablename__ = "user_account"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    name: Mapped[str] = mapped_column(String(30))
+    fullname: Mapped[Optional[str]] = mapped_column(String(30))
+    addresses: Mapped[List["Address"]] = relationship(
+        back_populates="user", cascade="all, delete-orphan"
+    )
+
+
+class Address(Base):
+    __tablename__ = "address"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    email_address: Mapped[str] = mapped_column(String(30))
+    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
+    user: Mapped["User"] = relationship(back_populates="addresses")
+```
+
+Using the [standalone mode](#standalone-mode) configuration file for the provider,
+we can generate a migration file by running this command:
+
+```bash
+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`:
+
+```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` field:
+
+```diff
+    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:
+
+```bash
+atlas migrate diff --env sqlalchemy
+```
+
+Observe a new migration file is generated:
+
+```sql
+-- Modify "user_account" table
+ALTER TABLE `user_account` ADD COLUMN `age` int NULL;
+```
+
+## Conclusion
+
+In this guide we demonstrated how projects using SQLAlchemy can use Atlas to automatically
+plan schema migrations based only on their data model. To learn more about executing
+migrations against your production database, read the documentation for the
+[`migrate apply`](/versioned/apply) command.
+
+Have questions? Feedback? Find our team [on our Discord server](https://discord.gg/zZ6sWVg6NT)
diff --git a/doc/md/reference.md b/doc/md/reference.md
index 2927e2ab..f625a868 100644
--- a/doc/md/reference.md
+++ b/doc/md/reference.md
@@ -118,7 +118,7 @@ an HCL, SQL, or ORM schema. See: https://atlasgo.io/versioned/diff
 #### Example
 
 ```
-  atlas migrate diff "--dev-url docker://mysql/8/dev" --to "file://schema.hcl"
+  atlas migrate diff --dev-url "docker://mysql/8/dev" --to "file://schema.hcl"
   atlas migrate diff --dev-url "docker://postgres/15/dev?search_path=public" --to "file://atlas.hcl" add_users_table
   atlas migrate diff --dev-url "mysql://user:pass@localhost:3306/dev" --to "mysql://user:pass@localhost:3306/dbname"
   atlas migrate diff --env dev --format '{{ sql . "  " }}'
diff --git a/doc/website/sidebars.js b/doc/website/sidebars.js
index b6e07be3..3c98b13c 100644
--- a/doc/website/sidebars.js
+++ b/doc/website/sidebars.js
@@ -311,6 +311,11 @@ module.exports = {
                     id: 'guides/orms/typeorm',
                     label: 'TypeORM'
                 },
+                {
+                    type: 'doc',
+                    id: 'guides/orms/sqlalchemy',
+                    label: 'SQLAlchemy'
+                },
                 {
                     type: 'doc',
                     id: 'guides/frameworks/sqlc-declarative',
diff --git a/sql/internal/sqlx/plan.go b/sql/internal/sqlx/plan.go
index 69cdf483..56f6a2f3 100644
--- a/sql/internal/sqlx/plan.go
+++ b/sql/internal/sqlx/plan.go
@@ -465,13 +465,13 @@ func dependsOn(c1, c2 schema.Change) bool {
 		case *schema.AddSchema:
 			return c1.T.Schema.Name == c2.S.Name
 		case *schema.DropTable:
-			return c1.T.Name == c2.T.Name && c1.T.Schema == c2.T.Schema // Table recreation.
+			return c1.T.Name == c2.T.Name && sameSchema(c1.T.Schema, c2.T.Schema) // Table recreation.
 		case *schema.AddTable:
 			if refTo(c1.T.ForeignKeys, c2.T) {
 				return true
 			}
 		case *schema.ModifyTable:
-			if (c1.T.Name != c2.T.Name || c1.T.Schema != c2.T.Schema) && refTo(c1.T.ForeignKeys, c2.T) {
+			if (c1.T.Name != c2.T.Name || sameSchema(c1.T.Schema, c2.T.Schema)) && refTo(c1.T.ForeignKeys, c2.T) {
 				return true
 			}
 		case *schema.AddObject:
@@ -506,7 +506,7 @@ func dependsOn(c1, c2 schema.Change) bool {
 		switch c2 := c2.(type) {
 		case *schema.AddTable:
 			// Table modification relies on its creation.
-			if c1.T.Name == c2.T.Name && c1.T.Schema == c2.T.Schema {
+			if c1.T.Name == c2.T.Name && sameSchema(c1.T.Schema, c2.T.Schema) {
 				return true
 			}
 			// Tables need to be created before referencing them.
@@ -548,7 +548,7 @@ func dependsOn(c1, c2 schema.Change) bool {
 		case *schema.AddSchema:
 			return c1.V.Schema.Name == c2.S.Name
 		case *schema.DropView:
-			return c1.V.Name == c2.V.Name && c1.V.Schema == c2.V.Schema // View recreation.
+			return c1.V.Name == c2.V.Name && sameSchema(c1.V.Schema, c2.V.Schema) // View recreation.
 		default:
 			return depOfAdd(c1.V.Deps, c2)
 		}
@@ -560,7 +560,7 @@ func dependsOn(c1, c2 schema.Change) bool {
 	case *schema.ModifyView:
 		if c2, ok := c2.(*schema.AddView); ok {
 			// View modification relies on its creation.
-			return c1.From.Name == c2.V.Name && c1.From.Schema == c2.V.Schema
+			return c1.From.Name == c2.V.Name && sameSchema(c1.From.Schema, c2.V.Schema)
 		}
 		return depOfAdd(c1.To.Deps, c2)
 	case *schema.AddFunc:
@@ -568,7 +568,7 @@ func dependsOn(c1, c2 schema.Change) bool {
 		case *schema.AddSchema:
 			return c1.F.Schema.Name == c2.S.Name
 		case *schema.DropFunc:
-			return c1.F.Name == c2.F.Name && c1.F.Schema == c2.F.Schema // Func recreation.
+			return c1.F.Name == c2.F.Name && sameSchema(c1.F.Schema, c2.F.Schema) // Func recreation.
 		default:
 			return depOfAdd(c1.F.Deps, c2)
 		}
@@ -577,7 +577,7 @@ func dependsOn(c1, c2 schema.Change) bool {
 	case *schema.ModifyFunc:
 		if c2, ok := c2.(*schema.AddFunc); ok {
 			// Func modification relies on its creation.
-			return c1.From.Name == c2.F.Name && c1.From.Schema == c2.F.Schema
+			return c1.From.Name == c2.F.Name && sameSchema(c1.From.Schema, c2.F.Schema)
 		}
 		return depOfAdd(c1.To.Deps, c2)
 	case *schema.AddProc:
@@ -585,7 +585,7 @@ func dependsOn(c1, c2 schema.Change) bool {
 		case *schema.AddSchema:
 			return c1.P.Schema.Name == c2.S.Name
 		case *schema.DropProc:
-			return c1.P.Name == c2.P.Name && c1.P.Schema == c2.P.Schema // Proc recreation.
+			return c1.P.Name == c2.P.Name && sameSchema(c1.P.Schema, c2.P.Schema) // Proc recreation.
 		default:
 			return depOfAdd(c1.P.Deps, c2)
 		}
@@ -594,7 +594,7 @@ func dependsOn(c1, c2 schema.Change) bool {
 	case *schema.ModifyProc:
 		if c2, ok := c2.(*schema.AddProc); ok {
 			// Proc modification relies on its creation.
-			return c1.From.Name == c2.P.Name && c1.From.Schema == c2.P.Schema
+			return c1.From.Name == c2.P.Name && sameSchema(c1.From.Schema, c2.P.Schema)
 		}
 		return depOfAdd(c1.To.Deps, c2)
 	case *schema.DropObject:
@@ -730,3 +730,13 @@ func dependsOnT(t1, t2 schema.Type) bool {
 	defer func() { recover() }()
 	return t1 == t2 || schema.UnderlyingType(t1) == t2
 }
+
+// sameSchema reports if the given schemas are the same.
+// Objects can be different as they might reside in two
+// different states (current and desired).
+func sameSchema(s1, s2 *schema.Schema) bool {
+	if s1 == nil || s2 == nil {
+		return s1 == s2
+	}
+	return s1.Name == s2.Name
+}


Test patch:
diff --git a/sql/internal/sqlx/plan_test.go b/sql/internal/sqlx/plan_test.go
index d464d295..571c2894 100644
--- a/sql/internal/sqlx/plan_test.go
+++ b/sql/internal/sqlx/plan_test.go
@@ -209,4 +209,21 @@ func TestSortChanges(t *testing.T) {
 		&schema.DropTable{T: t1},
 	})
 	require.Equal(t, []schema.Change{&schema.DropTable{T: t1}, &schema.DropFunc{F: f1}}, planned)
+
+	// Ignore functions that do not reside on the same schema.
+	f1.Schema = schema.New("s1")
+	f2 := &schema.Func{Name: "f1", Args: []*schema.FuncArg{{Name: "a1"}}}
+	planned = SortChanges([]schema.Change{
+		&schema.AddFunc{F: f1},
+		&schema.DropFunc{F: f2},
+	})
+	require.Equal(t, []schema.Change{&schema.AddFunc{F: f1}, &schema.DropFunc{F: f2}}, planned)
+
+	// Order functions that reside on the same schema.
+	f2.Schema = schema.New("s1")
+	planned = SortChanges([]schema.Change{
+		&schema.AddFunc{F: f1},
+		&schema.DropFunc{F: f2},
+	})
+	require.Equal(t, []schema.Change{&schema.DropFunc{F: f2}, &schema.AddFunc{F: f1}}, planned)
 }