gezerui888 · Oct 24, 2026 · Oct 25, 2026
@@ -7,6 +7,18 @@ on:
 jobs:
   lint-and-build:
     runs-on: ubuntu-latest
+    services:
+      postgres:
+        image: postgres:16-alpine
+        env:
+          POSTGRES_PASSWORD: postgres
+        ports:
+          - 5432:5432
+        options: >-
+          --health-cmd pg_isready
+          --health-interval 10s
+          --health-timeout 5s
+          --health-retries 5
     steps:
       - uses: actions/checkout@v3
 
@@ -16,13 +28,18 @@ jobs:
           node-version: 18
 
       - name: Install pnpm
-        run: npm install -g pnpm
+        run: npm install -g pnpm@10.34.8
 
       - name: Install dependencies
         run: pnpm install --frozen-lockfile
 
       - name: Run lint
         run: pnpm lint
 
+      - name: Test PostgreSQL schemas
+        env:
+          SQLCHAT_TEST_POSTGRES_URL: postgres://postgres:postgres@localhost:5432/postgres
+        run: pnpm test:postgres
+
       - name: Run build
-        run: pnpm build
+        run: pnpm build
@@ -10,7 +10,8 @@
     "export": "next export",
     "start": "next start",
     "lint": "next lint",
-    "stripe": "stripe listen --forward-to localhost:3000/api/stripe/webhook"
+    "stripe": "stripe listen --forward-to localhost:3000/api/stripe/webhook",
+    "test:postgres": "node --test tests/postgres-schema.test.cjs"
   },
   "dependencies": {
     "@emotion/react": "^11.10.6",

@@ -118,7 +118,20 @@ const getTableSchema = async (connection: Connection, databaseName: string): Pro
   for (const schema of schemaList) {
     for (const table of schema.tables) {
       const { rows: result } = await client.query(
-        `SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema NOT IN (${systemSchemas}) AND table_name=$1 AND table_schema=$2;`,
+        `SELECT cols.column_name, cols.data_type, cols.is_nullable,
+          CASE WHEN description.description IS NOT NULL THEN
+            pg_catalog.format('COMMENT ON COLUMN %I.%I.%I IS %L;',
+              cols.table_schema, cols.table_name, cols.column_name, description.description)
+          END AS column_comment
+        FROM information_schema.columns AS cols
+        JOIN pg_catalog.pg_namespace AS namespace ON namespace.nspname = cols.table_schema
+        JOIN pg_catalog.pg_class AS relation ON relation.relnamespace = namespace.oid AND relation.relname = cols.table_name
+        LEFT JOIN pg_catalog.pg_description AS description
+          ON description.objoid = relation.oid
+          AND description.classoid = 'pg_catalog.pg_class'::regclass
+          AND description.objsubid = cols.ordinal_position
+        WHERE cols.table_name=$1 AND cols.table_schema=$2
+        ORDER BY cols.ordinal_position;`,
         [table.name, schema.name]
       );
       const columnList = [];
@@ -130,7 +143,8 @@ const getTableSchema = async (connection: Connection, databaseName: string): Pro
       }
 
       let fullTableName = schema.name == "public" ? `"${table.name}"` : `"${schema.name}"."${table.name}"`;
-      table.structure = `CREATE TABLE ${fullTableName} (\n${columnList.join(",\n")}\n);`;
+      const columnComments = result.map((row) => row["column_comment"]).filter((comment) => comment !== null);
+      table.structure = [`CREATE TABLE ${fullTableName} (\n${columnList.join(",\n")}\n);`, ...columnComments].join("\n");
     }
   }
 

