WebAPI with .Net Core and Postgres in Visual Studio Code

Laxman Sahni
Jul 4, 2018 · 9 min read

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.

Image for post
Image for post
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
Image for post
Image for post
.NET Core SDK

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

  • Visual Studio Code 1.2x
Image for post
Image for post
VS Code

Downloaded the installer and used all the default install options.

  • Visual Studio Code Extensions
Image for post
Image for post
Extensions in VS Code

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
Image for post
Image for post
Postgres app

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

Image for post
Image for post
Click Initialize to create a server
  • pgAdmin
Image for post
Image for post
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).

TodoApi workspace in VS Code

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

Image for post
Image for post
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”

Image for post
Image for post
Failed to bind address by server

To resolve it,

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

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

Image for post
Image for post
launchUrl key of profiles

5. The following output is displayed:

["value1","value2"]
Image for post
Image for post
output of WebAPI

Adding NuGet Packages

  1. Open your integrated terminal
Image for post
Image for post
Integrated terminal of VS Code

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.

Image for post
Image for post
.NET Core 2.1
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 2.1.0dotnet add package NpgSql.EntityFrameworkCore.PostgreSQL.Design

Create Your Database

Open pgAdmin 4.x

Image for post
Image for post
Add New Server Dialog of pgAdmin

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.

Image for post
Image for post
database server config

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; }
}
}
Image for post
Image for post
TodoItem.cs

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

Image for post
Image for post
output of dotnet ef migrations add initial

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

Image for post
Image for post
Migrations folder

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

Image for post
Image for post
output of database update in terminal

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

Image for post
Image for post
Database & Tables in PostgreSQL

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.
Image for post
Image for post
TodoController
  • 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
}
]
Image for post
Image for post
Response of http://localhost:5002/api/todo in browser

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

  1. Open pgAdmin 4
  2. Select TodoApi server >> TodoList databse >> Schemas >> Tables >> TodoItems
  3. Right click on TodoItems table and select View/Edit Data
Image for post
Image for post
View Data of TodoItems table

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,

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store