5 ms·

Bringing psql’s \d to your web browser

76 points by gmac 2y ago

darby_eight 2y ago
This seems really cool, but also a nightmare to maintain. What are the plans for keeping this in sync with the upstream codebase?
- sroussey 2y ago
  uses emscripten to compile the C to WASM, so it’s automated.
- gmac 2y ago
  OP (and library author) here. This is a fair point, and one I freely admit to in the accompanying blog post. It’s probably not crucial to sync more than once per major Postgres version, but we’re still figuring out how we’ll deal with this.
williamjackson 2y ago
The fact that this is not a manual reimplementation, but a "transpilation" of the psql source from C to JavaScript, makes this super interesting to me. From the Postgres master branch (17devel), we take exec_command_d, exec_command_list and exec_command_sf_sv from command.c, and all of describe.c and sql_help.c, from src/bin/psql. We use plenty of RegExp search-and-replace to turn this C code into valid JS syntax. We implement some C library functions, such as strlen and strchr, and some Postgres support functions, such as printTable and printQuery, in JavaScript. Read more here: https://neon.tech/blog/bringing-psqls-d-to-your-web-browser https://neon.tech/blog/bringing-psqls-d-to-your-web-browser
- ivanjermakov 2y ago
  When I started my programming journey I thought I could just "translate" Pascal to C++ just with search/replace and minor adjustments. Turns out sometimes it's the best approach :)
codesnik 2y ago
I'm not sure I understand how to use that.
- theogravity 2y ago
  Yeah, I don't really get where / how I would use this. The documentation is really lacking on outlining the usage / use-case.
- gmac 2y ago
  It’s used in Neon’s web-based console to support \d and friends alongside regular SQL queries. You might well never need to use it, but if you’re building an online query editor it could be very handy!
arp242 2y ago
I always thought that most \-commands in psql were just frontends for queries? For example: % psql db_name (1)=# \set ECHO_HIDDEN (1)=# \d ********* QUERY ********** SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' TH EN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partit ioned index' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam WHERE c.relkind IN ('r','p','v','m','S','f','') AND n.nspname <> 'pg_catalog' AND n.nspname !~ '^pg_toast' AND n.nspname <> 'information_schema' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; ************************** List of relations Schema │ Name │ Type │ Owner ────────┼────────────────────────────────────────┼──────────┼──────── ... I re-implemented quite a bit of that in a PostgreSQL management tool I built, based just on the ECHO_HIDDEN queries. Never even had to look at any C code. I'm not sure if I entirely follow what translating C to JS gives you, other than a bit of (fairly simple) parsing of \cmd and flags? I didn't look too carefully at the code, so maybe I'm missing something.
- meepmorp 2y ago
  Yeah, I don't understand why they'd translate the C code instead of just writing catalog/info schema queries, which seems easier AND more educational.
- gmac 2y ago
  Some commands issue multiple queries and include logic for stitching the results into a nice table. There’s not a one-to-one mapping from commands to queries by any means.
  - arp242 2y ago
    Yeah, that's right; but the "glue code" I've had to write for this has been really minimal. Certainly less effort than translating C to JavaScript with regexps (that on its own is something of an accomplishment). I didn't port everything so maybe I missed some more complex bits. I mean, if this works well for you then that's grand; just wanted to point out to others that \-commands are typically very "non-magic" and you don't need to go the route of translating C code.
gmac 2y ago
OP and library author here. You’ll find the background to this in this blog post: https://neon.tech/blog/bringing-psqls-d-to-your-web-browser https://neon.tech/blog/bringing-psqls-d-to-your-web-browser
- dang 2y ago
  Ok, we've changed the URL to that from https://github.com/neondatabase/psql-describe https://github.com/neondatabase/psql-describe above. Thanks!
stuaxo 2y ago
Thw convertet is nice, I could imagine it being it's own library, with the generator, and a release including the generated code every postures release.
redwood 2y ago
Anyone using Neon in prod? How is it?
antifa 2y ago
I wish \d allowed sorting alphabetically.

