DBDiff · jasdeepkhalsa · Aug 5, 2026 · Aug 4, 2026 · Aug 4, 2026 · Aug 5, 2026
diff --git a/src/DB/Adapters/DBAdapterInterface.php b/src/DB/Adapters/DBAdapterInterface.php
@@ -111,4 +111,17 @@ public function getRoutines(Connection $connection): array;
      * MySQL and SQLite return [] (enums are column-level, not standalone types).
      */
     public function getEnums(Connection $connection): array;
+
+    /**
+     * Return a hash-per-table map for schema pre-scan.
+     *
+     * Returns [tableName => hashString] covering columns, indexes, constraints,
+     * engine, and collation. When source and target hashes match for a table,
+     * the diff layer can skip all per-table queries for that table.
+     *
+     * When $tables is non-empty only those tables are included in the result.
+     * Drivers that cannot implement an efficient batch hash (e.g. SQLite) should
+     * return [] — the caller falls back to diffing all common tables normally.
+     */
+    public function getSchemaHashMap(Connection $connection, array $tables = []): array;
 }
diff --git a/src/DB/Adapters/MySQLAdapter.php b/src/DB/Adapters/MySQLAdapter.php
@@ -174,6 +174,105 @@ public function getEnums(Connection $connection): array {
         return [];
     }
 
