Stoolap Basics: Data Models and Fundamental SQL Operations

Introduction to Stoolap’s Data Foundation

Welcome back, intrepid data explorer! In the previous chapters, we embarked on our Stoolap journey, understanding its unique position as a modern, high-performance embedded SQL database. We explored its architectural marvels like MVCC, parallel execution, and vector search, which set it apart from traditional embedded solutions. If you haven’t set up your Stoolap environment yet, now would be a great time to revisit Chapter 2.

In this chapter, we’re going to get our hands dirty with the very heart of any relational database: its data model and the fundamental SQL operations that allow us to interact with data. Think of it as learning the alphabet and basic grammar before writing a novel. We’ll cover how to define your data structures (tables), add new information, retrieve what you need, and keep your data up-to-date.

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

Understand the relational data model as applied in Stoolap.
Define database schemas using CREATE TABLE.
Add data to your tables using INSERT.
Retrieve data using SELECT with basic filtering.
Modify existing data with UPDATE.
Remove data using DELETE.

Ready to make your Stoolap instance sing with data? Let’s dive in!

Core Concepts: Speaking Stoolap’s Language (SQL)

Stoolap, at its core, is an SQL database. This means it understands and processes queries written in Structured Query Language (SQL), the universal language for managing data in relational databases. If you have a basic understanding of SQL, you’re already ahead! If not, don’t worry, we’ll cover the essentials step-by-step.

The Relational Data Model in Stoolap

Stoolap organizes data using the relational data model. Imagine your data neatly arranged in tables, much like spreadsheets. Each table represents a specific entity (e.g., “Products”, “Customers”, “Orders”).

Tables: The primary storage unit, a collection of related data organized into rows and columns.
Columns (Attributes): Define the type of data stored in each entry of a table (e.g., product_name, price, stock_quantity). Each column has a specific data type (e.g., TEXT, INTEGER, REAL).
Rows (Records/Tuples): A single entry in a table, containing data for each column (e.g., one specific product with its name, price, and quantity).
Primary Key: A column (or set of columns) that uniquely identifies each row in a table. This is crucial for maintaining data integrity and efficient data retrieval.

Stoolap’s modern architecture enhances this classic model with features like MVCC for high concurrency and parallel execution for speed, even for basic operations. This means your fundamental SQL commands are executed with an underlying power that traditional embedded databases often lack.

Stoolap’s SQL Dialect and Data Types

Stoolap aims for broad SQL compliance, supporting a dialect very similar to standard SQL (e.g., SQLite, PostgreSQL). This means many of the SQL commands you’re familiar with will work seamlessly.

When defining your columns, you’ll use various data types. While Stoolap’s official documentation (refer to the GitHub repository for the most up-to-date list) provides the definitive set, you can generally expect support for common types like:

INTEGER: Whole numbers (e.g., 1, 100, -5).
REAL or FLOAT: Floating-point numbers (e.g., 3.14, 99.99).
TEXT: Strings of characters (e.g., 'Hello World', 'Stoolap Database').
BOOLEAN: True or False values.
BLOB: Binary Large Object, for storing raw binary data (e.g., images, files).
TIMESTAMP or DATETIME: Date and time values.
UUID: Universally Unique Identifier, for unique keys.
VECTOR: A specialized data type unique to Stoolap, designed for storing high-dimensional numerical arrays, essential for vector search and semantic similarity (we’ll explore this in a dedicated chapter!).

For this chapter, we’ll stick to the more common types to build our foundation.

Defining Your Data: `CREATE TABLE`

The CREATE TABLE statement is how you define the structure of a new table in your database. It specifies the table’s name and all its columns, including their data types and any constraints (like PRIMARY KEY, NOT NULL, UNIQUE).

Syntax:

CREATE TABLE table_name (
    column1_name DATATYPE [CONSTRAINT],
    column2_name DATATYPE [CONSTRAINT],
    -- ... more columns
    PRIMARY KEY (column_name(s))
);

Example: Let’s create a table for a simple products catalog.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock_quantity INTEGER DEFAULT 0
);

Explanation:

