Tutorial 01: Basic CRUD Operations

Tutorial 01: Basic CRUD Operations

Time: 15 minutes Level: Basic Prerequisites: 5-minute Quickstart completed

---

What You’ll Learn

By the end of this tutorial, you’ll be able to:

• Create tables with various data types
• Insert, update, and delete data
• Query data with SELECT statements
• Use transactions for data consistency
• Handle errors and rollbacks

---

Real-World Use Case

You’re building an e-commerce application and need to manage a product catalog. You’ll learn how to:

• Store product information (name, price, inventory)
• Update prices and stock levels
• Delete discontinued products
• Query products by category and price range
• Ensure data consistency with transactions

---

Setup (2 minutes)

Start HeliosDB

If you haven’t already, start HeliosDB:

docker run -d \
  --name heliosdb \
  -p 5432:5432 \
  -e HELIOS_PASSWORD=mysecretpassword \
  heliosdb/heliosdb:latest

Connect to HeliosDB

psql -h localhost -p 5432 -U helios -d helios
# Password: mysecretpassword

Alternative: Use any PostgreSQL client (DBeaver, pgAdmin, TablePlus, etc.)

---

Step 1: Create a Table (3 minutes)

Goal

Create a products table to store e-commerce product data.

Code

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Execute

Copy-paste the SQL above into your psql client and press Enter.

Expected Output

CREATE TABLE

What Just Happened?

Let’s break down the table definition:

• product_id SERIAL PRIMARY KEY: Auto-incrementing ID (1, 2, 3, …)
• name VARCHAR(200) NOT NULL: Product name, max 200 chars, required
• price DECIMAL(10, 2) NOT NULL CHECK (price >= 0): Price with 2 decimal places, must be non-negative
• stock_quantity INTEGER DEFAULT 0: Inventory count, defaults to 0
• CHECK constraints: Ensure data integrity (no negative prices/stock)
• created_at, updated_at: Automatic timestamps

HeliosDB Optimization: The table is automatically optimized with:

• Primary key B-tree index on product_id
• Autonomous indexing will suggest indexes on category after queries

Verify

Check that the table was created:

\dt products

Output:

        List of relations
 Schema |   Name   | Type  | Owner
--------+----------+-------+-------
 public | products | table | helios

---

Step 2: Insert Data (3 minutes)

Goal

Add products to the catalog.

Single Insert

INSERT INTO products (name, description, price, stock_quantity, category)
VALUES (
    'Wireless Mouse',
    'Ergonomic wireless mouse with 3-button design',
    24.99,
    150,
    'Electronics'
);

Expected Output

INSERT 0 1

This means 1 row was inserted (product_id was auto-generated).

Multiple Inserts (Batch)

INSERT INTO products (name, description, price, stock_quantity, category) VALUES
    ('Mechanical Keyboard', 'RGB backlit mechanical keyboard with Cherry MX switches', 89.99, 75, 'Electronics'),
    ('USB-C Cable', '6ft USB-C to USB-C charging cable', 12.99, 500, 'Accessories'),
    ('Laptop Stand', 'Adjustable aluminum laptop stand', 39.99, 200, 'Accessories'),
    ('Webcam HD', '1080p HD webcam with built-in microphone', 59.99, 120, 'Electronics'),
    ('Desk Lamp', 'LED desk lamp with adjustable brightness', 34.99, 80, 'Office'),
    ('Notebook', 'Hardcover notebook, 200 pages', 8.99, 300, 'Office'),
    ('Wireless Headphones', 'Noise-canceling over-ear headphones', 149.99, 60, 'Electronics'),
    ('Monitor', '27-inch 4K UHD monitor', 349.99, 45, 'Electronics'),
    ('Phone Case', 'Protective phone case for iPhone 15', 19.99, 250, 'Accessories');

Expected Output

INSERT 0 9

9 rows inserted successfully!

Insert with Returned Values

HeliosDB supports PostgreSQL’s RETURNING clause:

INSERT INTO products (name, price, category)
VALUES ('Gaming Mouse', 49.99, 'Electronics')
RETURNING product_id, name, created_at;

Expected Output

 product_id |     name     |        created_at
------------+--------------+----------------------------
         11 | Gaming Mouse | 2025-11-07 10:45:23.123456

Why this matters: Get the auto-generated ID immediately without a separate SELECT.

---

Step 3: Query Data (SELECT) (3 minutes)

Goal

Retrieve products using various query techniques.

Basic SELECT - All Products

SELECT * FROM products;

Output: All 11 products with all columns.

Select Specific Columns

SELECT product_id, name, price, stock_quantity
FROM products;

Best Practice: Only select columns you need (faster queries, less network traffic).

Filter with WHERE

-- Products in Electronics category
SELECT name, price, stock_quantity
FROM products
WHERE category = 'Electronics';

