Moving PortfolioBlog to Supabase changed where PostgreSQL runs, while preserving ownership of the application’s data flow. The browser still calls my API, Business owns validation and application decisions, and Data owns persistence. Keeping these boundaries meant that fixing a connection did not require rebuilding the site around a different client SDK.
This walkthrough follows the .NET 10, EF Core 10 and Npgsql 10 integration used by this site. It does not introduce Supabase Auth or browser-side Data API access. Uploaded files have a different lifecycle; they are covered in the separate Storage article.
Choose a connection mode for the lifetime of the process
The API is a persistent process on Render. I selected the Session pooler from the Supabase Connect panel for IPv4 access. Its port is 5432, and its username follows postgres.PROJECT_REF rather than the postgres username of a direct connection. Copy the actual host from the panel: constructing it from a region name is a good way to reach the wrong endpoint.
The transaction pooler commonly serves short-lived/serverless workloads and uses port 6543. This integration deliberately selects Session mode for the application’s schema/search_path and session assumptions; the configuration code rejects 6543. That is a constraint of this implementation, rather than a claim that transaction pooling is unsuitable for every application.
Packages and server configuration
dotnet add package Npgsql --version 10.0.3
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 10.0.3
dotnet tool restoreThese commands describe package setup for a new backend. This repository instead manages versions centrally in Directory.Packages.props, with the matching EF Core Design package and dotnet-ef tool for scaffolding. A separate Supabase SDK is not required to use its PostgreSQL database: the standard PostgreSQL provider is sufficient.
ConnectionStrings__Portfolio="Host=YOUR_SESSION_POOLER_HOST;Port=5432;Database=postgres;Username=postgres.YOUR_PROJECT_REF;Password=YOUR_PASSWORD"
Database__Schema=public
# Optional: override the bundled provider CA certificate.
# Database__RootCertificate=/app/config/provider-ca.crtASP.NET Core interprets double underscores as configuration section separators. ConnectionStrings__Portfolio is available through GetConnectionString("Portfolio"). Its database password is independent of the Storage server key and the administrator password. The displayed values are placeholders. The real configuration stays in server settings, outside Angular build inputs and API response data.
Use verified TLS, rather than merely an encrypted socket
Production connections use SslMode.VerifyFull, which checks the certificate chain and server hostname. I distribute the provider’s public root CA as config/supabase-ca.crt and copy it into the API publish output. This certificate contains no private key. A provider CA change requires updating the certificate and deployment settings together; disabling verification is not the repair.
The standalone console below expects an Npgsql-format PORTFOLIO_PG_CONNECTION and an absolute certificate path in PORTFOLIO_PG_CA. The actual API also accepts PostgreSQL URIs; the small example intentionally omits that parser. It reads at most five published records from an existing post table and performs no schema or content writes.
using Npgsql;
var raw = Environment.GetEnvironmentVariable("PORTFOLIO_PG_CONNECTION")
?? throw new InvalidOperationException("Set PORTFOLIO_PG_CONNECTION to an Npgsql connection string.");
var certificate = Environment.GetEnvironmentVariable("PORTFOLIO_PG_CA")
?? throw new InvalidOperationException("Set PORTFOLIO_PG_CA to the Supabase root certificate path.");
var options = new NpgsqlConnectionStringBuilder(raw)
{
SslMode = SslMode.VerifyFull,
RootCertificate = Path.GetFullPath(certificate),
SearchPath = "public",
MaxPoolSize = 10,
Timeout = 15,
CommandTimeout = 30
};
using var timeout = new CancellationTokenSource(TimeSpan.FromSeconds(45));
await using var dataSource = NpgsqlDataSource.Create(options.ConnectionString);
await using var command = dataSource.CreateCommand("""
SELECT slug, title_tr
FROM post
WHERE published AND NOT deleted
ORDER BY source_created_at DESC, id
LIMIT @limit
""");
command.Parameters.AddWithValue("limit", 5);
await using var reader = await command.ExecuteReaderAsync(timeout.Token);
while (await reader.ReadAsync(timeout.Token))
Console.WriteLine($"{reader.GetString(0)} | {reader.GetString(1)}");A DataSource owns a connection pool. Disposing it at the end suits this one-shot console; the API reuses its shared DataSource for the application lifetime. Creating another pool on every request would undermine that design. MaxPoolSize=10 limits one pool, not the entire deployment: replicas and independently created pools also consume connections.
DB-first entities and HTTP DTOs have different jobs
SQL scripts define the schema. Data/Models and Data/Context are generated from it, while custom query filters and function mappings live in a partial context. Core/DTOs describe the HTTP contract. The database has title_tr/title_en, category_id and source_created_at columns; the API exposes a { tr, en } title and localized category labels. An API object is not itself a persistence model.
The data model has six tables: profile, experience, education, project, category and post. The singleton profile holds the aggregate revision and update time. Posts and projects reference its GUID, and each post also references a category GUID. The API reads entities and projections; backup tools query the same tables directly. There is no extra JSON table or export view.
The scaffold-models --check command compares the generated model with the real schema. Explicit table selection keeps managed Supabase auth/storage objects out of the application model. Putting business behavior into a generated file would lose it on regeneration, which is why repositories, mappings and partial files remain separate.
Apply publication and pagination on the server
var page = await context.Posts.AsNoTracking()
.Where(post => post.Published && !post.Deleted)
.OrderByDescending(post => post.SourceCreatedAt)
.ThenBy(post => post.Id)
.Skip((pageNumber - 1) * 5)
.Take(5)
.Select(post => new { post.Slug, Title = post.TitleTr })
.ToArrayAsync(cancellationToken);Here pageNumber has already been validated as positive. Filtering, ordering, five-item pagination and projection translate to SQL; the application does not download every article before slicing a list. The id tie-breaker gives records with identical source timestamps a stable order. An administrator’s draft access and a visitor’s publication filter remain separate decisions.
The console’s @limit value is a bound SQL parameter. User input is not concatenated into a command. Turkish search has its own portfolio_contains function, lowercasing with a deterministic Turkish collation and performing literal substring matching. A reader searching for a percent sign or underscore does not accidentally introduce a LIKE wildcard. This is a deliberately bounded substring search, not a benchmarked full-text engine.
RLS, database ownership and API authentication are separate controls
-- Excerpt from the application's SQL; not a new migration to run blindly.
ALTER TABLE post ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON TABLE post FROM PUBLIC, anon, authenticated;
ALTER VIEW site_document SET (security_invoker = true);
-- Inspect the actual grants:
SELECT has_table_privilege('anon', 'public.post', 'SELECT') AS anon_can_read,
has_table_privilege('authenticated', 'public.post', 'SELECT') AS user_can_read;Content tables, SQL history and the Data Protection key table have RLS enabled. anon/authenticated have no read grants on the tables or export view. The API currently connects as the owning PostgreSQL role, which normally bypasses RLS. Consequently I still enforce publication rules in public queries and administrator authorization at API endpoints. Moving to a narrower database role requires its own grants, ownership design and checks.
Save revisions, concurrent edits and retry boundaries
An administrator saves the revision they originally read. SiteService returns 409 Conflict for a stale revision, and AtomicWrite groups changes in a Serializable transaction. The repository conditionally increments the revision before writing changed relations. The execution strategy can retry the whole database operation for supported serialization/deadlock failures such as 40001/40P01. A Storage upload or email does not belong inside that retry boundary.
Database setup is separate from API startup. npm run db:setup applies schema.sql in a transaction under an advisory lock. The SQL is idempotent and skips seeding when a profile already exists. upgrade.sql handles the previous layout without a separate migration-history table. The API only reads the installed schema. Migration checks compare identities, publication choices, both languages and source dates, rather than relying on row counts alone.
Verify behavior rather than trusting a successful startup
npm run db:audit
node scripts/scaffold-models.mjs --check
node scripts/verify-production.mjs
npm run db:backup- For rejected connections, inspect the actual Session host/5432, postgres.PROJECT_REF username and database password. A Storage API key is not an SQL password.
- For certificate failures, check the published CA path and hostname. Keep VerifyFull enabled.
- For a 409 save, reload the current revision; do not display an old document as successfully saved.
- Tests use temporary schemas. They do not reset production content or install test records into the live schema.
A database export does not include uploaded images. db:backup saves content, server security configuration and Storage files separately. Security__DataProtectionKeys supplies the cookie and CSRF key ring, without a database table or persistent disk. Keeping the ring stable preserves sessions across restarts. generate-keyring.mjs adds a new key while retaining previous keys for existing sessions. The value never enters the browser bundle.