SQL 6 🛢️ Creating and Dropping Databases

Every table lives inside a database. Before you can create a table, insert data, or run a query, the database must exist. The CREATE DATABASE statement is the first command you run on a fresh RDBMS installation. The DROP DATABASE statement is the last command you run when the database is no longer needed.

These two statements are part of DDL — Data Definition Language. They define and modify the structure of the database server itself, not the tables within a database. The CREATE DATABASE command creates a new container for tables, indexes, views, and other objects. The DROP DATABASE command removes the container and everything inside it. The ALTER DATABASE command changes the properties of an existing database.

Key point: A database is a namespace, not a file. In PostgreSQL and MySQL, the database is a logical container that holds schemas, tables, and other objects. The physical files are managed by the RDBMS and are not directly accessible. In SQLite, the database is a single file on disk, and the CREATE DATABASE statement does not exist — the database is created when the file is created. Understanding the difference between the logical and physical layers is the first step to working with databases.

---

Why creating and dropping databases matters

A database is the unit of isolation on a database server. Multiple applications can share the same server, and each application gets its own database. The database is the boundary that separates one application’s tables from another’s.

The isolation problem. A server hosts multiple applications. Each application has its own tables, its own users, and its own permissions. The database is the container that keeps them separate. A user with access to one database cannot see or modify the tables in another database unless they are granted the privilege .

The development problem. A developer needs a local copy of the production database to test changes. The developer creates a new database, imports the schema, and loads the data. The production database is unaffected. The local database can be dropped and recreated as many times as needed.

The multi-tenant problem. A SaaS application serves multiple customers. Each customer’s data is isolated in its own database. The CREATE DATABASE statement provisions a new tenant. The DROP DATABASE statement deprovisions a tenant. The isolation is enforced by the database boundary.

The naming problem. The database name is the first level of the namespace. A table named users in the app_production database is a different table from users in the app_staging database. The same table name can be reused across databases without conflict.

The trade-off. Creating a database is not free. Each database consumes disk space, memory, and connection slots on the server. A server with hundreds of databases has more overhead than a server with a few. The database is the right unit of isolation for many applications, but not all. Some applications use schemas within a single database for isolation, which is lighter weight.

---

a. Creating a Database

The CREATE DATABASE statement creates a new database on the server. The syntax is simple:

CREATE DATABASE myapp;

The statement requires the CREATE privilege on the server. In PostgreSQL, the privilege is granted to the postgres superuser by default. A regular user must be granted the privilege explicitly:

GRANT CREATE ON DATABASE postgres TO app_user;

In MySQL, the CREATE privilege is granted at the database level or globally. A user with the global CREATE privilege can create any database. A user with the database-level CREATE privilege can create databases that match the grant.

The CREATE DATABASE statement can include options that configure the database. In PostgreSQL, the options include the owner, the encoding, the collation, and the tablespace:

CREATE DATABASE myapp
    OWNER app_user
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

The OWNER option sets the user who owns the database. The ENCODING option sets the character encoding. The LC_COLLATE and LC_CTYPE options set the collation and character classification. The TEMPLATE option specifies the template database from which the new database is copied. The template0 template is a clean template that does not include any user-defined objects .

In MySQL, the options include the character set and the collation:

CREATE DATABASE myapp
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

The CHARACTER SET option sets the character set. The COLLATE option sets the collation. The utf8mb4 character set supports the full range of Unicode, including emoji. The utf8mb4_unicode_ci collation is case-insensitive and accent-insensitive.

The IF NOT EXISTS clause prevents an error if the database already exists:

CREATE DATABASE IF NOT EXISTS myapp;

Without the clause, the statement fails if the database exists. With the clause, the statement is a no-op if the database exists. This is useful in scripts that may run multiple times.

---

b. Dropping a Database

The DROP DATABASE statement removes a database and all its objects. The syntax is:

DROP DATABASE myapp;

The statement requires the DROP privilege on the database. In PostgreSQL, the owner of the database has the privilege by default. In MySQL, the DROP privilege is granted at the database level or globally.

The DROP DATABASE statement is irreversible. All tables, indexes, views, sequences, and data in the database are removed. There is no undo. The statement should be used with extreme caution.

The IF EXISTS clause prevents an error if the database does not exist:

