Postgres tables are quoted when created

[matthewmueller]

Based on: https://prisma.slack.com/archives/CKQTGR6T0/p1563892709024100

I can confirm that with:

CREATE TABLE "public"."Post"("id" text NOT NULL  ,"createdAt" timestamp(3) NOT NULL  ,"updatedAt" timestamp(3) NOT NULL DEFAULT '1970-01-01 00:00:00' ,"published" boolean NOT NULL DEFAULT false ,"title" text NOT NULL DEFAULT '' ,"content" text   ,PRIMARY KEY ("id"));

You can only select the table via select * from "Post".
This doesn't work: select * from Post.

We need to be smarter about what tables need quotes and what tables do not.

[mikeslade]

@matthewmueller this behavior is found in the fields on the tables as well.

CREATE TABLE "public"."Agent"("id" text NOT NULL ,"firstName" text NOT NULL DEFAULT '' ,"middleName" text ,"lastName" text NOT NULL DEFAULT '' ,"suffix" text ,"recordStatus" text NOT NULL DEFAULT 'ACTIVE' ,"createdAt" timestamp(3) NOT NULL ,"updatedAt" timestamp(3) NOT NULL DEFAULT '1970-01-01 00:00:00' ,PRIMARY KEY ("id"));

None of these are reserved words in Postgres, but result in table creation like depicted below:

Which then requires queries like this to fetch items:

SELECT "firstName", "lastName", suffix FROM "Agent"

Looking further, it seems that Postgres just forces everything to lowercase if it is not surrounded by quotes. Given that the docs recommend PascalCase for models and camelCase for field names, I understand why the team simply wrapped everything in quotes. It is the only way to preserve the case-sensitive formatting in the table names and the field names. This does create some inconsistency for field names without capital letters, since Postgres drops the quotes on its own in that scenario.

[matthewmueller]

Leaving this here as well for reference: https://lerner.co.il/2013/11/30/quoting-postgresql/

[matthewmueller]

It turns out postgres is a bit weird and if we want capitalized models (to match the schema), we'll need to use double quotes. It looks like ActiveRecord also does it this way.

Would a possible solution be to lowercase everything? It would mean that BlogTag would become blogtag in the database but that may be ok.