bambit ag - churz & bündig
Dienstag, 12. April 2016

ASP.net Core mit Postgresql aus Heroku

Für die Datenhaltung setzt Heroku auf Postgresql. In dem Blogpost 5 Schritte um ASP.net Core mit postgresql zu verwenden habe ich gezeigt wie du mit Hilfe von Dapper in ASP.net Core auf eine Postgresql-Datenbank zugreifen kannst.

In einem ersten Schritt habe ich nun versucht die lokale Postgresql-Datenbank aus dem Projekt PostgresqlUndDapper mit einer Posgresql-Datenbank aus Heroku zu ersetzen. Mit folgenden Schritten kommst du zum Ziel:

  1. Heroku Account einrichten und Postgresql-Datenbank erstellen
  2. Entwicklungsumgebung für Heroku konfigurieren
  3. Zugriff auf Heroku Postgresql-Datenbank
  4. Daten von lokaler Postgresql-Datenbank auf Heroku migrieren

1. Heroku Account einrichten und Postgresql-Datenbank erstellen

Als aller erstes benötigst du natürlich ein Heroku Account. Ich habe mit der Free-Version gearbeitet. Für dich wird dies wahrscheinlich auch ausreichen. Nachdem die Registrierung sowie die Aktivierung deines Accounts abgeschlossen hast, kannst du über das Heroku-Dashboard einfach eine neue Postgresql-Datenbank anlegen:

Neue Datenbank in Heroku erstellen

2. Entwicklungsumgebung für Heroku konfigurieren

Heroku liefert ein eigenes Toolset um von deiner Entwicklungsumgebung aus mit Heroku zu arbeiten. Dieses Toolset nennt sich Toolbelt. In einem nächsten Schritt gilt es also diesen Toolbelt auf deiner lokalen Entwicklungsumgebung zu installieren.

3. Zugriff auf Heroku Postgresql-Datenbank

In einem nächsten Schritt geht es darum, den Zugriff von der ASP.net 5.0 Applikation auf die Posgresql-Datenbank von Heroku sicherzustellen. Dabei sind verschiedene Schritte und Code-Anpassungen nötig. Du findest die Anpassungen zusätzlich in dem Github-Repoistory unter dem Branch postgresqlfromheroku

3.1 Zugriffsinformationen ermitteln

Damit du von der ASP.net 5.0 Applikation auf die Postgresql-Datenbank auf Heroku zugreifen kannst, werden die Zugangsinformationen benötigt. Dazu benötigen wir folgende Angaben:

  • Server-Name
  • Port
  • Databasename
  • User Id
  • Passwort

Die Angaben erhälst du via dem Heroku Database-Dashboard. Wenn du dort deine neu erstellte Datenbank auswählst, siehst du unter dem Tab Connection Settings die benötigten Angaben:

Zugriffsinformationen um auf Postgresql von Heroku zuzugreifen

3.2 Anpassen des ConnectionString

Als Nächstes müssen wir den ConnectionString unserer ASP.net 5.0 Applikation anpassen. Dies machst du in der Klasse Startup.cs

namespace PostgresqlUndDapper
{
    public class Startup
    {
        public Startup(IHostingEnvironment env)
        {
            // Set up configuration sources.
            var builder = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json")
                .AddEnvironmentVariables();
            Configuration = builder.Build();
        }
 
        public IConfigurationRoot Configuration { get; set; }
 
        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddMvc();
 
            var connection = @"Server = 127.0.0.1; Port = 5432; Database = RetschgisPostgresqlTest; User Id = postgres; Password = {your password};";
 
            services.AddSingleton<IEmployeeRepository, EmployeeRepository>(parameter => new EmployeeRepository(connection));
        }
 
...
  }
}

Folgendermassen musst du die Informationen aus Heroku übertragen:

  • Host -> Server
  • Port -> Port
  • Database -> Database
  • User -> User Id
  • Password -> Passwort

Hinweis: Heroku rät davon ab direkt mit dem ConnectionString zu arbeiten. Für unsere Testzwecke sollte diese Ausnahme aber OK sein.

