
Get PostgreSQL Database Structure as a Detailed JavaScript Object - stephen
http://www.pg-structure.com
======
derefr
I would like to point out that SQL itself standardizes a set of tables (called
the information_schema) that can be queried, in a completely uniform way, to
learn pretty much every (SQL-standard-formalized) fact about your database.

information_schema doesn't cover anything the DBMS is doing that's _not_
standardized by SQL, though. This would mean that, for PG in particular,
things like partitioning/inheritance, tablespaces, and the distinction between
roles and schemas, aren't represented in the information_schema. (The objects
do show up, but only as their SQL-standard "superclass"—e.g. partitioned
tables just look like a set of regular tables + constraints + triggers +
rewrite rules; table columns with special types look like their underlying
storage type; etc.)

The information_schema also doesn't cover the pragmatics of the administration
of the database instance itself, like, for PG, the type of data you'd access
through any of the pg_stat_ tables.

But neither of these concerns are really relevant if you're building a generic
tool that wants to prod at SQL-standard database objects, I would think. Just
use the information_schema!

\---

I would _like_ to link here the relevant part of the SQL standards document,
because it's actually a very helpful and thorough reference. (Even if you
never program for more than one DBMS, just learning how it defines words like
"catalog", "schema", and "object" will make reading any particular DBMS's docs
10x easier.)

But, sadly, the SQL standard itself is a proprietary document that you have to
purchase! (See here: [https://modern-sql.com/standard](https://modern-
sql.com/standard)) This is a pretty odd thing, considering that unpaid
developers of FOSS systems like PG need to reference the standard for
compliance.

~~~
erichanson
To derefr's point, I'll also point to the "meta" PostgreSQL extension (part of
the Aquameta project, which I maintain) whose purpose is to provide a more
readable version of information_schema:

[https://github.com/aquametalabs/meta](https://github.com/aquametalabs/meta)
[http://blog.aquameta.com/intro-meta/](http://blog.aquameta.com/intro-meta/)

information_schema is pretty unruly, and pg_catalog is just crazy, in terms of
readability, but if you just want to do some simple introspection, meta might
be helpful.

That said, I see value in a nice Javascript object that is easy to traverse
and can be retrieved all at once.

~~~
aargh_aargh
There's broken link in your article to
[https://github.com/aquametalabs/aquameta/tree/master/core/00...](https://github.com/aquametalabs/aquameta/tree/master/core/000-meta)

------
miohtama
A little bit similar tool for Python:

[https://pypi.org/project/sqlacodegen/](https://pypi.org/project/sqlacodegen/)

Note that SQLAlchemy itself offers this functionality in core, codegen just
makes it more human readable static files.

~~~
stevesimmons
sqlcodegen is brilliant.

I used it to document a 1000-table db, and then added autogenerated docstrings
for each table with # of rows/columns, list of the column names, and the
contents of its first and last 5 rows.

This was invaluable in understanding a decade of legacy system development...

------
evangow
This is a very cool project, but I'm not sure exactly what I would use it for.
I could see it being useful if didn't start your project out using a tool for
schema migrations and need to back reverse engineer things to create fake
migrations.

I'm curious, does anyone here use it that would care to explain their use
case?

~~~
paps
We use it in our CI, just before deployment, to compare the DB structure of
what's being tested with the DB structure of our staging or production
environment.

It's a last minute check that prevented a lot of mistakes.

~~~
larkost
Wouldn't a dump and then diff of the schema into text form accomplish the same
thing?

~~~
hobs
It absolutely should, though you might want to have a specific set of
questions you ask in an ordered fashion as things like column order might
differ between production and development due to data sizes, dropping and
recreating dev instead of migration, etc.

None of those things actually being a problem could give you false positives,
so you might want some minor shuffling.

------
rodw
For what it is worth, if you mean the `>` symbol in the banner graphic to be
interpreted as an arrow not "greater than" if I were you I'd give the arrow
some kind of tail (`->` not just `>`). My first interpretation was definitely
"greater than" and I'm probably not alone.

If you _do_ really mean "pgsql > json` and not `pgsql -to-> json` then maybe
I'm just confused.

~~~
theta_d
Unix redirecting output uses the `>` symbol.

~~~
rodw
Oh, yeah. I am well aware of that and that interpretation did not enter my
mind at all (being entirely surrounded by graphics and not in a conventional
terminal-style font may be part of the reason why).

I still think it's a little confusing but I understand why it is that way at
least.

------
soulnothing
Is there a good source for the queries run on different data base types
(MySql, Postgres) to reflect it's schema?

~~~
mvc
The information schema is part of the SQL standard so the queries should be
the same in all the databases that support that (which is quite a few). Here's
the doc links for MySQL and PostgreSQL

[https://dev.mysql.com/doc/refman/8.0/en/information-
schema.h...](https://dev.mysql.com/doc/refman/8.0/en/information-schema.html)
[https://www.postgresql.org/docs/12/information-
schema.html](https://www.postgresql.org/docs/12/information-schema.html)

As it happens, I did a talk about this at re:Clojure this week. Videos aren't
up yet but they will appear here when they are available

[https://www.youtube.com/channel/UCbZW8yCqEncYciie8_1yy7w/fea...](https://www.youtube.com/channel/UCbZW8yCqEncYciie8_1yy7w/featured)

------
simbakhadder
Is the expected use case to build generic queries on any type of database?

Also why Javascript?

