undefined | Better HN

0 pointseyelidlessness6y ago0 comments

(The rest of your comment is very compelling, just have a nitpick.)

> If it helps, you don't really have to type SQL key words in all capitals ;) I stopped years ago.

I continue to urge my team to capitalize SQL keywords. This is because the vast majority of SQL queries in our codebases are embedded in another language, as a string. Syntax highlighting is not available (I know there are some tools for this in some environments for some host languages, but it's not widely available or even remotely a solved problem). Static analysis tools for this scenario are generally hard to come by. Every syntactical hint is a godsend for reading and comprehending these queries. I also encourage quoting every identifier even if it isn't strictly required, and extensively using whitespace to make a query's structure more apparent.

If I were writing and reading SQL under better circumstances, it's quite likely I would have different preferences.

0 comments

3 comments · 1 top-level

combatentropy6y ago· 2 in thread

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.

eyelidlessnessOP6y 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.

combatentropy6y 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).

2 more replies

j / k navigate · click thread line to collapse