Connecting to Heroku Postgres Database with Asp.Net

Question

I'm looking for the best way to connect to a Heroku Postgres database from an outside application. The application is in asp.net. I am trying to use NpgSQL but am getting a certificate unknown error. Has anyone done that? Do you have any pointers?

Answer 1

I use this to "translate" the DATABASE_URL to a connection string

var databaseUrl = Environment.GetEnvironmentVariable("DATABASE_URL");
var databaseUri = new Uri(databaseUrl);
var userInfo = databaseUri.UserInfo.Split(':');

var builder = new NpgsqlConnectionStringBuilder
{
    Host = databaseUri.Host,
    Port = databaseUri.Port,
    Username = userInfo[0],
    Password = userInfo[1],
    Database = databaseUri.LocalPath.TrimStart('/')
};

return builder.ToString();

Answer 2

I am using the following connection string to connect to Heroku Postgres Database:

string connString = "User ID=<user>;Password=<pass>;Host=<host>;Port=<port>;Database=<database>;Pooling=true;Use SSL Stream=True;SSL Mode=Require;TrustServerCertificate=True;"

where <user>, <pass>, <host>, <port> and <database> are values from heroku postgres app settings. Port is 5432 by default.

Then using NpgSql I create instance of NpgsqlConnection:

var conn = new NpgsqlConnection(connString);

Dafault sample: http://www.npgsql.org/doc/index.html