Published on

SQLite as a Document Database: How to Store JSON in 2026

SQLite functions as a document database by using a JSON (JavaScript Object Notation - a text-based format for storing data) data type to store flexible, unstructured information within a traditional table column. By using the built-in JSON1 extension, you can query, index, and update nested data just as you would in a specialized document store like MongoDB, but without the overhead of managing a separate server. This approach allows you to build local-first applications with dynamic schemas in under five minutes using a single file.

Why should you use SQLite for document storage?

SQLite is traditionally a relational database, which means it usually expects data to be organized into strict rows and columns. However, modern applications often deal with "messy" data that doesn't always fit into a fixed grid.

Using SQLite as a document store gives you the best of both worlds. You get the reliability of a standard database along with the flexibility to add new fields to your data without changing your table structure.

This setup is particularly useful for beginners because it eliminates the need to install complex database engines. Since SQLite is just a file on your computer, you can start storing complex JSON objects immediately without worrying about server configurations.

How does the JSON data type work in SQLite?

In SQLite, there isn't a separate "Document" column type, but rather a set of functions that treat text columns as JSON. You store your data as a string, and SQLite uses its internal logic to parse (read and understand) that string when you search through it.

We've found that this hybrid approach prevents the common frustration of having to "migrate" your database every time your app needs a new feature. You simply add a new key to your JSON object, and the database handles it automatically.

This flexibility makes SQLite a highly efficient choice for prototypes and small-scale production apps. It provides the speed of a local file with the power of modern data structures.

What do you need to get started?

To follow this guide, you will need a few basic tools installed on your machine. Don't worry if you haven't used these specific versions before; the commands remain very similar across updates.

  • Python 3.15+: Python comes with SQLite support built-in, so you don't need to install the database separately.
  • A Code Editor: VS Code or even a simple text editor will work fine.
  • Terminal Access: You will need to run a few commands in your Command Prompt, Terminal, or PowerShell.

Step 1: Create your first document table

First, you need to create a table that has a column dedicated to storing JSON data. In this example, we will create a "settings" table where each user can have different types of configurations.

Create a file named app.py and add the following code:

import sqlite3
import json

# Connect to a database file (it will be created if it doesn't exist)
connection = sqlite3.connect("my_data.db")
cursor = connection.cursor()

# Create a table with a column named 'data' to hold our JSON documents
cursor.execute("CREATE TABLE IF NOT EXISTS profiles (id INTEGER PRIMARY KEY, data TEXT)")

connection.commit()
print("Database and table created successfully!")

What you should see:

When you run python app.py, a new file named my_data.db will appear in your folder. The terminal will print "Database and table created successfully!"

Step 2: Insert JSON documents into the database

Now that the table exists, you can insert complex data. Instead of creating a column for "theme," "language," and "notifications," you just bundle them into one JSON object.

Add this code to your app.py file:

# Define a complex dictionary (a Python object that looks like JSON)
user_settings = {
    "name": "Alice",
    "theme": "dark",
    "notifications": {"email": True, "sms": False},
    "tags": ["developer", "beginner"]
}

# Convert the dictionary to a JSON string and insert it
json_string = json.dumps(user_settings)
cursor.execute("INSERT INTO profiles (data) VALUES (?)", (json_string,))

connection.commit()
print("Document inserted!")

What you should see:

The terminal will display "Document inserted!" indicating that your multi-layered data is now safely stored in a single database column.

Step 3: Query specific fields inside the JSON

The real power of a document database is the ability to search for specific values inside the blob of text. SQLite uses the -> and ->> operators to reach into the JSON.

Try adding this snippet to search for users who prefer "dark" mode:

# Use the ->> operator to extract a value as a simple string
cursor.execute("SELECT data->>'name' FROM profiles WHERE data->>'theme' = 'dark'")

result = cursor.fetchone()
print(f"User found: {result[0]}")

What you should see:

The output should show "User found: Alice". This proves SQLite is reading the data inside the text column rather than just treating it as a flat string.

Step 4: Update a single value in a document

One common fear beginners have is that they must overwrite the entire document to change one small piece. SQLite provides the json_set function to update specific keys without touching the rest of the data.

# Update only the 'theme' key to 'light' for the user named Alice
cursor.execute("""
    UPDATE profiles 
    SET data = json_set(data, '$.theme', 'light') 
    WHERE data->>'name' = 'Alice'
""")

connection.commit()
print("Document updated!")

What you should see:

The terminal will confirm "Document updated!". If you were to fetch the data again, the theme would be "light" but the "notifications" and "tags" would remain unchanged.

What are the common gotchas?

While SQLite is powerful, it is normal to run into a few hurdles when starting out. One major mistake is forgetting to use the json() function wrapper when inserting raw strings to ensure they are valid.

If your JSON is malformed (has a missing bracket or quote), SQLite will throw an error if you try to use JSON functions on it. Always validate your data in your code before sending it to the database to avoid these crashes.

Another thing to remember is that SQLite is a file-based system. If multiple programs try to write to the file at the exact same millisecond, you might see a "Database is locked" error.

This is usually solved by setting a timeout in your connection string. For most beginner projects, however, the default speed is more than enough to avoid this issue.

How do you optimize document queries?

As your database grows to thousands of items, searching through raw text can become slow. To fix this, you can use "Generated Columns" to create indexes (a way for the database to find data faster, like a book's index) on specific JSON keys.

-- Example of adding an index to the 'name' field inside the JSON
ALTER TABLE profiles ADD COLUMN user_name TEXT GENERATED ALWAYS AS (data->>'name') VIRTUAL;
CREATE INDEX idx_user_name ON profiles(user_name);

By doing this, SQLite pre-calculates the name field. When you search for a user, it looks at the index instead of reading every single JSON document in the table.

Next Steps

Now that you have built a basic document store with SQLite, you can explore more advanced features like full-text search or combining relational tables with JSON documents. Try building a small note-taking app where each note can have different metadata fields.

To deepen your understanding, check out the official SQLite JSON documentation.

Read the SQLite Documentation