jsonsql

A lightweight, SQL-like database engine backed by JSON files. You write familiar queries, and the data lives in plain JSON files you can open in any text editor.

This is a fun side project, not something you should rely on for anything serious. If you are building something real, use SQLite or PostgreSQL instead. They are fast, battle-tested, and handle everything this library does not. jsonsql exists because it was fun to build, not because the world needed another database.

---

Features

• SQL-like queries: SELECT, INSERT, UPDATE, DELETE with WHERE, ORDER BY, and LIMIT
• RETURNING clause on mutations to get rows back immediately
• PRIMARY KEY and UNIQUE constraints with enforcement
• Seven data types: integer, text, number, bool, int_array, text_array, number_array
• Fully async I/O with auto-save on mutation and an explicit flush for safe shutdown
• Atomic writes using a temp file and os.replace (no partial writes on crash or interruption)
• JSON with comments supported (// and /* */ are stripped before parsing)
• Direct Python API if you want to skip SQL entirely

---

Installation

python -m pip install "jsonsql @ git+https://github.com/Soheab/jsonsql"

Requires Python 3.12 or later.

---

File Format

jsonsql uses two separate files: a schema file that defines your tables and columns, and a data file that holds the rows.

schema.json

{
    "users": {
        "id": { "type": "integer", "primary": true },
        "username": { "type": "text", "unique": true },
        "score": { "type": "number" }
    },
    "posts": {
        "id": { "type": "integer", "primary": true },
        "user_id": { "type": "integer" },
        "title": { "type": "text" }
    }
}

data.json

{
    "users": [
        { "id": 1, "username": "alice", "score": 95.5 },
        { "id": 2, "username": "bob", "score": 78.0 }
    ],
    "posts": [
        { "id": 1, "user_id": 1, "title": "hello world" }
    ]
}

Each table in the schema is a dict of column names to their definition. Each table in the data file is a plain array of row objects. The schema is never modified at runtime; only the data file is written on save.

---

Quick Start

import asyncio
from pathlib import Path
from jsonsql import JsonDB

async def main():
    db = JsonDB(Path("data.json"), Path("schema.json"))
    await db.init()

    rows = db.sql("SELECT * FROM users")
    print(rows)
    # [{'id': 1, 'username': 'alice', 'score': 95.5}, ...]

    new_id = db.sql("INSERT INTO users (id, username, score) VALUES (3, 'carol', 88.5)")
    print(new_id)
    # 3

    await db.flush()

asyncio.run(main())

---

Async Model

jsonsql is built around asyncio. All file I/O is non-blocking, using asyncio.to_thread under the hood.

The three async methods you need to know:

• await db.init() -- loads and parses both JSON files. Must be called before any queries.
• await db.save() -- writes the current data state to disk atomically. Does not touch the schema file.
• await db.flush() -- waits for all in-flight auto-save tasks to finish. Call this before your program exits.

db.sql() itself is synchronous. Mutations (INSERT, UPDATE, DELETE) trigger an auto-save in the background by spawning a tracked asyncio.Task. Call await db.flush() before shutdown to ensure everything has been written.

async def main():
    db = JsonDB(Path("data.json"), Path("schema.json"))
    await db.init()

    db.sql("INSERT INTO logs (level, message) VALUES ('info', 'started')")
    db.sql("INSERT INTO logs (level, message) VALUES ('info', 'running')")

    await db.flush()

If you prefer explicit control, call await db.save() yourself.

---

Query Reference

SELECT

SELECT * FROM users
SELECT id, username FROM users
SELECT * FROM users WHERE score > 80
SELECT * FROM users WHERE username LIKE 'a%'
SELECT * FROM users WHERE score > 50 AND active = true
SELECT * FROM users ORDER BY score DESC
SELECT * FROM users ORDER BY username ASC LIMIT 10

Returns a list of dicts, one per matching row.

INSERT

INSERT INTO users (id, username) VALUES (4, 'dave')
INSERT INTO users (id, username) VALUES (5, 'eve') RETURNING *
INSERT INTO users (id, username) VALUES (6, 'frank') RETURNING id, username

Without RETURNING, returns the new row's integer ID. With RETURNING, returns a list of dicts for the inserted row.

UPDATE

UPDATE users SET username = 'alice2' WHERE id = 1
UPDATE users SET score = 100 WHERE username = 'bob' RETURNING *

Without RETURNING, returns the count of affected rows. With RETURNING, returns the updated rows after the change.

DELETE

DELETE FROM users WHERE id = 3
DELETE FROM users WHERE score < 10 RETURNING *

Without RETURNING, returns the count of deleted rows. With RETURNING, returns the rows as they were before deletion.

WHERE conditions

Operator	Example
=	WHERE id = 1
!= or <>	WHERE status != 'banned'
<, >, <=, >=	WHERE score >= 50
LIKE	WHERE name LIKE 'a%'
AND, OR	WHERE a > 1 AND b < 10

LIKE patterns: % matches any sequence of characters, _ matches exactly one. Matching is case-insensitive.

String literals use single quotes. Escape a single quote by doubling it: 'it''s fine'. Null, true, and false are unquoted keywords.

---

Data Types

Type	Python equivalent
integer	int
text	str
number	float
bool	bool
int[]	list[int]
text[]	list[str]
number[]	list[float]

---

Constraints

Constraints are defined in schema.json and enforced at runtime.

primary -- one per table, enforces uniqueness. Updating a row to its existing value is allowed.

unique -- any number of unique columns per table. A duplicate value raises a ValueError.

---

Python API

You can bypass SQL and work with the objects directly.

# Access a table
table = db.tables["users"]

# Get a record by row ID (returns a dict)
record = table.get_record(1)

# Insert a record
new_id = table.insert_record({"id": 7, "username": "grace", "score": 80.0})

# Update a record
table.update_record(1, {"username": "alice_updated"})

# Delete a record
table.delete_record(7)

# Work with columns and rows
col = table.get_column("username")
for row in col:
    print(row.id, row.value)

Mutations through the Python API trigger auto-saves the same way SQL mutations do.

---

Limitations

• No JOINs between tables
• No transactions or rollback
• No indexes; all WHERE conditions do a linear scan
• Not designed for high concurrency or large datasets
• No schema migration tools

If any of the above matters to you, please use SQLite or PostgreSQL.

If you are new to SQL and want to actually learn it, these are good starting points:

• SQLite Tutorial -- beginner-friendly, uses SQLite which runs locally with no setup
• PostgreSQL Tutorial -- covers Postgres from the basics up
• SQLBolt -- interactive lessons you run in the browser, no install needed
• W3Schools SQL -- quick reference for syntax and common queries