Using the PostgreSQL Service in an ASP.NET App on GE Predix

Datetime:2016-08-22 22:04:21          Topic: ASP.NET  PostgreSQL           Share

PostgreSQL is one of the data management services available for applications running on the Predix platform. This article shows how to work with a Cloud Foundry-hosted instance of the Postgres service from an ASP.NET application.

First, we will create and deploy a simple ASP.NET Core 1.0 application to Predix. Then, you can learn about accessing and managing your PostgreSQL database instance.

Prerequisites

To follow the steps of this tutorial, you need:

Preparing the application for Predix

To implement your ASP.NET application and make it ready for deployment to Predix:

1. Go to Microsoft Visual Studio 2015 and create a new project using a standard ASP.NET 5 template.

2. Compile and start the application to check if it works locally.

3. Create the AuthorsContext.cs database context file:

public class AuthorsContext : DbContext
{
    public AuthorsContext(DbContextOptions<AuthorsContext> options)
        : base(options)
    { }

    public DbSet<Author> Authors { get; set; }
}

4. Add a simple POCO object:

public class Author
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

5. Additionally, create a controller with one self-explanatory action:

public class HomeController : Controller
{
    private readonly AuthorsContext _context;

    public HomeController(AuthorsContext context)
    {
        _context = context;
    }

    public IActionResult Add(int id)
    {
        try
      {
          _context.Authors.Add(new Author()
        {
            Id = id,
            FirstName = "Eugene",
            LastName = "Lahansky"
        });
        _context.SaveChanges();

        var added = _context.Authors.FirstOrDefault(x => x.Id == id);
        return new JsonResult(string.Format("Inserted author: {0} {1}", added.FirstName, added.LastName));
      }
      catch (Exception ex)
      {
          return new JsonResult(string.Format("Error: {0}\r\n {1}", ex.Message, ex.InnerException));
      }
    }
}

6. Finally, configure the database context:

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc();
    const string envName = "VCAP_SERVICES";
    var settings = Environment.GetEnvironmentVariable(envName);
    var jSettings = JObject.Parse(settings);
    var postgresCreds = jSettings["postgres"][0]["credentials"];
    var username = postgresCreds["username"];
    var password = postgresCreds["password"];
    var host = postgresCreds["host"];
    var port = postgresCreds["port"];
    var database = postgresCreds["database"];

    var connectionString = $"User ID={username};Password={password};Server={host};Port={port};Database={database};Pooling=true;";
    services.AddDbContext<AuthorsContext>(
        opts => opts.UseNpgsql(connectionString)
    );
}

The application is now ready for pushing to Predix. To do this, run the following commands in your Command Prompt:

  1. cd <the folder where project.json is located>
  2. cf push testaspnetcore -b https://github.com/cloudfoundry-community/dotnet-core-buildpack

If everything is done right, you will see something similar to the shown in the image below.

Next, create a postgres service instance and bind it to your application:

  1. cf create-service postgres shared-nr <postgres service instance name>
  2. cf bind-service <app name> <postgres service instance name>
  3. cf restage <app name>

For adding new authors to the database, we need to create a table. Note that you cannot access the Predix PostgreSQL database using, for example, pgAdmin. The database can be accessed only from applications deployed to Predix.

Managing PostgreSQL on Predix

The easiest way to manage the PostgreSQL database is to use phpPgAdmin. I recommend to follow the instructions from the Predix Knowledge Base article explaining how to use the tool to access data in your Cloud Foundry-hosted Postgres instance. Shortly, the steps are as follows:

  1. Open cmd.exe and create a directory for phpPgAdmin.
  2. Clone the following Git repository:

    git clone https://github.com/cloudfoundry-community/phppgadmin-cf
  3. Find the manifest.yml file and update the name value to something unique:

    ---
    #Generated manifest
    applications:
    - name: phppgadmin-cfready-devnet
      memory: 1G
      instances: 1
  4. Push the application:

    cf push
  5. Bind your Postgres service to the phpPgAdmin application:

    cf bind-service <phppgadmin app name> <postgres service instance name>
  6. Restage the phpPgAdmin application:

    cf restage <phppgadmin app name>

After these steps, open phpPgAdmin in a browser, where you can get full access to your PostgreSQL database:

The source code for this tutorial is available in our GitHub account .

Conclusion

Because of security reasons, Predix does not allow users to access the PostgreSQL database from outside. The only possible way is to manage the database from an application running on Predix.

Also, do not forget to read the VCAP_SERVICES variable in your application. In this article, we have used the standard .NET method:

var settings = Environment.GetEnvironmentVariable(“VCAP_SERVICES”);

Related tutorials:

About the author

Eugene Lahanskyis a Solutions Architect at Altoros. With his extensive experience in .NET programming, he designs and develops web-based software for Windows platforms. Eugene also works with the UNIX stack of technologies, including NGINX, Docker, and Node.js.





About List