Microsoft have slowly been making progress with their cross platform efforts and .NET Core is starting to look like it might be interesting. In this post we look at integrating a .NET Core Web API with PostgreSQL running a mac (this code should also work on Linux).
You can download the source code for this post from here (GitHub)
First check that you have the dotnet command line installed.
dotnet --version
If you don’t have it yet then head off to here to download and install it.
Once you are all set up we can create a new project. You can see the types of projects you can create by running:
dotnet new --help
Templates Short Name Language Tags
----------------------------------------------------------------------
Console Application console [C#], F# Common/Console
Class library classlib [C#], F# Common/Library
Unit Test Project mstest [C#], F# Test/MSTest
xUnit Test Project xunit [C#], F# Test/xUnit
ASP.NET Core Empty web [C#] Web/Empty
ASP.NET Core Web App mvc [C#], F# Web/MVC
ASP.NET Core Web API webapi [C#] Web/WebAPI
Solution File sln Solution
Let’s create a new Web API project:
dotnet new webapi -n MyFirstWebAPI
cd MyFirstWebAPI/
dotnet restore
This will create a new application MyFirstWebAPI, we then cd into the new project and restore all the packages the application requires.
We can the current set of packages by looking in MyFirstWebAPI.csproj
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<TargetFramework>netcoreapp1.1</TargetFramework>
</PropertyGroup>
<ItemGroup>
<Folder Include="wwwroot\" />
</ItemGroup>
<ItemGroup>
<PackageReference Include="Microsoft.AspNetCore" Version="1.1.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.3" />
<PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.2" />
</ItemGroup>
</Project>
If we look in the Controllers folder we can see that they’ve given us a default api controller called ValuesController.
Lets run up the application and try it out.
dotnet run
Hosting environment: Production
Content root path: /Users/chrisgreening/Work/dotnetcore/MyFirstWebAPI
Now listening on: http://localhost:5000
Application started. Press Ctrl+C to shut down.
Let’s see if we can retrieve the list of values from ValuesController (I’m using jq to pretty print my JSON - you can install this using brew install jq)
curl localhost:5000/api/values -sS | jq
[
"value1",
"value2"
]
We’ve got a basic API server working, let’s try hooking it up to a PostgreSQL database. First lets get a database set up for our experimentation (I’m assuming you have either PostgreSQL installed locally or available in a docker environment - I’d recommend getting it set up in docker):
psql -U postgres
CREATE ROLE dotnet_user with encrypted password 'secret' LOGIN;
CREATE DATABASE dotnet_test with owner dotnet_user;
Now we need to add some new packages to our project. Let’s add them via the command line:
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools.DotNet
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL.Design
There is one small change we need to make to our csproj file - we need to change the reference to the Microsoft.EntityFrameworkCore.Tools.DotNet package from a PackageReference to a DotNetCliToolReference. This will expose the dotnet ef command to us. Edit MyFirstWebAPI.csproj and move the package into it’s own group and change it to DotNetCliToolReference.
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<TargetFramework>netcoreapp1.1</TargetFramework>
</PropertyGroup>
<ItemGroup>
<Folder Include="wwwroot\" />
</ItemGroup>
<ItemGroup>
<PackageReference Include="Microsoft.AspNetCore" Version="1.1.2" />
<PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.3" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="1.1.2" />
<PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.2" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="1.1.0" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.Design" Version="1.1.0" />
</ItemGroup>
<ItemGroup>
<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.1" />
</ItemGroup>
</Project>
Now run the following command to make sure we have all the packages installed:
dotnet restore
Let’s now setup our database connection in our app settings. Open up the file appsettings.json in the root of the project and modify it so it contains a connection string to the database we created earlier:
{
"Logging": {
"IncludeScopes": false,
"LogLevel": {
"Default": "Debug",
"System": "Information",
"Microsoft": "Information"
}
},
"ConnectionStrings": {
"DATABASE_URL": "User ID=dotnet_user;Password=secret;Host=localhost;Port=5432;Database=dotnet_test;Pooling=true;"
}
}
Let’s create some entities - we’ll create a simple Test entity - create a folder called Entities and a file called Test.cs:
using System;
namespace MyFirstWebAPI.Entities
{
public class Test
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
}
We can now create our DbContext - this is the class that gets the Entity Framework up and running. Create a new folder Database and a file ApplicationDbContext.cs
using System;
using Microsoft.EntityFrameworkCore;
namespace MyFirstWebAPI.Database
{
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
{
}
public DbSet<Entities.Test> TestRecords { get; set; }
}
}
We now need to add our ApplicationDbContext to the dependency injection framework and configure the connection string. Open up Startup.cs. Add a reference to EntityFrameworkCore
using Microsoft.EntityFrameworkCore;
And in the ConfigureServices method add the following code to setup our ApplicationDbContext:
var sqlConnectionString = Configuration.GetConnectionString("DATABASE_URL");
services.AddDbContext<Database.ApplicationDbContext>(options =>
options.UseNpgsql(
sqlConnectionString,
b => b.MigrationsAssembly("MyFirstWebAPI")
)
);
We should now be in a position to generate a database migration to add our new entity to the database.
dotnet ef migrations add "Create Test Entity"
This should have created us a new file Migrations/XXXX_Create Test Entity.cs which will contain code for and Up method for creating our new table and a Down method for destroying our new table.
Let’s now run the migration to update our database.
dotnet ef database update
If we now look in our database we should see the new table:
\connect dotnet_test
\d "TestRecords"
Table "public.TestRecords"
Column | Type | Modifiers
-------------+---------+------------------------------------------------------------
Id | integer | not null default nextval('"TestRecords_id_seq"'::regclass)
Description | text |
Name | text |
Indexes:
"PK_TestRecords" PRIMARY KEY, btree (Id)
Let’s now hook up our ValuesController to our DbContext. We will use the dependency injection system to automatically get a reference to the ApplicationDbContext. Modify the ValuesController class so that it has a member variable and a constructor:
public class ValuesController : Controller
{
Database.ApplicationDbContext dbContext;
public ValuesController(Database.ApplicationDbContext dbContext)
{
this.dbContext = dbContext;
}
We’ll wire up the Get method to get all the Test entities:
[HttpGet]
public IEnumerable<Entities.Test> Get()
{
return dbContext.TestRecords;
}
The get by id method:
[HttpGet("{id}")]
public Entities.Test Get(int id)
{
return dbContext.TestRecords.Where(t => t.Id == id).FirstOrDefault();
}
The post:
[HttpPost]
public Entities.Test Post([FromBody]Entities.Test value)
{
dbContext.TestRecords.Add(value);
dbContext.SaveChanges();
return value;
}
The put method:
[HttpPut("{id}")]
public Entities.Test Put(int id, [FromBody]Entities.Test value)
{
var entity = dbContext.TestRecords.Where(t => t.Id == id).FirstOrDefault();
entity.Name = value.Name;
entity.Description = value.Description;
dbContext.SaveChanges();
return entity;
}
And finally the delete method
[HttpDelete("{id}")]
public Entities.Test Delete(int id)
{
var entity = dbContext.TestRecords.Where(t => t.Id == id).FirstOrDefault();
dbContext.TestRecords.Remove(entity);
dbContext.SaveChanges();
return entity;
}
Now lets try out our new API:
dotnet run
Hosting environment: Production
Content root path: /Users/chrisgreening/Work/dotnetcore/MyFirstWebAPI
Now listening on: http://localhost:5000
Application started. Press Ctrl+C to shut down.
If we list our objects we should get an empty array as the database is empty:
curl localhost:5000/api/values -sS | jq
[]
Let’s create some objects:
curl localhost:5000/api/values -H "Content-Type: application/json" -X POST -d '{"Name": "Bob", "Description": "A cool guy"}' -sS | jq
{
"id": 1,
"name": "Bob",
"description": "A cool guy"
}
curl localhost:5000/api/values -H "Content-Type: application/json" -X POST -d '{"Name": "Jim", "Description": "Good chap"}' -sS | jq
{
"id": 2,
"name": "Jim",
"description": "Good chap"
}
curl localhost:5000/api/values -H "Content-Type: application/json" -X POST -d '{"Name": "Chris", "Description": "A bit bananas"}' -sS | jq
{
"id": 3,
"name": "Chris",
"description": "A bit bananas"
}
curl localhost:5000/api/values -sS | jq
[
{
"id": 1,
"name": "Bob",
"description": "A cool guy"
},
{
"id": 2,
"name": "Jim",
"description": "Good chap"
},
{
"id": 3,
"name": "Chris",
"description": "A bit bananas"
}
]
Let’s check our put command:
curl localhost:5000/api/values/3 -H "Content-Type: application/json" -X PUT -d '{"Name": "Chris", "Description": "What a guy!"}' -sS | jq
{
"id": 3,
"name": "Chris",
"description": "What a guy!"
}
curl localhost:5000/api/values/3 -sS | jq
{
"id": 3,
"name": "Chris",
"description": "What a guy!"
}
And let check we can delete
curl localhost:5000/api/values/3 -X DELETE
curl localhost:5000/api/values/2 -X DELETE
curl localhost:5000/api/values -sS | jq
[
{
"id": 1,
"name": "Bob",
"description": "A cool guy"
}
]
We’ve now got a nice little server running .Net core talking to a PostgreSQL database.