CREATE TABLE products: We’re telling Stoolap to create a new table named products.
product_id INTEGER PRIMARY KEY: This column will store unique integer IDs for each product. PRIMARY KEY ensures each product_id is unique and not NULL, making it the main identifier for rows in this table.
name TEXT NOT NULL: This column will store the product’s name as text. NOT NULL means this field cannot be left empty.
price REAL NOT NULL: The product’s price, stored as a real number (allowing decimals). Also cannot be empty.
stock_quantity INTEGER DEFAULT 0: The current stock quantity, an integer. If not specified during insertion, it will default to 0.

Adding Data: `INSERT INTO`

Once you have a table, you’ll want to populate it with data. The INSERT INTO statement does exactly that, adding new rows to your table.

Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- Or, if inserting values for all columns in order:
INSERT INTO table_name VALUES (value1, value2, ...);

Example: Let’s add a few products to our products table.

INSERT INTO products (product_id, name, price, stock_quantity)
VALUES (1, 'Stoolap T-Shirt', 25.00, 100);

INSERT INTO products (product_id, name, price, stock_quantity)
VALUES (2, 'Stoolap Mug', 12.50, 200);

INSERT INTO products (product_id, name, price)
VALUES (3, 'Stoolap Sticker Pack', 5.00); -- stock_quantity will default to 0

Explanation:

Each INSERT INTO statement adds one new row.
We explicitly list the columns we’re providing values for (product_id, name, price, stock_quantity), followed by the VALUES in the same order.
Notice how for product_id 3, we omitted stock_quantity, relying on its DEFAULT 0 constraint. This is a neat trick for optional fields!

Retrieving Data: `SELECT`

The SELECT statement is arguably the most frequently used SQL command. It allows you to fetch data from one or more tables.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition; -- Optional: to filter rows

Example: Let’s retrieve all products, specific columns, and filter our results.

-- Select all columns for all products
SELECT * FROM products;

-- Select only the name and price of all products
SELECT name, price FROM products;

-- Select products with a price greater than 10.00
SELECT name, price FROM products WHERE price > 10.00;

-- Select products that are currently out of stock
SELECT * FROM products WHERE stock_quantity = 0;

Explanation:

SELECT *: The asterisk * is a wildcard meaning “all columns”.
SELECT name, price: Specifies only the name and price columns should be returned.
FROM products: Indicates we are querying the products table.
WHERE price > 10.00: This is a WHERE clause, used to filter rows based on a condition. Only rows where the price is greater than 10.00 will be returned. Stoolap’s cost-based optimizer will analyze this condition to find the most efficient way to fetch these results, potentially leveraging indexes (which we’ll cover in a later chapter!).

Modifying Data: `UPDATE`

Sometimes data changes! Prices fluctuate, stock levels update. The UPDATE statement allows you to modify existing data in one or more rows.

Syntax:

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition; -- CRITICAL: specify which rows to update!

Example: Let’s update the price of a product and increase the stock.

-- Increase the price of 'Stoolap T-Shirt' (product_id 1)
UPDATE products
SET price = 27.50
WHERE product_id = 1;

-- Add 50 to the stock of 'Stoolap Mug' (product_id 2)
UPDATE products
SET stock_quantity = stock_quantity + 50
WHERE product_id = 2;

Explanation:

UPDATE products: Specifies the table to modify.
SET price = 27.50: Sets the price column to a new value.
WHERE product_id = 1: This WHERE clause is absolutely critical. Without it, the UPDATE statement would change the price for every single product in the table! Always be careful with UPDATE and DELETE statements.
Stoolap’s MVCC (Multi-Version Concurrency Control) ensures that even if other operations are reading or writing to the products table simultaneously, your UPDATE will happen without blocking and without corrupting data, providing a consistent view to all concurrent transactions.

Removing Data: `DELETE FROM`

When data is no longer needed, you can remove rows from a table using the DELETE FROM statement.

Syntax:

DELETE FROM table_name
WHERE condition; -- CRITICAL: specify which rows to delete!

Example: Let’s remove an out-of-stock product.

-- Delete the 'Stoolap Sticker Pack' (product_id 3)
DELETE FROM products
WHERE product_id = 3;

