Repository: ariga/atlas (go)

Issue:
Error: specutil: failed converting to *schema.Realm: sqlspec: table not found
Issue: 

We are in process of developing a new database and I am trying out atlas as tool to apply schema changes made on local server over to a deployment postgres server. 

I keep getting this error: 

` Error: specutil: failed converting to *schema.Realm: sqlspec: table "geoid_models" not found `

It has nothing to do with that specific table. If I remove that table from schema, error just moves on to another table

` Error: specutil: failed converting to *schema.Realm: sqlspec: table "vertical_datums" not found `

### inspect command

` atlas schema inspect -u "postgres://postgres:<pass>@localhost:5432/<db>?sslmode=disable" > src/db/schema.hcl`

### apply command
`atlas schema apply -u "postgres://<user>:<pass>@<server>:5432/<db>" -f src/db/schema.hcl`

### schema.hcl
```hcl
table "file_formats" {
  schema = schema.public
  column "id" {
    null = false
    type = serial
  }
  column "code" {
    null = false
    type = character_varying
  }
  column "description" {
    null = false
    type = character_varying
  }
  column "data_type" {
    null = false
    type = character_varying
  }
  primary_key {
    columns = [column.id]
  }
}
table "geoid_models" {
  schema = schema.public
  column "id" {
    null = false
    type = integer
  }
  column "name" {
    null = false
    type = character_varying
  }
  column "sort_order" {
    null = false
    type = smallint
  }
  primary_key {
    columns = [column.id]
  }
}
table "job_statuses" {
  schema = schema.public
  column "id" {
    null = false
    type = serial
  }
  column "status" {
    null = false
    type = character_varying
  }
  primary_key {
    columns = [column.id]
  }
}
table "request_statuses" {
  schema = schema.public
  column "id" {
    null = false
    type = serial
  }
  column "status" {
    null = false
    type = character_varying
  }
  primary_key {
    columns = [column.id]
  }
}
table "spatial_ref_sys" {
  schema = schema.public
  column "srid" {
    null = false
    type = integer
  }
  column "auth_name" {
    null = true
    type = character_varying(256)
  }
  column "auth_srid" {
    null = true
    type = integer
  }
  column "srtext" {
    null = true
    type = character_varying(2048)
  }
  column "proj4text" {
    null = true
    type = character_varying(2048)
  }
  primary_key {
    columns = [column.srid]
  }
  check "spatial_ref_sys_srid_check" {
    expr = "((srid > 0) AND (srid <= 998999))"
  }
}
table "vertical_datums" {
  schema = schema.public
  column "id" {
    null = false
    type = integer
  }
  column "name" {
    null = false
    type = character_varying
  }
  column "native_name" {
    null = false
    type = character_varying
  }
  primary_key {
    columns = [column.id]
  }
}
table "image_fp" {
  schema = schema.spatial
  column "objectid" {
    null = false
    type = serial
  }
  column "geom" {
    null = true
    type = sql("geometry(geometry,4269)")
  }
  column "location" {
    null = true
    type = character_varying(254)
  }
  column "url" {
    null = true
    type = character_varying(254)
  }
  column "missionid" {
    null = true
    type = integer
  }
  column "azureblobname" {
    null = true
    type = character_varying(1000)
  }
  primary_key {
    columns = [column.objectid]
  }
  index "image_fp_geom_geom_idx" {
    columns = [column.geom]
    type    = GIST
  }
}
table "dem_jobs" {
  schema = schema.transactional
  column "id" {
    null = false
    type = bigserial
  }
  column "job_id" {
    null = false
    type = bigint
  }
  column "vertical_datum_id" {
    null = false
    type = integer
  }
  column "contour_interval" {
    null = true
    type = real
  }
  column "geoid_model_id" {
    null = true
    type = integer
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "geoid_models_fkey" {
    columns     = [column.geoid_model_id]
    ref_columns = [table.geoid_models.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
  foreign_key "jobs_fkey" {
    columns     = [column.job_id]
    ref_columns = [table.jobs.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
  foreign_key "vertical_datums_fkey" {
    columns     = [column.vertical_datum_id]
    ref_columns = [table.vertical_datums.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
}
table "imagery_jobs" {
  schema = schema.transactional
  column "id" {
    null = false
    type = bigserial
  }
  column "job_id" {
    null = false
    type = bigint
  }
  column "jpeg_quality" {
    null    = false
    type    = smallint
    default = 80
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "jobs_fkey" {
    columns     = [column.job_id]
    ref_columns = [table.jobs.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
}
table "job_outputs" {
  schema = schema.transactional
  column "id" {
    null = false
    type = bigserial
  }
  column "job_id" {
    null = false
    type = bigint
  }
  column "file_path" {
    null = true
    type = character_varying
  }
  column "cleanup_status" {
    null = true
    type = character_varying
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "jobs_fkey" {
    columns     = [column.job_id]
    ref_columns = [table.jobs.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
  index "job_id_unique" {
    unique  = true
    columns = [column.job_id]
    type    = BTREE
  }
}
table "jobs" {
  schema = schema.transactional
  column "id" {
    null = false
    type = bigserial
  }
  column "request_id" {
    null = true
    type = bigint
  }
  column "data_type" {
    null = false
    type = character_varying
  }
  column "progress" {
    null = true
    type = character_varying
  }
  column "processing_started_at" {
    null = true
    type = time
  }
  column "processing_finished_at" {
    null = true
    type = time
  }
  column "job_status_id" {
    null = false
    type = integer
  }
  column "created_at" {
    null = false
    type = time
  }
  column "updated_at" {
    null = false
    type = time
  }
  column "geom" {
    null = true
    type = polygon
  }
  column "horizontal_datum_id" {
    null = false
    type = integer
  }
  column "projection_id" {
    null = false
    type = integer
  }
  column "mission_id" {
    null = false
    type = integer
  }
  column "file_format_id" {
    null = false
    type = integer
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "file_formats_fkey" {
    columns     = [column.file_format_id]
    ref_columns = [table.file_formats.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
  foreign_key "job_statuses_fkey" {
    columns     = [column.job_status_id]
    ref_columns = [table.job_statuses.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
  foreign_key "requests_fkey" {
    columns     = [column.request_id]
    ref_columns = [table.requests.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
}
table "landcover_jobs" {
  schema = schema.transactional
  column "id" {
    null = false
    type = bigserial
  }
  column "job_id" {
    null = false
    type = bigint
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "jobs_fkey" {
    columns     = [column.job_id]
    ref_columns = [table.jobs.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
}
table "lidar_jobs" {
  schema = schema.transactional
  column "id" {
    null = false
    type = bigserial
  }
  column "job_id" {
    null = false
    type = bigint
  }
  column "bin_unit" {
    null = true
    type = sql("linear_unit")
  }
  column "bin_size" {
    null = true
    type = real
  }
  column "fill_gaps" {
    null = true
    type = boolean
  }
  column "contour_interval" {
    null = true
    type = real
  }
  column "create_single_contour" {
    null = true
    type = boolean
  }
  column "ancillary_data" {
    null = true
    type = sql("channels")
  }
  column "data_returns" {
    null = true
    type = sql("lidar_returns")
  }
  column "vertical_unit" {
    null = false
    type = sql("linear_unit")
  }
  column "classes" {
    null = false
    type = sql("smallint[]")
  }
  column "point_size_estimate" {
    null = true
    type = bigint
  }
  column "geoid_model" {
    null = false
    type = sql("geoid")
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "jobs_fkey" {
    columns     = [column.job_id]
    ref_columns = [table.jobs.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
}
table "requests" {
  schema = schema.transactional
  column "id" {
    null = false
    type = bigserial
  }
  column "confirmation_sent" {
    null    = true
    type    = boolean
    default = false
  }
  column "created_at" {
    null = false
    type = timestamp
  }
  column "updated_at" {
    null = false
    type = timestamp
  }
  column "user_id" {
    null = false
    type = bigint
  }
  column "request_status_id" {
    null = false
    type = integer
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "request_status_fk" {
    columns     = [column.request_status_id]
    ref_columns = [table.request_statuses.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
  foreign_key "users_fk" {
    columns     = [column.user_id]
    ref_columns = [table.users.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
}
table "users" {
  schema = schema.transactional
  column "id" {
    null = false
    type = bigserial
  }
  column "email" {
    null = false
    type = character_varying
  }
  column "organization" {
    null = true
    type = character_varying
  }
  column "survey_optin" {
    null    = true
    type    = boolean
    default = false
  }
  primary_key {
    columns = [column.id]
  }
}
schema "public" {
}
schema "spatial" {
}
schema "transactional" {
}
```

