CODEWITHSUNDEEP
postgresqlsymfonydoctrine
user1032531
user1032531

Reputation: 26281

Should the table name "user" be used in a Symfony project?

I know this sounds like an "opinion" question, but I don't think it is.

Normally, I would consider not using "user" as a table name as it is a reserved word, and I would rather not bother having to deal with it when I write native SQL queries.

But I am not writing native SQL queries, but am having Symfony and the Doctrine ORM perform all the queries. And when I execute php bin/console make:user, Symfony prompts me to select the class name User which results with user as the table name. Furthermore, most of the tutorials I have read also use the name user. If using the name user is what is most common for other developers, I would rather stay consistent.

Most of the time, I don't have any issues, but every now and then, Doctrine crashes because it is querying the Postgres internal user table and not public.user. As a workaround, I tried adding * @ORM\Table(schema="public") to the User entity, but then when making migrations, it tries to duplicate the record resulting in errors. Maybe Symfony/Doctrine needs to be configured somewhere as using the public schema?

Thanks

Upvotes: 1

Views: 1558

Answers (2)

user1032531
user1032531

Reputation: 26281

Since my question was "Should the table name “user” be used in a Symfony project?", the following doesn't answer the question, but I am still posting it should it be helpful for others. Perhaps I will change the title to "How to use the table name “user” in a Doctrine project?", but not sure whether doing so is kosher.

I've since discovered other's experiencing issues resulting from this topic:

Also found the following at doctrine-adding-mapping:

Be careful not to use reserved SQL keywords as your table or column names (e.g. GROUP or USER). See Doctrine’s Reserved SQL keywords documentation for details on how to escape these. Or, change the table name with @ORM\Table(name="groups") above the class or configure the column name with the name="group_name" option.

Which directed me to quoting-reserved-words which directed me to escape the name using ticks.

  * @ORM\Table(name="`user`")

Upvotes: 5

Laurenz Albe
Laurenz Albe

Reputation: 246093

There is no table user in PostgreSQL.

Tutorials that use user as a table name are not trustworthy. However, if you consistently use double quotes, there should be no problem. Since you claim to have problems with native queries, you might have forgotten that occasionally.

One possible source of confusion is that there is a function named user in PostgreSQL. So if you use user in a context where a function is possible, you'll get a function call. If you use it with schema qualification, you will get an error that there is no such object. Otherwise, you will get a syntax error:

test=> SELECT user;
  user   
---------
 laurenz
(1 row)

test=> SELECT * FROM user;
  user   
---------
 laurenz
(1 row)

test=> TABLE user;
ERROR:  syntax error at or near "user"
LINE 1: TABLE user;
              ^
test=> SELECT * FROM public.user;
ERROR:  relation "public.user" does not exist
LINE 1: SELECT * FROM public.user;
                      ^

Upvotes: 0

Related Questions