SQLite Core

An educational project showcasing how to use SQLite with Python, covering both synchronous and asynchronous approaches.

Project Structure

sqlite-core/
├── app/                   # Main application code
├── .env.example           # Example environment variables file
├── pixi.lock              # Locked dependency versions for reproducible environments
├── pixi.toml              # Pixi project configuration: environments, dependencies, 
│                          # platforms and project-specific commands
└── playground-testing/    # Jupyter notebooks for playground testing

Each directory includes its own README.md with detailed information about its contents and usage, and every file contains comprehensive inline comments to explain the code.

Dependencies Overview

• pydantic-settings — a Pydantic-powered library for managing application configuration and environment variables with strong typing, validation, and seamless .env support.

• SQLAlchemy — the Python SQL toolkit and Object-Relational Mapper (ORM) used as the foundation for database modeling, querying, and transaction management in both synchronous and asynchronous contexts.

• Alembic — a lightweight database migration tool for SQLAlchemy, enabling structured, version-controlled evolution of the SQLite schema over time.

• sqlite3 — python’s built-in module for working with SQLite databases — no extra installation required.

• aiosqlite — an asynchronous wrapper around Python’s built-in sqlite3 module, enabling non-blocking SQLite database operations.

Testing & Development Dependencies

• JupyterLab — a next-generation web-based interactive development environment for Jupyter notebooks; used here to create interactive documents for testing and verifying code execution.

• sqlite — the official SQLite engine. While Python’s built-in sqlite3 module handles all runtime database operations (which is why the SQLite engine is listed under "Testing & Development"), it lacks a very important feature for testing (at least for me): the ability to connect to the database via a command-line interface and inspect or query data using SQL-like commands. That’s why I install the full SQLite engine — it includes a CLI tool that allows me to directly inspect, query, and manipulate the SQLite database file from the terminal — for example, to verify the schema structure or examine raw data.

Quick Start

I. Prerequisites

• Pixi package manager.

Platform note: All development and testing were performed on linux-64.
If you're using a different platform, you’ll need to:

1. Update the platforms list in the pixi.toml accordingly.
2. Ensure that platform-specific scripts are compatible with your operating system or replace them with equivalents.

II. Database Setup

1. Clone the repository

   git clone git@github.com:Sierra-Arn/sqlite-core.git
   cd sqlite-core

2. Install dependencies

   pixi install --all

3. Setup environment configuration

   pixi run copy-env

4. Create a database migration & Apply it

   pixi run alembic-revision
   pixi run alembic-upgrade

III. Testing

Once a database is ready, you can run and test the SQLite implementation with interactive Jupyter notebooks in playground-testing/:

1. Launch JupyterLab

   pixi run -e test jupyter lab

2. Test the SQLite implementation

   • JupyterLab should open automatically in your browser at the default address.
   • In JupyterLab, navigate to the playground-testing/ folder.
   • Open and execute the notebooks interactively.

3. (Optional) Verify database manually
   You can open a SQLite shell to manually verify that everything is working correctly:

   pixi run sqlite-shell

IV. Cleanup

When you finish testing, stop JupyterLab by pressing Ctrl+C in the terminal where it's running.

License

This project is licensed under the BSD-3-Clause License.