Wenn du die Anpassungen gemacht hast und anschliessend deine Applikation startest und die View Employees aufrufst (http://localhost:50217/Employee ), wirst du folgende Fehlermeldung erhalten:

Npgsql.NpgsqlException was unhandled by user code
  BaseMessage=no pg_hba.conf entry for host "XYZ", user "XYZ", database "XYZ", SSL off
  Code=28000
  ErrorCode=-2147467259
  File=auth.c
  HResult=-2147467259
  InternalPosition=0
  Line=474
  Message=28000: no pg_hba.conf entry for host "XYZ", user "XYZ", database "XYZ", SSL off
  MessageText=no pg_hba.conf entry for host "XYZ", user "XYZ", database "XYZ", SSL off
  Position=0
  Routine=ClientAuthentication
  Severity=FATAL
  Source=Npgsql
  StackTrace:
       bei Npgsql.NpgsqlConnector.DoReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage)
       bei Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage)
       bei Npgsql.NpgsqlConnector.HandleAuthentication()
       bei Npgsql.NpgsqlConnector.Open()
       bei Npgsql.NpgsqlConnectorPool.GetPooledConnector(NpgsqlConnection Connection)
       bei Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection connection)
       bei Npgsql.NpgsqlConnection.Open()
       bei PostgresqlUndDapper.RepositoriesPostgresql.AbstractPostgresqlRepository`2.Get() in D:\git\ASPNET5PlayGround\PostgresqlUndDapper\src\PostgresqlUndDapper\RepositoriesPostgresql\AbstractPostgresqlRepository.cs:Zeile 34.
       bei PostgresqlUndDapper.Controllers.EmployeeController.Index() in D:\git\ASPNET5PlayGround\PostgresqlUndDapper\src\PostgresqlUndDapper\Controllers\EmployeeController.cs:Zeile 21.
  InnerException:

Interessant ist der Name der Routine ClientAuthentication. Wie du hier entnehmen kannst wird von Heroku den Zugriff via SSL verlangt. Gemäss den ConnectionString-Optionen von Npgsql muss somit der ConnectionString mit folgenden Einträgen ergänzt werden:

var connection = @"Server = 127.0.0.1; Port = 5432; Database = RetschgisPostgresqlTest; User Id = postgres; 
Password = {your password}; SslMode=Require; UseSSLStream=true";

Nach dem du den ConnectionString ergänzt hast und die Applikation noch einmal startest, erhälst du wieder eine Fehlermeldung:

System.Security.Authentication.AuthenticationException was unhandled by user code
  HResult=-2146233087
  Message=The remote certificate is invalid according to the validation procedure
  Source=System
  StackTrace:
       bei System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)
       bei System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
       bei System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)
       bei System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation)
       bei Npgsql.NpgsqlConnector.RawOpen(Int32 timeout)
       bei Npgsql.NpgsqlConnector.Open()
       bei Npgsql.NpgsqlConnectorPool.GetPooledConnector(NpgsqlConnection Connection)
       bei Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection connection)
       bei Npgsql.NpgsqlConnection.Open()
       bei PostgresqlUndDapper.RepositoriesPostgresql.AbstractPostgresqlRepository`2.Get() in D:\git\ASPNET5PlayGround\PostgresqlUndDapper\src\PostgresqlUndDapper\RepositoriesPostgresql\AbstractPostgresqlRepository.cs:Zeile 34.
       bei PostgresqlUndDapper.Controllers.EmployeeController.Index() in D:\git\ASPNET5PlayGround\PostgresqlUndDapper\src\PostgresqlUndDapper\Controllers\EmployeeController.cs:Zeile 21.
  InnerException:

Hier gibt es zwei Lösungsansätze. Quick-and-Dirty ist, dass man beim Erstellen der NpgSqlConnection für UserCertificateValidationCallback ein Delegate übergibt, welcher immer true retourniert. Für unser Beispiel ausreichend, für eine echte Applikation wäre so ein Ansatz wie hier beschrieben wohl eher zu Verfolgen: http://www.limilabs.com/blog/the-remote-certificate-is-invalid-according-to-the-validation-procedure. Damit eine NpgSqlConnection immer gleich konfiguriert ist, bauen wir die Klasse AbstractPostgresqlRepository wie folgt um:

public abstract class AbstractPostgresqlRepository<TEntity,TPrimaryKey> : IRepository<TEntity, TPrimaryKey> where TEntity : BaseEntity<TPrimaryKey>
{
    private string _connectionString;
 
    public AbstractPostgresqlRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
 
 
    protected NpgsqlConnection GetConnection()
    {
        NpgsqlConnection connection = new NpgsqlConnection(_connectionString);
        connection.UserCertificateValidationCallback = delegate { return true; };
        return connection;
    }
 
 
    public IEnumerable<TEntity> Get()
    {
        using (NpgsqlConnection connection = GetConnection())
        {   
            connection.Open();
            string query = string.Format("SELECT * FROM {0}", TableName);
            return connection.Query<TEntity>(query);
        }
    }
 
