Module 3 — Creating Databases

1. CREATE DATABASE

What it is: CREATE DATABASE is a DDL (Data Definition Language) command that creates a new, empty database on your PostgreSQL server. A database is an isolated container that holds all the tables, views, indexes, and other objects for a specific project.

Why we use it: Every application needs its own database to keep its data separate from other applications. For example, a blog app and an e-commerce app should each have their own database to avoid data mixing and security issues.

When we use it: At the start of a new project, or when setting up a development/staging environment that mirrors production.

-- Create a database for a blog application
CREATE DATABASE blog_db;

Naming Conventions

• Use snake_case: blog_db, not BlogDB or blog-db
• Use a descriptive suffix: _db makes it clear this is a database
• Keep it short but meaningful

2. Listing and Connecting

What it is: PostgreSQL provides meta-commands (starting with \) to navigate between databases. These are specific to the psql client tool and help you manage your database server.

Why we use it: In a typical development environment, you'll have multiple databases (one per project, plus test databases). You need to quickly switch between them to run queries, check data, or perform maintenance.

When we use it: Every time you connect to PostgreSQL — you first list available databases, then connect to the one you need.

List all databases

-- In psql, use the meta-command:
\l

Connect to a database

-- In psql, switch to a database:
\c blog_db

Check which database you're in

-- In psql:
SELECT current_database();

3. DROP DATABASE

What it is: DROP DATABASE is a DDL command that permanently removes a database from the server, including all of its tables, data, indexes, views, and every other object inside it.

Why we use it: To clean up test databases, remove deprecated projects, or free up server resources. In production, this is extremely rare and restricted to superusers.

When we use it: During development (removing test databases), during cleanup of old projects, or in automated test pipelines that create and destroy databases.

-- Delete a database (requires no active connections)
DROP DATABASE blog_db;

Safe deletion with IF EXISTS

-- Only drops if it exists (prevents errors in scripts)
DROP DATABASE IF EXISTS blog_db;

4. Creating a Database with Options

What it is: PostgreSQL allows you to customize database creation with various options like owner, encoding, locale, and template. These options control how the database stores and sorts data.

Why we use it: Different projects may need different character encodings (for multi-language support), different owners (for access control), or different locales (for sorting order). Specifying these at creation time ensures consistent behavior.

When we use it: When setting up production databases, when working with international data, or when the database needs a specific owner for security reasons.

-- Specify the owner
CREATE DATABASE blog_db OWNER postgres;
 
-- Specify encoding (UTF-8 is the standard)
CREATE DATABASE blog_db
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

Common Mistakes to Avoid

1. Forgetting you can't rename a database — Plan your naming before creation
2. Dropping the wrong database — Always double-check with \l before dropping
3. Active connections block DROP — Close all connections or use DROP DATABASE ... WITH (FORCE) in Postgres 13+

Practice Exercise

1. Connect to your PostgreSQL server with psql -U postgres
2. Create a database called learning_hub
3. List all databases with \l
4. Connect to learning_hub with \c learning_hub
5. Verify you're connected with SELECT current_database();
6. Create another database called test_db
7. Drop test_db with DROP DATABASE test_db;
8. Verify test_db is gone with \l