Quick Start Tutorial

Quick Start Tutorial

This tutorial will guide you through creating your first database with Stoolap and performing basic operations.

Installation

Before starting, ensure you have Stoolap installed. If not, follow the Installation Guide.

# Install with Go
go get github.com/stoolap/stoolap

# Or build from source
git clone https://github.com/stoolap/stoolap.git
cd stoolap
go build -o stoolap ./cmd/stoolap

Starting the CLI

Stoolap includes a command-line interface (CLI) for interactive use:

# Start with an in-memory database (data is lost when the CLI exits)
./stoolap -db memory://

# Or with persistent storage (data is saved to disk)
./stoolap -db file:///path/to/data

# Or with the MVCC engine (recommended for transactional applications)
./stoolap -db db:///path/to/data

Creating a Table

Let’s create a simple table to store product information:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    price FLOAT NOT NULL,
    category TEXT,
    in_stock BOOLEAN,
    created_at TIMESTAMP
);

Inserting Data

Now let’s add some sample products:

-- Insert a single product
INSERT INTO products (id, name, description, price, category, in_stock, created_at)
VALUES (1, 'Laptop', 'High-performance laptop with 16GB RAM', 1299.99, 'Electronics', TRUE, NOW());

-- Insert multiple products
INSERT INTO products (id, name, description, price, category, in_stock, created_at) VALUES 
(2, 'Smartphone', '5G smartphone with 128GB storage', 799.99, 'Electronics', TRUE, NOW()),
(3, 'Headphones', 'Wireless noise-cancelling headphones', 249.99, 'Accessories', TRUE, NOW()),
(4, 'Monitor', '27-inch 4K monitor', 349.99, 'Electronics', FALSE, NOW()),
(5, 'Keyboard', 'Mechanical gaming keyboard', 129.99, 'Accessories', TRUE, NOW());

Querying Data

Basic SELECT

Retrieve all products:

SELECT * FROM products;

Filtering with WHERE

Retrieve products in a specific category:

SELECT name, price FROM products WHERE category = 'Electronics';

Sorting with ORDER BY

Sort products by price from highest to lowest:

SELECT name, price FROM products ORDER BY price DESC;

Limiting Results

Get only the 3 most expensive products:

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

Updating Data

Let’s update the price of a product:

UPDATE products SET price = 1199.99 WHERE id = 1;

Update multiple fields:

UPDATE products 
SET price = 349.99, description = 'Updated description'
WHERE id = 2;

Deleting Data

Remove a product from the database:

DELETE FROM products WHERE id = 5;

Creating an Index

Indexes speed up queries on frequently searched columns:

-- Create an index on the category column
CREATE INDEX idx_category ON products(category);

-- Create a unique index on the name column
CREATE UNIQUE INDEX idx_name ON products(name);

Working with Transactions

Transactions ensure that multiple operations succeed or fail as a unit:

-- Start a transaction
BEGIN TRANSACTION;

-- Perform operations
UPDATE products SET price = price * 0.9 WHERE category = 'Electronics';
INSERT INTO products (id, name, price, category) VALUES (6, 'Tablet', 499.99, 'Electronics');

-- Commit the transaction to save changes
COMMIT;

-- Or roll back to discard changes
-- ROLLBACK;

Using Joins

Let’s create a categories table and join it with our products:

-- Create categories table
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);

-- Add some categories
INSERT INTO categories (id, name, description) VALUES
(1, 'Electronics', 'Electronic devices and gadgets'),
(2, 'Accessories', 'Peripherals and accessories for devices');

-- Update products to use category ids
ALTER TABLE products ADD COLUMN category_id INTEGER;
UPDATE products SET category_id = 1 WHERE category = 'Electronics';
UPDATE products SET category_id = 2 WHERE category = 'Accessories';

-- Join tables to get category information
SELECT p.id, p.name, p.price, c.name AS category_name, c.description AS category_description
FROM products p
JOIN categories c ON p.category_id = c.id;

Using Aggregation Functions

Get summary statistics for your products:

-- Count products by category
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- Get average price by category
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

-- Get price range by category
SELECT 
    category,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    AVG(price) AS avg_price
FROM products
GROUP BY category;

Working with Common Table Expressions (CTEs)

CTEs make complex queries more readable:

-- Find top products by category
WITH category_stats AS (
    SELECT 
        category,
        AVG(price) as avg_price,
        MAX(price) as max_price
    FROM products
    GROUP BY category
)
SELECT 
    p.name,
    p.price,
    cs.avg_price,
    ROUND((p.price / cs.avg_price - 1) * 100, 2) as pct_above_avg
FROM products p
JOIN category_stats cs ON p.category = cs.category
WHERE p.price > cs.avg_price
ORDER BY pct_above_avg DESC;

Next Steps

Now that you’ve learned the basics, you might want to explore:

For a more comprehensive reference, browse the rest of the Stoolap Wiki.