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 stringPostgreSQL
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).
| Character | In a URL | Why it breaks |
|---|---|---|
| @ | %40 | Ends the user name and password: everything after the first unencoded @ is read as the host. |
| : | %3A | Separates user from password, and host from port. |
| / | %2F | Starts the database name. |
| ? | %3F | Starts the options. |
| # | %23 | Starts a fragment: the rest of the string is thrown away. |
| % | %25 | Starts an escape itself, so a literal % must be escaped too. |
| & | %26 | Separates options. |
| + | %2B | Some parsers (PyMongo among them) read + as a space. |
| space | %20 | Ends 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.