Learn the Basics

This page will guide you through creating your first database with Oxibase and performing basic operations.

Installation

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

# Install with Cargo
cargo install oxibase

# Or build from source
git clone https://github.com/oxibase/oxibase.git
cd oxibase
cargo build --release

Starting the CLI

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

# Start with an in-memory database (data is lost when the CLI exits)
oxibase

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

# Execute a query directly
oxibase -q -e "SELECT 1 + 1 as sum;"

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, description, price, category, in_stock, created_at) VALUES 
(6, 'Tablet', '12.9-inch iPad Pro', 999.99, 'Electronics', TRUE, NOW());
SELECT * FROM products;
-- Roll back to discard changes
ROLLBACK;

SELECT * FROM products;
-- 
-- Or comit the transaction to save changes
-- COMMIT;

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;

Creating User-Defined Functions

Extend SQL with custom functions written in Rhai, Python, or JavaScript:

-- Create a function that calculates discount
CREATE FUNCTION calculate_discount(price FLOAT, discount_rate FLOAT) RETURNS FLOAT
LANGUAGE RHAI AS 'price * (1.0 - discount_rate)';

-- Use the function in queries
SELECT name, price, calculate_discount(price, 0.1) as discounted_price
FROM products;

-- Function with string manipulation
CREATE FUNCTION format_name(first_name TEXT, last_name TEXT) RETURNS TEXT
LANGUAGE RHAI AS 'first_name + " " + last_name.to_upper()';

SELECT format_name('John', 'Doe') as full_name;

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 Documentation.

Copyright © 2025-2026 Oxibase Contributors. Gabriel Maeztu.