PostgreSQL, often simply “Postgres”, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data, securely and supporting best practices, and retrieve it later, as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users. Recent versions also provide replication of the database itself for security and scalability.

In this series:

Now, we can see the source code at here

Step 1: Launch a PostgreSQL service container

$ docker run -d --name my-postgres -e POSTGRES_PASSWORD=password postgres

docker_core_16

This image includes EXPOSE 5432 (the postgres port), so standard container linking will make it automatically available to the linked containers. The default postgres user and database are created in the entrypoint with initdb.

Environment Variables
The PostgreSQL image uses several environment variables which are easy to miss. While none of the variables are required, they may significantly aid you in using the image.

  1. POSTGRES_PASSWORD
    This environment variable is recommended for you to use the PostgreSQL image. This environment variable sets the superuser password for PostgreSQL. The default superuser is defined by the POSTGRES_USER environment variable. In the above example, it is being set to “mysecretpassword”.
  2. POSTGRES_USER
    This optional environment variable is used in conjunction with POSTGRES_PASSWORD to set a user and its password. This variable will create the specified user with superuser power and a database with the same name. If it is not specified, then the default user of postgres will be used.
  3. PGDATA
    This optional environment variable can be used to define another location – like a subdirectory – for the database files. The default is /var/lib/postgresql/data, but if the data volume you’re using is a fs mountpoint (like with GCE persistent disks), Postgres initdb recommends a subdirectory (for example /var/lib/postgresql/data/pgdata ) be created to contain the data.
  4. POSTGRES_DB
    This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used.

Step 2: Make sure the PostgreSQL Docker container is up and running with:

 $ docker ps -a

Step 3: Test connection from your host system
docker_core_17

Now, we will build an ASP.NET Core MVC application that performs basic data access using Entity Framework. We will use migrations to create the database from our model.

Prerequisites

The following prerequisites are needed to complete:

Create a new project

  • Open Visual Studio 2015
  • File ‣ New ‣ Project…
  • From the left menu select Templates ‣ Visual C# ‣ Web
  • Select the ASP.NET Core Web Application (.NET Core) project template
  • Enter PostgreApp as the name and click OK
  • Wait for the New ASP.NET Core Web Application dialog to appear
  • Select the Web Application template and ensure that Authentication is set to No Authentication
  • Click OK

Install Entity Framework

To use EF Core, install the package for the database provider(s) you want to target. In here uses PostgreSQL. For a list of available providers see Database Providers.

  • Tools ‣ NuGet Package Manager ‣ Package Manager Console
  • Run Install-Package Npgsql.EntityFrameworkCore.PostgreSQL
    
  "dependencies": {
    "Microsoft.NETCore.App": {
      "version": "1.0.0",
      "type": "platform"
    },
    "Microsoft.AspNetCore.Diagnostics": "1.0.0",
    "Microsoft.AspNetCore.Mvc": "1.0.0",
    "Microsoft.AspNetCore.Razor.Tools": {
      "version": "1.0.0-preview2-final",
      "type": "build"
    },
    "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
    "Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
    "Microsoft.AspNetCore.StaticFiles": "1.0.0",
    "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
    "Microsoft.Extensions.Configuration.Json": "1.0.0",
    "Microsoft.Extensions.Logging": "1.0.0",
    "Microsoft.Extensions.Logging.Console": "1.0.0",
    "Microsoft.Extensions.Logging.Debug": "1.0.0",
    "Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
    "Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.0.0",
    "Npgsql.EntityFrameworkCore.PostgreSQL": "1.0.0",
    "Microsoft.EntityFrameworkCore": "1.0.0",
    "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
  }

The PostgreSQL Entity Framework 7 provider can be downloaded as a NuGet package. Add the NuGet package Npgsql.EntityFrameworkCore.PostgreSQL to your dependencies in the project.json file.

We will also be using some Entity Framework commands to maintain the database. So we will install the commands package as well.

  • Run Install-Package Microsoft.EntityFrameworkCore.Tools –Pre
  • Open project.json
  • Locate the tools section and add the ef command as shown below
   "tools": {
    "BundlerMinifier.Core": "2.0.238",
    "Microsoft.AspNetCore.Razor.Tools": "1.0.0-preview2-final",
    "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final",
    "Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final"
  },

Create our model

