Use double quotes when referencing database objects

[12cowdog72]

Related to #739

Postgres allows certain reserved keywords and special characters (question marks, for example) to be used in table/column/etc. names, but they must be escaped in double quotes.

The following migration:

create table_for(Mail) do
  add from : String
  add read? : Bool
end

ideally should generate the following SQL:

create table "mails" (
  "from" text,
  "read?" boolean
);

but since it does not add double quotes, the generated SQL statements will cause a syntax error in Postgres. This also happens in queries and such which could make it impossible to use Avram with certain existing databases without renaming every offending field.

[jwoertink]

I had no idea that you could add characters to table/column names... That's a pretty neat use case for Bool; however, I feel a bit hesitant about its use for anything else. This would allow someone to do add username? : String which would generate methods like UserQuery.new.username?("..."), or make passing in named args to save operations like SaveUser.create("username?": "...").

I feel like using this could lead to both bad code, and lots of weird edge cases we'd have to consider. It may just be easier to disallow using any non-alphanumeric character for these. Though, you may have a database that already has a column like this, in which case we need to implement https://github.com/luckyframework/avram/issues/378. I wonder if Ecto is able to handle this? 🤔

Thanks for bringing this up! Definitely something new to consider!

[robacarp]

The decision of using punctuation in column names aren't always up to the app developer -- sometimes the database is inherited. Regardless, I think it would be prudent to reference column names with quotes regardless of the decision to let people use punctuation in their column names.

[jwoertink]

Yeah, that makes sense. I guess we can start there. That will at least fix some of the SQL issues, and doesn't really hurt anything for simple DBs.

[matthewmcgarvey]

I think we already have a way to specify a column name that's different from the model's attribute, and same for the table name, so there is workarounds. We just need to also quote it, I guess