The database connection behind an analytics dashboard deserves its own design review. A working query proves that the app reached a server. It does not, by itself, prove that the intended server answered or that credentials stayed out of the browser.
In Pierre Analytics, a Node.js and Express server reads PostgreSQL through pg. The React frontend asks that server for dashboard data; it never receives the database URL. This chapter explains the connection decisions observed in the project. It belongs to the Building Pierre Analytics series; see the system overview for the rest of the architecture.
Choose the connection mode for the runtime
Supabase offers direct connections and pooler modes. The project’s local test used the Session Pooler over IPv4 on port 5432. That choice matched the tested network and a persistent Node process. Supabase documents Session Pooler as the IPv4 alternative to a direct connection; transaction mode is a different option with different session behavior. Copy the connection details from the correct project’s Connect panel instead of constructing a hostname from memory. Supabase’s connection guide explains the modes.
There are two pooling layers in this setup. Supabase provides the remote pooler; the application also creates one pg.Pool for reuse inside its process. The current code caps that application pool at ten connections and sets connection and idle timeouts. These are implementation settings, not a universal recommendation for every hosting plan.
Keep secrets on the server
The server reads DATABASE_URL from its environment. It rejects a missing URL, a non-PostgreSQL URL, and, in production mode, a URL that lacks sslmode=verify-full. The code accepts one trusted certificate source: DB_SSL_CA_FILE for a local file or DB_SSL_CA_BASE64 for environments that supply the certificate through a variable. It rejects simultaneous use of both.
The names are safe to document; their real values are not. The URL contains the database user and password. The certificate is not a password, but publishing the actual project configuration or copying it into a frontend bundle would still reveal unnecessary infrastructure detail. Keep all of these values in server configuration and exclude .env and certificate files from upload archives and source control. For a local test, use a dedicated test database and a local environment file rather than a production database.
When a password contains URL-reserved characters, it must be encoded as part of the URL. Otherwise the URL parser can mistake a character in the password for a separator. A failed login can therefore mean a malformed connection string, not a bad database account. Do not paste the whole connection string into an issue, screenshot, terminal transcript or article while diagnosing it.
Verify the TLS peer
TLS encryption and server identity are separate checks. The project supplies its trusted certificate authority to pg and sets rejectUnauthorized: true, so Node verifies the certificate chain and hostname. Its database module removes sslmode from the URL after validating that production configuration requested verify-full, because the URL parser used by pg can replace an explicit SSL configuration when an SSL query parameter is present. The project comment records this as a configuration trap found during setup.
This is a case where copying a one-line sample can be misleading. The relevant safety property is the effective driver configuration. A local connection test reported authorized TLS and hostname verification with both the certificate-file and base64-variable paths. The Supabase SSL instructions explain why downloading a certificate alone is insufficient; the client has to use it.
Avoid “fixing” a certificate error by disabling verification. Check that the certificate belongs to the selected Supabase project, that the pooler host came from that project’s Connect panel, and that the certificate value reached only the server. Do not print either value while debugging.
What worked
The project’s delivery record documents a local connection to a dedicated Supabase test project through the Session Pooler with authorized TLS. Migrations, authentication, event ingestion and dashboard reads were validated against that test database. A separate build archive was extracted and exercised locally with environment values supplied at runtime; the archive excluded the environment file, certificate and test data.
This is strong evidence for the tested local path. It does not verify that a hosting provider will accept a certificate variable, expose the expected network route or keep the same runtime settings. Hosted validation remains a separate step.
What was difficult
The connection mode, certificate location and URL parsing all had to agree. A direct database address can fail on an IPv4-only network. A correct Session Pooler URL paired with the wrong project’s certificate will fail verification. A URL with an SSL option can unexpectedly override a separately supplied pg SSL object. The practical debugging order is to confirm mode and host, confirm server-only variables, then inspect sanitized error categories and TLS authorization in a dedicated test environment.
Current limitations and next steps
This article does not include a copyable production connection string or deployment recipe. The actual Hostinger runtime has not been tested in the project record. Before deployment, an operator must verify that environment values and the project certificate can be provisioned securely, run migrations against the intended database, and check both process health and a database-backed health response. The measurement chapter explains what the resulting dashboard counts once events arrive.
Sources and further reading
Primary and official sources used to verify factual guidance. Product features and policies can change; check the linked source before a consequential decision.
- Connect to your database — Supabase Docs: Connection modes, IPv4 Session Pooler, and SSL certificate guidance.
- TLS (SSL) — Node.js Documentation: Certificate authority and peer verification behavior.