Airflow SQLite Backend Error Fix

In this tutorial, you'll learn about Airflow SQLite Backend Error Fix. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Airflow shows:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked

SQLite does not support concurrent writes. When the Airflow scheduler, webserver, and workers all try to write to the same SQLite database, only one writer succeeds and the rest get "database is locked." SQLite is only suitable for single-user local development.

Step-by-Step Fix

1. Switch to PostgreSQL or MySQL

WRONG — trying to tune SQLite for concurrent access:

# SQLite is fundamentally single-writer

RIGHT — configure PostgreSQL:

# Install PostgreSQL
sudo apt-get install postgresql postgresql-contrib
sudo -u postgres psql -c "CREATE USER airflow WITH PASSWORD 'airflow';"
sudo -u postgres psql -c "CREATE DATABASE airflow;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE airflow TO airflow;"

Update airflow.cfg:

sql_alchemy_conn = postgresql+psycopg2://airflow:airflow@localhost:5432/airflow

For MySQL:

sql_alchemy_conn = mysql+mysqlconnector://airflow:airflow@localhost:3306/airflow

2. Re-initialize the database

# Drop the old SQLite database (backup first!)
cp airflow.db airflow.db.backup

# Initialize with the new backend
airflow db init

3. Set connection pooling for SQLite (temporary fix)

If you must use SQLite temporarily:

[sql_alchemy_conn]
sql_alchemy_conn = sqlite:////path/to/airflow.db
sql_alchemy_pool_enabled = False
sql_engine_encoding = utf-8

This disables connection pooling and reduces but does not eliminate locking.

4. Run only one Airflow Process

WRONG — running scheduler + webserver + worker on SQLite:

airflow scheduler &
airflow webserver &
airflow celery worker &

RIGHT — run sequentially for development:

# Terminal 1
airflow scheduler

# Terminal 2 (only when needed)
airflow standalone  # Bundles scheduler + webserver

5. Use sequential executor with SQLite

WRONG — using LocalExecutor or CeleryExecutor with SQLite:

executor = LocalExecutor  # Requires multiple DB connections

RIGHT — use SequentialExecutor (single-process):

executor = SequentialExecutor

SequentialExecutor runs one task at a time and avoids concurrent database access.

6. Migrate existing data

# Export existing connections/variables from SQLite
airflow connections export connections.json
airflow variables export variables.json

# Import to new database
airflow connections import connections.json
airflow variables import variables.json

Expected output: Airflow runs without database locking errors.

Prevention

• Use PostgreSQL for any multi-Process Airflow deployment.
• Never use SQLite in production — it is strictly for local testing.
• For development, use airflow standalone which handles SQLite locking better.
• Set up PostgreSQL before deploying Airflow in a team environment.
• Document the database backend in your project's README.

Common Mistakes with sqlite backend

1. Using foldl instead of foldl' causing stack overflow on large lists
2. Forgetting deriving (Show, Eq) on custom data types needed for debugging
3. Placing the wildcard pattern first in case expressions, making all subsequent patterns unreachable

These mistakes appear frequently in real-world AIRFLOW code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