SQLite Support

Complete guide to using SQLite with Akron ORM - file-based database perfect for development and small applications.

Overview

SQLite is a lightweight, file-based database that requires no separate server process. It's perfect for development, testing, prototyping, and small to medium applications.

✅ Advantages

  • • Zero configuration required
  • • Single file database storage
  • • ACID compliant transactions
  • • Cross-platform compatibility
  • • Perfect for development and testing

Connection Setup

Basic Connection

SQLite Connection Examples
1from akron import Akron
2
3# File database (recommended for production)
4db = Akron("sqlite:///app.db")
5
6# In-memory database (testing only)
7db = Akron("sqlite:///:memory:")
8
9# Relative path
10db = Akron("sqlite:///./data/app.db")
11
12# Absolute path
13db = Akron("sqlite:///C:/projects/myapp/database.db")

Connection URLs

File Database

sqlite:///path/to/database.db

Creates or connects to a file-based database

In-Memory Database

sqlite:///:memory:

Creates a temporary database in RAM (lost on disconnect)

Data Types

SQLite uses dynamic typing with storage classes. Akron maps Python types automatically:

Python TypeSQLite StorageExample
intINTEGER42, -123
strTEXT"Hello World"
floatREAL3.14, -2.5
boolINTEGER1 (True), 0 (False)
bytesBLOBBinary data

Complete Example

SQLite Full CRUD Example
1from akron import Akron
2from pydantic import BaseModel
3from akron.models import ModelMixin
4
5# Define your model
6class User(BaseModel, ModelMixin):
7 id: int
8 name: str
9 email: str
10 age: int
11 active: bool = True
12
13# Connect to SQLite database
14db = Akron("sqlite:///users.db")
15
16# Create table
17User.create_table(db)
18
19# Insert data
20user1 = User(id=1, name="Alice Johnson", email="alice@example.com", age=28)
21user2 = User(id=2, name="Bob Smith", email="bob@example.com", age=35)
22
23User.insert(db, user1)
24User.insert(db, user2)
25
26# Query data
27all_users = User.select(db)
28print(f"Total users: {len(all_users)}")
29
30# Find specific user
31alice = User.find(db, {"name": "Alice Johnson"})
32print(f"Found: {alice.name} ({alice.email})")
33
34# Update user
35User.update(db, {"id": 1}, {"age": 29})
36
37# Delete user
38User.delete(db, {"id": 2})
39
40# Verify changes
41remaining_users = User.select(db)
42print(f"Remaining users: {len(remaining_users)}")

Performance Optimization

SQLite-Specific Tips

WAL Mode

Enable Write-Ahead Logging for better concurrency:

1# Enable WAL mode for better performance
2db.execute_raw("PRAGMA journal_mode=WAL;")

Indexing

Create indexes for frequently queried columns:

1# Create index on email column
2db.execute_raw("CREATE INDEX IF NOT EXISTS idx_user_email ON users(email);")

Batch Operations

Use transactions for bulk operations:

1# Batch insert with transaction
2users = [
3 User(id=i, name=f"User {i}", email=f"user{i}@example.com", age=20+i)
4 for i in range(1000)
5]
6
7# This is automatically wrapped in a transaction
8for user in users:
9 User.insert(db, user)

CLI Commands

Use Akron CLI for database management tasks:

SQLite CLI Examples
1# Create a table
2akron create-table users --db "sqlite:///app.db" --schema '{"id": "int", "name": "str", "email": "str"}'
3
4# Inspect database schema
5akron inspect-schema --db "sqlite:///app.db"
6
7# Seed with test data
8akron seed users --db "sqlite:///app.db" --data '[{"id": 1, "name": "Test User", "email": "test@example.com"}]'
9
10# Execute raw SQL
11akron raw-sql --db "sqlite:///app.db" --query "SELECT name FROM sqlite_master WHERE type='table';"
12
13# Create migration
14akron makemigrations add_users_table --db "sqlite:///app.db" --schema '{"id": "int", "name": "str", "email": "str", "created_at": "str"}'

Best Practices

✅ Do

  • • Use file databases for persistent data
  • • Enable WAL mode for production applications
  • • Create indexes on frequently queried columns
  • • Use :memory: databases for testing
  • • Backup database files regularly

❌ Don't

  • • Use SQLite for high-concurrency write scenarios
  • • Store large blobs directly in the database
  • • Use network storage for database files
  • • Rely on :memory: databases for important data
  • • Forget to handle file locking issues

Common Issues & Solutions

Database is locked

This usually happens when another process has the database open or a transaction wasn't properly closed.

1# Solution: Enable WAL mode and check for proper connection handling
2db.execute_raw("PRAGMA journal_mode=WAL;")
3db.execute_raw("PRAGMA busy_timeout=30000;") # 30 second timeout

File not found

SQLite will create the database file if it doesn't exist, but the directory must exist.

1import os
2from akron import Akron
3
4# Ensure directory exists
5os.makedirs("./data", exist_ok=True)
6db = Akron("sqlite:///./data/app.db")

Related Documentation

Akron Constructor

Learn about connection setup and configuration

CLI Commands

Command-line tools for database management

MySQL Support

Compare with MySQL features and capabilities

Model Definitions

Learn about Pydantic model integration