76 points by gmac 2y ago

This seems really cool, but also a nightmare to maintain. What are the plans for keeping this in sync with the upstream codebase?

sroussey 2y ago
uses emscripten to compile the C to WASM, so it’s automated.
gmac 2y ago
OP (and library author) here. This is a fair point, and one I freely admit to in the accompanying blog post. It’s probably not crucial to sync more than once per major Postgres version, but we’re still figuring out how we’ll deal with this.

williamjackson 2y ago

The fact that this is not a manual reimplementation, but a "transpilation" of the psql source from C to JavaScript, makes this super interesting to me. From the Postgres master branch (17devel), we take exec_command_d, exec_command_list and exec_command_sf_sv from command.c, and all of describe.c and sql_help.c, from src/bin/psql. We use plenty of RegExp search-and-replace to turn this C code into valid JS syntax. We implement some C library functions, such as strlen and strchr, and some Postgres support functions, such as printTable and printQuery, in JavaScript. Read more here: https://neon.tech/blog/bringing-psqls-d-to-your-web-browser https://neon.tech/blog/bringing-psqls-d-to-your-web-browser

ivanjermakov 2y ago
When I started my programming journey I thought I could just "translate" Pascal to C++ just with search/replace and minor adjustments. Turns out sometimes it's the best approach :)

codesnik 2y ago

I'm not sure I understand how to use that.

theogravity 2y ago
Yeah, I don't really get where / how I would use this. The documentation is really lacking on outlining the usage / use-case.
gmac 2y ago
It’s used in Neon’s web-based console to support \d and friends alongside regular SQL queries. You might well never need to use it, but if you’re building an online query editor it could be very handy!

arp242 2y ago

I always thought that most \-commands in psql were just frontends for queries? For example: % psql db_name (1)=# \set ECHO_HIDDEN (1)=# \d ********* QUERY ********** SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' TH EN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partit ioned index' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam WHERE c.relkind IN ('r','p','v','m','S','f','') AND n.nspname <> 'pg_catalog' AND n.nspname !~ '^pg_toast' AND n.nspname <> 'information_schema' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; ************************** List of relations Schema │ Name │ Type │ Owner ────────┼────────────────────────────────────────┼──────────┼──────── ... I re-implemented quite a bit of that in a PostgreSQL management tool I built, based just on the ECHO_HIDDEN queries. Never even had to look at any C code. I'm not sure if I entirely follow what translating C to JS gives you, other than a bit of (fairly simple) parsing of \cmd and flags? I didn't look too carefully at the code, so maybe I'm missing something.

meepmorp 2y ago
Yeah, I don't understand why they'd translate the C code instead of just writing catalog/info schema queries, which seems easier AND more educational.
gmac 2y ago
Some commands issue multiple queries and include logic for stitching the results into a nice table. There’s not a one-to-one mapping from commands to queries by any means.
- arp242 2y ago
  Yeah, that's right; but the "glue code" I've had to write for this has been really minimal. Certainly less effort than translating C to JavaScript with regexps (that on its own is something of an accomplishment). I didn't port everything so maybe I missed some more complex bits. I mean, if this works well for you then that's grand; just wanted to point out to others that \-commands are typically very "non-magic" and you don't need to go the route of translating C code.

gmac 2y ago

OP and library author here. You’ll find the background to this in this blog post: https://neon.tech/blog/bringing-psqls-d-to-your-web-browser https://neon.tech/blog/bringing-psqls-d-to-your-web-browser

dang 2y ago
Ok, we've changed the URL to that from https://github.com/neondatabase/psql-describe https://github.com/neondatabase/psql-describe above. Thanks!

stuaxo 2y ago

Thw convertet is nice, I could imagine it being it's own library, with the generator, and a release including the generated code every postures release.

redwood 2y ago

Anyone using Neon in prod? How is it?

antifa 2y ago

I wish \d allowed sorting alphabetically.