+    public function getSchemaHashMap(Connection $connection, array $tables = []): array
+    {
+        $db = $connection->getDatabaseName();
+
+        $engineRows = $connection->select(
+            "SELECT TABLE_NAME AS Name, ENGINE AS Engine, TABLE_COLLATION AS Collation
+             FROM INFORMATION_SCHEMA.TABLES
+             WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'",
+            [$db]
+        );
+        $engineMap = [];
+        foreach ($engineRows as $row) {
+            $engineMap[$row['Name']] = ($row['Engine'] ?? '') . '|' . ($row['Collation'] ?? '');
+        }
+
+        $colMap = $this->fetchColMap($connection, $db);
+        $idxMap = $this->fetchIdxMap($connection, $db);
+        $conMap = $this->fetchConMap($connection, $db);
+
+        $hashMap = [];
+        foreach (array_keys($engineMap) as $tableName) {
+            if (!empty($tables) && !in_array($tableName, $tables, true)) {
+                continue;
+            }
+            $parts = [
+                $engineMap[$tableName],
+                isset($colMap[$tableName]) ? implode(';', $colMap[$tableName]) : '',
+                isset($idxMap[$tableName]) ? implode(';', $idxMap[$tableName]) : '',
+                isset($conMap[$tableName]) ? implode(';', $conMap[$tableName]) : '',
+            ];
+            $hashMap[$tableName] = hash('sha256', implode('###', $parts));
+        }
+
+        return $hashMap;
+    }
+
+    private function fetchColMap(Connection $connection, string $db): array
+    {
+        $rows = $connection->select(
+            "SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE,
+                    COALESCE(COLUMN_DEFAULT, '') AS col_default,
+                    IS_NULLABLE, COALESCE(EXTRA, '') AS extra
+             FROM INFORMATION_SCHEMA.COLUMNS
+             WHERE TABLE_SCHEMA = ?
+             ORDER BY TABLE_NAME, ORDINAL_POSITION",
+            [$db]
+        );
+        $map = [];
+        foreach ($rows as $row) {
+            $map[$row['TABLE_NAME']][] =
+                $row['COLUMN_NAME'] . '|' . $row['COLUMN_TYPE'] . '|' .
+                $row['col_default']  . '|' . $row['IS_NULLABLE'] . '|' . $row['extra'];
+        }
+        return $map;
+    }
+
+    private function fetchIdxMap(Connection $connection, string $db): array
+    {
+        $rows = $connection->select(
+            "SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX
+             FROM INFORMATION_SCHEMA.STATISTICS
+             WHERE TABLE_SCHEMA = ?
+             ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX",
+            [$db]
+        );
+        $map = [];
+        foreach ($rows as $row) {
+            $map[$row['TABLE_NAME']][] =
+                $row['INDEX_NAME'] . '|' . $row['COLUMN_NAME'] . '|' .
+                $row['NON_UNIQUE'] . '|' . $row['SEQ_IN_INDEX'];
+        }
+        return $map;
+    }
+
+    private function fetchConMap(Connection $connection, string $db): array
+    {
+        $rows = $connection->select(
+            "SELECT kcu.TABLE_NAME, kcu.CONSTRAINT_NAME,
+                    kcu.COLUMN_NAME, kcu.REFERENCED_TABLE_NAME, kcu.REFERENCED_COLUMN_NAME,
+                    rc.UPDATE_RULE, rc.DELETE_RULE
+             FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
+             JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
+               ON kcu.CONSTRAINT_NAME  = rc.CONSTRAINT_NAME
+              AND kcu.TABLE_SCHEMA     = rc.CONSTRAINT_SCHEMA
+             WHERE kcu.TABLE_SCHEMA = ?
+             ORDER BY kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION",
+            [$db]
+        );
+        $map = [];
+        foreach ($rows as $row) {
+            $map[$row['TABLE_NAME']][] =
+                $row['CONSTRAINT_NAME'] . '|' . $row['COLUMN_NAME'] . '|' .
+                ($row['REFERENCED_TABLE_NAME'] ?? '')  . '|' .
+                ($row['REFERENCED_COLUMN_NAME'] ?? '') . '|' .
+                ($row['UPDATE_RULE'] ?? '')  . '|' . ($row['DELETE_RULE'] ?? '');
+        }
+        return $map;
+    }
+
     /**
      * Strip MySQL-specific DEFINER, ALGORITHM, and SQL SECURITY clauses
      * from a CREATE statement so that definitions can be compared across

diff --git a/src/DB/Adapters/PostgresAdapter.php b/src/DB/Adapters/PostgresAdapter.php
@@ -195,6 +195,98 @@ public function getEnums(Connection $connection): array {
         return $enums;
     }
 
+    public function getSchemaHashMap(Connection $connection, array $tables = []): array
+    {
+        // Single CTE query: hash columns + indexes + constraints per table.
+        // Each sub-CTE mirrors what fetchColumns/fetchIndexes/fetchConstraints returns.
+        $rows = $connection->select(
+            "WITH col_data AS (
+                 SELECT table_name,
+                        string_agg(
+                            column_name         || '|' || data_type              || '|' ||
+                            COALESCE(udt_name,'')                                || '|' ||
+                            COALESCE(character_maximum_length::text,'')          || '|' ||
+                            COALESCE(numeric_precision::text,'')                 || '|' ||
+                            COALESCE(numeric_scale::text,'')                     || '|' ||
+                            COALESCE(datetime_precision::text,'')                || '|' ||
+                            COALESCE(column_default, '')                         || '|' ||
+                            is_nullable                                          || '|' ||
+                            COALESCE(is_identity,'NO')                           || '|' ||
+                            COALESCE(identity_generation,'')                     || '|' ||
+                            COALESCE(is_generated,'NEVER')                       || '|' ||
+                            COALESCE(generation_expression,'')                   || '|' ||
+                            COALESCE(domain_name,''),
+                            ';' ORDER BY ordinal_position
+                        ) AS col_str
+                 FROM information_schema.columns
+                 WHERE table_schema = 'public'
+                 GROUP BY table_name
+             ),
+             idx_data AS (
+                 SELECT tablename AS table_name,
+                        string_agg(indexname || '|' || indexdef, ';' ORDER BY indexname) AS idx_str
+                 FROM pg_indexes
+                 WHERE schemaname = 'public'
+                 GROUP BY tablename
+             ),
+             con_base AS (
+                 SELECT tc.table_name,
+                        tc.constraint_name || '|' || tc.constraint_type        || '|' ||
+                        COALESCE(tc.is_deferrable,'NO')                        || '|' ||
+                        COALESCE(tc.initially_deferred,'NO')                   || '|' ||
+                        COALESCE(rc.update_rule,'')                            || '|' ||
+                        COALESCE(rc.delete_rule,'')                            || '|' ||
+                        COALESCE(rc.match_option,'')                           AS con_sig
+                 FROM information_schema.table_constraints tc
+                 LEFT JOIN information_schema.referential_constraints rc
+                   ON tc.constraint_name  = rc.constraint_name
+                  AND tc.constraint_schema = rc.constraint_schema
+                 WHERE tc.table_schema = 'public'
+             ),
+             pg_ext_data AS (
+                 SELECT c.relname AS table_name,
+                        con.conname || '|' || pg_get_constraintdef(con.oid) || '|' ||
+                        CASE WHEN con.convalidated THEN 'v' ELSE 'nv' END AS ext_sig
+                 FROM pg_constraint con
+                 JOIN pg_class c     ON con.conrelid = c.oid
+                 JOIN pg_namespace n ON c.relnamespace = n.oid
+                 WHERE n.nspname = 'public'
+                   AND con.contype IN ('c', 'x', 'n')
+             ),
+             con_data AS (
+                 SELECT table_name,
+                        string_agg(con_sig, ';' ORDER BY con_sig) AS con_str
+                 FROM con_base GROUP BY table_name
+             ),
+             ext_data AS (
+                 SELECT table_name,
+                        string_agg(ext_sig, ';' ORDER BY ext_sig) AS ext_str
+                 FROM pg_ext_data GROUP BY table_name
+             )
+             SELECT c.table_name,
+                    md5(
+                        COALESCE(c.col_str, '') || '###' ||
+                        COALESCE(i.idx_str, '') || '###' ||
+                        COALESCE(co.con_str,'') || '###' ||
+                        COALESCE(e.ext_str, '')
+                    ) AS schema_hash
+             FROM col_data c
+             LEFT JOIN idx_data i  ON i.table_name  = c.table_name
+             LEFT JOIN con_data co ON co.table_name = c.table_name
+             LEFT JOIN ext_data e  ON e.table_name  = c.table_name"
+        );
+
+        $hashMap = [];
+        foreach ($rows as $row) {
+            $name = $row['table_name'];
+            if (!empty($tables) && !in_array($name, $tables, true)) {
+                continue;
+            }
+            $hashMap[$name] = $row['schema_hash'];
+        }
+        return $hashMap;
+    }
+
     // -------------------------------------------------------------------------
     // Private helpers
     // -------------------------------------------------------------------------
@@ -442,37 +534,27 @@ private function buildColumnType(array $col): string {
         if (!empty($col['domain_name'])) {
             return $col['domain_name'];
         }
+        $dataType  = $col['data_type'];
         $simpleMap = [
             'time without time zone' => 'time',
             'time with time zone'    => 'timetz',
             'double precision'       => 'double precision',
             'ARRAY'                  => $col['udt_name'],
         ];
-        return $simpleMap[$col['data_type']] ?? $this->resolveParameterisedType($col);
-    }
-
-    /**
-     * Resolve column types that carry length, precision, or scale parameters.
-     * All other types fall through to $dataType unchanged.
-     */
-    private function resolveParameterisedType(array $col): string {
-        $dataType = $col['data_type'];
-        $result   = $dataType;
-
+        if (isset($simpleMap[$dataType])) {
+            return $simpleMap[$dataType];
+        }
+        $result = $dataType;
         if ($dataType === 'character varying' || $dataType === 'character') {
-            $len    = $col['character_maximum_length'];
-            $base   = $dataType === 'character varying' ? 'varchar' : 'char';
-            $result = $len ? "$base($len)" : $base;
+            $base   = ['character varying' => 'varchar', 'character' => 'char'][$dataType];
+            $result = $col['character_maximum_length'] ? "$base({$col['character_maximum_length']})" : $base;
         } elseif ($dataType === 'numeric' || $dataType === 'decimal') {
             $p      = $col['numeric_precision'];
-            $s      = $col['numeric_scale'];
-            $result = ($p !== null) ? "$dataType($p,$s)" : $dataType;
+            $result = ($p !== null) ? "$dataType($p,{$col['numeric_scale']})" : $dataType;
         } elseif (str_starts_with($dataType, 'timestamp')) {
-            $p      = $col['datetime_precision'];
-            $base   = $dataType === 'timestamp with time zone' ? 'timestamptz' : 'timestamp';
-            $result = ($p > 0) ? "$base($p)" : $base;
+            $base   = ['timestamp with time zone' => 'timestamptz'][$dataType] ?? 'timestamp';
+            $result = ($col['datetime_precision'] > 0) ? "$base({$col['datetime_precision']})" : $base;
         }
-
         return $result;
     }
 }
