Hacker News Reader

Comment by nick_ | Hacker News Reader

Why is snake_case recommended? What advantage(s) does it provide?

It's pretty typical to signal word boundaries in some way.

Thinking about the most general norms across database vendors, consider a table name for User Posts: You could write that as UserPosts, but in a lot of databases you're going to end up with userposts, since typically SQL and identifiers are naturally case insensitive; you can preserve case by quoting that "UserPosts", but now you have extra characters just to get the case sensitivity. Finally, you can use snake case: user_posts... true you still end up with extra characters, but ultimately I think a little less shifting. Snake case also keeps you in line with much historical precedence in writing names for the database as well.

Of course, but using one delimiter/convention for more than one things makes it hard/impossible to disambiguate them. With foo_bar_baz, you can't easily tell what's what. `"FooBar_Baz"` or maybe `foo_bar__baz` is clear.

One reason to stick to a restricted naming scheme for tables is that table names cannot be parametrized. This means you need to implement escaping manually in tools/scripts where table names are not hardcoded. Snake casing creates word boundaries that do not rely on casing and thus do not need to be quoted, there for your manual escaping/sanitization is greatly simplified (lowercase ascii characters, numbers and underscore). I’ve found this reduces the number of footguns by one in databases maintained by a team with varying degrees of familiarity with RDBMS.

Postgres stores and refers to your tables in lowercase (even though you can reference them case insensitively) so naming conventions that involve case don't really work.

Some ORMs will use a "camelCaseNameInQuotes", in which case PostgreSQL will preserve the case (as will other databases), and manual querying will require quotes everywhere.

Alternatively, you can use camelCase without quotes in your SQL, and ignore the fact that Postgres will lowercase it.

You just need to quote your identifiers with double quotes, e.g.:

    SELECT * FROM "MyTable"

You can even use reserved SQL keywords in table/column names as long as they are double-quoted.

With quotes you can even use spaces in table and column names.

Reply on news.ycombinator.com