18 ms·

Letss gooo!! Supabase keeps on delivering things that I need. I am regularly surprised how bad the tooling is for SQL/databases. Especially that there is no de

Letss gooo!! Supabase keeps on delivering things that I need. I am regularly surprised how bad the tooling is for SQL/databases. Especially that there is no decent formatter that supports plpgsql, and doesn't absolutely mangle your queries (sometimes actually breaking your queries..). Best options atm are imo TablePlus and DataGrip, I have tried a bunch of options in vscode and although there are useful tools, it's all not really there. (Also excited to see what else they will release this launch week!)

bob1029 3y ago
> I am regularly surprised how bad the tooling is for SQL/databases Agreed. Even when using something open like SQLite, you will find a lot of difficulty in answering simple (to me) questions like "how do I parse this command text into an AST".
epgui 3y ago
It has tons of annoying quirks, but I couldn't imagine running a DBT project without it: https://github.com/sqlfluff/sqlfluff https://github.com/sqlfluff/sqlfluff
pjmlp 3y ago
Tooling is quite good on comercial space, now on free beer side, it is another matter.
tracker1 3y ago
FWIW, the MS SQL tooling is decent... Hopefully this goes as far to understand the db/schema/table structure of a connected database.
kshahkshah 3y ago
Check out Beekeeper Studio
- bbkane 3y ago
  Love Beekeeper Studio!
- rapind 3y ago
  A monthly fee? No thanks.
  - kshahkshah 3y ago
    Sheesh. It's open source, has a free version, and then a premium version so the bootstrapped, not-VC backed, creators avoid starving themselves and their families while working on the tool...
    - reactordev 3y ago
      Sadly, this is why this space has laggard for so long. Devs don’t want to pay for tooling but expect others to pay for their product.
      - Xymist 3y ago
        There is an important differentiator, though. I'm happy to pay _once_ for tooling as a product, and potentially for subsequent major versions. I am not, however, willing to be a source of MRR.
        gabereiser 3y ago
        I generally agree with this. There’s only one or two tools I’ll begrudgingly subscribe to with this model. I would be way more inclined to use the tools if it were a single purchase and I’ll upgrade when I feel like it. I’m looking at you JetBrains and Adobe…
        alex23478 3y ago
        JetBrains offer perpetual fallback licenses together with their yearly subscriptions: https://sales.jetbrains.com/hc/en-gb/articles/207240845-What-is-a-perpetual-fallback-license- https://sales.jetbrains.com/hc/en-gb/articles/207240845-What...
        3y ago
- codeptualize 3y ago
  Will try! It looks quite similar to table plus, any killer features I’m overlooking?
cpursley 3y ago
Agree, would love to see this built into TablePlus. What I really want is a state-based way to define my database. EdgeDB & Prism have made some progress here but they really only handle table changes. Really wish this were built into Postgres somehow (or any solid migration story). I want to be able to define my tables, views, functions and everything and the engine behind it automatically figures out all the necessary changes. For example, change a table (add a column or something) and it is smart enough to update any dependent views and functions. Including handling of data migrations.
- kiwicopple 3y ago
  fwiw, I personally am interested in this approach too[0]. I keep running into roadblocks around the ordering of events and some of the hairy issues around "destructive" actions (eg: renaming columns). i think we can get there, especially once we make progress with this LSP. There are other notable mentions in this space: Reshape: https://fabianlindfors.se/blog/schema-migrations-in-postgres-using-reshape/ https://fabianlindfors.se/blog/schema-migrations-in-postgres... Atlas: https://atlasgo.io/ https://atlasgo.io/ A "custom" example on GitLab: https://gitlab.com/delibrium/delibrium-postgrest/-/tree/master/database https://gitlab.com/delibrium/delibrium-postgrest/-/tree/mast... [0] https://github.com/kiwicopple/declarative-schemas https://github.com/kiwicopple/declarative-schemas
  - cpursley 3y ago
    Glad to hear that! We’re using Hasura (started before Supabase was a thing, otherwise we’d be using Supabase) and their Rails inspired migration is quite good. However, we’ve run into the problem of modifying functions and views that are dependent on other things (often views and custom functions). It’s a real bear to make changes - it involves dropping multiple views/functions (in the correct order) before applying the new migration - and then carefully adding it all back and hoping nothing breaks. I don’t like the solution of “stop using views and functions” as I prefer to use use the full power of Postgres (everything close to the data, less application level logic, great plugins). Not sure what approach should be taken to creat a state based migration solution, but it feels like the tech around Postgres, language servers, MML, etc has matured enough to solve the issue in an automated way.