Now it’s time to define a context and entity classes that make up your model.

  • Right-click on the project in Solution Explorer and select Add ‣ New Folder
  • Enter Models as the name of the folder
  • Right-click on the Models folder and select Add ‣ New Item…
  • From the left menu select Installed ‣ Code
  • Select the Class item template
  • Enter Store.cs as the name and click OK
  • Replace the contents of the file with the following code
namespace PostgreApp.Models
{
    public class Store
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

Create the context for Entity Framework Core

First, create a folder named “Repository” by right clicking on the project and choosing the correct option from the menu. Next, add a class to the “Repository” folder named “StoreDbContext.cs”.
Open the file after creating it and add some code to have the class inherit from DbContext like so:

using Microsoft.EntityFrameworkCore;
using PostgreApp.Models;

namespace PostgreApp.Repository
{
    public class StoreDbContext: DbContext
    {
        public StoreDbContext() : base()
        { }

        public StoreDbContext(DbContextOptions<StoreDbContext> options) : base(options)
        { }

        public DbSet<Store> Stores { get; set; }
    }
}

Creating the Store Repository Class

In the Repository folder, create a class file named IStoreRepository.cs and replace the existing code with the following code:

using PostgreApp.Models;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace PostgreApp.Repository
{
    public interface IStoreRepository
    {
        Task<List<Store>> GetStoresAsync();
        Task  InsertStore(Store store);
        void Save();
    }
}

In the Repository folder, create a class file named StoreRepository.cs file. Replace the existing code with the following code, which implements the IStoreRepository interface:

using System.Collections.Generic;
using System.Threading.Tasks;
using PostgreApp.Models;
using Microsoft.EntityFrameworkCore;

namespace PostgreApp.Repository
{
    public class StoreRepository : IStoreRepository
    {
        private readonly StoreDbContext _context;

        public StoreRepository(StoreDbContext context)
        {
            _context = context;
        }

        public async Task<List<Store>> GetStoresAsync()
        {
            return await _context.Stores.ToListAsync();
        }

        public async Task InsertStore(Store store)
        {
            _context.Stores.Add(store);
            try
            {
                await _context.SaveChangesAsync();
            }
            catch { }
        }
    }
}

Add the connection string

The connection string can be added in the OnConfiguring method in the class which implements the DbContext, or via dependency injection in the constructor using the options. The connection string property User ID needs to exist in the database and should have the create database rights.

Open appsettings.json and change its content as follow:

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  },
  "Data": {
    "StoreDbContext": {
      "ConnectionString": "User ID=postgres;Password=password;Server=postgres;Port=5432;Database=POSTGRES_USER;Integrated Security=true;Pooling=true;"
    }
  }
}

Register the context with dependency injection

In order for our MVC controllers to make use of StoreDbContext we are going to register it as a service.

Open Startup.cs and add the following using statements at the start of the file:

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using PostgreApp.Repository;

Now we can use the AddDbContext method to register it as a service.

  • Locate the ConfigureServices method
  • Add the lines that are highlighted in the following code
        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            //Add PostgreSQL support
            services.AddEntityFrameworkNpgsql()
                .AddDbContext<StoreDbContext>(options =>
                    options.UseNpgsql(Configuration["Data:StoreDbContext:ConnectionString"]));

            // Add framework services.
            services.AddMvc();

            // Add our PostgreSQL Repository
            services.AddTransient<IStoreRepository, StoreRepository>();
        }

The last updates we need to make are to the Configure method.

// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory, DbSeeder dbSeeder)
        {
            loggerFactory.AddConsole(Configuration.GetSection("Logging"));
            loggerFactory.AddDebug();

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseBrowserLink();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }

            app.UseStaticFiles();

            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });

            // Create DB on startup
            using (var serviceScope = app.ApplicationServices.GetRequiredService<IServiceScopeFactory>().CreateScope())
            {
                serviceScope.ServiceProvider.GetService<StoreDbContext>().Database.Migrate();
            }
        }

This piece is necessary since we’re planning to use code-first EF migrations. The “Database.Migrate()” piece is actually responsible for two things:

  • Creating the database in PostgreSQL if it doesn’t already exist
  • Migrating the DB schemas to the latest versions

Entity Framework does not do this automatically, so this piece is necessary.

Setting Up Code-First Migrations