diff --git a/src/DB/Adapters/SQLiteAdapter.php b/src/DB/Adapters/SQLiteAdapter.php
@@ -128,6 +128,13 @@ public function getEnums(Connection $connection): array {
         return [];
     }
 
+    public function getSchemaHashMap(Connection $connection, array $tables = []): array
+    {
+        // SQLite databases are local files; the latency overhead that motivates
+        // pre-scan hashing does not apply. Return [] to fall back to per-table diffs.
+        return [];
+    }
+
     // -------------------------------------------------------------------------
     // Private helpers
     // -------------------------------------------------------------------------

diff --git a/src/DB/DBManager.php b/src/DB/DBManager.php
@@ -144,4 +144,8 @@ public function getRoutines(string $connection): array {
     public function getEnums(string $connection): array {
         return $this->adapter->getEnums($this->getDB($connection));
     }
+
+    public function getSchemaHashMap(string $connection, array $tables = []): array {
+        return $this->adapter->getSchemaHashMap($this->getDB($connection), $tables);
+    }
 }
diff --git a/src/DB/Schema/DBSchema.php b/src/DB/Schema/DBSchema.php
@@ -2,6 +2,7 @@
 
 use Diff\Differ\ListDiffer;
 
+use DBDiff\Logger;
 use DBDiff\Params\ParamsFactory;
 use DBDiff\Params\TableFilter;
 use DBDiff\Diff\SetDBCollation;
