Data definition language and data manipulation language

#backend #database #sql

What is DDL?

DDL- Data Definition Language - is the part of SQL that defines and modifies the structure of a database: the tables, their columns, data types, and constraints. It doesn't touch the data living inside those tables; it shapes the containers the data lives in.

The core DDL commands are:

CREATE - build a new table, schema, or other database object
ALTER - change an existing table's structure (add/remove/modify columns)
DROP - delete a table or object entirely, structure and data both
TRUNCATE - empty a table of all its rows while keeping the structure intact

What is DML?

DML - Data Manipulation Language - is what you use once the structure already exists. It reads and changes the data inside the tables DDL created:

INSERT - add new rows
UPDATE - modify existing rows
DELETE - remove rows
SELECT - read rows (some references classify this separately as DQL, Data Query Language, since it only reads and never changes anything - but it's commonly grouped with DML in practice)

The distinction, in plain terms

DDL answers "what does the data look like?" DML answers "what does the data say, right now?" You use DDL once (or occasionally, when the schema evolves) and DML constantly, every time a row needs to be added, changed, or removed.

Practical examples - from the Sunrise Supermarket project

When I built the Sunrise Supermarket database, the very first statements were pure DDL - defining the shape before anything could be stored in it:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    city VARCHAR(50) NOT NULL,
    loyalty_points INT DEFAULT 0
);

If I later decided customers needed a signup date, that's DDL too — I'm changing the shape of the table, not its contents:

ALTER TABLE customers
ADD COLUMN signup_date DATE DEFAULT CURRENT_DATE;

Once the structure existed, everything after that was DML. Populating the table:

INSERT INTO customers (customer_id, full_name, email, phone_number, city)
VALUES (1, 'Emilio ochieng', 'emilioochieng44@gmail.com', '0705216347', 'Nairobi');

Changing a row already there:

UPDATE orders
SET status = 'Delivered'
WHERE order_id = 2;

And removing one:

DELETE FROM orders
WHERE order_id = 4;

What I understood from this

The distinction sounds academic until you actually build something - then it becomes obvious that DDL mistakes are expensive (drop the wrong table, and the data's gone with it) while DML mistakes are usually recoverable (a bad UPDATE can be corrected with another UPDATE). That's part of why I lean on constraints - NOT NULL, UNIQUE, CHECK - at the DDL stage: they're a one-time investment that quietly prevents an entire category of bad DML later. A CHECK (unit_price > 0) on the products table means no INSERT or UPDATE can ever sneak a negative price in, no matter who's writing the query.

DEV Community