Standard Library
SQLite Database

SQLite Module

The sqlite module provides embedded database funcality using SQLite.

funcs

sqlite.open()

Open or create a database:

let db = sqlite.open("myapp.db");

sqlite.exec()

Execute SQL statements (CREATE, INSERT, UPDATE, DELETE):

sqlite.exec(db, "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
)");
 
sqlite.exec(db, "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')");

sqlite.query()

Execute SELECT queries:

let results = sqlite.query(db, "SELECT * FROM users");
print(results);

sqlite.close()

Close the database connection:

sqlite.close(db);

Complete Example

// Open database
let db = sqlite.open("app.db");
 
// Create table
sqlite.exec(db, "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
)");
 
// Insert users
sqlite.exec(db, "INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30)");
sqlite.exec(db, "INSERT INTO users (name, email, age) VALUES ('Bob', 'bob@example.com', 25)");
 
// Query all users
let all_users = sqlite.query(db, "SELECT * FROM users");
print("All users:");
print(all_users);
 
// Query with WHERE clause
let adult_users = sqlite.query(db, "SELECT * FROM users WHERE age >= 18");
print("Adults:");
print(adult_users);
 
// Update
sqlite.exec(db, "UPDATE users SET age = 31 WHERE name = 'Alice'");
 
// Delete
sqlite.exec(db, "DELETE FROM users WHERE id = 2");
 
// Close
sqlite.close(db);

CRUD Operations

Create

func create_user(db, name, email, age) {
    let sql = "INSERT INTO users (name, email, age) VALUES ('" + name + "', '" + email + "', " + tostring(age) + ")";
    sqlite.exec(db, sql);
}
 
create_user(db, "Charlie", "charlie@example.com", 28);

Read

func get_user_by_id(db, user_id) {
    let sql = "SELECT * FROM users WHERE id = " + tostring(user_id);
    let results = sqlite.query(db, sql);
    if (len(results) > 0) {
        return results[0];
    }
    return null;
}
 
let user = get_user_by_id(db, 1);
if (user != null) {
    print(user["name"]);
}

Update

func update_user_email(db, user_id, new_email) {
    let sql = "UPDATE users SET email = '" + new_email + "' WHERE id = " + tostring(user_id);
    sqlite.exec(db, sql);
}
 
update_user_email(db, 1, "newemail@example.com");

Delete

func delete_user(db, user_id) {
    let sql = "DELETE FROM users WHERE id = " + tostring(user_id);
    sqlite.exec(db, sql);
}
 
delete_user(db, 3);

Application Example

// Task Manager Application
let db = sqlite.open("tasks.db");
 
// Setup
sqlite.exec(db, "CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    completed BOOLEAN DEFAULT 0,
    priority TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
)");
 
// Add tasks
func add_task(db, title, description, priority) {
    let sql = "INSERT INTO tasks (title, description, priority) VALUES ('" + 
              title + "', '" + description + "', '" + priority + "')";
    sqlite.exec(db, sql);
}
 
add_task(db, "Write docs", "Complete documentation", "high");
add_task(db, "Fix bug", "Fix login issue", "urgent");
add_task(db, "Code review", "Review PR #123", "medium");
 
// Get all tasks
let tasks = sqlite.query(db, "SELECT * FROM tasks ORDER BY priority");
print("Tasks:");
let i = 0;
while (i < len(tasks)) {
    let task = tasks[i];
    print("- " + task["title"] + " [" + task["priority"] + "]");
    i = i + 1;
}
 
// Mark task complete
sqlite.exec(db, "UPDATE tasks SET completed = 1 WHERE id = 1");
 
// Get incomplete tasks
let incomplete = sqlite.query(db, "SELECT * FROM tasks WHERE completed = 0");
print("Incomplete tasks: " + tostring(len(incomplete)));
 
sqlite.close(db);

Best Practices

SQL Injection Prevention

Be careful with user input - properly escape strings or use parameterized queries:

// Potentially unsafe
let user_input = "Alice";
let sql = "SELECT * FROM users WHERE name = '" + user_input + "'";
 
// Better: validate and sanitize input
func safe_query(db, name) {
    // Remove dangerous characters
    let safe_name = replace(name, "'", "''");
    let sql = "SELECT * FROM users WHERE name = '" + safe_name + "'";
    return sqlite.query(db, sql);
}

Resource Management

Always close database connections:

let db = sqlite.open("app.db");
// ... use database
sqlite.close(db);  // Don't forget!

Error Handling

Check query results:

let results = sqlite.query(db, "SELECT * FROM users WHERE id = 999");
if (len(results) == 0) {
    print("User not found");
} else {
    print(results[0]["name"]);
}

Common Patterns

Migrations

func init_database(db) {
    // Create version table
    sqlite.exec(db, "CREATE TABLE IF NOT EXISTS schema_version (version INTEGER)");
    
    let version_result = sqlite.query(db, "SELECT version FROM schema_version");
    let current_version = 0;
    
    if (len(version_result) > 0) {
        current_version = version_result[0]["version"];
    }
    
    // Apply migrations
    if (current_version < 1) {
        sqlite.exec(db, "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
        sqlite.exec(db, "INSERT INTO schema_version VALUES (1)");
    }
    
    if (current_version < 2) {
        sqlite.exec(db, "ALTER TABLE users ADD COLUMN email TEXT");
        sqlite.exec(db, "UPDATE schema_version SET version = 2");
    }
}

Next Steps

Config & EnvHTTP Server & Client