-- Be careful! This would delete ALL rows from the table if uncommented:
-- DELETE FROM products;

Explanation:

DELETE FROM products: Specifies the table from which to delete rows.
WHERE product_id = 3: Again, the WHERE clause is vital. It targets specific rows for deletion. Without it, you would empty your entire table!

Step-by-Step Implementation: Building a Simple Product Catalog

Now, let’s put these concepts into practice. We’ll simulate interacting with a Stoolap database. In a real application, you’d use a Stoolap client library (likely in Rust) to execute these SQL commands. For now, imagine you’re typing these into a SQL client connected to your Stoolap instance.

Step 1: Connect to your Stoolap instance (Conceptual)

For this exercise, we’ll assume you have a Stoolap instance running (as covered in Chapter 2) and a way to execute SQL queries against it. If you’re building a Rust application, this would involve using the stoolap crate. For a quick test, you might use a tool provided by Stoolap for direct SQL interaction, if available, or integrate it into a simple Rust program.

Step 2: Create the products table

Let’s define our product catalog.

-- SQL: Create Table
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    price REAL NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP
);

Explanation:

We added a description field (optional, since it doesn’t have NOT NULL).
We also added a last_updated field, which automatically stores the current timestamp when a row is inserted, or if the column isn’t specified, defaults to the time of insertion. This is useful for tracking changes.

Step 3: Insert initial product data

Let’s populate our catalog with some items.

-- SQL: Insert Data
INSERT INTO products (product_id, name, description, price, stock_quantity)
VALUES (101, 'Mechanical Keyboard', 'High-performance mechanical keyboard with RGB backlighting.', 129.99, 50);

INSERT INTO products (product_id, name, description, price, stock_quantity)
VALUES (102, 'Wireless Mouse', 'Ergonomic wireless mouse with customizable buttons.', 49.99, 120);

INSERT INTO products (product_id, name, price) -- description and stock_quantity will use defaults
VALUES (103, 'Monitor Stand', 29.99);

INSERT INTO products (product_id, name, description, price, stock_quantity)
VALUES (104, 'Stoolap Dev Kit', 'A special kit for Stoolap developers, includes stickers!', 75.00, 10);

Step 4: Retrieve and verify data

Let’s see what’s in our table.

-- SQL: Select All
SELECT * FROM products;

You should see all four products listed with their details.

-- SQL: Select specific columns and filter
SELECT name, price, stock_quantity
FROM products
WHERE stock_quantity < 100;

This query should return the ‘Mechanical Keyboard’, ‘Monitor Stand’, and ‘Stoolap Dev Kit’.

Step 5: Update product information

Our ‘Wireless Mouse’ is on sale, and we received a new shipment of ‘Monitor Stand’.

-- SQL: Update Price
UPDATE products
SET price = 39.99 -- 10 off!
WHERE product_id = 102;

-- SQL: Update Stock
UPDATE products
SET stock_quantity = stock_quantity + 200, last_updated = CURRENT_TIMESTAMP
WHERE product_id = 103;

Step 6: Verify updates

Let’s check the changes.

-- SQL: Verify Updates
SELECT product_id, name, price, stock_quantity, last_updated
FROM products
WHERE product_id IN (102, 103);

You should see the updated price for product 102 and increased stock for product 103, along with a new last_updated timestamp for 103.

Step 7: Delete a product

The ‘Stoolap Dev Kit’ was a limited edition and is now discontinued.

-- SQL: Delete Product
DELETE FROM products
WHERE product_id = 104;

Step 8: Final verification

Check that the product is gone.

-- SQL: Final Select
SELECT * FROM products;

The ‘Stoolap Dev Kit’ (product 104) should no longer appear in the results. Congratulations! You’ve successfully performed fundamental SQL operations with Stoolap.

Mini-Challenge: Expanding Your Catalog

Now it’s your turn!

Challenge:

Create a new table called categories with category_id (INTEGER PRIMARY KEY) and category_name (TEXT NOT NULL, UNIQUE).
Insert at least three categories (e.g., ‘Electronics’, ‘Office’, ‘Accessories’).
Add a new column category_id (INTEGER) to your existing products table. This column should allow NULL values initially.
Update your products table to assign appropriate category_id values to your existing products, linking them to your new categories.
Select all products, showing their name, price, and category_name (you might need to use a simple JOIN - if you’re not familiar, just select name, price, and category_id for now).

