CODEWITHSUNDEEP
jsonpostgresql-9.2
user3396639
user3396639

Reputation: 121

Convert a postgres db schema to a json format

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?

Upvotes: 12

Views: 6447

Answers (2)

lorefnon
lorefnon

Reputation: 13105

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

Upvotes: 2

m79lkm
m79lkm

Reputation: 3070

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.

Upvotes: 7

Related Questions