entity sync does not work with postgres when tables have schemas defined

[fnimick]

Description

Entity sync fails at runtime with a SQL error when a table is defined to have a schema other than the default.

If the schema does not exist, a missing schema error occurs. If the schema is created, the next sync works. Then, subsequent syncs attempt to re-create the table and a table already exists error occurs:

called `Result::unwrap()` on an `Err` value: Exec(SqlxError(Database(PgDatabaseError { severity: Error, code: "42P07", message: "relation \"test_entity\" already exists", detail: None, hint: None, position: None, where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("heap.c"), line: Some(1162), routine: Some("heap_create_with_catalog") })))

The full SQL debug error log is:

026-02-12T22:58:36.169216Z DEBUG sea_orm::entity::registry: Registering entities with prefix `sea_orm_sync_test::entity::`
2026-02-12T22:58:36.169460Z DEBUG sea_orm::entity::registry: Registered sea_orm_sync_test::entity::test_entity
2026-02-12T22:58:36.169691Z DEBUG sea_orm::driver::sqlx_postgres: SELECT CURRENT_SCHEMA()
2026-02-12T22:58:36.170714Z  INFO sqlx::query: summary="SELECT CURRENT_SCHEMA()" db.statement="" rows_affected=1 rows_returned=1 elapsed=652.25µs elapsed_secs=0.00065225
2026-02-12T22:58:36.170800Z DEBUG sea_orm::driver::sqlx_postgres: SELECT "pg_type"."typname", "pg_enum"."enumlabel" FROM "pg_catalog"."pg_type" INNER JOIN "pg_catalog"."pg_enum" ON "pg_enum"."enumtypid" = "pg_type"."oid" INNER JOIN "pg_catalog"."pg_namespace" ON "pg_namespace"."oid" = "pg_type"."typnamespace" WHERE "pg_namespace"."nspname" = 'public' ORDER BY "pg_type"."typname" ASC, "pg_enum"."enumsortorder" ASC, "pg_enum"."enumlabel" ASC
2026-02-12T22:58:36.182587Z  INFO sqlx::query: summary="SELECT \"pg_type\".\"typname\", \"pg_enum\".\"enumlabel\" FROM …" db.statement="\n\nSELECT \"pg_type\".\"typname\", \"pg_enum\".\"enumlabel\" FROM \"pg_catalog\".\"pg_type\" INNER JOIN \"pg_catalog\".\"pg_enum\" ON \"pg_enum\".\"enumtypid\" = \"pg_type\".\"oid\" INNER JOIN \"pg_catalog\".\"pg_namespace\" ON \"pg_namespace\".\"oid\" = \"pg_type\".\"typnamespace\" WHERE \"pg_namespace\".\"nspname\" = $1 ORDER BY \"pg_type\".\"typname\" ASC, \"pg_enum\".\"enumsortorder\" ASC, \"pg_enum\".\"enumlabel\" ASC\n" rows_affected=0 rows_returned=0 elapsed=3.427666ms elapsed_secs=0.003427666
2026-02-12T22:58:36.182723Z DEBUG sea_orm::driver::sqlx_postgres: SELECT "table_name", "user_defined_type_schema", "user_defined_type_name" FROM "information_schema"."tables" WHERE "table_schema" = 'public' AND "table_type" = 'BASE TABLE' AND "table_name" NOT IN (SELECT "pg_class"."relname" FROM "pg_inherits" JOIN "pg_class" ON "pg_inherits"."inhrelid" = "pg_class"."oid" AND "pg_class"."relkind" IN ('r', 't', 'v', 'm', 'f', 'p'))
2026-02-12T22:58:36.189766Z  INFO sqlx::query: summary="SELECT \"table_name\", \"user_defined_type_schema\", \"user_defined_type_name\" …" db.statement="\n\nSELECT \"table_name\", \"user_defined_type_schema\", \"user_defined_type_name\" FROM \"information_schema\".\"tables\" WHERE \"table_schema\" = $1 AND \"table_type\" = $2 AND \"table_name\" NOT IN (SELECT \"pg_class\".\"relname\" FROM \"pg_inherits\" JOIN \"pg_class\" ON \"pg_inherits\".\"inhrelid\" = \"pg_class\".\"oid\" AND \"pg_class\".\"relkind\" IN ($3, $4, $5, $6, $7, $8))\n" rows_affected=0 rows_returned=0 elapsed=6.814292ms elapsed_secs=0.006814292
2026-02-12T22:58:36.189892Z DEBUG sea_orm::driver::sqlx_postgres: CREATE TABLE "test"."test_entity" ( "id" uuid NOT NULL PRIMARY KEY )
2026-02-12T22:58:36.190604Z  INFO sqlx::query: summary="CREATE TABLE \"test\".\"test_entity\" ( …" db.statement="\n\nCREATE TABLE \"test\".\"test_entity\" ( \"id\" uuid NOT NULL PRIMARY KEY )\n" rows_affected=0 rows_returned=0 elapsed=544.166µs elapsed_secs=0.000544166

Steps to Reproduce

Define an entity with #[sea_orm(schema_name = "non_empty", table_name = "...")] and run schema sync.

Expected Behavior

Entity sync correctly handles tables in schemas.

Actual Behavior

Entity sync appears to not detect tables in schemas, and attempts to recreate them every time.

Reproduces How Often

Always

Workarounds

Disable sync, or move tables out of schemas.

If all your tables are in the same schema, you can set connect_options.set_schema_search_path("my_schema");. However, you then need to remove the schema_name on the table macro, or the same error occurs.

Versions

sea-orm-sync-test v0.1.0 (/Users/francis/tessi/sea-orm-sync-test)
├── sea-orm v2.0.0-rc.32
│   ├── sea-orm-macros v2.0.0-rc.32 (proc-macro)
│   │   ├── sea-bae v0.2.1 (proc-macro)
│   ├── sea-query v1.0.0-rc.31
│   │   ├── sea-query-derive v1.0.0-rc.12 (proc-macro)
│   ├── sea-query-sqlx v0.8.0-rc.14
│   │   ├── sea-query v1.0.0-rc.31 (*)
│   ├── sea-schema v0.17.0-rc.17
│   │   ├── sea-query v1.0.0-rc.31 (*)
│   │   ├── sea-query-sqlx v0.8.0-rc.14 (*)
│   │   ├── sea-schema-derive v0.3.0 (proc-macro)

Mac OS Tahoe 26.2, postgres 17.7.1

A repository with a minimal reproduction is here: https://github.com/fnimick/sea-orm-schema-sync-bug-repro

[fnimick]

Upon investigation, I think this affects mysql (and possibly sqlite?) as well.

There are two parts to this problem, the first is that schema discovery only runs over a single schema, which is the current schema selected from the db - see

sea-orm/sea-orm-sync/src/schema/builder.rs

Line 109 in 32d50ff

let current_schema: String = db

.

The second problem that I see is that the return values of discovery in sea-schema only capture the name and not any schema information - see postgres and mysql and sqlite . As a result, there's no way to determine whether a table in a given schema has been created correctly, even if we expand the discovery to include more schemas.