Hint: For adding a column to an existing table, look into the ALTER TABLE statement. For assigning categories, you’ll use UPDATE with a WHERE clause.

What to observe/learn: This challenge introduces ALTER TABLE and the concept of relating tables, which is fundamental to relational databases. It also reinforces INSERT, UPDATE, and SELECT in a slightly more complex scenario.

Common Pitfalls & Troubleshooting

Even with basic SQL, some common issues can arise:

Forgetting WHERE clauses in UPDATE or DELETE: This is the most dangerous pitfall! Always double-check your UPDATE and DELETE statements to ensure you’re only affecting the intended rows. If you accidentally run DELETE FROM products; without a WHERE clause, all your data will be gone! (And Stoolap’s MVCC won’t save you from yourself, though transactions could).
Data Type Mismatches: Trying to insert text into an INTEGER column, or a number into a BOOLEAN column. Stoolap will typically throw an error, reminding you to use the correct data type.
Violating Constraints (PRIMARY KEY, NOT NULL, UNIQUE):
- Trying to insert a NULL value into a NOT NULL column.
- Trying to insert a duplicate value into a PRIMARY KEY or UNIQUE column. Stoolap will prevent these actions to maintain data integrity.
Syntax Errors: A missing comma, an extra parenthesis, a misspelled keyword. SQL is precise! Read error messages carefully; they often point directly to the problem area.
Case Sensitivity: While SQL keywords (SELECT, FROM) are generally case-insensitive, table and column names can be case-sensitive depending on the database and operating system. It’s best practice to stick to a consistent naming convention (e.g., all lowercase or snake_case) to avoid issues.

Summary

Phew! You’ve just taken a massive leap in your Stoolap journey. We’ve covered the bedrock of any relational database interaction.

Here are the key takeaways from this chapter:

Stoolap uses the relational data model, organizing data into tables with columns and rows.
It understands standard SQL for data definition and manipulation.
The CREATE TABLE statement defines your database schema, specifying columns, data types, and constraints.
INSERT INTO adds new rows (records) to your tables.
SELECT retrieves data, with WHERE clauses used for filtering.
UPDATE modifies existing data in rows, with WHERE being crucial to target specific records.
DELETE FROM removes rows, and like UPDATE, requires careful use of WHERE.
Stoolap’s underlying architecture (MVCC, parallel execution) empowers these fundamental operations with high performance and concurrency.

In the next chapter, we’ll build on these basics, exploring more advanced SELECT operations, including sorting, aggregation, and joining multiple tables. This will unlock the true power of relational data analysis within Stoolap!

References

Stoolap GitHub Repository: The primary source for Stoolap’s latest features, documentation, and releases. (Version v0.1.0-alpha as of 2026-03-20 is the latest stable release, with active development.)
W3Schools SQL Tutorial: A comprehensive and beginner-friendly resource for standard SQL syntax.
PostgreSQL Documentation: Data Types: A good reference for common SQL data types, many of which are analogous in Stoolap.

This page is AI-assisted and reviewed. It references official documentation and recognized resources where relevant.

Stoolap Basics: Data Models and Fundamental SQL Operations

Table of Contents

Introduction to Stoolap’s Data Foundation

Core Concepts: Speaking Stoolap’s Language (SQL)

The Relational Data Model in Stoolap

Stoolap’s SQL Dialect and Data Types

Defining Your Data: CREATE TABLE

Adding Data: INSERT INTO

Retrieving Data: SELECT

Modifying Data: UPDATE

Removing Data: DELETE FROM

Step-by-Step Implementation: Building a Simple Product Catalog

Mini-Challenge: Expanding Your Catalog

Common Pitfalls & Troubleshooting

Summary

References

Defining Your Data: `CREATE TABLE`

Adding Data: `INSERT INTO`

Retrieving Data: `SELECT`

Modifying Data: `UPDATE`

Removing Data: `DELETE FROM`