WebAPI with .Net Core and Postgres in Visual Studio Code

9 min readJul 4, 2018

Visual Studio Code is giving a tough competition to Eclipse as a unified open-source IDE. Developers prefer VS Code over Eclipse on Slant exceeding by huge margin. I finally decided to bite the bullet and see what all the buzz is about.

Eclipse vs. Visual Studio ratings by developers

In this tutorial you use Entity Framework Core and Postgres to create the simplest web service application in Visual Studio Code.

Software requirements

Operating Systems: macOS (64-bit)
.NET Core SDK

Download the 2.x SDK binary and use the default install options.

Visual Studio Code 1.2x

Downloaded the installer and used all the default install options.

Visual Studio Code Extensions

Navigate to the extension tab in VS Code, or use the shortcut Cmd+Shift+X, and install: C#, C# Extensions, C# XML Documentation Comments, and Code Runner

PostgreSql 10.x

Download the installer ➜ Move to Applications folder ➜ Double Click ➜ Click “Initialize” to create a new server

pgAdmin

Download the installer ➜ Move to Applications folder ➜ Double Click

About PostgreSQL

PostgreSQL is an ACID-compliant Object Relational Database Management System, or ORDBMS. Put simply, it is a database that allows you to relate one piece of data to another (thus, “relational”). It runs on nearly any operating system including Linux, Unix, macOS and Windows. Its unique combination of simplicity and power makes it a popular choice for individual users and small businesses, but enterprise businesses like Yahoo, Uber as well.

Aside from standard relational database features, some of the most notable features in Postgres are:

Streaming replication
Schemas
User-defined objects like operators, data types, and functions
Nested transactions
Table inheritance
Partitioning
Several unusual data types, like Money, Geometry, IP addresses, JSON, and data ranges.
Can execute stored procedures in over a dozen programming languages, including Java, Perl, Python, Ruby, and C/C++.

Due to its first-class support for JSON, Postgres is often a good alternative to “No-SQL” databases like MongoDB.

Create the project

First create a folder in a spot on your machine where you want your project to live. I’m going to put mine on ~/Documents/Workspace folder.

From Terminal, run the following commands:

mkdir TodoApi
dotnet new webapi -o TodoApi
code TodoApi

The TodoApi folder opens in Visual Studio Code (VS Code).

Select Yes to the Warn message “Required assets to build and debug are missing from ‘TodoApi’. Add them?”

Warning pop-up to add required assets to build and debug

Press Debug (F5) to build and run the program. If 500x port is already bound by other process, server will crash with exception having message “Failed to bind to address”

To resolve it,

Open launch.json in your workspace
Go to NET Core Launch (web) config
Specify the unused port in “args” array (command-line args). I use port 5002.

Error has gone 😀. WebAPI is launched in your default browser with URL

http://localhost:5002

4. Append endpoint api/values in browser as mentioned in launchSettings.json’s project profile’s launchUrl key

5. The following output is displayed:

["value1","value2"]

Adding NuGet Packages

Open your integrated terminal

2. Execute the following commands

.NET Core 2.1 installs Entity Framework Core 2.1 by default. However, if you install PostgreSQL package through NuGet package manager, it’ll install PostgreSQL 2.1.1. It results in version conflict between PostgreSQL 2.1.1 & Entity Framework Core 2.1.0.

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 2.1.0dotnet add package NpgSql.EntityFrameworkCore.PostgreSQL.Design

Create Your Database

Open pgAdmin 4.x

In the Add New Server Dialog, enter a server name in the General tab, I chose “TodoApi”. In the Connection tab you can enter localhost for the hostname and type a password of your choice. For this example my password will be password. You can leave the other fields as their default values.

Add a model class

In VS Code create a Models Folder in the root of your project.

A model is an object representing the data in your app. In this case, the only model is a to-do item. Inside of the Models folder create a TodoItem.cs class with the following code:

namespace TodoApi.Models {     public class TodoItem   
{        
 
public long Id { get; set; }        
public string Name { get; set; }         
public bool IsComplete { get; set; }       }
}

Create the database context

The database context is the main class that coordinates Entity Framework functionality for a given data model. You create this class by deriving from the Microsoft.EntityFrameworkCore.DbContext class.

Add a TodoContext.cs class in the Models folder:

using Microsoft.EntityFrameworkCore;  
namespace TodoApi.Models {     
public class TodoContext : DbContext     
  {         
    public TodoContext(DbContextOptions<TodoContext> options)                            : base(options)         
{         
}       
    public DbSet<TodoItem> TodoItems { get; set; }     
  
   } 
}

Register the database context

In this step, the database context is registered with the dependency injection container. Services (such as the DB context) that are registered with the dependency injection (DI) container are available to the controllers.

Register the DB context with the service container using the built-in support for dependency injection. Replace the contents of the Startup.cs file with the following code:

