Database connection strings

The format for each database and each driver, with real examples, the TLS setting spelled the way each one wants it, and what to do when the password has an @ in it. Every example was built by the same code as the free builder, and the forms marked as tested were used to log in to a real server.

Build or convert a connection string

PostgreSQL

URI · Keyword/value (libpq) · JDBC · SQLAlchemy · .NET (Npgsql) · psql command · Environment variables (libpq)

postgresql://app_user:s3cret-Pa55@db.example.com:5432/shop?sslmode=verify-full

MySQL

URI · JDBC (Connector/J 8+) · SQLAlchemy · Go DSN (go-sql-driver/mysql) · .NET (MySqlConnector) · mysql command

mysql://app_user:s3cret-Pa55@db.example.com:3306/shop

MariaDB

URI · JDBC (MariaDB Connector/J 3) · SQLAlchemy · Go DSN (go-sql-driver/mysql) · .NET (MySqlConnector) · mariadb command

mariadb://app_user:s3cret-Pa55@db.example.com:3306/shop

SQL Server

ADO.NET (Microsoft.Data.SqlClient) · JDBC · ODBC · SQLAlchemy · Prisma · sqlcmd command

Server=tcp:db.example.com,1433;Database=shop;User Id=app_user;Password=s3cret-Pa55;Encrypt=True;TrustServerCertificate=False;

Oracle

Easy Connect · python-oracledb · JDBC (thin) · SQLAlchemy · .NET (ODP.NET) · Connect descriptor (tnsnames.ora) · SQL*Plus command

tcps://db.example.com:1521/ORCLPDB1

MongoDB

URI · mongosh command · PyMongo

mongodb://app_user:s3cret-Pa55@db.example.com:27017/shop?authSource=admin&tls=true

SQLite

File path · SQLAlchemy · JDBC · SQLite URI filename (read-only) · Prisma · sqlite3 command

/var/data/shop.db

Special characters in passwords

The single most common reason a connection string with the right password fails. In a URL these characters must be percent-encoded; in key=value forms the value is quoted instead (single quotes for libpq, double quotes for ADO.NET, braces for ODBC and SQL Server JDBC).

CharacterIn a URLWhy it breaks
@%40Ends the user name and password: everything after the first unencoded @ is read as the host.
:%3ASeparates user from password, and host from port.
/%2FStarts the database name.
?%3FStarts the options.
#%23Starts a fragment: the rest of the string is thrown away.
%%25Starts an escape itself, so a literal % must be escaped too.
&%26Separates options.
+%2BSome parsers (PyMongo among them) read + as a space.
space%20Ends the string on a command line, and is trimmed by some parsers.

Questions

What is a database connection string?

One line that tells a driver where a database is and how to log in: the host and port, the database name, the user and password, and options such as TLS. Most are URLs (postgresql://, mysql://, mongodb://); SQL Server and .NET use key=value pairs separated by semicolons; Java uses JDBC URLs that start with jdbc:.

Why does my connection string break when the password has special characters?

In a URL, @ : / ? # and % mean something, so a password containing them must be percent-encoded (@ is %40). Key=value forms need quotes around such a value instead. Most "authentication failed" and "invalid URI" errors with a correct password come from this.

Is it safe to put the password in the connection string?

It works, but the string then ends up in config files, shell history and process lists. Prefer the driver's separate password setting, an environment variable read at start-up, or a password file (~/.pgpass for PostgreSQL).

One connection string, two useful things to do with it

Browse the database with sensitive columns masked and every read logged, or migrate it to another engine with every table counted and checksummed afterwards. Free to start.