PostgreSQL data provider for Nano applications._
Data Provider implementation for PostgreSQL data access.
📖 Learn more about Nano Data. 📖 Learn more about Nano Azure PostgreSQL.
Try it out yourself using the Api.Data.PostgreSQL, or Console.Data.PostgreSQL example.
Install the Nano.Data.PostgreSQL NuGet package.
dotnet add package Nano.Data.PostgreSQL;Register the PostgreSqlProvider provider during application startup in the ConfigureServices(...) method.
.ConfigureServices(services =>
{
services
.AddNanoData<PostgreSqlProvider, TContext>();
})A BaseDbContext and BaseDbContextFactory must also be implemented and used as TContext, and also an initial migration added.
dotnet ef migrations add Initial --project {project-name}Add the data configuration to appsettings.json.
"Data": {
"BatchSize": 25,
"BulkBatchSize": 500,
"BulkBatchDelay": 1000,
"QueryRetryCount": 0,
"UseLazyLoading": false,
"StartupAction": "None",
"UseSensitiveDataLogging": false,
"QuerySplittingBehavior": "SingleQuery",
"DefaultCollation": null,
"ConnectionString": null,
"Repository": {
"UseAutoSave": false,
"QueryIncludeDepth": 4
},
"Identity": null,
"ConnectionPool": null,
"HealthCheck": {
"UnhealthyStatus": "Unhealthy"
}
}...and appsettings.Development.json
"Data": {
"UseMigrateDatabase": true,
"ConnectionString": "Host=host.docker.internal;Port=5432;Database=nanoDb;Username=sa;Password=myPassword_123"
}Add PostgreSQL as a service dependency in docker-compose.yml.
services:
{service-name}:
depends_on:
- database
database:
image: postgis/postgis:latest
ports:
- 5432:5432
networks:
- network
environment:
POSTGRES_USER: sa
POSTGRES_PASSWORD: myPassword_123
POSTGRES_DB: nanoDbAdd the %SERVICE_NAME%-secret for the connectionstring to the deployment.yaml.
spec:
template:
spec:
containers:
env:
- name: Data__ConnectionString
valueFrom:
secretKeyRef:
name: %SERVICE_NAME%-data-secret
key: data-connectionstringAdd the following environment variables to the buid-and-deply.yml.
env:
DATA_HOST: ${{ github.ref == 'refs/heads/master' && secrets.PRODUCTION_POSTGRE_HOST || secrets.STAGING_POSTGRE_HOST }}
DATA_NAME: nanoDb
DATA_USER: api-data-postgre-user
DATA_PASSWORD: ${{ github.ref == 'refs/heads/master' && secrets.PRODUCTION_POSTGRE_NANO_DB_PASSWORD || secrets.STAGING_POSTGRE_NANO_DB_PASSWORD }}
DATA_ADMIN_USER: ${{ github.ref == 'refs/heads/master' && secrets.PRODUCTION_POSTGRE_ADMIN_USER || secrets.STAGING_POSTGRE_ADMIN_USER }}
DATA_ADMIN_PASSWORD: ${{ github.ref == 'refs/heads/master' && secrets.PRODUCTION_POSTGRE_ADMIN_PASSWORD || secrets.STAGING_POSTGRE_ADMIN_PASSWORD }}
DATA_CONNECTIONSTRING: Host=${{ env.DATA_HOST }};Port=${{ vars.DATA_POSTGRE_PORT }};Database=${{ env.DATA_NAME }};Username=${{ env.DATA_USER }};Password=${{ env.DATA_PASSWORD }};SSL Mode=Prefer;Trust Server Certificate=true
DATA_MIGRATION_CONNECTIONSTRING: Host=${{ env.DATA_HOST }};Port=${{ vars.DATA_POSTGRE_PORT }};Database=${{ env.DATA_NAME }};Username=${{ env.DATA_ADMIN_USER }};Password=${{ env.DATA_ADMIN_PASSWORD }};SSL Mode=Prefer;Trust Server Certificate=trueAdditionally, this step has been added to ensure database migrations are applied, and the application database user has been created before the application is deployed.
- name: Database Migration
shell: pwsh
run: |
dotnet ef database update `
--no-build `
--startup-project $env:APP_NAME `
--connection "$env:DATA_MIGRATION_CONNECTIONSTRING" `;
if ($LastExitCode -ne 0)
{
throw "error";
};
sudo apt-get update
sudo apt-get install -y postgresql-client
$userExists = psql "host=$env:DATA_HOST port=$env:DATA_POSTGRE_PORT user=$env:DATA_ADMIN_USER password=$env:DATA_ADMIN_PASSWORD dbname=postgres" `
-tAc "SELECT 1 FROM pg_roles WHERE rolname='$env:DATA_USER';"
if ($userExists -ne "1")
{
psql "host=$env:DATA_HOST port=$env:DATA_POSTGRE_PORT user=$env:DATA_ADMIN_USER password=$env:DATA_ADMIN_PASSWORD dbname=postgres" `
-c "CREATE ROLE $env:DATA_USER WITH LOGIN PASSWORD '$env:DATA_PASSWORD';"
}
$userDbExists = psql "host=$env:DATA_HOST port=$env:DATA_POSTGRE_PORT user=$env:DATA_ADMIN_USER password=$env:DATA_ADMIN_PASSWORD dbname=$env:DATA_NAME" `
-tAc "SELECT 1 FROM pg_roles WHERE rolname='$env:DATA_USER';"
if ($userDbExists -ne "1")
{
psql "host=$env:DATA_HOST port=$env:DATA_POSTGRE_PORT user=$env:DATA_ADMIN_USER password=$env:DATA_ADMIN_PASSWORD dbname=$env:DATA_NAME" `
-c "GRANT CONNECT ON DATABASE $env:DATA_NAME TO $env:DATA_USER;"
psql "host=$env:DATA_HOST port=$env:DATA_POSTGRE_PORT user=$env:DATA_ADMIN_USER password=$env:DATA_ADMIN_PASSWORD dbname=$env:DATA_NAME" `
-c "GRANT USAGE ON SCHEMA public TO $env:DATA_USER;"
psql "host=$env:DATA_HOST port=$env:DATA_POSTGRE_PORT user=$env:DATA_ADMIN_USER password=$env:DATA_ADMIN_PASSWORD dbname=$env:DATA_NAME" `
-c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO $env:DATA_USER;"
psql "host=$env:DATA_HOST port=$env:DATA_POSTGRE_PORT user=$env:DATA_ADMIN_USER password=$env:DATA_ADMIN_PASSWORD dbname=$env:DATA_NAME" `
-c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO $env:DATA_USER;"
}Last, the application connectionstring must be added in a secret in Kubernetes in the Kubernetes Deploy step.
sudo kubectl create secret generic $env:SERVICE_NAME-data-secret ` --from-literal=data-connectionstring=$env:DATA_CONNECTIONSTRING --save-config --dry-run=client -o yaml | sudo kubectl apply -f -;
if ($LastExitCode -ne 0)
{
throw "error";
};