Part 31: Creating Databases and Schemas: CREATE DATABASE & CREATE SCHEMA

Data Definition Language (DDL) allows database engineers to create, configure, and manage database namespaces.

1. Creating Databases

-- Create a new database:
CREATE DATABASE devify_production
  WITH 
  ENCODING = 'UTF8'
  CONNECTION LIMIT = 100;

2. Creating Schemas (Namespaces)

In enterprise PostgreSQL or SQL Server databases, a single database can contain multiple logical schemas:

CREATE SCHEMA IF NOT EXISTS billing;
CREATE SCHEMA IF NOT EXISTS analytics;
CREATE SCHEMA IF NOT EXISTS auth;

-- Creating a table inside a specific schema:
CREATE TABLE billing.invoices (
  id BIGINT PRIMARY KEY,
  amount DECIMAL(12, 2) NOT NULL
);

Key Takeaways

• Use CREATE DATABASE for global instance separation.
• Use CREATE SCHEMA to organize related domain tables cleanly within one database.

In Part 32, we will learn Dropping Databases & Schemas Safely with IF EXISTS!