Output: 5 electronics products.

Filter by Price Range

-- Products under $50
SELECT name, price, category
FROM products
WHERE price < 50
ORDER BY price ASC;

Output: Products sorted by price (cheapest first).

Pattern Matching with LIKE

-- Products with "Mouse" in the name
SELECT name, price
FROM products
WHERE name LIKE '%Mouse%';

Output:

        name        | price
--------------------+-------
 Wireless Mouse     | 24.99
 Gaming Mouse       | 49.99

Wildcards:

• % = any characters (0 or more)
• _ = single character

Combining Conditions

-- Electronics products under $100 with stock > 50
SELECT name, price, stock_quantity
FROM products
WHERE category = 'Electronics'
  AND price < 100
  AND stock_quantity > 50;

Operators:

• AND - both conditions must be true
• OR - at least one condition must be true
• NOT - negates a condition

Aggregations

-- Total inventory value by category
SELECT
    category,
    COUNT(*) AS product_count,
    SUM(stock_quantity) AS total_stock,
    AVG(price) AS avg_price,
    SUM(price * stock_quantity) AS total_value
FROM products
GROUP BY category
ORDER BY total_value DESC;

Expected Output:

   category   | product_count | total_stock |      avg_price       |   total_value
--------------+---------------+-------------+----------------------+------------------
 Electronics  |             6 |         450 |  82.489999999999995  | 37122.50
 Accessories  |             3 |         950 |  24.323333333333334  | 23108.50
 Office       |             2 |         380 |  21.99               |  8356.20

Aggregate Functions:

• COUNT(*) - number of rows
• SUM(column) - total
• AVG(column) - average
• MIN(column) - minimum value
• MAX(column) - maximum value

---

Step 4: Update Data (2 minutes)

Goal

Modify existing product data.

Update a Single Product

-- Increase price of Wireless Mouse by 10%
UPDATE products
SET
    price = price * 1.10,
    updated_at = CURRENT_TIMESTAMP
WHERE name = 'Wireless Mouse';

Expected Output

UPDATE 1

1 row was updated.

Verify the Update

SELECT name, price, updated_at
FROM products
WHERE name = 'Wireless Mouse';

Output:

      name      | price |        updated_at
----------------+-------+----------------------------
 Wireless Mouse | 27.49 | 2025-11-07 10:50:15.654321

Price increased from $24.99 to $27.49 (10% increase).

Update Multiple Rows

-- Restock all products in Office category
UPDATE products
SET stock_quantity = stock_quantity + 100
WHERE category = 'Office';

Output: UPDATE 2 (2 office products updated).

Update with RETURNING

-- Reduce stock after a sale
UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 1
RETURNING name, stock_quantity AS new_stock;

Output:

      name      | new_stock
----------------+-----------
 Wireless Mouse |       145

---

Step 5: Delete Data (2 minutes)

Goal

Remove products from the catalog.

Delete a Single Product

-- Delete discontinued product
DELETE FROM products
WHERE name = 'Notebook';

Expected Output

DELETE 1

Delete with Condition

-- Remove out-of-stock products
DELETE FROM products
WHERE stock_quantity = 0;

Safety Note: Always use WHERE clause! Without it, ALL rows are deleted.

Delete with RETURNING

-- Remove products under $10 and log what was deleted
DELETE FROM products
WHERE price < 10
RETURNING product_id, name, price;

Output: Shows what was deleted.

---

Step 6: Transactions (3 minutes)

Goal

Ensure data consistency with ACID transactions.

Scenario: Process an Order

When a customer orders 10 Wireless Mice:

1. Reduce stock by 10
2. If stock goes below 0, rollback (prevent overselling)

Transaction with COMMIT

BEGIN;

-- Check current stock
SELECT stock_quantity FROM products WHERE product_id = 1;

-- Reduce stock
UPDATE products
SET stock_quantity = stock_quantity - 10
WHERE product_id = 1;

-- Verify
SELECT stock_quantity FROM products WHERE product_id = 1;

COMMIT;

What Happened:

• BEGIN starts a transaction
• All changes are temporary until COMMIT
• COMMIT makes changes permanent

Transaction with ROLLBACK

BEGIN;

-- Try to reduce stock by 200 (more than available)
UPDATE products
SET stock_quantity = stock_quantity - 200
WHERE product_id = 1;

-- Check stock (would be negative!)
SELECT stock_quantity FROM products WHERE product_id = 1;

-- Abort the transaction
ROLLBACK;

-- Verify stock unchanged
SELECT stock_quantity FROM products WHERE product_id = 1;

What Happened:

• ROLLBACK undoes all changes since BEGIN
• Data integrity preserved

Transaction with Constraint Enforcement

BEGIN;