@@ -0,0 +1,71 @@
+const assert = require("node:assert/strict");
+const { test } = require("node:test");
+const { Client } = require("pg");
+require("ts-node").register({ transpileOnly: true, compilerOptions: { module: "CommonJS" } });
+const newConnector = require("../src/lib/connectors/postgres").default;
+
+// Use a disposable database owned by the test user, with CREATE access to public.
+// The test removes only the schemas and table it creates.
+test("PostgreSQL schemas preserve column comments", { skip: !process.env.SQLCHAT_TEST_POSTGRES_URL }, async () => {
+  const url = new URL(process.env.SQLCHAT_TEST_POSTGRES_URL);
+  const client = new Client({ connectionString: url.toString() });
+  const suffix = `${process.pid}_${Date.now()}`;
+  const schemas = [`comments_${suffix}`, `comments " ${suffix}`];
+  const publicTable = `uncommented_${suffix}`;
+  const quote = (name) => `"${name.replace(/"/g, '""')}"`;
+  const text = "Customer's address\\unit\n第二行 */ --";
+  await client.connect();
+  try {
+    for (const schema of schemas) {
+      await client.query(`CREATE SCHEMA ${quote(schema)}`);
+      await client.query(`CREATE TABLE ${quote(schema)}.items (removed text, value text, plain integer)`);
+      await client.query(`ALTER TABLE ${quote(schema)}.items DROP COLUMN removed`);
+    }
+    await client.query(`COMMENT ON COLUMN ${quote(schemas[0])}.items.value IS 'First schema'`);
+    const { rows: comments } = await client.query("SELECT format('COMMENT ON COLUMN %I.items.value IS %L', $1::text, $2::text) AS sql", [
+      schemas[1],
+      text,
+    ]);
+    await client.query(comments[0].sql);
+    await client.query(`CREATE VIEW ${quote(schemas[1])}.item_view AS SELECT value FROM ${quote(schemas[1])}.items`);
+    await client.query(`COMMENT ON COLUMN ${quote(schemas[1])}.item_view.value IS 'View comment'`);
+    await client.query(`ALTER TABLE ${quote(schemas[1])}.items ADD COLUMN "quoted column" text`);
+    await client.query(`COMMENT ON COLUMN ${quote(schemas[1])}.items."quoted column" IS 'Quoted column'`);
+    await client.query(`CREATE TABLE public.${quote(publicTable)} (id integer NOT NULL)`);
+
+    const connector = newConnector({
+      host: url.hostname,
+      port: url.port || "5432",
+      username: decodeURIComponent(url.username),
+      password: decodeURIComponent(url.password),
+      database: url.pathname.slice(1),
+    });
+    const result = await connector.getTableSchema(url.pathname.slice(1));
+    const structure = (schema, table) => result.find((item) => item.name === schema).tables.find((item) => item.name === table).structure;
+    const first = structure(schemas[0], "items");
+    const second = structure(schemas[1], "items");
+    assert.ok(first.includes("IS 'First schema';"));
+    assert.ok(!second.includes("First schema"));
+    assert.ok(second.includes("items.\"quoted column\" IS 'Quoted column';"));
+    assert.equal((second.match(/COMMENT ON COLUMN/g) || []).length, 2);
+    assert.ok(structure(schemas[1], "item_view").includes("IS 'View comment';"));
+    assert.equal(structure("public", publicTable), `CREATE TABLE "${publicTable}" (\nid INTEGER NOT NULL\n);`);
+
+    // Replay the emitted statements to verify identifier and literal escaping.
+    await client.query(`COMMENT ON COLUMN ${quote(schemas[1])}.items.value IS NULL`);
+    const commentSql = second.slice(second.indexOf("COMMENT ON COLUMN"));
+    await client.query(commentSql);
+    const { rows } = await client.query(
+      "SELECT col_description(c.oid, a.attnum) AS description FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid WHERE n.nspname = $1 AND c.relname = 'items' AND a.attname = 'value'",
+      [schemas[1]]
+    );
+    assert.equal(rows[0].description, text);
+  } finally {
+    try {
+      for (const schema of schemas) await client.query(`DROP SCHEMA IF EXISTS ${quote(schema)} CASCADE`);
+      await client.query(`DROP TABLE IF EXISTS public.${quote(publicTable)}`);
+    } finally {
+      await client.end();
+    }
+  }
+});