Function getTableSchema

src/lib/connectors/postgres/index.ts:95–139

(connection: Connection, databaseName: string)

};

const getTableSchema = async (connection: Connection, databaseName: string): Promise<Schema[]> => {
  connection.database = databaseName;
  const client = await newPostgresClient(connection);
  const { rows } = await client.query(
    `SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN (${systemSchemas}) AND table_name NOT IN (${systemTables}) AND (table_type='BASE TABLE' or table_type='VIEW') AND table_catalog=$1;`,
    [databaseName]
  );

  const schemaList: Schema[] = [];
  for (const row of rows) {
    if (row["table_name"]) {
      const schema = schemaList.find((schema) => schema.name === row["table_schema"]);
      if (schema) {
        schema.tables.push({ name: row["table_name"] as string, structure: "" } as Table);
      } else {
        schemaList.push({
          name: row["table_schema"],
          tables: [{ name: row["table_name"], structure: "" } as Table],
        });
      }
    }
  }

  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;`,
        [table.name, schema.name]
      );
      const columnList = [];
      // TODO(steven): transform it to standard schema string.
      for (const row of result) {
        columnList.push(
          `${row["column_name"]} ${row["data_type"].toUpperCase()} ${String(row["is_nullable"]).toUpperCase() === "NO" ? "NOT NULL" : ""}`
        );
      }

      let fullTableName = schema.name == "public" ? `"${table.name}"` : `"${schema.name}"."${table.name}"`;
      table.structure = `CREATE TABLE ${fullTableName} (\n${columnList.join(",\n")}\n);`;
    }
  }

  await client.end();
  return schemaList;
};

const newConnector = (connection: Connection): Connector => {
  return {