Rust for SQLite: Data Engineering Foundations

This repository contains hands-on examples and labs for using SQLite with Rust for data engineering tasks. A Coursera course from Pragmatic AI Labs.

Contents

This repository has example projects in ./examples and hands-on labs in ./labs. Make sure you have the Rust toolchain installed.

This repository is Codespaces ready and set as a template repository. You can open it directly in a GitHub Codespace — Rust, rust-analyzer, and all extensions are pre-installed.

Labs

Complete these hands-on labs to reinforce your learning:

Lab	Topic	Example
Lab 1: What is SQLite and Why Use It with Rust?	Connections, setup, SQLite vs. client-server	examples/1-sqlite-intro
Lab 2: Core SQL Operations in Rust	CREATE TABLE, INSERT, SELECT, UPDATE, DELETE	examples/2-core-sql
Lab 3: Error Handling and Transactions	Result, ?, transactions, in-memory test DBs	examples/3-error-handling
Lab 4: Loading Data from CSV	csv crate, bulk inserts, type coercion	examples/4-csv-ingestion
Lab 5: Loading and Exporting JSON	serde_json, JSON blobs, exporting results	examples/5-json
Lab 6: Dumping and Migrating Databases	SQL dump, restore, ALTER TABLE migrations	examples/6-db-dump
Lab 7: CLI Architecture with clap	clap derive API, subcommands, env vars	examples/7-cli-architecture
Lab 8: Crawling the Filesystem and Persisting Metadata	walkdir, file metadata, upserts	examples/8-filesystem-crawl
Lab 9: Querying, Reporting, and Exporting Results	Aggregate queries, CSV/JSON export	examples/9-query-export

Course Outline

Module 1: SQLite Foundations

Lesson 1.1 — What is SQLite and why use it with Rust?

• SQLite connections in Rust
• SQLite vs. client-server databases
• The rusqlite crate: setup and first connection

Lesson 1.2 — Core SQL operations in Rust

• CREATE TABLE, INSERT, SELECT, UPDATE, DELETE
• Schema design for data pipelines
• Parameterized queries with rusqlite

Lesson 1.3 — Error handling and transactions

• Transactions and in-memory test databases
• Mapping SQLite errors to Rust's Result
• Batching writes for performance and consistency

Module 2: Data Ingestion and Export

Lesson 2.1 — Loading data from CSV

• Bulk-loading CSV rows into SQLite
• Parsing CSV with the csv crate
• Handling missing values and schema mismatches

Lesson 2.2 — Loading and exporting JSON

• serde_json ingestion and export
• Deserializing JSON into Rust structs
• Storing JSON blobs and exporting query results

Lesson 2.3 — Dumping and migrating databases

• SQL dump generation and schema migrations
• Generating and restoring SQL dump files
• Adding columns and evolving tables safely

Module 3: Building the CLI Project

Lesson 3.1 — CLI architecture with clap

• Multi-subcommand CLI with clap
• clap derive API and subcommand dispatch
• DB path via flags, env vars, and defaults

Lesson 3.2 — Crawling the filesystem and persisting metadata

• walkdir and file metadata upserts
• Walking directory trees and reading metadata
• Incremental crawls with upserts

Lesson 3.3 — Querying, reporting, and exporting results

• Aggregate queries and CSV/JSON export
• Largest files, totals by extension, recent changes
• Exporting results from the CLI

Graded Project: fscrawl

Build fscrawl — a Rust CLI tool that walks a directory, stores file metadata in SQLite, and supports these subcommands:

• crawl — walk a directory tree and insert/upsert file records (path, size, extension, modified timestamp)
• query — report largest files, totals by extension, and recently changed files
• export — write results to CSV or JSON
• db-dump — export the full database as a SQL dump file
• summary — print aggregate statistics to stdout

A starter implementation is in fscrawl/.

# Build
cargo build -p fscrawl

# Crawl the current directory
cargo run -p fscrawl -- crawl .

# Report stats
cargo run -p fscrawl -- query --top 5

# Export to JSON
cargo run -p fscrawl -- export --format json --output results.json

# Dump the database
cargo run -p fscrawl -- db-dump --output backup.sql

# Aggregate summary
cargo run -p fscrawl -- summary

Local Setup

1. Install the Rust toolchain:

   curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

2. Clone this repository:

   git clone https://github.com/alfredodeza/rust-for-sqlite.git
   cd rust-for-sqlite

3. Build the entire workspace:

   cargo build --workspace

4. Run an example:

   cargo run -p sqlite-intro

5. Run tests:

   cargo test --workspace

Key Crates

Crate	Purpose
rusqlite	SQLite bindings for Rust
clap	CLI argument parsing
serde / serde_json	JSON serialization and deserialization
csv	CSV reading and writing
walkdir	Recursive directory traversal
anyhow	Ergonomic error handling

Resources

• rusqlite documentation
• SQLite documentation
• The Rust Book
• clap documentation

Coursera Courses

• Rust for Data Engineering Specialization
• MLOps Machine Learning Operations Specialization
• Linux and Bash for Data Engineering