Connecting and Databases
psql CLI, pgAdmin, CREATE DATABASE, roles and superusers
Skills taught: pg.psql, pg.create-database, pg.roles, pg.connection-strings
The connection string
postgresql://username:password@host:port/database
Examples:
postgresql://postgres:secret@localhost:5432/myapp
postgresql://app_user:pass@db.example.com:5432/production
Environment variable (conventional):
DATABASE_URL=postgresql://postgres:secret@localhost:5432/myapp
Default values:
host = localhost
port = 5432
username = postgres (the default superuser)
database = same as username if omitted
psql — the PostgreSQL CLI
# Connect to a database
psql postgresql://postgres:secret@localhost:5432/myapp
# Or with flags
psql -h localhost -p 5432 -U postgres -d myapp
# Meta-commands (no semicolon needed)
\l -- list databases
\c myapp -- connect to a database
\dt -- list tables in current schema
\d users -- describe table 'users'
\dn -- list schemas
\du -- list roles/users
\q -- quit
# Run a SQL file
\i /path/to/script.sql
Creating databases and roles
-- Create a database for your app
CREATE DATABASE myapp;
-- Create a role (user) with a password
CREATE ROLE app_user WITH LOGIN PASSWORD 'strongpassword';
-- Grant all privileges on the database to the role
GRANT ALL PRIVILEGES ON DATABASE myapp TO app_user;
-- Connect to the new database
\c myapp
-- Grant usage on the public schema (needed in Postgres 15+)
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;
-- Delete a database (all data gone)
DROP DATABASE IF EXISTS myapp;
Database vs schema vs table
PostgreSQL Server
├── Database: myapp (isolated — cross-DB queries need foreign data wrappers)
│ ├── Schema: public (default schema — most tables go here)
│ │ ├── Table: users
│ │ ├── Table: posts
│ │ └── Table: comments
│ └── Schema: analytics (separate namespace for reporting tables)
│ └── Table: events
└── Database: myapp_test (separate DB for test environment)
Tip: For most applications: one database per application, one schema (public). Use multiple schemas if you need multi-tenant isolation or want to separate application and analytics tables cleanly.
Frequently Asked Questions
What GUI tools exist for PostgreSQL?
pgAdmin 4 is the official free GUI. TablePlus, DataGrip (JetBrains), DBeaver, and Postico (macOS) are popular commercial/freemium options. Most developers use a mix: GUI tools for exploration and schema inspection, CLI (psql) for quick queries and scripting.
How do I connect to PostgreSQL from Node.js?
Use the 'pg' npm package: const { Pool } = require('pg'); const pool = new Pool({ connectionString: process.env.DATABASE_URL }); const result = await pool.query('SELECT * FROM users'); — or use an ORM like Prisma or Drizzle that manages connections for you.