4 ms·

Fair enough. The vast majority of SQL queries in my codebase likewise was embedded in another language as a string. Yet I instantly became happier when I starte

Fair enough. The vast majority of SQL queries in my codebase likewise was embedded in another language as a string. Yet I instantly became happier when I started lowercasing everything. I also quote as few identifiers as possible. It's a matter of a clean look, which for me leads to clear thought. I will agree with you, though, that indentation is critical. But I have taken it one step further and reaped a boon with a more advanced technique: I extract as much as I can of those multi-line SQL statements into database views and, if absolutely necessary, functions. I define these in .sql files, so first of all: syntax highlighting! The strings embedded in my code therefore become mostly one-liners: select * from view where a = ? etc. I version-control the SQL files right along with the normal codebase. Migrations aren't complicated for me, the way that some people complain about them. Then again, I am super-comfortable writing raw SQL. Especially with Postgres, which lets you wrap BEGIN and ROLLBACK around definitions and redefinitions of tables, views, functions --- all DDL --- it's very safe to test and idempotent to run over and over.

eyelidlessness 6y ago
This is something I've seriously considered, I just worry that one more layer of indirection creates one more opportunity for problems. (Also being maybe overly publicly blunt, I work with a number of juniors who don't have a great track record for following or even taking interest in why things are structured the way they are, and who also don't have great comfort with SQL.) RE version control: yes, everything that interfaces with the database is in version control. RE migrations: I also don't find them confounding, and I've mandated that my team write them all in raw SQL rather than reusing abstractions that were available in our environment, because what I discovered was that migrations were being altered unexpectedly over time where imported abstractions were changing. Everyone has benefited from this. RE transactions: my only complaint is that (AFAIK, and would happily be corrected if I'm wrong), `BEGIN`, any operation, and `END` are separate statements. When I'm experimenting before testing a full migration, I'll often break down smaller problems and test them within a transaction in my preferred GUI (Postico). Which by default runs the statement where the cursor is placed, and only runs multiple statements if you (remember to) select them. I would love to be able to wrap an entire statement (or set of statements if need be) in a single `BEGIN ... RETURNING * ... ROLLBACK` statement.
- combatentropy 6y ago
  I just use psql. psql> \i changes.sql The file, changes.sql in this case, might look like this: begin; alter table t1 add column c ...; alter table t2 drop column d ...; alter table t3 alter column e ...; drop view if exists v; create view v as select ... ; rollback; I save the file in version control with the rollback statement, for safety. When I am ready to run it, I temporarily change "rollback" to "commit". If I wanted to test out just some of the statements, I would comment out the others. EDIT: You can inspect the changes in flight by inserting a select-statement: begin; alter table t1 alter column c ...; select * from t1 where ... ; rollback; (Interesting, Hacker News's user interface puts no reply link below your comment. So I could not reply directly. I guess it allows only so many nested replies. This is probably an optimization for readability or to avoid flamewars).
  - eyelidlessness 6y ago
    That's great, but the statefulness of trying something is still a problem. It would still be nice to be able to wrap a whole change or set of changes with a guarantee that I'll be able to view the outcome without a state change in the database. This nicety is compounded by the fact that I'm often working with datasets that take a few hours to get set up before I can even evaluate. There are a ton of other guard rails I can set up to make that turnaround less shitty, but even cloning a backup database in the worst case is painful. There's no reason the expressiveness of SQL can't embrace complex expressions as a single statement to encapsulate a whole transaction in a single expression. CTEs already do most of that, they just don't have any transactional capability. Edit: I also don't just use psql, because having normal editing controls that I'm used to in my operating system is muscle memory I leverage and also error prone if I try to use the wrong tool with the wrong muscle memory. I know how to move a caret around every single Mac app, but it works differently the moment I enter anything other than emacs in my terminal.
  - eyelidlessness 6y ago
    > You can inspect the changes in flight by inserting a select-statement All true, but you still can't express a transaction as a single expression. > Interesting, Hacker News's user interface puts no reply link below your comment. So I could not reply directly. I guess it allows only so many nested replies. This is probably an optimization for readability or to avoid flamewars They just hide the reply link at a certain depth of threading. You can navigate to the parent comment (by parent link or datestamp depending) and reply.

eyelidlessness 6y ago

This is something I've seriously considered, I just worry that one more layer of indirection creates one more opportunity for problems. (Also being maybe overly publicly blunt, I work with a number of juniors who don't have a great track record for following or even taking interest in why things are structured the way they are, and who also don't have great comfort with SQL.) RE version control: yes, everything that interfaces with the database is in version control. RE migrations: I also don't find them confounding, and I've mandated that my team write them all in raw SQL rather than reusing abstractions that were available in our environment, because what I discovered was that migrations were being altered unexpectedly over time where imported abstractions were changing. Everyone has benefited from this. RE transactions: my only complaint is that (AFAIK, and would happily be corrected if I'm wrong), `BEGIN`, any operation, and `END` are separate statements. When I'm experimenting before testing a full migration, I'll often break down smaller problems and test them within a transaction in my preferred GUI (Postico). Which by default runs the statement where the cursor is placed, and only runs multiple statements if you (remember to) select them. I would love to be able to wrap an entire statement (or set of statements if need be) in a single `BEGIN ... RETURNING * ... ROLLBACK` statement.

combatentropy 6y ago
I just use psql. psql> \i changes.sql The file, changes.sql in this case, might look like this: begin; alter table t1 add column c ...; alter table t2 drop column d ...; alter table t3 alter column e ...; drop view if exists v; create view v as select ... ; rollback; I save the file in version control with the rollback statement, for safety. When I am ready to run it, I temporarily change "rollback" to "commit". If I wanted to test out just some of the statements, I would comment out the others. EDIT: You can inspect the changes in flight by inserting a select-statement: begin; alter table t1 alter column c ...; select * from t1 where ... ; rollback; (Interesting, Hacker News's user interface puts no reply link below your comment. So I could not reply directly. I guess it allows only so many nested replies. This is probably an optimization for readability or to avoid flamewars).
- eyelidlessness 6y ago
  That's great, but the statefulness of trying something is still a problem. It would still be nice to be able to wrap a whole change or set of changes with a guarantee that I'll be able to view the outcome without a state change in the database. This nicety is compounded by the fact that I'm often working with datasets that take a few hours to get set up before I can even evaluate. There are a ton of other guard rails I can set up to make that turnaround less shitty, but even cloning a backup database in the worst case is painful. There's no reason the expressiveness of SQL can't embrace complex expressions as a single statement to encapsulate a whole transaction in a single expression. CTEs already do most of that, they just don't have any transactional capability. Edit: I also don't just use psql, because having normal editing controls that I'm used to in my operating system is muscle memory I leverage and also error prone if I try to use the wrong tool with the wrong muscle memory. I know how to move a caret around every single Mac app, but it works differently the moment I enter anything other than emacs in my terminal.
- eyelidlessness 6y ago
  > You can inspect the changes in flight by inserting a select-statement All true, but you still can't express a transaction as a single expression. > Interesting, Hacker News's user interface puts no reply link below your comment. So I could not reply directly. I guess it allows only so many nested replies. This is probably an optimization for readability or to avoid flamewars They just hide the reply link at a certain depth of threading. You can navigate to the parent comment (by parent link or datestamp depending) and reply.