[HN Gopher] Bringing psql's \d to your web browser
___________________________________________________________________
Bringing psql's \d to your web browser
Author : gmac
Score : 73 points
Date : 2024-05-08 16:14 UTC (1 days ago)
(HTM) web link (neon.tech)
(TXT) w3m dump (neon.tech)
| darby_eight wrote:
| This seems really cool, but also a nightmare to maintain. What
| are the plans for keeping this in sync with the upstream
| codebase?
| sroussey wrote:
| uses emscripten to compile the C to WASM, so it's automated.
| gmac wrote:
| 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 wrote:
| 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
| ivanjermakov wrote:
| 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 wrote:
| I'm not sure I understand how to use that.
| theogravity wrote:
| Yeah, I don't really get where / how I would use this. The
| documentation is really lacking on outlining the usage / use-
| case.
| gmac wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| Certainly there's a decently long tail of rarely-used
| commands, obscure features of commands, and multiple code
| paths supporting multiple server versions.
| matharmin wrote:
| If you just need one or two of the commands, just using those
| queries directly is easy.
|
| If you want to support _all_ of them, including handling all
| the different Postgres versions, it becomes quite a lot to
| implement.
| gmac wrote:
| 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
| dang wrote:
| Ok, we've changed the URL to that from
| https://github.com/neondatabase/psql-describe above. Thanks!
| stuaxo wrote:
| 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 wrote:
| Anyone using Neon in prod? How is it?
| antifa wrote:
| I wish \d allowed sorting alphabetically.
___________________________________________________________________
(page generated 2024-05-09 23:02 UTC)