Creating Databases

Introduction

A database is a logical container for tables. Inside one DBMS server you can have many databases — typically one per application or environment. Creating one is a single statement.

Understanding the topic

Core concepts to understand:

• CREATE DATABASE shop; creates a new empty database.
• DROP DATABASE shop; deletes it permanently.
• Connect to it with \c shop in psql or USE shop; in MySQL.
• Each database has its own users, schemas and tables.
• Use environment-specific databases: shop_dev, shop_test, shop_prod.

Syntax reference

Visual workflow / architecture:

Postgres Server
   ├── shop_dev   (developers)
   ├── shop_test  (CI tests)
   └── shop_prod  (production)
         ├── public.users
         ├── public.orders
         └── public.products

Real-world use

Every SaaS team has at least three databases — local, staging, production — to avoid accidents. Some multi-tenant apps create one DB per customer for isolation.

Best practices

• Use one database per app per environment.
• Never run migrations directly against prod from a laptop.
• Set OWNER when creating to control privileges.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Difference between a database and a schema.
• Q2. SQL to create and drop a database.
• Q3. Why have separate dev and prod databases?
• Q4. What is database OWNER?
• Q5. How would you copy a database for testing?