Let’s add support for migrations to our project now. Remember in the previous section where we needed to edit the project.json file and added the “EntityFrameworkCore.Tools” JSON to the tools section? This is the part where that step comes in handy.

  • Tools –> NuGet Package Manager –> Package Manager Console
  • Run Add-Migration InitialCreate to scaffold a migration to create the initial set of tables for your model.

The output should reflect “To undo this action, use Remove-Migration.” That means we’re good. This option is available because we wired up the appropriate tool in the project.json file. If everything went well, you should have seen a new folder named “Migrations” created in our project along with two files: StoreDbContextModelSnapshot.cs and _InitialCreate.cs.
These files will be used when our application starts up in order to create a DB (on the first run) that is capable of housing our service’s state. However, we still need to wire up both these migrations and the actual StoredDbContext within .NET Core’s Kestrel engine.

Create a controller

Next, we’ll add an MVC controller that will use EF to query and save data.

  • Right-click on the Controllers folder in Solution Explorer and select Add ‣ New Item…
  • From the left menu select Installed ‣ Server-side
  • Select the Class item template
  • Enter StoreController.cs as the name and click OK
  • Replace the contents of the file with the following code
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using PostgreApp.Repository;
using PostgreApp.Models;


namespace PostgreApp.Controllers
{
    public class StoreController : Controller
    {
        private readonly IStoreRepository _storeRepository;

        public StoreController(IStoreRepository storeRepository)
        {
            _storeRepository = storeRepository;
        }
        // GET: /<controller>/
        public async Task<IActionResult> Index()
        {
            var stores = await _storeRepository.GetStoresAsync();
            return View(stores);
        }

        public IActionResult Create()
        {
            return View();
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public IActionResult Create(Store store)
        {
            if (ModelState.IsValid)
            {
                _storeRepository.InsertStore(store);
                return RedirectToAction("Index");
            }

            return View(store);
        }
    }
}

Create views

Now that we have a controller it’s time to add the views that will make up the user interface.

We’ll start with the view for our Index action, that displays all blogs.

  • Right-click on the Views folder in Solution Explorer and select Add ‣ New Folder
  • Enter Store as the name of the folder
  • Right-click on the Store folder and select Add ‣ New Item…
  • From the left menu select Installed ‣ ASP.NET
  • Select the MVC View Page item template
  • Enter Index.cshtml as the name and click Add
  • Replace the contents of the file with the following code
@model List<PostgreApp.Models.Store>

@{
    ViewBag.Title = "Stores";
}

<h2>Stores</h2>

<p>
    <a asp-controller="Store" asp-action="Create">Create New</a>
</p>

<table class="table">
    <tr>
        <th>Id</th>
        <th>Name</th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Id)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Name)
            </td>
        </tr>
    }
</table>

We’ll also add a view for the Create action, which allows the user to enter details for a new store.

  • Right-click on the Store folder and select Add ‣ New Item…
  • From the left menu select Installed ‣ ASP.NET Core
  • Select the MVC View Page item template
  • Enter Create.cshtml as the name and click Add
  • Replace the contents of the file with the following code
@model PostgreApp.Models.Store

@{
    ViewBag.Title = "New Store";
}

<h2>@ViewData["Title"]</h2>

<form asp-controller="Store" asp-action="Create" method="post" class="form-horizontal" role="form">
    <div class="form-horizontal">
        <div asp-validation-summary="All" class="text-danger"></div>
        <div class="form-group">
            <label asp-for="Name" class="col-md-2 control-label"></label>
            <div class="col-md-10">
                <input asp-for="Name" class="form-control" />
                <span asp-validation-for="Name" class="text-danger"></span>
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
</form>

The Program.cs file

We ended up with the following code for the Program.cs file. The interesting part is the UseUrls() which I didn’t have while trying to make it run with Docker, then it wasn’t bound to the right network, and the application wasn’t accessible outside of the Docker container.

Now, we will change its content as follow:

using System.IO;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;

namespace PostgreApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            // Get environment variables
            var config = new ConfigurationBuilder()
                .AddEnvironmentVariables("")
                .Build();
            // You need to add these lines for accessing outside of Docker
            var url = config["ASPNETCORE_URLS"] ?? "http://*:5000";
            var env = config["ASPNETCORE_ENVIRONMENT"] ?? "Development";

            var host = new WebHostBuilder()
                .UseKestrel()
                .UseUrls(url)
                .UseEnvironment(env)
                .UseContentRoot(Directory.GetCurrentDirectory())
                .UseIISIntegration()
                .UseStartup<Startup>()
                .Build();

            host.Run();
        }
    }
}