DROP DATABASE IF EXISTS myapp;

Without the clause, the statement fails if the database does not exist. With the clause, the statement is a no-op if the database does not exist. This is useful in scripts that may run multiple times.

The CASCADE clause drops the database even if there are objects that depend on it. In PostgreSQL, the CASCADE clause removes all objects that depend on the database. In MySQL, the CASCADE clause is not supported — the DROP DATABASE statement always drops all objects.

The RESTRICT clause prevents the drop if there are objects that depend on the database. This is the default behavior in PostgreSQL. The RESTRICT clause is the safer option because it prevents accidental data loss.

---

c. Altering a Database

The ALTER DATABASE statement changes the properties of an existing database. The statement can rename the database, change the owner, or change the configuration parameters.

To rename a database in PostgreSQL:

ALTER DATABASE myapp RENAME TO myapp_new;

The rename requires the CREATEDB privilege and ownership of the database. The database cannot be renamed while it is in use by other connections. The pg_terminate_backend function can terminate the connections:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'myapp' AND pid <> pg_backend_pid();

ALTER DATABASE myapp RENAME TO myapp_new;

In MySQL, the ALTER DATABASE statement changes the character set and collation:

ALTER DATABASE myapp
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

MySQL does not support renaming a database with ALTER DATABASE. The rename must be done by dumping the database, creating a new one, and importing the dump.

To change the owner of a database in PostgreSQL:

ALTER DATABASE myapp OWNER TO new_owner;

The new owner must have the CREATE privilege on the database. The owner of a database has all privileges on the database and can grant privileges to other users.

To change the configuration parameters of a database:

ALTER DATABASE myapp SET timezone TO 'UTC';
ALTER DATABASE myapp SET search_path TO myschema, public;

The SET clause changes a configuration parameter for the database. The parameter takes effect for new connections to the database. The RESET clause restores the parameter to its default value.

---

Complete Example Session

This session creates a database, creates a user, grants privileges, creates a table, inserts data, queries the data, and drops the database.

-- ============================================
-- PART 1: CREATE A DATABASE
-- ============================================

CREATE DATABASE bookstore
    OWNER bookstore_admin
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

-- The database is created with the owner, encoding, and collation.
-- The template0 template ensures a clean database.

-- ============================================
-- PART 2: CREATE A USER
-- ============================================

CREATE USER bookstore_app WITH ENCRYPTED PASSWORD 'strong_password';

-- The user is created with a password.
-- The password is encrypted in the system catalog.

-- ============================================
-- PART 3: GRANT PRIVILEGES
-- ============================================

GRANT CONNECT ON DATABASE bookstore TO bookstore_app;
GRANT USAGE ON SCHEMA public TO bookstore_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO bookstore_app;

-- The user can connect to the database.
-- The user can use the public schema.
-- The user can read and write the tables.

-- ============================================
-- PART 4: CONNECT TO THE DATABASE
-- ============================================

\c bookstore

-- The psql client connects to the bookstore database.
-- The prompt changes to bookstore=#.

-- ============================================
-- PART 5: CREATE A TABLE
-- ============================================

CREATE TABLE books (
    book_id     SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    author      VARCHAR(100) NOT NULL,
    price       DECIMAL(8, 2) NOT NULL CHECK (price >= 0),
    published   DATE
);

-- The table is created in the public schema of the bookstore database.

-- ============================================
-- PART 6: INSERT DATA
-- ============================================

INSERT INTO books (title, author, price, published)
VALUES
    ('The Great Gatsby', 'F. Scott Fitzgerald', 12.99, '1925-04-10'),
    ('1984', 'George Orwell', 14.99, '1949-06-08'),
    ('To Kill a Mockingbird', 'Harper Lee', 11.99, '1960-07-11');

-- Three rows are inserted into the books table.

-- ============================================
-- PART 7: QUERY THE DATA
-- ============================================

SELECT * FROM books;

-- Output:
--  book_id |         title          |        author        | price |  published
-- ---------+------------------------+----------------------+-------+------------
--        1 | The Great Gatsby       | F. Scott Fitzgerald  | 12.99 | 1925-04-10
--        2 | 1984                   | George Orwell        | 14.99 | 1949-06-08
--        3 | To Kill a Mockingbird  | Harper Lee           | 11.99 | 1960-07-11
-- (3 rows)