-- This will fail due to CHECK constraint (stock_quantity >= 0)
UPDATE products
SET stock_quantity = -50
WHERE product_id = 1;
-- ERROR: new row for relation "products" violates check constraint

Automatic Rollback: HeliosDB automatically rolls back on errors.

---

Complete Example: Order Processing System

Here’s a complete transaction that processes a customer order:

-- Create orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Process an order (atomic operation)
BEGIN;

-- Check product availability
DO $$
DECLARE
    available_stock INTEGER;
BEGIN
    SELECT stock_quantity INTO available_stock
    FROM products
    WHERE product_id = 1;

    IF available_stock >= 10 THEN
        -- Sufficient stock, process order
        INSERT INTO orders (product_id, quantity)
        VALUES (1, 10);

        UPDATE products
        SET stock_quantity = stock_quantity - 10
        WHERE product_id = 1;

        RAISE NOTICE 'Order processed successfully';
    ELSE
        RAISE EXCEPTION 'Insufficient stock: % available', available_stock;
    END IF;
END $$;

COMMIT;

Production-Ready: This pattern ensures atomic order processing.

---

Troubleshooting

Problem: “relation ‘products’ does not exist”

Cause: Table hasn’t been created yet.

Solution:

-- Check if table exists
\dt products

-- If not, create it (see Step 1)

Problem: “ERROR: duplicate key value violates unique constraint”

Cause: Trying to insert duplicate primary key or unique value.

Solution:

-- Let SERIAL auto-generate IDs, don't specify product_id
INSERT INTO products (name, price) VALUES ('New Product', 29.99);

Problem: “ERROR: check constraint violated”

Cause: Data violates CHECK constraint (e.g., negative price).

Solution:

-- Ensure values meet constraints
INSERT INTO products (name, price, stock_quantity)
VALUES ('Valid Product', 19.99, 50);  -- Both price and stock are >= 0

Problem: Updates affect more rows than expected

Cause: WHERE clause too broad.

Solution:

-- Always verify with SELECT first
SELECT * FROM products WHERE category = 'Electronics';

-- Then update
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';

---

Performance Tips

1. Use Indexes for Frequent Queries

-- If you often query by category
CREATE INDEX idx_products_category ON products(category);

-- HeliosDB autonomous indexing will suggest this automatically!

2. Batch Inserts

-- SLOW: One row at a time (avoid)
INSERT INTO products (name, price) VALUES ('Product 1', 10);
INSERT INTO products (name, price) VALUES ('Product 2', 20);

-- FAST: Batch insert
INSERT INTO products (name, price) VALUES
    ('Product 1', 10),
    ('Product 2', 20),
    ('Product 3', 30);

Performance: Batch inserts are 10-100x faster!

3. Use EXPLAIN to Analyze Queries

EXPLAIN ANALYZE
SELECT * FROM products WHERE category = 'Electronics';

Output: Shows query execution plan, index usage, timing.

---

Next Steps

Congratulations! You’ve mastered basic CRUD operations in HeliosDB.

Continue Learning

1. Tutorial 02: Advanced Queries - Joins, CTEs, window functions
2. Tutorial 03: Multi-Protocol - Connect via Oracle, MySQL
3. Tutorial 09: Indexing & Optimization - Performance tuning

Real-World Applications

• E-commerce: Product catalogs, inventory management
• SaaS: User management, subscription tracking
• Analytics: Data warehousing, reporting
• IoT: Time-series data, sensor readings

Get Help

• Stuck? Check the Troubleshooting Guide
• Questions? See the FAQ
• Community: Join Discord

---

Complete Code Reference

Here’s the complete SQL from this tutorial:

-- Create table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO products (name, description, price, stock_quantity, category) VALUES
    ('Wireless Mouse', 'Ergonomic wireless mouse', 24.99, 150, 'Electronics'),
    ('Mechanical Keyboard', 'RGB backlit keyboard', 89.99, 75, 'Electronics'),
    ('USB-C Cable', '6ft charging cable', 12.99, 500, 'Accessories'),
    ('Laptop Stand', 'Adjustable aluminum stand', 39.99, 200, 'Accessories'),
    ('Webcam HD', '1080p webcam', 59.99, 120, 'Electronics');

-- Query data
SELECT * FROM products WHERE category = 'Electronics';

-- Update data
UPDATE products SET price = price * 1.10 WHERE product_id = 1;

-- Delete data
DELETE FROM products WHERE stock_quantity = 0;

-- Transaction
BEGIN;
UPDATE products SET stock_quantity = stock_quantity - 10 WHERE product_id = 1;
COMMIT;

Run this code: All examples are tested and ready to copy-paste!

---

Tutorial Complete! 🎉

You’ve learned the fundamentals of database operations. Ready for more? Start Tutorial 02: Advanced Queries.