6 ms·

Get PostgreSQL Database Structure as a Detailed JavaScript Object

158 points by stephen 7y ago

soulnothing 7y ago
Is there a good source for the queries run on different data base types (MySql, Postgres) to reflect it's schema?
- mvc 7y ago
  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.html https://dev.mysql.com/doc/refman/8.0/en/information-schema.h... 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/featured https://www.youtube.com/channel/UCbZW8yCqEncYciie8_1yy7w/fea...
- pmart123 7y ago
  One way I found helpful was to look at ORM libraries like sqlalchemy to see how they queried each db to translate schemas into objects.
evangow 7y ago
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?
- stephen 7y ago
  I'm using it generate a "flush_database()" stored procedure (after applying the latest migrations), that means each "before each" of a test can "execute flush_database" to get a clean slate db for its boundary case (w/o having to know/lookup "oh we've got 10-20-50 some entity tables now"). So, yeah, just custom/minor infrastructure/tooling stuff that is based on the db schema.
  - aargh_aargh 7y ago
    Doesn't your RDBMS support transactions?
- deleted 7y ago
  [deleted]
- sciolistse 7y ago
  It seems like it could be used for declarative schema management (like https://www.skeema.io/ https://www.skeema.io/) where you define the table structure you want in code and the application diffs a database and performs whatever changes is needed to reflect the requested schema.
- paps 7y ago
  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 7y ago
    Wouldn't a dump and then diff of the schema into text form accomplish the same thing?
    - fanf2 7y ago
      Or something a bit more high-level like Migra? https://github.com/djrobstep/migra https://github.com/djrobstep/migra
    - hobs 7y ago
      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.
  - djrobstep 7y ago
    You might be interested in the service I'm building, which does a similar thing in a more structured way: https://djrobstep.com/ci-for-databases https://djrobstep.com/ci-for-databases
- djrobstep 7y ago
  I have a similar project called `schemainspect` - It creates a data structure representation of a postgres schema. I then use this data in my database diffing project `migra`, to autogenerate database migrations. You can also use schema diffing to test that your production and development databases match explicitly. Overall it's a much more flexible but rigorous approach than the old-fashioned rails/django migrations.
- kristiandupont 7y ago
  I've made something similar which I use for two things. 1) I create typescript types from my schema, and 2) I run a "linter" over my schema that validates a number of rules that I've created. It's been a great help honestly.
simbakhadder 7y ago
Is the expected use case to build generic queries on any type of database? Also why Javascript?
derefr 7y ago
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.
- xemoka 7y ago
  Right, and anything else should be in the pg_catalog; information_schema are just views over that. https://www.postgresql.org/docs/current/catalogs.html https://www.postgresql.org/docs/current/catalogs.html
- stephen 7y ago
  True! Good point and clarification. The nice aspect of pg-structure for me was getting all of that information_schema goodness already pulled out into hashes/DTOs from a 1-line "await pgStructure(...)" call. I.e. w/o pg-structure, I'd probably end up writing a mini/hacky version of it that did the same thing, "do these ~3-4 SQL queries and mash them into some nice hashes for my code generator to consume". Which is not terrible, but it's easier to just pull in another npm package. :-)
deleted 7y ago
[deleted]
miohtama 7y ago
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 7y ago
  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...
- isoprophlex 7y ago
  This is brilliant! Thanks for sharing this, made my day!
rodw 7y ago
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 7y ago
  Unix redirecting output uses the `>` symbol.
  - rodw 7y ago
    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.

158 points by stephen 7y ago

soulnothing 7y ago

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

mvc 7y ago
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.html https://dev.mysql.com/doc/refman/8.0/en/information-schema.h... 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/featured https://www.youtube.com/channel/UCbZW8yCqEncYciie8_1yy7w/fea...
pmart123 7y ago
One way I found helpful was to look at ORM libraries like sqlalchemy to see how they queried each db to translate schemas into objects.

evangow 7y ago

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?

stephen 7y ago
I'm using it generate a "flush_database()" stored procedure (after applying the latest migrations), that means each "before each" of a test can "execute flush_database" to get a clean slate db for its boundary case (w/o having to know/lookup "oh we've got 10-20-50 some entity tables now"). So, yeah, just custom/minor infrastructure/tooling stuff that is based on the db schema.
- aargh_aargh 7y ago
  Doesn't your RDBMS support transactions?
deleted 7y ago
[deleted]
sciolistse 7y ago
It seems like it could be used for declarative schema management (like https://www.skeema.io/ https://www.skeema.io/) where you define the table structure you want in code and the application diffs a database and performs whatever changes is needed to reflect the requested schema.
paps 7y ago
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 7y ago
  Wouldn't a dump and then diff of the schema into text form accomplish the same thing?
  - fanf2 7y ago
    Or something a bit more high-level like Migra? https://github.com/djrobstep/migra https://github.com/djrobstep/migra
  - hobs 7y ago
    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.
- djrobstep 7y ago
  You might be interested in the service I'm building, which does a similar thing in a more structured way: https://djrobstep.com/ci-for-databases https://djrobstep.com/ci-for-databases
djrobstep 7y ago
I have a similar project called `schemainspect` - It creates a data structure representation of a postgres schema. I then use this data in my database diffing project `migra`, to autogenerate database migrations. You can also use schema diffing to test that your production and development databases match explicitly. Overall it's a much more flexible but rigorous approach than the old-fashioned rails/django migrations.
kristiandupont 7y ago
I've made something similar which I use for two things. 1) I create typescript types from my schema, and 2) I run a "linter" over my schema that validates a number of rules that I've created. It's been a great help honestly.

simbakhadder 7y ago

Is the expected use case to build generic queries on any type of database? Also why Javascript?

derefr 7y ago

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.

xemoka 7y ago
Right, and anything else should be in the pg_catalog; information_schema are just views over that. https://www.postgresql.org/docs/current/catalogs.html https://www.postgresql.org/docs/current/catalogs.html
stephen 7y ago
True! Good point and clarification. The nice aspect of pg-structure for me was getting all of that information_schema goodness already pulled out into hashes/DTOs from a 1-line "await pgStructure(...)" call. I.e. w/o pg-structure, I'd probably end up writing a mini/hacky version of it that did the same thing, "do these ~3-4 SQL queries and mash them into some nice hashes for my code generator to consume". Which is not terrible, but it's easier to just pull in another npm package. :-)

deleted 7y ago

[deleted]

miohtama 7y ago

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 7y ago
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...
isoprophlex 7y ago
This is brilliant! Thanks for sharing this, made my day!

rodw 7y ago

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 7y ago
Unix redirecting output uses the `>` symbol.
- rodw 7y ago
  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.