Basic CRUD Operations

Learn the fundamentals of Create, Read, Update, and Delete operations with SIMPLE_SQL.

Setting Up

local
    db: SIMPLE_SQL_DATABASE
do
    -- Create file-based database
    create db.make ("myapp.db")

    -- Or use in-memory database for testing
    create db.make_memory
end

Create (INSERT)

Insert data using the fluent builder or raw SQL.

Using the Fluent Builder

-- Create table first
db.execute ("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, active INTEGER DEFAULT 1)")

-- Insert a row
db.insert_into ("users")
    .value ("name", "Alice")
    .value ("email", "alice@example.com")
    .execute

-- Get the auto-generated ID
print ("Inserted user ID: " + db.last_insert_rowid.out)

Using Raw SQL with Parameters

db.execute_with_args ("INSERT INTO users (name, email) VALUES (?, ?)", <<"Bob", "bob@example.com">>)

Read (SELECT)

Query data and iterate through results.

Select All Rows

local
    result: SIMPLE_SQL_RESULT
do
    result := db.select_from ("users").execute

    across result as row loop
        print (row.integer_value ("id").out + ": ")
        print (row.string_value ("name") + "%N")
    end
end

Select with Conditions

result := db.select_from ("users")
    .columns ("id, name, email")
    .where ("active = ?", 1)
    .order_by ("name ASC")
    .limit (10)
    .execute

Select Single Row

result := db.select_from ("users")
    .where ("id = ?", user_id)
    .execute

if attached result.first as user then
    print ("Found: " + user.string_value ("name"))
else
    print ("User not found")
end

Update

Modify existing rows.

Update Single Row

db.update ("users")
    .set ("name", "Alice Smith")
    .set ("email", "alice.smith@example.com")
    .where ("id = ?", 1)
    .execute

print ("Updated " + db.changes.out + " row(s)")

Batch Update

-- Deactivate all users who haven't logged in
db.update ("users")
    .set ("active", 0)
    .where ("last_login < datetime('now', '-90 days')")
    .execute

Delete

Remove rows from the database.

Delete Single Row

db.delete_from ("users")
    .where ("id = ?", user_id)
    .execute

print ("Deleted " + db.changes.out + " row(s)")

Complete Example

class CRUD_EXAMPLE

create
    make

feature {NONE}

    make
        local
            db: SIMPLE_SQL_DATABASE
            result: SIMPLE_SQL_RESULT
        do
            create db.make_memory

            -- Create
            db.execute ("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)")
            db.insert_into ("products").value ("name", "Widget").value ("price", 9.99).execute
            db.insert_into ("products").value ("name", "Gadget").value ("price", 19.99).execute

            -- Read
            result := db.select_from ("products").execute
            across result as p loop
                print (p.string_value ("name") + ": $" + p.real_value ("price").out + "%N")
            end

            -- Update
            db.update ("products").set ("price", 14.99).where ("name = ?", "Widget").execute

            -- Delete
            db.delete_from ("products").where ("price > 15").execute

            db.close
        end

end

Next Steps

• Query Builder - Advanced query construction
• Transactions - Atomic operations
• API Reference - Full SIMPLE_SQL_DATABASE documentation