@@ -83,12 +84,30 @@ function getDiff() {
             $diffs[] = $diff;
         }
 
-        $commonTables = array_intersect($sourceTables, $targetTables);
+        $commonTables = array_values(array_intersect($sourceTables, $targetTables));
+
+        // Pre-scan: fetch a hash of every table's schema in two batch queries
+        // (one per DB side). Tables whose hashes match are identical and can be
+        // skipped entirely, avoiding the 7-14 per-table queries that otherwise
+        // fire for every common table — critical for large Supabase databases.
+        $sourceHashes = $this->manager->getSchemaHashMap('source', $commonTables);
+        $targetHashes = $this->manager->getSchemaHashMap('target', $commonTables);
+
+        $skipped = 0;
         foreach ($commonTables as $table) {
+            if (isset($sourceHashes[$table], $targetHashes[$table])
+                && $sourceHashes[$table] === $targetHashes[$table]) {
+                $skipped++;
+                continue;
+            }
             $tableDiff = $tableSchema->getDiff($table);
             $diffs = array_merge($diffs, $tableDiff);
         }
 
+        if ($skipped > 0) {
+            Logger::info("Pre-scan: skipped $skipped / " . count($commonTables) . " unchanged tables");
+        }
+
         foreach ($deletedTables as $i => $table) {
             $diff = new DropTable($table, $this->manager, 'target');
             $diff->sortOrder = $i;