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

By | April 26, 2015

Advertisement from Google

vNext

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; }
}

Advertisement from Google

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.

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

  1. Andrew

    So it seems that NHibernate fully compatible with new ASP.NET. Have you tried something more production ready with nginx in front of kestrel? What about security in Ubuntu (I mean corporate solutions like LDAP/AD)? Have you tried such things?

    Long time ago I used this framework with mono http://nancyfx.org/. But it seems to me that approach with native MVC is better to make it work under non-windows OS.

    Reply
    1. druss Post author

      Yes, I have set up nginx in front of kestrel as a reverse proxy, it works fine, but kestrel is not ready for production yet. It keeps crashing after a while. In the future I am going to write a short article about nginx + kestrel setup.

      Reply
  2. Mike Wallace

    Thanks for another great post Druss! This series is the only resource so far for this stack so far that I’ve found. Looking forward to the database migration section in the next post. Also any thoughts incorporating either the Repository or Command/Query patterns?

    Reply
    1. druss Post author

      Thanks for feedback! It’s nice to hear that someone read this articles 🙂 In the next part I am also going to show how to use built in dependency injection framework, and as part of this I will write simple repository.

      Reply
  3. Antonio Rezende

    Thank you for the content available on your blog. Just a question: Did you manage to implement Identity with NHibernate in vnext? Thanks in advance.

    Reply
  4. Shane

    Great article, Question: How would you unit test this? How would one create a InMemory instance for postgresql? I have an abstraction on the Session as a UnitofWork so need a test case that if commit is not called, that nothing is saved.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *