Backend Development › Relational Databases & SQL

Database Connection and Connection String

How an app connects to a database: host, port, credentials and options.

Also known as: connection string, DSN, database URL, DATABASE_URL, connection URI

To use a database, your app opens a connection to the database server, over the network (or a local socket). The connection string says where it is and how to log in.

postgresql://app_user:s3cret@db.example.com:5432/shop?sslmode=require
└───┬────┘   └──┬───┘ └──┬───┘ └──────┬──────┘ └┬─┘ └─┬┘ └────┬─────┘
 database    user  password          host     port  db     options
 type

Formats differ between languages and drivers, but they carry the same pieces.

import os, psycopg                 # one common Postgres driver
conn = psycopg.connect(os.environ["DATABASE_URL"])
with conn.cursor() as cur:
    cur.execute("SELECT 1")

Good practice

• Don’t hard-code it, and don’t commit it. It contains the password. Read it from an environment variable (the name DATABASE_URL is a common convention), as part of your configuration.
• Different credentials per environment, with least privilege: the app user doesn’t need to be a superuser.
• Use TLS when connecting over a network.
• Special characters in the password (@, /, :, #) must be URL-encoded in a URL-style string, or the parsing breaks.
• Connections are expensive and limited. Opening one per request is slow, and databases cap how many they accept. Applications use a connection pool and always release connections (use with blocks or try/finally).
• Set timeouts for connecting and querying (timeouts).

When it fails

A database client such as psql is handy for testing the same string outside your app (database client).