CockroachDb with Entity Framework and Web API

Download full source code.

Getting going with CockroachDb on a local computer is relatively straightforward once you get over the hump of figuring out single node mode. I wrote a couple of posts about it recently, you can find them here, and here.

CockroachDb uses a Postgres interface, so there is no problem using it with .NET, you can even use it with Entity Framework.

In this example, I use CockroachDb, with Entity Framework Core in a Web API application using .NET 8.

Starting the CockroachDb container

Create a Dockerfile with the following -

FROM cockroachdb/cockroach:latest
ENV COCKROACH_USER=root
ENV COCKROACH_PASSWORD=admin
CMD ["start-single-node", "--insecure"]

This will get the latest image, set the username and password, and start CockroachDb in single node mode.

Build this docker image -

docker build -t cockroachdockerfile .

And then start the container -

docker run -d --name=mycockroachdockerfile -p 26257:26257 -p 8080:8080 cockroachdockerfile

This command also forwards the SQL interface and admin UI ports to your host computer.

The Web API Application

The code itself is very simple, so you can stop here and download the attached zip if that’s all you need.

To use CockroachDb with Entity Framework you need to add a NuGet package called Npgsql.EntityFrameworkCore.PostgreSQL.

It wires into the service collection in the same way as the SQL Server EF package.

In Program.cs add this -

builder.Services.AddDbContext<SalesContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("SalesDb")));

My connection string is stored in appsettings.Development.json -

{
  "ConnectionStrings": {
    "SalesDb": "Server=localhost:26257;Database=salesdb;User Id=root;Password=admin;Include Error Detail=true;"
  }
}

Note the port, User Id, and Password match the configuration shown above.

In the source code, you will find a seeder to set some initial data, and a controller to perform some CRUD operations on the table that the seeder creates.

That’s about, it should all be familiar if you work with Web API and Entity Framework.

Download full source code.