DEV Community

Nucu Labs
Nucu Labs

Posted on

Adding PostgreSQL support to your ASP.Net Core application via Docker

I’ve been playing recently with ASP.Net Core and I’m developing a small Web API project in order to teach myself the framework.

In still article I’ll explain how to use a Postgres database with Docker, in order to make your development experience more enjoyable.

Before moving on, please make sure that you’ve installed:

First, create the docker-compose.yml file in your root directory of the project, and populate it with:

version: '3.4'
services:
  postgres:
    image: postgres
    environment:
      - POSTGRES_USER=app
      - POSTGRES_PASSWORD=app
      - POSTGRES_DB=mydbname
    volumes:
      - ./volumes/data/db:/var/lib/postgresql/data
    ports:
       - 5432:5432
Enter fullscreen mode Exit fullscreen mode

This specific postgres configuration passes some self-explanatory environments variables to the postgres database, it binds the ./volumes/data directory to the postgres’ data volume and it exposes the port 5432. We bind the volume so we can achieve data persistency and explore the files generated by the postgres database.

Next, add the following packages to your ASP.Net Core application:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL.Design
Enter fullscreen mode Exit fullscreen mode

Add your database connection configuration to the appsettings.Development.json file, note that they match the strings from the docker-compose.yml file. When running in production you should deploy each service in a separate container and configure them with environment variables. Never hardcode sensitive information in your files!

{
"DatabaseConfig": {
    "PostgresSQL": "Server=localhost;Port=5432;Database=mydbname;User Id=app;Password=app;"
  },
...
}
Enter fullscreen mode Exit fullscreen mode

Now in the Startup.cs file, when configuring the application context, specify that you want to use PostgresSQL.

services.AddDbContext<ApplicationContext>(options =>
{
    options.UseNpgsql(Configuration.GetSection("DatabaseConfig")["PostgresSQL"]);
});
Enter fullscreen mode Exit fullscreen mode

This is it! Make sure that your application is not running, change the current directory to the root of the project and start the database with docker-compose up. After the database has started open a new terminal and add the migrations:

dotnet ef migrations add InitialMigration
dotnet ef database update

Enter fullscreen mode Exit fullscreen mode

If you'd like to stop the database you can run docker ps and docker stop <hash> where <hash> is the hash of your database container. Following the steps from above gives you an isolated development database that you can use alongside your ASP.Net Core application. If you need to add ElasticSearch, Redis or another service, all you need to do is change the docker-compose.yml file to include them and voilà.

Thank you for reading and have a nice day!

Top comments (4)

Collapse
 
viniciuscavagnolli profile image
Vinicius Cavagnolli

Very cool article and straight to the point.
I had to do it last week, as I'm coding a central API for logs using Serilog, and as this one had to be built in a more low budget fashion, I decided not to go for SQL Server (which is my go-to DB on ASP.Net Core right now) and decided to use a PostgreSQL instance.
We are not using it on docker in production, but I used it on dev environment... It's so easy to setup and Docker is so simple to configure at windows 10... I'm probably using it more in the next few projects.
BTW, good luck learning ASP.Net Core, it's a very cool and extensive framework (also very stable for it's young age), I'm sure you'll fall in love as I did since it was on .net framework era.

Collapse
 
nuculabs_dev profile image
Nucu Labs

Thank you for the kind words! Microsoft blew my mind with ASP.Net Core, I'll definitely use it when I get the opportunity. I've only used frameworks written in Python.
Microsoft changed a lot lately and I hope they continue to bring us great products!

Good luck with your project and happy coding! :)

Collapse
 
aristotelesbr profile image
Aristóteles Coutinho • Edited

After exec dotnet ef database update command returns this error for me:

uild started...
Build succeeded.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (76ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" TYPE boolean;
      ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" SET NOT NULL;
      ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" DROP DEFAULT;
Failed executing DbCommand (76ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" TYPE boolean;
ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" SET NOT NULL;
ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" DROP DEFAULT;
Npgsql.PostgresException (0x80004005): 42804: column "TwoFactorEnabled" cannot be cast automatically to type boolean
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ERROR
    SqlState: 42804
    MessageText: column "TwoFactorEnabled" cannot be cast automatically to type boolean
    Hint: You might need to specify "USING "TwoFactorEnabled"::boolean".
    File: tablecmds.c
    Line: 10363
    Routine: ATPrepAlterColumnType
42804: column "TwoFactorEnabled" cannot be cast automatically to type boolean
Enter fullscreen mode Exit fullscreen mode
Collapse
 
nuculabs_dev profile image
Nucu Labs

That's strange, are you starting /w a fresh database? At the first look this seems to be an issue between ASP.NET Core and PostgreSql, try testing /w Maria/Mysql also.