3 ms·

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

by nick_ 2y ago

Boxxed 2y ago
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.
- Kwpolska 2y ago
  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.
- levkk 2y ago
  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.
  - Izkata 2y ago
    With quotes you can even use spaces in table and column names.
sbuttgereit 2y ago
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.
- nick_ 2y ago
  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.
catlifeonmars 2y ago
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.

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

by nick_ 2y ago

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

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.

Kwpolska 2y ago
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.
levkk 2y ago
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.
- Izkata 2y ago
  With quotes you can even use spaces in table and column names.

sbuttgereit 2y ago

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.

nick_ 2y ago
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.

catlifeonmars 2y ago

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.