using System;using System.Collections.Generic;using System.Linq;using System.Threading.Tasks;using Microsoft.AspNetCore.Builder;using Microsoft.AspNetCore.Hosting;using Microsoft.AspNetCore.HttpsPolicy;using Microsoft.AspNetCore.Mvc;using Microsoft.Extensions.Configuration;using Microsoft.Extensions.DependencyInjection;using Microsoft.Extensions.Logging;using Microsoft.Extensions.Options;using Microsoft.EntityFrameworkCore;using TodoApi.Models;namespace TodoApi{public class Startup{public Startup(IConfiguration configuration){Configuration = configuration;}public IConfiguration Configuration { get; }// This method gets called by the runtime. Use this method to add services to the container.public void ConfigureServices(IServiceCollection services){// Add framework services.services.AddDbContext<TodoContext>(options =>options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection")));services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);}// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.public void Configure(IApplicationBuilder app, IHostingEnvironment env){if (env.IsDevelopment()){app.UseDeveloperExceptionPage();}else{app.UseHsts();}app.UseHttpsRedirection();app.UseMvc();}}}

Change Connection String

Next open appsettings.json file, in this file you will add your PostgreSQL connection string.

"ConnectionStrings": {"DefaultConnection":"Host=localhost;Port=5432;Username=postgres;Password=password;Database=TodoList;"}

Run database Migration

It’s finally time to add your migration and update your database. We are going to call our migration “initial” but you can call it whatever you like.

In the VS Code integrated terminal execute the command: dotnet ef migrations add initial

output of dotnet ef migrations add initial

This should have generated some files under a new folder in your project called Migrations

Lastly to update the database execute the command: dotnet ef database update

Your database should now be updated in PgAdmin. You may have to refresh the database to see the changes.

Add a controller

In the Controllers folder, create a class named TodoController. Copy the following code:

using Microsoft.AspNetCore.Mvc; 
using System.Collections.Generic; 
using System.Linq; 
using TodoApi.Models;  
namespace TodoApi.Controllers {     
[Route("api/[controller]")]     
[ApiController]     
public class TodoController : ControllerBase     
{        
private readonly TodoContext _context;          public TodoController(TodoContext context)         
{             _context = context;              
if (_context.TodoItems.Count() == 0)             
{                 
_context.TodoItems.Add(new TodoItem { Name = "Item1" });                 _context.SaveChanges();             
}         
}            
} 
}

The preceding code defines an API controller class without methods. In the next sections, methods are added to implement the API. The class is annotated with an [ApiController] attribute to enable some convenient features.

The controller’s constructor uses Dependency Injection to inject the database context (TodoContext) into the controller. The database context is used in each of the CRUD methods in the controller. The constructor adds an item to the Postgres database if one doesn't exist.

Get to-do items

To get to-do items, add the following methods to the TodoController class:

[HttpGet] 
public ActionResult<List<TodoItem>> GetAll() 
{     
return _context.TodoItems.ToList(); 
} 
 
[HttpGet("{id}", Name = "GetTodo")] 
public ActionResult<TodoItem> GetById(long id) 
{    
var item = _context.TodoItems.Find(id);     
if (item == null)    
{         
return NotFound();     
}     
return item; 
}

These methods implement the two GET methods:

GET /api/todo
GET /api/todo/{id}

Routing and URL paths

The [HttpGet] attribute denotes a method that responds to an HTTP GET request. The URL path for each method is constructed as follows:

Take the template string in the controller’s Route attribute:

namespace TodoApi.Controllers 
{     
[Route("api/[controller]")]     
[ApiController]     
public class TodoController : ControllerBase     
{         
private readonly TodoContext _context;

[controller] is a variable which takes the controller class name minus the "Controller" suffix. For this sample, the controller class name is TodoController and the root name is "todo". ASP.NET Core routing is case insensitive.

If the [HttpGet] attribute has a route template (such as [HttpGet("/products")], append that to the path. This sample doesn't use a template.

In the following GetById method, "{id}" is a placeholder variable for the unique identifier of the to-do item. When GetById is invoked, it assigns the value of "{id}" in the URL to the method's idparameter.

[HttpGet(“{id}”, Name = “GetTodo”)] 
public ActionResult<TodoItem> GetById(long id) 
{ 
var item = _context.TodoItems.Find(id); 
if (item == null) 
{ 
return NotFound(); 
} 
return item; 
}

Name = "GetTodo" creates a named route. Named routes:

Enable the app to create an HTTP link using the route name.

Return values

The GetAll method returns a collection of TodoItem objects. MVC automatically serializes the object to JSON and writes the JSON into the body of the response message. The response code for this method is 200, assuming there are no unhandled exceptions. Unhandled exceptions are translated into 5xx errors.

In contrast, the GetById method returns the ActionResult<T> type, which represents a wide range of return types. GetById has two different return types:

If no item matches the requested ID, the method returns a 404 error. Returning NotFound returns an HTTP 404 response.
Otherwise, the method returns 200 with a JSON response body. Returning item results in an HTTP 200 response.

Launch the app

In VS Code, press F5 to launch the app. Navigate to http://localhost:5002/api/todo (the Todocontroller we created).

Here’s a sample HTTP response for the GetAll method:

[   
{     "id": 1,     
"name": "Item1",     
"isComplete": false   
} 
]

Response of http://localhost:5002/api/todo in browser

Let’s match it with data in TodoItems table. Do the following steps,

Open pgAdmin 4
Select TodoApi server >> TodoList databse >> Schemas >> Tables >> TodoItems
Right click on TodoItems table and select View/Edit Data

Response of http://localhost:5002/api/todo is matched with data in TodoItems table. 👏

If you want to navigate to http://localhost:5002/api/todo/2,

Here’s a sample HTTP response for the GetById method:

[   
{     "id": 2,     
"name": "Item1",     
"isComplete": false   
} 
]

Where to Go From Here?

You can download the completed project using the link at the bottom of this tutorial.

Download Materials

As always, any feedback is appreciated, so feel free to comment down here or reach out on twitter — and, as always,