7 ms·

This solves a major problem that I built an npm package called "pgstrap"[1] for. It generates a "database structure" directory so that my database schema is ava

This solves a major problem that I built an npm package called "pgstrap"[1] for. It generates a "database structure" directory so that my database schema is available to LLMs (it also makes code review easier because you can see the changes to various tables). So I have a SQL file for each table in my database, neatly organized into directories for each schema. Rails has a similar idea with schema.rb I'm not sure whether or not it's better to have your editor database-aware or to have your codebase have appropriate context committed. On one hand, less generated code/artifacts make for a cleaner codebase. On the other hand, not everyone uses VC Code or will know how to use this integration. Database browser GUIs have never really had a single winner. That said, VS Code does have enough dominance to potentially make themselves "the standard way to view a database in development" [1] https://github.com/seveibar/pgstrap https://github.com/seveibar/pgstrap

netghost 1y ago
That seems like a really pragmatic tool, thanks for sharing it! I'm curious, do you output triggers, store procedures, and such? Many tools seem to stop after you've defined tables, columns, and indices, but I'd love some better tooling to make use of the rest of the DB's features.
- seveibar 1y ago
  Yep! It basically runs pg_dump and categorizes all of the output into different files so it should be comprehensive. I think there's `functions/function_name.sql`, `misc.sql`, `triggers.sql` etc.
  - what 1y ago
    You built it but you don’t know what it outputs?
  - netghost 1y ago
    Thanks! And curses. Now I'm going down another rabbit hole rehashing ways to interface with my database in a side project that is doomed to never get past the "there has to be a better way to…" phase ;)
jsmith99 1y ago
I just use a MCP server (with copilot or cline) that has a read only login to my database.
- BoorishBears 1y ago
  Which is strictly worse than just giving the LLM access to the source of truth for the database. You're adding a round trip to the database and the LLM and inserting a tool call in the conversation before it even starts generating any code. And the reference Postgres MCP implementation doesn't include Postgres types or materialized views, and is one of the most widely used packages: Zed.dev's MCP server for example, is seemingly just a port of it and has the same problem.
  - fwip 1y ago
    MCP also gives the LLM access to your example data, which can add clarity beyond what your schema alone provides.
  - tempaccount420 1y ago
    I don't see how a round trip of <500ms, which is equivalent to maybe 50 tokens, is worse than including many thousands more extra tokens in the prompt, just in case they might be useful. Not to mention the context fatigue. If designed well - by suspending generation in memory and inserting a <function_result>, without restarting generation and fetching cache from disk - the round trip/tool call is better (costs the equivalent of 50 tokens for waiting + function_result tokens).
    - BoorishBears 1y ago
      You're dealing with the full TTFT x2 + the tokens all the prompts of all your MCPs before you even get to that round trip to the DB. And you don't have to wonder about "if designed well": the reference implementation that's getting 20k downloads a week and getting embedded in downstream editors is is not designed well and will make the round trip every time and still not give the LLM the full information of the table. Most MCP implementations are crappy half-assed implementations in similar fashion because everyone was rushing to post how they added <insert DB/API/Data Source> to MCP. And if you're worried about "context fatigue" (you mean LLMs getting distracted by relevant information...), you should 100% prefer a well known schema format to N MCP prompt definitions with tool usage instructions that weren't even necessarily tuned for the LLM in question. LLMs are much more easily derailed by the addition of extra tools and having to reason about when to call them and the results of calling them, than they are a prompt caching friendly block of tokens with easy to follow meaning.
semiquaver 1y ago
I’m confused. Isn’t including the canonical state of the database schema in version control along with all the migrations that brought it to that point a completely standard part of every web framework?
- wredcoll 1y ago
  That "works" for about as long as you have <10 employees and <3 customers or so. After that the railsapp doesn't get to be the sole owner of the db.
  - sbarre 1y ago
    Do you have multiple separate apps that can change a shared DB schema? How do you keep that all in sync across your apps?
    - int_19h 1y ago
      In situations like these, the database admins are the ones responsible for the schema; the apps are mere users.
    - scott_w 1y ago
      So usually one is the “main” but, personally speaking, I’d just say “don’t do it,” for the obvious issues I suspect you’re aware of ;-) By “don’t do it,” I mean having multiple apps talk to one DB schema.
      - throwaway7783 1y ago
        "don't do it" is the right answer. Others have pointed it out as well, many large SaaS companies I worked with, have had apps owning their databases. Anyone else needs anything - use APIs (and ETL if you need everything)
        ecb_penguin 1y ago
        It's definitely not the right answer. It's actually the completely wrong answer. Services are slow, restrictive, and don't enjoy the benefits of an actual DBMS, like transactions. You also add additional dependencies and failure points.
