Casing issues when targeting postgres with IdentityContext #12

Open
julielerman opened this Issue Jan 7, 2017 · 8 comments

Projects

None yet

2 participants

@julielerman

Really impressive tool. I'm still having some issues that you may not have encountered yet. Here's one of them:

I am getting a lot of relation [tablename] does not exist when connecting to or querying my database.
ASP.net COre IdentityContext defines table names with camel casing and so code first creates them that way in the database.
I can open up and interact with the identity database in DataGrip with no issues.
When I connect to the database via vscode-database (btw I'm on a mac FWIW and using .NET Core 1.1) I get a ton of errors because it seems to force all queries to lower case.

Here are some screenshots to better explain:

camel cased databases are not listed:
image

If I start again (connect to postgres server) and type in my database at the prompt then it's reading it but I get these errors for every table in the database:

image

after I close them all, the status bar indicates I am connected to that database anyway:

image

here is that database in datagrip and I have no problems with interacting (tehre's no data yet so that's why the results are empty)

image

@julielerman

thought I would tag @roji here (not sure if that will work) since I'm using the provider he wrote (https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL) . Will send him a link just in case

@roji
roji commented Jan 7, 2017

Hey @julielerman!

This looks like one of the most common PostgreSQL issues around - PostgreSQL automatically folds unquoted identifiers (table/column names) to lowercase, so SELECT * FROM AspNetUsers becomes SELECT * FROM aspnetusers, which fails because that table doesn't exist.

Who's generating the SQL here? If it's vscode-database than it's going to have to be modified to quote identifiers, i.e. SELECT * FROM "AspNetUsers".

@julielerman

Thanks. Yeah. I have to do that in Datagrip IDE too. It works there but not in this tool. When I try to query using the quoted names, it still fails because of something under the covers. For example Select * from "AspNetUsers" fails with
relation "aspnetuserclaims" does not exist
So perhaps it's inside the extension

For the authors: here are the error details:

PostgreSQL Error: error: relation "aspnetuserclaims" does not exist
at Connection.parseE (/Users/julialerman/.vscode/extensions/bajdzis.vscode-database-1.0.11/node_modules/pg/lib/connection.js:554:11)
at Connection.parseMessage (/Users/julialerman/.vscode/extensions/bajdzis.vscode-database-1.0.11/node_modules/pg/lib/connection.js:381:17)
at Socket. (/Users/julialerman/.vscode/extensions/bajdzis.vscode-database-1.0.11/node_modules/pg/lib/connection.js:117:22)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:176:18)
at Socket.Readable.push (_stream_readable.js:134:10)
at TCP.onread (net.js:543:20)

@julielerman

It's not so easy to change the casing of tables created via identitydbcontext. will keep digging.

@julielerman

I found a work around. The internal identity context code is naming the tables but those are not based on the entity type names. So I have to get those names after they've been set and make them lower.
This simply forces the postgre database tables to be lower case. I could not see anything in your code base that was forcing the queries to be lower case. Did I miss something?

@julielerman

I finally realized that it was better to ensure that the database tables followed postgres convention. I wrote a blog post about overriding the camel-cased aspnet identity table names here: http://thedatafarm.com/data-access/ef-core-postgres-and-the-camel-cased-identity-tables/. So I'll slose this issue since it is not the fault of the extension.

@julielerman julielerman closed this Jan 8, 2017
@roji
roji commented Jan 8, 2017 edited

@julielerman PostgreSQL doesn't actually have a convention (or an actual problem) with camel-case - it just requires that non-lowercase identifiers be quoted... That isn't very different from how SQL Server expects identifiers with spaces to be enclosed in brackets.

In other words, while it may be a good temporary workaround, you should not be forced to change any table names - whoever's producing SQL here has the responsibility of applying to correct PostgreSQL rules. I'd leave this open for the extension to be fixed.

@julielerman

thanks @roji ...my lack of postgres know-how is showing! I'll modify my blog post and will have to think about how to handle this in my efcore course.

@julielerman julielerman reopened this Jan 8, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment