[vNext] Use PostgreSQL + Fluent NHibernate from ASP.NET 5 (DNX) on Ubuntu

In the previous part we have installed APS.NET 5 application on Ubuntu, now we are gonna install and configure PostgreSQL on Ubuntu and access database from our application through NHibernate.

Install and configure PostgreSQL

First, install PostgreSQL:

sudo apt-get install postgresql postgresql-contrib

Next, set root password:

sudo -u postgres psql postgres
\password postgres

And then create test database:

sudo -u postgres createdb mydb

Add Fluent NHibernate to the application

Open project.json and add following dependencies:

"NHibernate": "4.0.3.4000",
"FluentNHibernate": "2.0.1.0",
"Npgsql": "2.2.5.0"

Create simple model:

public class Item
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

and mapping for that model:

using FluentNHibernate.Mapping;

public class ItemMap : ClassMap<Item>
{
    public ItemMap()
    {
        Id(item => item.Id).GeneratedBy.Increment();
        Map(item => item.Name);
        Table("items");
    }
}

Now you can add or delete items from database like this:

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;
using System.Collections.Generic;

using System;
using System.Linq;

namespace DAL
{
    public class ItemContext
    {
        public IList<Item> GetAll()
        {
            var sessionFactory = CreateSessionFactory();

            using (var session = sessionFactory.OpenSession())
            {
                using (var trans = session.BeginTransaction())
                {
                    var item = new Item { Name = "Test"};
                    session.SaveOrUpdate(item);
                    trans.Commit();
                }

                using (session.BeginTransaction())
                {
                    var items = session.CreateCriteria(typeof(Item)).List<Item>();
                    return items;
                }
            }
        }

        private ISessionFactory CreateSessionFactory()
        {
            return Fluently
                .Configure()
                    .Database(
                        PostgreSQLConfiguration.Standard
                        .ConnectionString(c =>
                            c.Host("localhost")
                            .Port(5432)
                            .Database("mydb")
                            .Username("postgres")
                            .Password("password!")))
                    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<ItemMap>())
                    .ExposeConfiguration(TreatConfiguration)
                .BuildSessionFactory();
        }
        private static void TreatConfiguration(Configuration configuration)
        {
            // dump sql file for debug
            Action<string> updateExport = x =>
            {
                using (var file = new System.IO.FileStream(@"update.sql", System.IO.FileMode.Append, System.IO.FileAcce$
                using (var sw = new System.IO.StreamWriter(file))
                {
                    sw.Write(x);
                    sw.Close();
                }
            };
            var update = new SchemaUpdate(configuration);
            update.Execute(updateExport, true);
        }
    }
}

It will automatically create database scheme when you connect for the first time.

Add WebAPI controller to the application

Now it is time to test our data access layer, so we need to add some Web API methods for that.

Add ItemController.cs file to the Controllers folder:

using DAL;
using Microsoft.AspNet.Mvc;
using System.Collections.Generic;
using System.Linq;
using System.Threading;

namespace HelloMvc.Controllers
{
    [Route("api/[controller]")]
    public class ItemController : Controller
    {
        [HttpGet]
        public IEnumerable<Item> GetAll()
        {
            var allItems = new ItemContext().GetAll();
            return allItems;
        }
    }
}

Now start your application with:

k kestrel

and go to: http://127.0.0.1:5004/api/item

In the next part, I am going to show how to use ASP.NET 5 dependency injection and Fluent NHibernate auto-mapping with automatic migration.