zX41ZdbW 1y ago
Wow, this is precisely how ClickHouse stores table metadata! A set of .sql files in the directories, corresponding to databases.

netghost 1y ago

That seems like a really pragmatic tool, thanks for sharing it! I'm curious, do you output triggers, store procedures, and such? Many tools seem to stop after you've defined tables, columns, and indices, but I'd love some better tooling to make use of the rest of the DB's features.

seveibar 1y ago
Yep! It basically runs pg_dump and categorizes all of the output into different files so it should be comprehensive. I think there's `functions/function_name.sql`, `misc.sql`, `triggers.sql` etc.
- what 1y ago
  You built it but you don’t know what it outputs?
- netghost 1y ago
  Thanks! And curses. Now I'm going down another rabbit hole rehashing ways to interface with my database in a side project that is doomed to never get past the "there has to be a better way to…" phase ;)

jsmith99 1y ago

I just use a MCP server (with copilot or cline) that has a read only login to my database.

BoorishBears 1y ago
Which is strictly worse than just giving the LLM access to the source of truth for the database. You're adding a round trip to the database and the LLM and inserting a tool call in the conversation before it even starts generating any code. And the reference Postgres MCP implementation doesn't include Postgres types or materialized views, and is one of the most widely used packages: Zed.dev's MCP server for example, is seemingly just a port of it and has the same problem.
- fwip 1y ago
  MCP also gives the LLM access to your example data, which can add clarity beyond what your schema alone provides.
- tempaccount420 1y ago
  I don't see how a round trip of <500ms, which is equivalent to maybe 50 tokens, is worse than including many thousands more extra tokens in the prompt, just in case they might be useful. Not to mention the context fatigue. If designed well - by suspending generation in memory and inserting a <function_result>, without restarting generation and fetching cache from disk - the round trip/tool call is better (costs the equivalent of 50 tokens for waiting + function_result tokens).
  - BoorishBears 1y ago
    You're dealing with the full TTFT x2 + the tokens all the prompts of all your MCPs before you even get to that round trip to the DB. And you don't have to wonder about "if designed well": the reference implementation that's getting 20k downloads a week and getting embedded in downstream editors is is not designed well and will make the round trip every time and still not give the LLM the full information of the table. Most MCP implementations are crappy half-assed implementations in similar fashion because everyone was rushing to post how they added <insert DB/API/Data Source> to MCP. And if you're worried about "context fatigue" (you mean LLMs getting distracted by relevant information...), you should 100% prefer a well known schema format to N MCP prompt definitions with tool usage instructions that weren't even necessarily tuned for the LLM in question. LLMs are much more easily derailed by the addition of extra tools and having to reason about when to call them and the results of calling them, than they are a prompt caching friendly block of tokens with easy to follow meaning.

semiquaver 1y ago

I’m confused. Isn’t including the canonical state of the database schema in version control along with all the migrations that brought it to that point a completely standard part of every web framework?

wredcoll 1y ago
That "works" for about as long as you have <10 employees and <3 customers or so. After that the railsapp doesn't get to be the sole owner of the db.
- sbarre 1y ago
  Do you have multiple separate apps that can change a shared DB schema? How do you keep that all in sync across your apps?
  - int_19h 1y ago
    In situations like these, the database admins are the ones responsible for the schema; the apps are mere users.
  - scott_w 1y ago
    So usually one is the “main” but, personally speaking, I’d just say “don’t do it,” for the obvious issues I suspect you’re aware of ;-) By “don’t do it,” I mean having multiple apps talk to one DB schema.
    - throwaway7783 1y ago
      "don't do it" is the right answer. Others have pointed it out as well, many large SaaS companies I worked with, have had apps owning their databases. Anyone else needs anything - use APIs (and ETL if you need everything)
      - ecb_penguin 1y ago
        It's definitely not the right answer. It's actually the completely wrong answer. Services are slow, restrictive, and don't enjoy the benefits of an actual DBMS, like transactions. You also add additional dependencies and failure points.

zX41ZdbW 1y ago

Wow, this is precisely how ClickHouse stores table metadata! A set of .sql files in the directories, corresponding to databases.