SQLite Path Woes: When Environment Variables Fail in Production

SQLite Across Platforms: When Environment Variables Aren’t Enough

The ai-agents-bot-social-publisher project was days away from its first production deployment. Eight n8n workflows designed to harvest posts from social networks and distribute them by category had sailed through local testing. Then came the moment of truth: pushing everything to a Linux server.

The logs erupted with a single, merciless error: no such table: users. Every SQLite node in every workflow was desperately searching for a database at C:\projects\ai-agents\admin-agent\database\admin_agent.db. A Windows path. On a Linux server, naturally, it didn’t exist.

The first instinct was elegant. Why not leverage n8n’s expression system to handle the complexity? Add DATABASE_PATH=/data/admin_agent.db to the docker-compose.yml, reference it with $env.DATABASE_PATH in the SQLite node configuration, and let the runtime magic take care of the rest. The team deployed with confidence. The workflows crashed with the same error. After investigating n8n v2.4.5’s task runner behavior, the truth emerged: environment variables simply weren’t being passed to the SQLite execution context as advertised in the documentation. The expression lived in the configuration file, but the actual runtime ignored it completely.

This was the moment to abandon elegance for reliability.

Instead of trusting runtime variable resolution, the team built deploy-time path replacement. A custom script in deploy/deploy-n8n.js intercepts each workflow’s JSON before uploading it to the server. It finds every reference to the environment variable expression and replaces it with the absolute production path: /var/lib/n8n/data/admin_agent.db. No runtime magic. No assumptions. No surprises. Just a straightforward string replacement that guarantees correct paths on deployment.

But n8n had another quirk waiting. The system maintains workflows in two states: a stored version living in the database, and an active version loaded into memory and actually executing. When you update a workflow through the API, only the stored version changes. The active version can remain frozen with old parameters—intentionally, to avoid interrupting in-flight executions. This created a dangerous sync gap between what the code said and what actually ran. The solution was mechanical: explicitly deactivate and reactivate each workflow after deployment.

The team also formalized database initialization. Instead of recreating SQLite from scratch on every deployment, they introduced migration scripts (schema.sql, seed_questions.sql) executed before workflow activation. It seemed like unnecessary complexity at first, but it solved a real problem: adding a phone column to the users table later just meant adding a new migration file, not rebuilding the entire database.

Now deployment is a single command: node deploy/deploy-n8n.js --env .env.deploy. Workflows instantiate with correct paths. The database initializes properly. Everything works.

The lesson: never rely on relative paths inside Docker containers or on runtime expressions for critical configuration values. Know exactly where your application will live in production, and bake those paths in during deployment, not at runtime.

“Well, SQLite,” I asked the logs, “have you found your database yet?” SQLite answered with blessed silence. 😄