Pull request:
sql: support cross-schema references in HCL
Resolves #1046 

Patch:
diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index 568dbc8a..5779ba7a 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -137,7 +137,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for mysql56
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="MySQL" -dialect="mysql56" ./...
+        run: go test -race -count=2 -v -run="MySQL" -version="mysql56" ./...
   
   integration-mysql57:
     runs-on: ubuntu-latest
@@ -168,7 +168,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for mysql57
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="MySQL" -dialect="mysql57" ./...
+        run: go test -race -count=2 -v -run="MySQL" -version="mysql57" ./...
   
   integration-mysql8:
     runs-on: ubuntu-latest
@@ -199,7 +199,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for mysql8
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="MySQL" -dialect="mysql8" ./...
+        run: go test -race -count=2 -v -run="MySQL" -version="mysql8" ./...
   
   integration-maria107:
     runs-on: ubuntu-latest
@@ -230,7 +230,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for maria107
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="MySQL" -dialect="maria107" ./...
+        run: go test -race -count=2 -v -run="MySQL" -version="maria107" ./...
   
   integration-maria102:
     runs-on: ubuntu-latest
@@ -261,7 +261,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for maria102
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="MySQL" -dialect="maria102" ./...
+        run: go test -race -count=2 -v -run="MySQL" -version="maria102" ./...
   
   integration-maria103:
     runs-on: ubuntu-latest
@@ -292,7 +292,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for maria103
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="MySQL" -dialect="maria103" ./...
+        run: go test -race -count=2 -v -run="MySQL" -version="maria103" ./...
   
   integration-postgres10:
     runs-on: ubuntu-latest
@@ -322,7 +322,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for postgres10
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="Postgres" -dialect="postgres10" ./...
+        run: go test -race -count=2 -v -run="Postgres" -version="postgres10" ./...
   
   integration-postgres11:
     runs-on: ubuntu-latest
@@ -352,7 +352,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for postgres11
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="Postgres" -dialect="postgres11" ./...
+        run: go test -race -count=2 -v -run="Postgres" -version="postgres11" ./...
   
   integration-postgres12:
     runs-on: ubuntu-latest
@@ -382,7 +382,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for postgres12
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="Postgres" -dialect="postgres12" ./...
+        run: go test -race -count=2 -v -run="Postgres" -version="postgres12" ./...
   
   integration-postgres13:
     runs-on: ubuntu-latest
@@ -412,7 +412,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for postgres13
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="Postgres" -dialect="postgres13" ./...
+        run: go test -race -count=2 -v -run="Postgres" -version="postgres13" ./...
   
   integration-postgres14:
     runs-on: ubuntu-latest
@@ -442,7 +442,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for postgres14
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="Postgres" -dialect="postgres14" ./...
+        run: go test -race -count=2 -v -run="Postgres" -version="postgres14" ./...
   
   integration-tidb5:
     runs-on: ubuntu-latest
@@ -466,7 +466,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for tidb5
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="TiDB" -dialect="tidb5" ./...
+        run: go test -race -count=2 -v -run="TiDB" -version="tidb5" ./...
   
   integration-tidb6:
     runs-on: ubuntu-latest
@@ -490,7 +490,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for tidb6
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="TiDB" -dialect="tidb6" ./...
+        run: go test -race -count=2 -v -run="TiDB" -version="tidb6" ./...
   
   integration-sqlite:
     runs-on: ubuntu-latest
