A Simple Analogy

SQLite is like a personal database librarian. Instead of going to a central library (server), you keep your database with you wherever you go.

---

Why SQLite?

• Serverless: No separate server needed
• Lightweight: Single file
• Portable: Works on any device
• ACID compliant: Data integrity
• Edge computing: Perfect for IoT

---

Setup and Connections

using System.Data.SQLite;

// Connection string
const string connectionString = "Data Source=mydb.sqlite;Version=3;";

// Using Entity Framework
builder.Services.AddDbContext<AppContext>(options =>
    options.UseSqlite("Data Source=app.db")
);

// Direct connection
using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT * FROM Users WHERE Id = @id";
        command.Parameters.AddWithValue("@id", 1);
        
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Name"]);
            }
        }
    }
}

---

Full-Text Search

-- Create FTS table
CREATE VIRTUAL TABLE articles_fts USING fts5(
  id,
  title,
  content,
  category
);

-- Insert data
INSERT INTO articles_fts SELECT id, title, content, category FROM articles;

-- Search
SELECT * FROM articles_fts WHERE articles_fts MATCH 'database AND optimization' LIMIT 10;

// In C#
var results = await context.Articles
    .FromSqlRaw("SELECT * FROM articles_fts WHERE articles_fts MATCH @query", 
        new SqliteParameter("@query", searchTerm))
    .ToListAsync();

---

JSON Support

-- Store JSON data
CREATE TABLE config (
  id INTEGER PRIMARY KEY,
  name TEXT,
  settings JSON
);

INSERT INTO config VALUES (1, 'app', '{"theme": "dark", "language": "en"}');

-- Query JSON
SELECT name, json_extract(settings, '$.theme') as theme FROM config;

-- Update JSON
UPDATE config SET settings = json_set(settings, '$.theme', 'light') WHERE id = 1;

---

Transactions

using (var transaction = connection.BeginTransaction())
{
    try
    {
        var cmd1 = connection.CreateCommand();
        cmd1.CommandText = "UPDATE accounts SET balance = balance - 100 WHERE id = 1";
        cmd1.ExecuteNonQuery();
        
        var cmd2 = connection.CreateCommand();
        cmd2.CommandText = "UPDATE accounts SET balance = balance + 100 WHERE id = 2";
        cmd2.ExecuteNonQuery();
        
        transaction.Commit();
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        throw;
    }
}

---

Indexing

-- Create index on frequently searched columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_customer ON orders(customer_id, status);

-- Analyze for optimization
ANALYZE;

-- Get query plan
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1 AND status = 'pending';

---

Offline-First Sync

public class SyncService
{
    private readonly SqliteConnection _localDb;
    private readonly HttpClient _api;
    
    public async Task SyncAsync()
    {
        try
        {
            // Get changes from server
            var remoteChanges = await _api.GetAsync("/api/sync/changes");
            
            // Merge into local database
            using (var transaction = _localDb.BeginTransaction())
            {
                foreach (var change in remoteChanges)
                {
                    await ApplyChangeAsync(_localDb, change);
                }
                transaction.Commit();
            }
            
            // Push local changes to server
            var localChanges = GetLocalChanges();
            await _api.PostAsync("/api/sync/push", localChanges);
        }
        catch (HttpRequestException)
        {
            // No connection, work offline
            Console.WriteLine("Working offline");
        }
    }
}

---

Best Practices

1. WAL mode: Enable Write-Ahead Logging for concurrency
2. Pragma settings: Optimize for your use case
3. Regular backups: Automatic snapshots
4. Connection pooling: Reuse connections
5. Index wisely: Test before creating

---

Related Concepts

• Database replication
• Conflict resolution
• Edge computing
• Offline-first architecture

---

Summary

SQLite provides a lightweight, serverless database perfect for embedded applications and offline-first systems. Use full-text search, JSON, and transactions for powerful local data management.