Blog

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

Eugene Lahansky

using-predix-postgresql-service-in-asp-net-app-v2

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; }
}
  1. Add a simple POCO object:
public class Author
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}
  1. 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));
      }
    }
}
  1. 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.

using-ge-predix-postgre-sql-in-asp-net-core-app

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, 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 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 name with a unique value:
---
#Generated manifest
applications:
- name: phppgadmin-cfready-devnet
  memory: 1G
  instances: 1
  1. Push the application:

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

    cf bind-service <phppgadmin app name> <postgres service instance name>
  3. 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:

managing-ge-predix-postgre-sql

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

 

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 Lahansky is 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.


For the next parts of this series, subscribe to our blog or follow @altoros.

Get new posts right in your inbox!

4 Comments
  • Hugo

    Is it possible to use

    > dotnet ef database update

    in Predix?

    • Eugene

      Hi George,

      Definitely. Predix limits are actually buildpack limitations. Just make sure that Entity Framework libs are compatible with dotnet version of your app.

      • Hugo

        Thank you Eugene! How can I run this command (dotnet …) on a Predix server?

        Please forgive for my n00b questions:)

        • Eugene

          As you can see, this question was already asked on predix forum.
          https://forum.predix.io/questions/1704/how-can-i-ssh-to-my-app-instance.html
          Cloud Foundry allows (https://docs.cloudfoundry.org/devguide/deploy-apps/ssh-apps.html) you provisioning to your application container, but Predix hides this feature for users.
          So, the answer is – no, you can’t run your commands on Predix server 🙂

Benchmarks and Research

Subscribe to new posts

Get new posts right in your inbox!