Dockerfiles

Each Dockerfile is a script, composed of various commands (instructions) and arguments listed successively to automatically perform actions on a base image in order to create (or form) a new one. They are used for organizing things and greatly help with deployments by simplifying the process start-to-finish.

In root of our app, create new file with its content as below:

FROM microsoft/dotnet:1.0.0-preview2-sdk

# Set environment variables
ENV ASPNETCORE_URLS="http://*:5000"
ENV ASPNETCORE_ENVIRONMENT="Development"

# Copy files to app directory
COPY . /app

# Set working directory
WORKDIR /app

# Restore NuGet packages
RUN ["dotnet", "restore"]

# Open up port
EXPOSE 5000

# Run the app
ENTRYPOINT ["dotnet", "run"]

  • Line 1: FROM directive is probably the most crucial amongst all others for Dockerfiles. It defines the base image to use to start the build process. It can be any image, including the ones you have created previously. If a FROM image is not found on the host, docker will try to find it (and download) from the docker image index. It needs to be the first command declared inside a Dockerfile.
  • Line 4+5: The ENV command is used to set the environment variables (one or more). These variables consist of “key = value” pairs which can be accessed within the container by scripts and applications alike. This functionality of docker offers an enormous amount of flexibility for running programs.
  • Line 8: The COPY instruction copies new files or directories from and adds them to the filesystem of the container at the path .
  • Line 11: The WORKDIR directive is used to set where the command defined with CMD is to be executed.
  • Line 14: The RUN command is the central executing directive for Dockerfiles. It takes a command as its argument and runs it to form the image. Unlike CMD, it actually is used to build the image (forming another layer on top of the previous one which is committed).
  • Line 17: The EXPOSE command is used to associate a specified port to enable networking between the running process inside the container and the outside world (i.e. the host).
  • Line 20: The ENTRYPOINT argument sets the concrete default application that is used every time a container is created using the image. For example, if you have installed a specific application inside an image and you will use this image to only run that application, you can state it with ENTRYPOINT and whenever a container is created from that image, your application will be the target.

Building an ASP.NET Core Image

Step 1: Open the “Docker Quickstart Terminal” from the start menu.
idocker1

After a few seconds your Docker terminal should be ready. We can run the docker images command to see all the images:

$ docker images

idocker2

Step 2: Build an image from a Dockerfile

We always have the dockerfile for our custom ASP.NET Core ready to go, we need to convert that dockerfile into the image by running the docker build command. Let’s have a look this build command:
idocker3

We’ll change current path to project root:

$ cd /D/VS2015/Docker/PostgreApp/src/PostgreApp/

and ensure that Docker can see our project directory:

$ ll

idocker4

We’ll check the directory listing of the root directory of a typical Linux file system by using:

$ ls

So lets take a look how we can build and run our docker image.

$ docker build -t postgre-dotnetcore .

idocker5

Check the image has been created correctly and is present in our Docker machine:

$ docker images

idocker6

Okay cool, our image ready to run.

Now the next we’re going to do is we are going to fire up a PostgreSQL database, and we’re going to give it a name and any time we want to link a container to another container simply give it a name and then we can reference it by that name.

$ docker run -d --name my-postgres -e POSTGRES_PASSWORD=password postgres 

Alright, Lets run docker ps, and there we go, we can see that is now up

idocker7

Containers can be linked to another container’s ports directly using -link remote_name:local_alias in the client’s docker run. This will set a number of environment variables that can then be used to connect.

We need the docker run command to build container, then we’re going to link it again the postgre database.

$ docker run -d -p 5000:5000 --link my-postgres:postgres postgre-dotnetcore

idocker8

So lets run off to the browser, and we’r going to add port 5000, and then we go.

The first visiting:
idocker9

Add new store
idocker10

Then list stores
idocker11

Now, we can see the source code at here

As a result, Docker provides a great way to containerize so that we don’t have to install anything we don’t want. Also we can go in, stop the containers, remove them, and then remove the images.

Advertisements