Schema error when runnning update-database command to create identity tables

JORGE MALDONADO BARRERA 211 Reputation points
2022-02-16T16:07:32.15+00:00

Hi,

I am manually implementing authentication to a new ASP.NET 6 project. What I mean is that I am selecting "None" when I am asked "Authentication type" when creating the project. The reason is that I want to avoid mixing Razor Pages with my MVC project so I will be writing the necessary code for this part of the site. The database I use is PostgreSQL so I am following this tutorial and made the necessary modifications to use the npgsql data provider. The process is really straightforward. However, I get an error when running the update-database command after adding the migration. The error says "Couldn´t set schema (Parameter ´schema')".

When I create a new PostgreSQL database, a schema named public is automatically included but I never use it so I always create a new schema. In this case, my schema name is testdb001. My database and schema have the same name but this is not an issue at all. I performed the following 3 tests when running the update-database command:

  1. My connection string included testdb001 schema parameter: "host=localhost; database=testdb001; schema=testdb001; port=5433; user id=******; password=******;"
  2. My connection string included public schema parameter: "host=localhost; database=testdb001; schema=public; port=5433; user id=******; password=******;"
  3. My connection string did not include the schema parameter: "host=localhost; database=testdb001; port=5433; user id=******; password=******;"

(The PostgreSQL port number I use is 5433)

The first 2 tests failed and displayed the "Couldn´t set schema (Parameter ´schema')" error.,
The third test succeeded and created the identity tables in the public schema. However, I want the tables in the testdb001 schema.

What can I do so the schema parameter in my connection string does not throw an error?

Regards,
Jorge Maldonado

Microsoft Identity Manager
Microsoft Identity Manager
A family of Microsoft products that manage a user's digital identity using identity synchronization, certificate management, and user provisioning.
594 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,080 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. JORGE MALDONADO BARRERA 211 Reputation points
    2022-02-18T17:19:50.74+00:00

    I found the solution.

    For npgsql you must use search path as a connection string parameter instead of schema. So a connection string is as follows

    "host=localhost; database=testdb001; search path=testdb001; port=5433; user id=some-user; password=some-password;"

    and not

    "host=localhost; database=testdb001; schema=testdb001; port=5433; user id=some-user; password=some-password;"

    Regards,
    Jorge Maldonado

    0 comments No comments