Using the PostgreSQL Service from an ASP.NET App on GE’s Predix

PostgreSQL is one of the data management services available for applications running on the Predix platform. Here, you will learn about accessing and managing a PostgreSQL database instance hosted on Cloud Foundry.

Prerequisites

To follow the steps of this tutorial, you need:

• a Predix account
• Microsoft Visual Studio 2015
• Microsoft .NET Core 1.0
• the Cloud Foundry CLI
• the Git CLI

Preparing the application for Predix

To implement your ASP.NET application and make it ready for deployment to Predix, proceed with the following steps:

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 a 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 what’s 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>

To add new authors to the database, you 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 on Predix.

Managing PostgreSQL on Predix

The easiest way to manage the PostgreSQL database is via using phpPgAdmin. We recommend to follow the instructions from the Predix Knowledge Base tutorial explaining how to utilize 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 name with a unique value.

---
#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 the browser, where you can get full access to your PostgreSQL database.

The source code for this tutorial is available in this GitHub repository.

Conclusion

Due to 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.

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

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

Further reading

• Deploying an ASP.NET Application to GE Predix
• How to Get Started with ASP.NET and WebSocket on GE Predix
• Introducing a One-Click Button for Deploying to GE Predix