SQLite's Windows Path Problem in Production: An n8n Deploy Story

Deploying SQLite to Production: When Environment Variables Become Your Enemy

The ai-agents-admin-agent project had eight n8n workflows ready for their first production deployment to a Linux server. Everything looked perfectly aligned until the logs came pouring in: no such table: users. Every workflow crashed with the same frustration. The culprit? All the SQLite nodes were stubbornly pointing to C:\projects\ai-agents\admin-agent\database\admin_agent.db—a Windows path that simply didn’t exist on the server.

The instinct was to reach for elegance. Why not use n8n’s expression system? Store the database path as an environment variable $env.DATABASE_PATH, reference it in each SQLite node, and let the runtime handle the resolution. The team added the variable to docker-compose.yml for local development, deployed with confidence, and waited for success. It didn’t come. The workflows still tried to access that Windows path. After digging through n8n v2.4.5’s task runner behavior, the truth emerged: environment variables weren’t being passed to the SQLite node execution context the way the documentation suggested. The expression was stored in the configuration, but the actual runtime simply ignored it.

This was the moment to abandon elegant solutions in favor of something brutally practical. The team implemented deploy-time path replacement. Instead of trusting runtime resolution, a custom deployment script in deploy/deploy-n8n.js intercepts the workflow JSON before uploading it to the server. It finds every instance of the environment variable expression and replaces it with /var/lib/n8n/data/admin_agent.db—the actual absolute path where the database would live in production. Pure string manipulation, zero guesswork, guaranteed to work.

But production had another surprise waiting. The team discovered that n8n stores workflows in two distinct states: stored (persisted in the database) and active (loaded into memory). Updating a workflow through the API only touches the stored version. The active workflow keeps running with its old parameters. The deployment process had to explicitly deactivate and reactivate each workflow after modification to force n8n to reload from the updated stored version.

Then came database initialization. The deployment script SSH’d to the server, copied migration files (schema.sql, seed_questions.sql), and executed them through the n8n API before activating the workflows. This approach meant future schema changes—adding a phone column to the users table, for instance—required only a new migration file, not a complete database rebuild.

The final deployment workflow became elegantly simple: node deploy/deploy-n8n.js --env .env.deploy. Workflows materialized with correct paths, the database initialized properly, and everything worked.

Here’s the lesson: don’t rely on relative paths in Docker containers or on runtime expressions in critical parameters. Know exactly where your application will live, and substitute the correct path during deployment. It’s unglamorous, but predictable.

GitHub is the only technology where “it works on my machine” counts as adequate documentation. 😄