    public TEntity Get(TPrimaryKey id)
    {
        using (NpgsqlConnection connection = GetConnection())
        {
            connection.Open();
            string query = string.Format("SELECT * FROM {0} WHERE Id = @Id LIMIT 1", TableName);
            return connection.Query<TEntity>(query, new { Id = id }).First();;
        }
    }
 
    public void Add(TEntity entity)
    {
        using (NpgsqlConnection connection = GetConnection())
        {
            connection.Open();
            IEnumerable<KeyValuePair<string, string>> RowsAndValues = ResolveProperties(entity);
            IEnumerable<string> keys = RowsAndValues.Select(c => c.Key);
            IEnumerable<string> values = RowsAndValues.Select(c => c.Value);
            string query = string.Format("INSERT INTO {0} ({1}) VALUES ({2});", TableName, string.Join(",",keys), string.Join(",", values));
            connection.Execute(query);
        }
    }
 
    public void Update(TEntity entity)
    {
        using (NpgsqlConnection connection = GetConnection())
        {
            connection.Open();
 
            IEnumerable<KeyValuePair<string, string>> RowsAndValues = ResolveProperties(entity);
            IEnumerable<string> keys = RowsAndValues.Select(c => c.Key);
            IEnumerable<string> values = RowsAndValues.Select(c => c.Value);
            string query = string.Format("UPDATE {0} SET ({1}) = ({2}) WHERE Id = @Id;", TableName, string.Join(",", keys), string.Join(",", values));
            connection.Execute(query, new { Id = entity.Id });
        }
    }
 
    public void Remove(TEntity entity)
    {
        using (NpgsqlConnection connection = GetConnection())
        {
            connection.Open();
            string query = string.Format("DELETE FROM {0} WHERE Id = @Id", TableName);
            connection.Execute(query, new { Id = entity.Id });
        }
    }
 
    private IEnumerable<KeyValuePair<string, string>> ResolveProperties(TEntity entity)
    {
        List<KeyValuePair<string, string>> result = new List<KeyValuePair<string, string>>();
 
        PropertyInfo[] infos = entity.GetType().GetProperties();
        foreach (PropertyInfo info in infos)
        {
            if(info.GetCustomAttribute<KeyAttribute>() == null)
            {
                result.Add(new KeyValuePair<string, string>(info.Name, string.Format("'{0}'", info.GetValue(entity))));
            }
        }
 
        return result;
    }
 
    protected abstract string TableName { get; }
}

Nun solltest du die Applikation ohne Probleme starten können und auch beim Öffnen von http://localhost:50217/Employee keine Fehlermeldungen mehr erhalten.

4. Daten von lokaler Postgresql-Datenbank auf Heroku migrieren

Damit wir nun beim Öffnen von http://localhost:50217/Employee auch effektiv Employees angezeigt bekommen, müssen wir die Daten aus unserer lokalen Posgresql-Datenbank zu der Posgresql-Datenbank von Heroku migrieren.

Dazu gibt es zwei Möglichkeiten:

  1. Via Import / Export
  2. Via Datenbankscript

Die Variante via Backup hat Heroku selber hervorragend beschrieben: https://devcenter.heroku.com/articles/heroku-postgres-import-export

Wenn du es via Datenbankscript machen willst, kannst du wie folgt vorgehen:

1. Öffne PgAdmin und verbinde dich auf den Datenbankserver. Als Zugriffsinformationen nimmst du die gleichen wie wir sie unter 3.1 ermittelt haben

2. Suche dir deine Datenbank aus der Liste heraus

Mit PgAdmin auf Heroku Postgresql zugreifen

3. Führe das folgende Datenbankscript aus

CREATE TABLE Employees (
    Id serial primary key not null,
    Name varchar(255) not null,
    YearOfContract integer not null
);
 
INSERT INTO Employees(Name, YearOfContract)
VALUES
  ('Hans Scheff', 2008),
  ('Fränzi Finanzi', 2009),
  ('Klaus Werber', '2011'),
  ('Fabienne Schaffer', 2009);

Wenn du die Applikation nun startest, solltest du unter der Employees-View alle erfassten Mitarbeiter sehen.

Autor

Reto Gurtner

säg am Reto hallo

bambit - Microsoft Partner für Softwareentwicklung in Bern

Sind Sie auf der Suche nach Microsoft .NET Experten?

Angebot ansehen