Convert a postgres db schema to a json format

Question

I am looking for a way to convert a postgres db schema to a JSON format data.

e.g.:

{"Table" : 
   {"name": "varchar(20)",
    "Age" : "int"
   },
}

Could someone help me with this?

Washy

Answer 1

Here is a solution using php:

$conn = pg_connect("host={$host} port=5432 dbname={$db} user={$user} password={$pass}");

$sql = <<<SQL
SELECT tables.table_name, columns.column_name, columns.data_type, columns.udt_name
FROM information_schema.tables AS tables
    JOIN information_schema.columns AS columns
        ON tables.table_name = columns.table_name
WHERE tables.table_type = 'BASE TABLE'
AND tables.table_schema NOT IN
('pg_catalog', 'information_schema');
SQL;

$result = pg_query($conn, $sql);
$table_meta = new stdClass;
while ($row =  pg_fetch_object($result)) {
    if (!isset($table_meta->{$row->table_name})) $table_meta->{$row->table_name} = new stdClass;
    $table_meta->{$row->table_name}->{$row->column_name} = $row->udt_name;
}

$table_json = json_encode($table_meta);

echo $table_json;

---

This is as close as I can get using postgres 9.2.4

select row_to_json(table_schema)
from (
    select t.table_name, array_agg( c ) as columns
    from information_schema.tables t
    inner join (
        select cl.table_name, cl.column_name, cl.udt_name
        from information_schema.columns cl
    ) c (table_name,column_name,udt_name) on c.table_name = t.table_name
    where t.table_type = 'BASE TABLE'
    AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
    group by t.table_name
) table_schema;

The result is not pretty:

{"table_name":"users","columns":[
    {"table_name":"users","column_name":"user_id","udt_name":"int4"},
    {"table_name":"users","column_name":"user_email","udt_name":"varchar"}
]}

postgres 9.3 offers more json functions than 9.2. If possible upgrade and check out the 9.3 json function docs. If it's not possible to upgrade I would accomplish this with a script like the php code I posted earlier.

Pahang

Thanks for pointing out that better JSON functions are available in 9.3 (a very good excuse of many for requesting our upgrade happen sooner rather than later and that we go all the way to 9.3.3 not just patch to 9.2.7) – Muriel

is it possible to get the relation table of relation fields? – Coles

Answer 2

tbls is primarily a tool for documenting databases, but it also has yaml output support which makes it useful for things like code generation (I use in ts-sql-codegen - a code-generator for type safe database access for typescript).

The output format looks something like this.

You can either use the yaml directly or convert it to json using another tool like yq

Valeric