Switching from SQL Server to postgreSQL, Snake Casing

Posts   
 
    
mprothme avatar
mprothme
User
Posts: 80
Joined: 05-Oct-2017
# Posted on: 25-Sep-2024 16:57:24   

We're currently in the process of migrating from SQL Server to Postgres. We followed the instructions given at the following post, specifically the following quoted section

If your postgresql database doesn't exists: Right click 'relational model data' in the project explorer and select 'Add relational model data storage for a database' and select the postgresql driver. then open 'Sync' and uncheck the sync with database for the sql server model data and click Perform tasks. The designer will now create tables from the entities in teh postgresql container. You can then export a DDL SQL script to create the database in the postgresql server.

and it's worked well so far! The only issue we're running into is that when we sync Postgres with the model, the output casing matches the casing of Entities and their related fields, which is Pascal case to match dotnet naming conventions.

From what we've read, the best practice in Postgres is to use snake lowercase for tables and columns (so an entity called CustomerData would be in a table called customer_data and a column called CustomerId would be customer_id).

Is there any way to have the sync process adjust the casing so we don't need to do that by hand (if that's even possible to adjust by hand)? I'd add that we don't need to maintain the connection to SqlServer so if there's an issue here because we have both the SqlServer and postgres connection in Relational Model Data then we can delete the SqlServer connection.

Walaa avatar
Walaa
Support Team
Posts: 14981
Joined: 21-Aug-2005
# Posted on: 26-Sep-2024 12:21:23   

In the Project Settings/Model First Development, please check the "Relational model data element construction".

Please give it a try.

mprothme avatar
mprothme
User
Posts: 80
Joined: 05-Oct-2017
# Posted on: 26-Sep-2024 22:16:43   

Awesome thanks for the info!

If anyone runs into this in the future I was able to achieve my desired results by doing the following:

  1. Open Project Settings
  2. Expanded Conventions > Entity Model > Model First Development
  3. Under Relational Model Data Element Construction
  4. Check "insert underscore at word break case sensitive dbs"
  5. Select AllLowerCase in the "Relational model data element name casing case sensitive dbs"