LocalDB for MicroPython

A lightweight SQL database library for MicroPython. This is a MicroPython port of LocalDB.js.

Supports simple SQL queries and persists data to JSON files. Works on MicroPython environments such as ESP32, ESP8266, Raspberry Pi Pico, and standard Python.

日本語版 README はこちら

Features

• SQL Support - Standard SQL syntax for data operations
• Operators - WHERE clause with various operators (=, !=, >, <, BETWEEN, IN, LIKE, etc.)
• Aggregation - COUNT(), SUM(), AVG(), MAX(), MIN()
• String Functions - UPPER(), LOWER(), LENGTH()
• JOIN Support - INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN
• Grouping - GROUP BY with HAVING clause
• Sorting & Paging - ORDER BY, LIMIT, OFFSET
• Data Persistence - Automatic save to JSON files

Installation

Simply copy localdb.py to your MicroPython device:

# Using mpremote
mpremote cp localdb.py :

# Or using ampy
ampy --port /dev/ttyUSB0 put localdb.py

For standard Python, just place localdb.py in your project directory.

Quick Start

from localdb import LocalDB

# Create database (data saved to mydb_*.json files)
db = LocalDB('mydb')

# Create table
db.execute("CREATE TABLE users (id, name, age, city)")

# Insert data
db.execute("INSERT INTO users VALUES (1, 'Alice', 30, 'Tokyo')")
db.execute("INSERT INTO users VALUES (2, 'Bob', 25, 'Osaka')")

# Query data
result = db.execute("SELECT * FROM users WHERE age > 25")
print(result)
# [{'id': 1, 'name': 'Alice', 'age': 30, 'city': 'Tokyo'}]

# Update data
db.execute("UPDATE users SET age = 31 WHERE name = 'Alice'")

# Delete data
db.execute("DELETE FROM users WHERE name = 'Bob'")

# Close database
db.close()

Advanced Examples

WHERE Conditions

# Comparison operators
db.execute("SELECT * FROM users WHERE age >= 30")
db.execute("SELECT * FROM users WHERE age != 25")

# BETWEEN
db.execute("SELECT * FROM users WHERE age BETWEEN 25 AND 35")

# IN / NOT IN
db.execute("SELECT * FROM users WHERE city IN ('Tokyo', 'Osaka')")

# LIKE (% = any characters, _ = single character)
db.execute("SELECT * FROM users WHERE name LIKE 'A%'")

# IS NULL / IS NOT NULL
db.execute("SELECT * FROM users WHERE city IS NOT NULL")

# AND / OR
db.execute("SELECT * FROM users WHERE city = 'Tokyo' AND age > 25")
db.execute("SELECT * FROM users WHERE city = 'Tokyo' OR city = 'Osaka'")

Aggregation Functions

# COUNT
db.execute("SELECT COUNT(*) AS total FROM users")

# SUM, AVG, MAX, MIN
db.execute("SELECT SUM(age) AS total_age FROM users")
db.execute("SELECT AVG(age) AS avg_age FROM users")
db.execute("SELECT MAX(age) AS max_age, MIN(age) AS min_age FROM users")

GROUP BY

# Group by city
db.execute("SELECT city, COUNT(*) AS count FROM users GROUP BY city")

# With HAVING
db.execute("""
    SELECT city, COUNT(*) AS count
    FROM users
    GROUP BY city
    HAVING count > 1
""")

JOIN

# Create related tables
db.execute("CREATE TABLE orders (id, user_id, product, price)")
db.execute("INSERT INTO orders VALUES (1, 1, 'Laptop', 1200)")

# INNER JOIN
db.execute("""
    SELECT users.name, orders.product, orders.price
    FROM users
    INNER JOIN orders ON users.id = orders.user_id
""")

# LEFT JOIN
db.execute("""
    SELECT users.name, orders.product
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id
""")

ORDER BY, LIMIT, OFFSET

# Sort descending
db.execute("SELECT * FROM users ORDER BY age DESC")

# Limit results
db.execute("SELECT * FROM users LIMIT 10")

# Pagination
db.execute("SELECT * FROM users LIMIT 10 OFFSET 20")

String Functions

db.execute("SELECT name, UPPER(name) AS upper_name FROM users")
db.execute("SELECT name, LOWER(name) AS lower_name FROM users")
db.execute("SELECT name, LENGTH(name) AS name_length FROM users")

DISTINCT

db.execute("SELECT DISTINCT city FROM users")

API Reference

Constructor

LocalDB(db_name='localdb', storage_path='')

Parameter	Type	Description
db_name	str	Database name (file prefix)
storage_path	str	Directory path for data files

Methods

Method	Description	Returns
execute(query)	Execute SQL query	List (SELECT) or dict (others)
get_tables()	Get all table names	List of table names
get_schema(table_name)	Get table schema	List of column names
clear()	Delete all data	None
close()	Save and close database	None

Supported SQL Syntax

DDL

• CREATE TABLE [IF NOT EXISTS] table_name (col1, col2, ...)
• DROP TABLE [IF EXISTS] table_name

DML

• INSERT INTO table_name [(columns)] VALUES (values)
• UPDATE table_name SET col = value [WHERE ...]
• DELETE FROM table_name [WHERE ...]

Query

• SELECT [DISTINCT] columns FROM table [JOIN ...] [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...] [LIMIT n [OFFSET m]]

Data Storage

Data is automatically saved to the localdb/ directory (created if not exists):

• localdb/{db_name}_schema.json - Table schemas
• localdb/{db_name}_{table_name}.json - Table data

You can specify a custom directory:

db = LocalDB('mydb', storage_path='data')  # saves to data/ folder
db = LocalDB('mydb', storage_path='')      # saves to current directory

Limitations

• No transaction support
• No index support (full table scan)
• No nested subqueries
• Single database connection only
• Limited to available memory/storage

Platform Support

Platform	Status
ESP32	✅ Tested
ESP8266	✅ Compatible
Raspberry Pi Pico	✅ Compatible
Standard Python 3.x	✅ Tested

Performance Tips

1. Limit result sets - Use LIMIT to reduce memory usage
2. Select specific columns - Avoid SELECT * when possible
3. Close database - Call close() to ensure data is saved
4. Batch inserts - Group multiple INSERTs together

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

This project is licensed under the MIT License - see the LICENSE file for details.

Author

UO Soft (uosoft@uosoft.net)

Related Projects

• LocalDB.js - JavaScript version for browsers