j45 3y ago
SequelPro is quite decent for MySql. Tooling for Postgres seems to be more for the technical who prefer command line than not.
- jonquest 3y ago
  Sequel Ace is the forked and actively developed version of Sequel Pro. I don't think the owners of Sequel Pro ever announced it was abandoned, but the GitHub repo's lack of what appears to be any real development activity certainly suggests it is.
  - j45 3y ago
    That's great to know, thanks, I'll try it out. At this point my muscle memory is on the Sequel Pro shortcut keys, except I'm finding myself around Postgres more and more.
mbork_pl 3y ago
> I am regularly surprised how bad the tooling is for SQL/databases. This. I'd really like to have a working SQL pretty printer/formatter. Psql is great, but lacks autocompletion in several places. That said, a big shoutout to pspg (https://github.com/okbob/pspg https://github.com/okbob/pspg) - an excellent pager for psql (also for general csvs).
- WorldMaker 3y ago
  SQL pretty printers have probably the widest variety of possible "pretty" options to support. I've seen so many disagreements over the years on how the right way to print SQL is. For instance, I've gotten huge fights by saying that I prefer "Sentence case" (example: `Select * from someTable where someId in (select otherId from otherTable)`; only "top-level" introductory keywords get a single capital letter to make them easier to scan for like you are reading prose, kind of, and no semicolons unless entirely required such as the termination of a Merge statement) versus how many people still seem to be telnetting their SQL in 2023 from terminals or punch cards that mandate shouting SQL keywords in all caps everywhere. (ETA: Being a big fan of Merge statements for a bunch of ETL trivia is partly how I got to "Sentence case". Merge statements are tough to read at the best of times and eliminating extraneous casing changes as noise by using more lower-case than not was a very useful thing to me. I don't need to highlight keywords versus everything else in a modern syntax highlighting bath, so casing becomes useful for way finding. A lower-case "select" is in the middle of something else.)
  - tracker1 3y ago
    With A formatter in place, I'm generally much happier, even if I don't always agree with the structure.

bob1029 3y ago

> I am regularly surprised how bad the tooling is for SQL/databases Agreed. Even when using something open like SQLite, you will find a lot of difficulty in answering simple (to me) questions like "how do I parse this command text into an AST".

epgui 3y ago

It has tons of annoying quirks, but I couldn't imagine running a DBT project without it: https://github.com/sqlfluff/sqlfluff https://github.com/sqlfluff/sqlfluff

pjmlp 3y ago

Tooling is quite good on comercial space, now on free beer side, it is another matter.

tracker1 3y ago

FWIW, the MS SQL tooling is decent... Hopefully this goes as far to understand the db/schema/table structure of a connected database.

kshahkshah 3y ago

Check out Beekeeper Studio

bbkane 3y ago
Love Beekeeper Studio!
rapind 3y ago
A monthly fee? No thanks.
- kshahkshah 3y ago
  Sheesh. It's open source, has a free version, and then a premium version so the bootstrapped, not-VC backed, creators avoid starving themselves and their families while working on the tool...
  - reactordev 3y ago
    Sadly, this is why this space has laggard for so long. Devs don’t want to pay for tooling but expect others to pay for their product.
    - Xymist 3y ago
      There is an important differentiator, though. I'm happy to pay _once_ for tooling as a product, and potentially for subsequent major versions. I am not, however, willing to be a source of MRR.
      - gabereiser 3y ago
        I generally agree with this. There’s only one or two tools I’ll begrudgingly subscribe to with this model. I would be way more inclined to use the tools if it were a single purchase and I’ll upgrade when I feel like it. I’m looking at you JetBrains and Adobe…
        alex23478 3y ago
        JetBrains offer perpetual fallback licenses together with their yearly subscriptions: https://sales.jetbrains.com/hc/en-gb/articles/207240845-What-is-a-perpetual-fallback-license- https://sales.jetbrains.com/hc/en-gb/articles/207240845-What...
        3y ago
codeptualize 3y ago
Will try! It looks quite similar to table plus, any killer features I’m overlooking?

cpursley 3y ago

Agree, would love to see this built into TablePlus. What I really want is a state-based way to define my database. EdgeDB & Prism have made some progress here but they really only handle table changes. Really wish this were built into Postgres somehow (or any solid migration story). I want to be able to define my tables, views, functions and everything and the engine behind it automatically figures out all the necessary changes. For example, change a table (add a column or something) and it is smart enough to update any dependent views and functions. Including handling of data migrations.

kiwicopple 3y ago
fwiw, I personally am interested in this approach too[0]. I keep running into roadblocks around the ordering of events and some of the hairy issues around "destructive" actions (eg: renaming columns). i think we can get there, especially once we make progress with this LSP. There are other notable mentions in this space: Reshape: https://fabianlindfors.se/blog/schema-migrations-in-postgres-using-reshape/ https://fabianlindfors.se/blog/schema-migrations-in-postgres... Atlas: https://atlasgo.io/ https://atlasgo.io/ A "custom" example on GitLab: https://gitlab.com/delibrium/delibrium-postgrest/-/tree/master/database https://gitlab.com/delibrium/delibrium-postgrest/-/tree/mast... [0] https://github.com/kiwicopple/declarative-schemas https://github.com/kiwicopple/declarative-schemas
- cpursley 3y ago
  Glad to hear that! We’re using Hasura (started before Supabase was a thing, otherwise we’d be using Supabase) and their Rails inspired migration is quite good. However, we’ve run into the problem of modifying functions and views that are dependent on other things (often views and custom functions). It’s a real bear to make changes - it involves dropping multiple views/functions (in the correct order) before applying the new migration - and then carefully adding it all back and hoping nothing breaks. I don’t like the solution of “stop using views and functions” as I prefer to use use the full power of Postgres (everything close to the data, less application level logic, great plugins). Not sure what approach should be taken to creat a state based migration solution, but it feels like the tech around Postgres, language servers, MML, etc has matured enough to solve the issue in an automated way.

j45 3y ago

SequelPro is quite decent for MySql. Tooling for Postgres seems to be more for the technical who prefer command line than not.

jonquest 3y ago
Sequel Ace is the forked and actively developed version of Sequel Pro. I don't think the owners of Sequel Pro ever announced it was abandoned, but the GitHub repo's lack of what appears to be any real development activity certainly suggests it is.
- j45 3y ago
  That's great to know, thanks, I'll try it out. At this point my muscle memory is on the Sequel Pro shortcut keys, except I'm finding myself around Postgres more and more.

mbork_pl 3y ago

> I am regularly surprised how bad the tooling is for SQL/databases. This. I'd really like to have a working SQL pretty printer/formatter. Psql is great, but lacks autocompletion in several places. That said, a big shoutout to pspg (https://github.com/okbob/pspg https://github.com/okbob/pspg) - an excellent pager for psql (also for general csvs).

WorldMaker 3y ago
SQL pretty printers have probably the widest variety of possible "pretty" options to support. I've seen so many disagreements over the years on how the right way to print SQL is. For instance, I've gotten huge fights by saying that I prefer "Sentence case" (example: `Select * from someTable where someId in (select otherId from otherTable)`; only "top-level" introductory keywords get a single capital letter to make them easier to scan for like you are reading prose, kind of, and no semicolons unless entirely required such as the termination of a Merge statement) versus how many people still seem to be telnetting their SQL in 2023 from terminals or punch cards that mandate shouting SQL keywords in all caps everywhere. (ETA: Being a big fan of Merge statements for a bunch of ETL trivia is partly how I got to "Sentence case". Merge statements are tough to read at the best of times and eliminating extraneous casing changes as noise by using more lower-case than not was a very useful thing to me. I don't need to highlight keywords versus everything else in a modern syntax highlighting bath, so casing becomes useful for way finding. A lower-case "select" is in the middle of something else.)
- tracker1 3y ago
  With A formatter in place, I'm generally much happier, even if I don't always agree with the structure.