@@ -508,7 +508,7 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for sqlite
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="SQLite.*" -dialect="sqlite" ./...
+        run: go test -race -count=2 -v -run="SQLite.*" -version="sqlite" ./...
   
   integration-cockroach:
     runs-on: ubuntu-latest
@@ -532,5 +532,5 @@ jobs:
             ${{ runner.os }}-go-
       - name: Run integration tests for cockroach
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="Cockroach" -dialect="cockroach" ./...
+        run: go test -race -count=2 -v -run="Cockroach" -version="cockroach" ./...
   
\ No newline at end of file
diff --git a/cmd/atlas/internal/cmdapi/migrate.go b/cmd/atlas/internal/cmdapi/migrate.go
index 4403d37c..9df1303e 100644
--- a/cmd/atlas/internal/cmdapi/migrate.go
+++ b/cmd/atlas/internal/cmdapi/migrate.go
@@ -552,10 +552,8 @@ func statusPrint(out io.Writer, avail, pending []migrate.File, revs migrate.Revi
 	switch len(pending) {
 	case len(avail):
 		cur = "No version applied yet"
-	case 0:
-		cur = cyan(avail[len(avail)-1].Version())
 	default:
-		cur = cyan(avail[len(avail)-len(pending)].Version())
+		cur = cyan(applied[len(applied)-1].Version())
 		// If the last pending version is partially applied, tell so.
 		if partial {
 			cur += fmt.Sprintf(" (%d statements applied)", revs[len(revs)-1].Applied)
@@ -575,16 +573,15 @@ func statusPrint(out io.Writer, avail, pending []migrate.File, revs migrate.Revi
 	if partial {
 		exec += " + 1 partially"
 	}
-	fmt.Fprintf(out, "Migration Status: %s\n", state)
-	fmt.Fprintf(out, "%s%s Current Version:\t%s\n", indent2, dash, cur)
-	fmt.Fprintf(out, "%s%s Next Version:\t%s\n", indent2, dash, next)
-	// fmt.Fprintf(out, "%s%s Available Files:\t%s\n", indent2, dash, cyan(strconv.Itoa(len(avail))))
-	fmt.Fprintf(out, "%s%s Executed Files:\t%s\n", indent2, dash, exec)
 	c := cyan
 	if len(pending) == 0 {
 		c = green
 	}
-	fmt.Fprintf(out, "%s%s Pending Files:\t%s", indent2, dash, c(strconv.Itoa(len(pending))))
+	fmt.Fprintf(out, "Migration Status: %s\n", state)
+	fmt.Fprintf(out, "%s%s Current Version:  %s\n", indent2, dash, cur)
+	fmt.Fprintf(out, "%s%s Next Version:     %s\n", indent2, dash, next)
+	fmt.Fprintf(out, "%s%s Executed Files:   %s\n", indent2, dash, exec)
+	fmt.Fprintf(out, "%s%s Pending Files:    %s", indent2, dash, c(strconv.Itoa(len(pending))))
 	if partial {
 		fmt.Fprintf(out, " (partially)")
 	}
diff --git a/doc/md/versioned/diff.md b/doc/md/versioned/diff.md
deleted file mode 100644
index a6a82e2c..00000000
--- a/doc/md/versioned/diff.md
+++ /dev/null
@@ -1,36 +0,0 @@
----
-id: diff
-slug: /versioned/diff
-title: Planning new migrations
----
-With the `atlas migrate` commands users can implement a kind fo workflow that we
-call _versioned migration authoring_. This kind of workflow is a synthesis between _declarative_ workflows,
-where developers specify the desired state of their database, and _versioned migrations_
-where each change is explicitly defined as a migration script with a specific version.
-
-Practically speaking, this means that the developer maintains the [schema definition](/atlas-schema/sql-resources),
-e.g the _desired state_, and Atlas maintains the `migrations/` directory, which contains the
-explicit SQL scripts to move from one version to the next.
-
-In addition, Atlas maintains a file name `atlas.sum` which is used to ensure the integrity of
-the migration directory and force developers to deal with situations where migration order or
-contents was modified after the fact.
-
-[Learn more about versioned migration authoring](/concepts/declarative-vs-versioned#migration-authoring)
-
-### Flags
-When using `migrate diff` to plan a migration users must supply multiple parameters:
-* `--dev-url` a [URL](/concepts/url) to a [Dev-database](/concepts/dev-database) that will be used
- to compute the diff.
-* `--dir` the URL of the migration directory, by default it is `file://migrations`, e.g a
- directory named `migrations` in the current working directory.
-* `--to` the URL of the desired state, can be an HCL file or another database.
-
-### Example
-```
-atlas migrate diff --dir file://my/project/migrations --to schema.hcl --dev-url mysql://root:pass@localhost:3306/dev
-```
-
-### Reference
-
-[CLI Command Reference](/cli-reference#atlas-migrate-diff)
\ No newline at end of file
diff --git a/doc/md/versioned/diff.mdx b/doc/md/versioned/diff.mdx
new file mode 100644
index 00000000..81171c64
--- /dev/null
+++ b/doc/md/versioned/diff.mdx
@@ -0,0 +1,360 @@
+---
+id: diff
+slug: /versioned/diff
+title: Automatic Migration Authoring
+---
+
+import Tabs from '@theme/Tabs';
+import TabItem from '@theme/TabItem';
+
+With the `atlas migrate diff` command users can implement a kind of workflow that we
+call _versioned migration authoring_. This workflow is a synthesis between _declarative_ workflows,
+where developers specify the desired state of their database, and _versioned migrations_
+where each change is explicitly defined as a migration script with a specific version.
+
+Practically speaking, this means developers define the desired state with either an HCL
+[schema definition](/atlas-schema/sql-resources) or a database connection, and Atlas maintains
+the `migrations` directory, which contains the explicit SQL scripts to move from one version to
+the next.
+
+### Generate migrations from Atlas schemas
+
+Suppose we have an Atlas schema with one table and an empty migration directory:
+
+```hcl title="schema.hcl"
+schema "test" {}
+
+table "users" {
+  schema = schema.test
+  column "id" {
+    type = int
+  }
+}
+```
+
+Let's run `atlas migrate diff` with the necessary parameters to generate a migration script for
+creating our `users` table:
+
+* `--dir` the URL to the migration directory, by default it is `file://migrations`.
+* `--to` the URL of the desired state, an HCL file or a database connection.
+* `--dev-url` a [URL](/concepts/url) to a [Dev Database](/concepts/dev-database) that will be used to compute the diff.
+
+```bash
+atlas migrate diff --dir="file://migrations" --to="file://schema.hcl" \
+ --dev-url="mysql://root:pass@:3306/test" create_users
+```
+
+Run `ls migrations`, and you will notice Atlas created 2 files:
+
+<Tabs
+defaultValue="migration_file"
+values={[
+{label: '20220811074144_create_users.sql', value: 'migration_file'},
+{label: 'atlas.sum', value: 'sum_file'},
+]}>
+<TabItem value="migration_file">
+
+By default, migration files are named with the following format `{{ now }}_{{ name }}.sql`.
+If you wish to use a different file format, use the `--dir-format` option.
+
+```sql
+-- create "users" table
+CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
+```
+
+</TabItem>
+<TabItem value="sum_file">
+
+In addition to the migration directory, Atlas maintains a file name `atlas.sum` which is used
+to ensure the integrity of the migration directory and force developers to deal with situations
+where migration order or contents was modified after the fact.
+
+```text
+h1:t1fEP1rSsGf1gYrYCjsGyEyuM0cnhATlq93B7h8uXxY=
+20220811074144_create_users.sql h1:liZcCBbAn/HyBTqBAEVar9fJNKPTb2Eq+rEKZeCFC9M=
+```
+
+</TabItem>
+</Tabs>
+
+Let's repeat the process above by changing our HCL schema file and running Atlas migration authoring again.
+We add a new column `name` to our HCL schema:
+
+```hcl title="schema.hcl" {8-10}
+schema "test" {}
+
+table "users" {
+  schema = schema.test
+  column "id" {
+    type = int
+  }
+  column "name" {
+    type = varchar(255)
+  }
+}
+```
+
+Then, run `atlas migrate diff`:
+
+```bash
+atlas migrate diff --dir="file://migrations" --to="file://schema.hcl" \
+ --dev-url="mysql://root:pass@:3306/test" add_users_name
+```
+
+You will notice Atlas added a new file to the migration directory:
+
+<Tabs
+defaultValue="migration_file2"
+values={[
+{label: '20220811074144_create_users.sql', value: 'migration_file1'},
+{label: '20220811074314_add_users_name.sql', value: 'migration_file2'},
+{label: 'atlas.sum', value: 'sum_file'},
+]}>
+<TabItem value="migration_file1">
+
+```sql
+-- create "users" table
+CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
+```
+
+</TabItem>
+<TabItem value="migration_file2">
+
+```sql
+-- modify "users" table
+ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL;
+```
+
+</TabItem>
+<TabItem value="sum_file">
+
+```text
+h1:w2ODzVxhTKdBVBdzqntHw7rHV8lKQF98TmNevOEZfIo=
+20220811074144_create_users_table.sql h1:KnMSZM/E4TBGidYCZ+UHxkHEWaRWeyuPIUjSHRybQqA=
+20220811074314_add_users_name.sql h1:jUpaANgD0SjI5DjaHuJxtHZ6Wq98act0MmE5oZ+NRU0=
+```
+
+</TabItem>
+</Tabs>
+
+The following diagram explains how it works. Atlas loads the **current state**
+by replaying the migration directory onto the provided [dev database](/concepts/dev-database),
+compares it against the **desired state** and writes a new migration script for moving from the current
+to the desired state.
+
+![Diff From Schema](https://atlasgo.io/uploads/images/versioned-migration/diff-from-schema.png)
+
+### Generate migrations from database schemas
+
+Suppose we have a database with a `users` table that was created manually or by an ORM like [Ent](https://entgo.io),
+we can tell Atlas that this is our **desired** state, and we want to generate a migration script to create this table.
+
+```text
+mysql> describe users;
+
++-------+------+------+-----+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+------+------+-----+---------+-------+
+| id    | int  | YES  |     | NULL    |       |
++-------+------+------+-----+---------+-------+
+```
+
+Let's run `atlas migrate diff` with the necessary parameters to generate a migration script for
+creating our `users` table:
+
+```bash
+atlas migrate diff --dir="file://migrations" --to="mysql://root:pass@:3306/public" \
+ --dev-url="mysql://root:pass@:3306/test" create_users
+```
+
+Run `ls migrations`, and you will notice Atlas created 2 files:
+
+<Tabs
+defaultValue="migration_file"
+values={[
+{label: '20220811102532_create_users.sql', value: 'migration_file'},
+{label: 'atlas.sum', value: 'sum_file'},
+]}>
+<TabItem value="migration_file">
+
+By default, migration files are named with the following format `{{ now }}_{{ name }}.sql`.
+If you wish to use a different file format, use the `--dir-format` option.
+
+```sql
+-- create "users" table
+CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
+```
+
+</TabItem>
+<TabItem value="sum_file">
+
+In addition to the migration directory, Atlas maintains a file name `atlas.sum` which is used
+to ensure the integrity of the migration directory and force developers to deal with situations
+where migration order or contents was modified after the fact.
+
+```text
+h1:/6UW2WaPV1laJpEB7oVesKB9RuL59dgDhlTp5atDbNg=
+20220811102532_create_users.sql h1:IJiVpbh3FLMeDJSzxxKPuFU3m4AHBgThBfs3VFXAXVo=
+```
+
+</TabItem>
+</Tabs>
+
+### Generate migrations with custom qualifiers
+
+When working on a specific database schema, Atlas generates migration scripts without schema qualifiers
+to allow executing them multiple times on different schemas. However, in some cases, it is necessary
+to have those qualifiers. To address this, Atlas allows passing another flag to `migrate diff` named `--qualifier`.
+
+Let's run the [example above](#generate-migration-from-database-schema), with the `--qualifier` flag and compare
+the output:
+
+```bash
+atlas migrate diff --dir="file://migrations" --to="file://schema.hcl" \
+ --dev-url="mysql://root:pass@:3306/test" --qualifier="market" create_users
+```
+
+Running `cat migrations/*.sql` will print the same migration script but the `users` table will be qualified with
+the `market` schema:
+
+```sql
+-- create "users" table
+CREATE TABLE `market`.`users` (`id` int NOT NULL, `name` varchar(255) NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
+```
+
+### Generate migrations with custom formats
+
+Some migration tools use a different file format than the one used by Atlas. Therefore, Atlas provides
+an option named `--dir-format` that allows controlling the format of the migration directory.
+
+```bash
+atlas migrate diff --dir="file://migrations" --to="file://schema.hcl" \
+ --dev-url="mysql://root:pass@:3306/test" --dir-format="golang-migrate" create_users
+```
+
+Run `ls migrations`, and you will notice Atlas created 3 files:
+
+<Tabs
+defaultValue="migration_file_up"
+values={[
+{label: '20220811114629_create_users.up.sql', value: 'migration_file_up'},
+{label: '20220811114629_create_users.down.sql', value: 'migration_file_down'},
+{label: 'atlas.sum', value: 'sum_file'},
+]}>
+<TabItem value="migration_file_up">
+
+```sql
+-- create "users" table
+CREATE TABLE `users` (`name` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
+```
+
+</TabItem>
+<TabItem value="migration_file_down">
+
+```sql
+-- reverse: create "users" table
+DROP TABLE `users`;
+```
+
+</TabItem>
+<TabItem value="sum_file">
+
+In addition to the migration directory, Atlas maintains a file name `atlas.sum` which is used
+to ensure the integrity of the migration directory and force developers to deal with situations
+where migration order or contents was modified after the fact.
+
+```text
+h1:NRHsNIjvSSzprr/EzMdtszQg3t3pVLk4G4N1tX4rMfk=
+20220811114629_create_users.up.sql h1:Ng3GHrdk2davokjOctgVdxC+6QsK4JzaLX6RT3QstJc=
+```
+
+</TabItem>
+</Tabs>
+
+### Generate migrations for the entire database
+
+Atlas supports generating migrations for databases or multiple schemas. In PostgreSQL, a database can
+be created with the `CREATE DATABASE` command and can hold multiple schemas. In MySQL however, a database
+is an instance with one or more schemas.
+
+Suppose we have an Atlas schema that defines two database schemas where each one contains a single table.
+
+```hcl title="schema.hcl"
+schema "auth" {}
+schema "market" {}
+
+table "users" {
+  schema = schema.market
+  column "name" {
+    type = int
+  }
+}
+
+table "tokens" {
+  schema = schema.auth
+  column "value" {
+    type = int
+  }
+}
+```
+
+Let's run `atlas migrate diff` to generate migration scripts for creating the entire schema.
+However, unlike the previous examples where the `--dev-url` flag was set to a [URL](concepts/url.mdx)
+of a specific schema, in this case we omit the schema name from the connection string.
+
+<Tabs
+defaultValue="mysql"
+values={[
+{label: 'MySQL', value: 'mysql'},
+{label: 'PostgreSQL', value: 'postgres'},
+]}>
+<TabItem value="mysql">
+
+```bash
+atlas migrate diff --dir="file://migrations" --to="file://schema.hcl" \
+ --dev-url="mysql://root:pass@:3306/" create_all
+```
+
+Running `cat migrations/*.sql` will print the followings:
+
+```sql
+-- add new schema named "auth"
+CREATE DATABASE `auth`;
+-- add new schema named "market"
+CREATE DATABASE `market`;
+-- create "tokens" table
+CREATE TABLE `auth`.`tokens` (`value` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
+-- create "users" table
+CREATE TABLE `market`.`users` (`name` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
+```
+
+</TabItem>
+<TabItem value="postgres">
+
+```bash
+atlas migrate diff --dir="file://migrations" --to="file://schema.hcl" \
+ --dev-url="postgres://root:pass@:5434/database?sslmode=disable" create_all
+```
+
+Running `cat migrations/*.sql` will print the followings:
+
+```sql
+-- Add new schema named "auth"
+CREATE SCHEMA "auth";
+-- Add new schema named "market"
+CREATE SCHEMA "market";
+-- create "tokens" table
+CREATE TABLE "auth"."tokens" ("value" integer NOT NULL);
+-- create "users" table
+CREATE TABLE "market"."users" ("name" integer NOT NULL);
+```
+
+</TabItem>
+</Tabs>
+
+As you can see, Atlas generates statements for creating the `auth` and `market` schemas,
+and added them as qualifiers in the created tables.
+
+### Reference
+
+[CLI Command Reference](/cli-reference#atlas-migrate-diff)
\ No newline at end of file
diff --git a/doc/website/sidebars.js b/doc/website/sidebars.js
index 313bf330..9aaacc8f 100644
--- a/doc/website/sidebars.js
+++ b/doc/website/sidebars.js
@@ -36,7 +36,7 @@ module.exports = {
             label: "Versioned Workflows",
             collapsed: false,
             items: [
-                {type: 'doc', id: 'versioned/diff', label: 'Migration Planning'},
+                {type: 'doc', id: 'versioned/diff', label: 'Migration Authoring'},
                 {type: 'doc', id: 'versioned/lint', label: 'Migration Linting'},
                 {type: 'doc', id: 'versioned/new', label: 'Manual Migrations'},
             ]
diff --git a/internal/ci/ci.tmpl b/internal/ci/ci.tmpl
index ed7d6a84..b91a123b 100644
--- a/internal/ci/ci.tmpl
+++ b/internal/ci/ci.tmpl
@@ -135,5 +135,5 @@ jobs:
             {{ "${{ runner.os }}-go-" }}
       - name: Run integration tests for {{ .Version }}
         working-directory: internal/integration
-        run: go test -race -count=2 -v -run="{{ .Regex }}" -dialect="{{ .Version }}" ./...
+        run: go test -race -count=2 -v -run="{{ .Regex }}" -version="{{ .Version }}" ./...
   {{ end }}
\ No newline at end of file
diff --git a/sql/internal/specutil/convert.go b/sql/internal/specutil/convert.go
index 9543802a..46d19206 100644
--- a/sql/internal/specutil/convert.go
+++ b/sql/internal/specutil/convert.go
@@ -506,7 +506,11 @@ func FromForeignKey(s *schema.ForeignKey) (*sqlspec.ForeignKey, error) {
 	for _, v := range s.RefColumns {
 		ref := ColumnRef(v.Name)
 		if s.Table != s.RefTable {
-			ref = externalColRef(v.Name, s.RefTable.Name)
+			if s.Table.Schema == s.RefTable.Schema {
+				ref = externalColRef(v.Name, s.RefTable.Name)
+			} else {
+				ref = crossSchemaColRef(v.Name, s.RefTable.Name, s.RefTable.Schema.Name)
+			}
 		}
 		r = append(r, ref)
 	}
@@ -631,6 +635,10 @@ func externalColRef(cName string, tName string) *schemahcl.Ref {
 	return &schemahcl.Ref{V: "$table." + tName + ".$column." + cName}
 }
 
+func crossSchemaColRef(cName, tName, sName string) *schemahcl.Ref {
+	return &schemahcl.Ref{V: "$table." + sName + "." + tName + ".$column." + cName}
+}
+
 // SchemaRef returns the schemahcl.Ref to the schema with the given name.
 func SchemaRef(n string) *schemahcl.Ref {
 	return &schemahcl.Ref{V: "$schema." + n}
diff --git a/sql/internal/specutil/spec.go b/sql/internal/specutil/spec.go
index 35734225..86ef797e 100644
--- a/sql/internal/specutil/spec.go
+++ b/sql/internal/specutil/spec.go
@@ -113,6 +113,9 @@ func Marshal(v interface{}, marshaler schemahcl.Marshaler, schemaSpec func(schem
 			d.Tables = append(d.Tables, tables...)
 			d.Schemas = append(d.Schemas, spec)
 		}
+		if err := QualifyReferencedTables(d.Tables, s); err != nil {
+			return nil, err
+		}
 	default:
 		return nil, fmt.Errorf("specutil: failed marshaling spec. %T is not supported", v)
 	}
@@ -144,6 +147,31 @@ func QualifyDuplicates(tableSpecs []*sqlspec.Table) error {
 	return nil
 }
 
+// QualifyReferencedTables sets the Qualified field equal to the schema name in any tables
+// that are references across schema boundaries from any foreign key.
+func QualifyReferencedTables(tableSpecs []*sqlspec.Table, realm *schema.Realm) error {
+	// Iterate over all tables in the realm. If any of the tables has a foreign key referencing a table in another
+	// schema, the targeted table needs to have a qualifier set.
+	for _, s := range realm.Schemas {
+		for _, t := range s.Tables {
+			for _, f := range t.ForeignKeys {
+				if f.Table.Schema != f.RefTable.Schema {
+					for _, tbl := range tableSpecs {
+						n, err := SchemaName(tbl.Schema)
+						if err != nil {
+							return err
+						}
+						if f.RefTable.Name == tbl.Name && f.RefTable.Schema.Name == n && tbl.Qualifier == "" {
+							tbl.Qualifier = n
+						}
+					}
+				}
+			}
+		}
+	}
+	return nil
+}
+
 // HCLBytesFunc returns a helper that evaluates an HCL document from a byte slice instead
 // of from an hclparse.Parser instance.
 func HCLBytesFunc(ev schemahcl.Evaluator) func(b []byte, v interface{}, inp map[string]string) error {
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index 5305145b..71487918 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -94,6 +94,9 @@ func MarshalSpec(v interface{}, marshaler schemahcl.Marshaler) ([]byte, error) {
 			d.Schemas = append(d.Schemas, doc.Schemas...)
 			d.Enums = append(d.Enums, doc.Enums...)
 		}
+		if err := specutil.QualifyReferencedTables(d.Tables, s); err != nil {
+			return nil, err
+		}
 	default:
 		return nil, fmt.Errorf("specutil: failed marshaling spec. %T is not supported", v)
 	}


Test patch:
diff --git a/internal/integration/cockroach_test.go b/internal/integration/cockroach_test.go
index b0b6b5f4..c6140089 100644
--- a/internal/integration/cockroach_test.go
+++ b/internal/integration/cockroach_test.go
@@ -40,7 +40,7 @@ var crdbTests = map[string]*crdbTest{
 
 func crdbRun(t *testing.T, fn func(*crdbTest)) {
 	for version, tt := range crdbTests {
-		if flagDialect == "" || flagDialect == version {
+		if flagVersion == "" || flagVersion == version {
 			t.Run(version, func(t *testing.T) {
 				tt.once.Do(func() {
 					var err error
diff --git a/internal/integration/integration_test.go b/internal/integration/integration_test.go
index dca524a7..6e32d73a 100644
--- a/internal/integration/integration_test.go
+++ b/internal/integration/integration_test.go
@@ -33,11 +33,11 @@ import (
 
 var (
 	dbs         []io.Closer
-	flagDialect string
+	flagVersion string
 )
 
 func TestMain(m *testing.M) {
-	flag.StringVar(&flagDialect, "dialect", "", "[mysql56, postgres10, tidb5, ...] what dialect (version) to test")
+	flag.StringVar(&flagVersion, "version", "", "[mysql56, postgres10, tidb5, ...] what version to test")
 	flag.Parse()
 	code := m.Run()
 	for _, db := range dbs {
diff --git a/internal/integration/mysql_test.go b/internal/integration/mysql_test.go
index 5a0ad37a..f73d8630 100644
--- a/internal/integration/mysql_test.go
+++ b/internal/integration/mysql_test.go
@@ -42,7 +42,7 @@ var myTests = map[string]*myTest{
 
 func myRun(t *testing.T, fn func(*myTest)) {
 	for version, tt := range myTests {
-		if flagDialect == "" || flagDialect == version {
+		if flagVersion == "" || flagVersion == version {
 			t.Run(version, func(t *testing.T) {
 				tt.once.Do(func() {
 					var err error
@@ -718,6 +718,67 @@ schema "second" {
 	})
 }
 
+func TestMySQL_HCL_ForeignKeyCrossSchema(t *testing.T) {
+	const expected = `table "credit_cards" {
+  schema = schema.financial
+  column "id" {
+    null = false
+    type = int
+  }
+  column "user_id" {
+    null = false
+    type = int
+  }
+  primary_key {
+    columns = [column.id]
+  }
+  foreign_key "user_id_fkey" {
+    columns     = [column.user_id]
+    ref_columns = [table.users.users.column.id]
+    on_update   = NO_ACTION
+    on_delete   = NO_ACTION
+  }
+  index "user_id_fkey" {
+    columns = [column.user_id]
+  }
+}
+table "users" "users" {
+  schema = schema.users
+  column "id" {
+    null = false
+    type = int
+  }
+  column "email" {
+    null = false
+    type = varchar(255)
+  }
+  primary_key {
+    columns = [column.id]
+  }
+}
+schema "financial" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_general_ci"
+}
+schema "users" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_general_ci"
+}
+`
+	myRun(t, func(t *myTest) {
+		t.dropSchemas("financial", "users")
+		realm := t.loadRealm()
+		hcl, err := mysql.MarshalHCL(realm)
+		require.NoError(t, err)
+		t.applyRealmHcl(string(hcl) + "\n" + expected)
+		realm, err = t.drv.InspectRealm(context.Background(), &schema.InspectRealmOption{Schemas: []string{"users", "financial"}})
+		require.NoError(t, err)
+		actual, err := mysql.MarshalHCL(realm)
+		require.NoError(t, err)
+		require.Equal(t, expected, string(actual))
+	})
+}
+
 func TestMySQL_DefaultsHCL(t *testing.T) {
 	n := "atlas_defaults"
 	myRun(t, func(t *myTest) {
diff --git a/internal/integration/postgres_test.go b/internal/integration/postgres_test.go
index 6c3ed7a1..34c4a0ac 100644
--- a/internal/integration/postgres_test.go
+++ b/internal/integration/postgres_test.go
@@ -20,7 +20,6 @@ import (
 	"ariga.io/atlas/sql/postgres"
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlclient"
-
 	"entgo.io/ent/dialect"
 	_ "github.com/lib/pq"
 	"github.com/stretchr/testify/require"
@@ -46,7 +45,7 @@ var pgTests = map[string]*pgTest{
 
 func pgRun(t *testing.T, fn func(*pgTest)) {
 	for version, tt := range pgTests {
-		if flagDialect == "" || flagDialect == version {
+		if flagVersion == "" || flagVersion == version {
 			t.Run(version, func(t *testing.T) {
 				tt.once.Do(func() {
 					var err error
@@ -537,6 +536,60 @@ schema "second" {
 	})
 }
 
+func TestPostgres_HCL_ForeignKeyCrossSchema(t *testing.T) {
+	const expected = `table "credit_cards" {
+  schema = schema.financial
+  column "id" {
+    null = false
+    type = serial
+  }
+  column "user_id" {
+    null = false
+    type = integer
+  }
+  primary_key {
+    columns = [column.id]
+  }
+  foreign_key "user_id_fkey" {
+    columns     = [column.user_id]
+    ref_columns = [table.users.users.column.id]
+    on_update   = NO_ACTION
+    on_delete   = NO_ACTION
+  }
+}
+table "users" "users" {
+  schema = schema.users
+  column "id" {
+    null = false
+    type = bigserial
+  }
+  column "email" {
+    null = false
+    type = character_varying
+  }
+  primary_key {
+    columns = [column.id]
+  }
+}
+schema "financial" {
+}
+schema "users" {
+}
+`
+	pgRun(t, func(t *pgTest) {
+		t.dropSchemas("financial", "users")
+		realm := t.loadRealm()
+		hcl, err := postgres.MarshalHCL(realm)
+		require.NoError(t, err)
+		t.applyRealmHcl(string(hcl) + "\n" + expected)
+		realm, err = t.drv.InspectRealm(context.Background(), &schema.InspectRealmOption{Schemas: []string{"users", "financial"}})
+		require.NoError(t, err)
+		actual, err := postgres.MarshalHCL(realm)
+		require.NoError(t, err)
+		require.Equal(t, expected, string(actual))
+	})
+}
+
 func (t *pgTest) applyRealmHcl(spec string) {
 	realm := t.loadRealm()
 	var desired schema.Realm
diff --git a/internal/integration/testdata/postgres/cli-migrate-apply.txt b/internal/integration/testdata/postgres/cli-migrate-apply.txt
index d699d1a9..66452cd6 100644
--- a/internal/integration/testdata/postgres/cli-migrate-apply.txt
+++ b/internal/integration/testdata/postgres/cli-migrate-apply.txt
@@ -1,4 +1,4 @@
-only postgres12 postgres14
+only postgres14
 
 ! atlas migrate apply
 stderr 'Error: checksum file not found'
diff --git a/internal/integration/testdata/postgres/cli-migrate-status.txt b/internal/integration/testdata/postgres/cli-migrate-status.txt
new file mode 100644
index 00000000..9a94d434
--- /dev/null
+++ b/internal/integration/testdata/postgres/cli-migrate-status.txt
@@ -0,0 +1,51 @@
+only postgres12
+
+# make sure sum file is correct
+atlas migrate hash --force
+
+# for clean database
+atlas migrate status --url URL --revisions-schema $db
+cmp stdout status_clean.txt
+
+# apply one
+atlas migrate apply --url URL --revisions-schema $db 1
+atlas migrate status --url URL --revisions-schema $db
+cmp stdout status_one_applied.txt
+
+# apply next (and last)
+atlas migrate apply --url URL --revisions-schema $db 1
+atlas migrate status --url URL --revisions-schema $db
+cmp stdout status_ok.txt
+
+-- migrations/1.sql --
+CREATE TABLE "users" ("id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY, PRIMARY KEY ("id"));
+
+-- migrations/2.sql --
+ALTER TABLE "users" ADD COLUMN "happy" boolean NOT NULL DEFAULT true;
+
+-- broken_migration.sql --
+CREATE TABLE "users" ("id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY, PRIMARY KEY ("id"));
+THIS LINE ADDS A SYNTAX ERROR;
+
+-- fixed_migration.sql --
+CREATE TABLE "users" ("id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY, PRIMARY KEY ("id"));
+ALTER TABLE "users" ADD COLUMN "happy" boolean NOT NULL DEFAULT true;
+
+-- status_clean.txt --
+Migration Status: PENDING
+  -- Current Version:  No version applied yet
+  -- Next Version:     1
+  -- Executed Files:   0
+  -- Pending Files:    2
+-- status_one_applied.txt --
+Migration Status: PENDING
+  -- Current Version:  1
+  -- Next Version:     2
+  -- Executed Files:   1
+  -- Pending Files:    1
+-- status_ok.txt --
+Migration Status: OK
+  -- Current Version:  2
+  -- Next Version:     Already at latest version
+  -- Executed Files:   2
+  -- Pending Files:    0
\ No newline at end of file
diff --git a/internal/integration/tidb_test.go b/internal/integration/tidb_test.go
index 2312860f..c0b79666 100644
--- a/internal/integration/tidb_test.go
+++ b/internal/integration/tidb_test.go
@@ -31,7 +31,7 @@ var tidbTests = map[string]*myTest{
 
 func tidbRun(t *testing.T, fn func(*myTest)) {
 	for version, tt := range tidbTests {
-		if flagDialect == "" || flagDialect == version {
+		if flagVersion == "" || flagVersion == version {
 			t.Run(version, func(t *testing.T) {
 				tt.once.Do(func() {
 					var err error
diff --git a/sql/mysql/sqlspec_test.go b/sql/mysql/sqlspec_test.go
index 1420b13d..e9901f20 100644
--- a/sql/mysql/sqlspec_test.go
+++ b/sql/mysql/sqlspec_test.go
@@ -8,8 +8,11 @@ import (
 	"fmt"
 	"testing"
 
+	"ariga.io/atlas/schemahcl"
 	"ariga.io/atlas/sql/internal/spectest"
+	"ariga.io/atlas/sql/internal/specutil"
 	"ariga.io/atlas/sql/schema"
+	"ariga.io/atlas/sql/sqlspec"
 	"github.com/stretchr/testify/require"
 )
 
@@ -1186,6 +1189,41 @@ func TestInputVars(t *testing.T) {
 	spectest.TestInputVars(t, EvalHCL)
 }
 
+func TestQualifyReferencedTables(t *testing.T) {
+	var (
+		col       = schema.NewIntColumn("col", "integer")
+		targetCol = schema.NewIntColumn("target_column", "integer")
+		targetTbl = schema.NewTable("target_table").AddColumns(targetCol)
+		realm     = schema.NewRealm(
+			schema.New("target_schema").AddTables(targetTbl),
+			schema.New("sch").
+				AddTables(
+					schema.NewTable("tbl").
+						AddColumns(col).
+						AddForeignKeys(
+							schema.NewForeignKey("col_fk").
+								SetRefTable(targetTbl).
+								AddColumns(col).
+								AddRefColumns(targetCol),
+						),
+				),
+		)
+		tables = []*sqlspec.Table{
+			{
+				Name:   "tbl",
+				Schema: &schemahcl.Ref{V: "$schema.sch"},
+			},
+			{
+				Name:   "target_table",
+				Schema: &schemahcl.Ref{V: "$schema.target_schema"},
+			},
+		}
+	)
+	require.NoError(t, specutil.QualifyReferencedTables(tables, realm))
+	require.Zero(t, tables[0].Qualifier)
+	require.Equal(t, "target_schema", tables[1].Qualifier)
+}
+
 func TestParseType_Decimal(t *testing.T) {
 	for _, tt := range []struct {
 		input   string
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index 406d7a31..f04de979 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -9,8 +9,11 @@ import (
 	"strconv"
 	"testing"
 
+	"ariga.io/atlas/schemahcl"
 	"ariga.io/atlas/sql/internal/spectest"
+	"ariga.io/atlas/sql/internal/specutil"
 	"ariga.io/atlas/sql/schema"
+	"ariga.io/atlas/sql/sqlspec"
 	"github.com/stretchr/testify/require"
 )
 
@@ -1264,3 +1267,38 @@ func TestRegistrySanity(t *testing.T) {
 func TestInputVars(t *testing.T) {
 	spectest.TestInputVars(t, EvalHCL)
 }
+
+func TestQualifyReferencedTables(t *testing.T) {
+	var (
+		col       = schema.NewIntColumn("col", "integer")
+		targetCol = schema.NewIntColumn("target_column", "integer")
+		targetTbl = schema.NewTable("target_table").AddColumns(targetCol)
+		realm     = schema.NewRealm(
+			schema.New("target_schema").AddTables(targetTbl),
+			schema.New("sch").
+				AddTables(
+					schema.NewTable("tbl").
+						AddColumns(col).
+						AddForeignKeys(
+							schema.NewForeignKey("col_fk").
+								SetRefTable(targetTbl).
+								AddColumns(col).
+								AddRefColumns(targetCol),
+						),
+				),
+		)
+		tables = []*sqlspec.Table{
+			{
+				Name:   "tbl",
+				Schema: &schemahcl.Ref{V: "$schema.sch"},
+			},
+			{
+				Name:   "target_table",
+				Schema: &schemahcl.Ref{V: "$schema.target_schema"},
+			},
+		}
+	)
+	require.NoError(t, specutil.QualifyReferencedTables(tables, realm))
+	require.Zero(t, tables[0].Qualifier)
+	require.Equal(t, "target_schema", tables[1].Qualifier)
+}