-- ============================================
-- PART 8: ALTER THE DATABASE
-- ============================================

ALTER DATABASE bookstore SET timezone TO 'UTC';

-- The timezone for the database is set to UTC.
-- New connections use UTC.

-- ============================================
-- PART 9: DROP THE DATABASE
-- ============================================

\c postgres

DROP DATABASE bookstore;

-- The database and all its tables and data are removed.
-- The user remains.

-- ============================================
-- PART 10: DROP THE USER
-- ============================================

DROP USER bookstore_app;

-- The user is removed.

The ten parts cover creating a database, creating a user, granting privileges, connecting to the database, creating a table, inserting data, querying the data, altering the database, dropping the database, and dropping the user.

---

Quick Reference

The Database Statements

Statement	Purpose
CREATE DATABASE	Create a new database
DROP DATABASE	Remove a database
ALTER DATABASE	Change database properties
IF NOT EXISTS	Prevent error if exists
IF EXISTS	Prevent error if not exists

The PostgreSQL Options

Option	Purpose
OWNER	Set the owner
ENCODING	Set the character encoding
LC_COLLATE	Set the collation
LC_CTYPE	Set the character classification
TEMPLATE	Set the template database
TABLESPACE	Set the tablespace

The MySQL Options

Option	Purpose
CHARACTER SET	Set the character set
COLLATE	Set the collation
ENCRYPTION	Set the encryption

The Privileges

Privilege	Purpose
CREATE	Create a database
DROP	Drop a database
CONNECT	Connect to a database
USAGE	Use a schema
SELECT, INSERT, UPDATE, DELETE	Read and write tables

The psql Meta-Commands

Command	Purpose
\l	List databases
\c database_name	Connect to a database
\dt	List tables
\d table_name	Describe a table
\du	List users

---

Best Practices

✅ Do This:

-- Use IF NOT EXISTS for idempotent scripts
CREATE DATABASE IF NOT EXISTS myapp;                              -- ✅

-- Set the owner explicitly
CREATE DATABASE myapp OWNER app_user;                             -- ✅

-- Use template0 for a clean database
CREATE DATABASE myapp TEMPLATE template0;                         -- ✅

-- Use IF EXISTS before dropping
DROP DATABASE IF EXISTS myapp;                                    -- ✅

-- Terminate connections before renaming
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'myapp'; -- ✅

❌ Don’t Do This:

-- Don't drop a database without a backup
DROP DATABASE production;                                         -- ❌

-- Don't use template1 if you want a clean database
CREATE DATABASE myapp TEMPLATE template1;                         -- ⚠️ may include objects

-- Don't grant excessive privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO app_user;               -- ❌

-- Don't rename a database while it is in use
ALTER DATABASE myapp RENAME TO myapp_new;  -- may fail             -- ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Database already exists	No IF NOT EXISTS	Add IF NOT EXISTS
Cannot drop database	Active connections	Terminate connections first
Permission denied	Missing privilege	Grant the privilege
Wrong encoding	Not specified	Set ENCODING and LC_COLLATE
Lost data	No backup before drop	Back up the database first

---

Real-World Examples

1. Create a Database

CREATE DATABASE myapp;

2. Create with Owner

CREATE DATABASE myapp OWNER app_user;

3. Create with Encoding

CREATE DATABASE myapp ENCODING 'UTF8';

4. Create with MySQL Options

CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. Drop a Database

DROP DATABASE myapp;

6. Drop with IF EXISTS

DROP DATABASE IF EXISTS myapp;

7. Rename a Database

ALTER DATABASE myapp RENAME TO myapp_new;

8. Change Owner

ALTER DATABASE myapp OWNER TO new_owner;

9. List Databases

\l

10. Connect to a Database

\c myapp

---

Visual

The Database Hierarchy

┌──────────────────────────────────────────────┐
│  SERVER                                      │
│    ├─ Database: myapp_production             │
│    │    ├─ Schema: public                    │
│    │    │    ├─ Table: users                 │
│    │    │    ├─ Table: orders                │
│    │    │    └─ View: active_users           │
│    │    └─ Schema: analytics                 │
│    │         └─ Table: events                │
│    │                                         │
│    ├─ Database: myapp_staging                │
│    │    └─ Schema: public                    │
│    │         ├─ Table: users                 │
│    │         └─ Table: orders                │
│    │                                         │
│    └─ Database: myapp_test                   │
│         └─ Schema: public                    │
│                                              │
│  Each database is a separate namespace.      │
│  Tables with the same name can exist in      │
│  different databases.                        │
│                                              │
└──────────────────────────────────────────────┘

The CREATE DATABASE Options

┌──────────────────────────────────────────────┐
│  CREATE DATABASE                             │
│                                              │
│  CREATE DATABASE myapp                       │
│      OWNER app_user                          │
│      ENCODING 'UTF8'                         │
│      LC_COLLATE 'en_US.UTF-8'                │
│      LC_CTYPE 'en_US.UTF-8'                  │
│      TEMPLATE template0;                     │
│                                              │
│  The OWNER has all privileges on the database.│
│  The ENCODING sets the character set.        │
│  The LC_COLLATE sets the sort order.         │
│  The TEMPLATE0 is the clean template.        │
│                                              │
└──────────────────────────────────────────────┘

The DROP DATABASE

┌──────────────────────────────────────────────┐
│  DROP DATABASE                               │
│                                              │
│  DROP DATABASE myapp;                        │
│                                              │
│  Removes:                                    │
│    ├─ All tables                             │
│    ├─ All indexes                            │
│    ├─ All views                              │
│    ├─ All sequences                          │
│    └─ All data                               │
│                                              │
│  Irreversible.                               │
│  Always back up before dropping.             │
│                                              │
└──────────────────────────────────────────────┘

The Privileges

┌──────────────────────────────────────────────┐
│  PRIVILEGES                                  │
│                                              │
│  CREATE:  Create a database                  │
│  DROP:    Drop a database                    │
│  CONNECT: Connect to a database              │
│  USAGE:   Use a schema                       │
│  SELECT:  Read rows                          │
│  INSERT:  Add rows                           │
│  UPDATE:  Modify rows                        │
│  DELETE:  Remove rows                        │
│                                              │
│  Grant only the privileges the user needs.   │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Create database	CREATE DATABASE myapp;
Drop database	DROP DATABASE myapp;
Alter database	ALTER DATABASE myapp ...;
Idempotent create	CREATE DATABASE IF NOT EXISTS myapp;
Idempotent drop	DROP DATABASE IF EXISTS myapp;
Owner option	OWNER app_user
Encoding option	ENCODING 'UTF8'
Template option	TEMPLATE template0
PostgreSQL client	psql
List databases	\l
Connect	\c myapp
LFCA weight	Not a core LFCA topic

Key takeaways:

• A database is a namespace that contains schemas, tables, and other objects. The CREATE DATABASE statement creates a new namespace. The DROP DATABASE statement removes it along with everything inside it.
• The CREATE DATABASE statement requires the CREATE privilege. In PostgreSQL, the privilege is granted to the postgres superuser by default. A regular user must be granted the privilege explicitly.
• The IF NOT EXISTS and IF EXISTS clauses prevent errors. They make the statements idempotent, which is useful in scripts that may run multiple times.
• The options configure the database. The OWNER option sets the owner. The ENCODING option sets the character encoding. The LC_COLLATE and LC_CTYPE options set the collation and character classification. The TEMPLATE option specifies the template database.
• The DROP DATABASE statement is irreversible. All tables, indexes, views, and data are removed. There is no undo. Always back up the database before dropping it.
• The ALTER DATABASE statement changes the properties of an existing database. It can rename the database, change the owner, or change the configuration parameters. The database cannot be renamed while it is in use.
• The privileges control who can do what. The CREATE privilege allows creating databases. The DROP privilege allows dropping databases. The CONNECT privilege allows connecting to a database. Grant only the privileges the user needs.

Remember: The database is the first level of the namespace. It contains the schemas, the tables, and the data. The CREATE DATABASE statement creates it. The DROP DATABASE statement removes it. The ALTER DATABASE statement changes it. The privileges control who can access it. Always use IF NOT EXISTS and IF EXISTS in scripts. Always back up before dropping. The database is the container for everything else.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!