I built a browser game at cluebolt.com as a learning project. It currently uses Flask for the API, SQLite for persistence, and a lot of JavaScript for the game logic. Everything works so far, but I'm trying to plan ahead and understand when a refactor or database migration is actually justified.
Should I wait until SQLite becomes a clear bottleneck, such as from concurrent users, multiple application servers, or slow queries? Or is it better to switch to a production database like PostgreSQL early on? I'm also considering using an ORM or another abstraction layer so the application isn't tightly coupled to the SQLite file.
4 Answers
There’s also an argument for switching earlier if you already expect multiple users to write to shared data. PostgreSQL gives you stronger concurrency and more flexibility for future deployment options, extensions, and scaling. The tradeoff is more operational complexity, so I’d base the decision on expected usage and requirements rather than treating SQLite as automatically unsuitable for production.
If you want to make a later migration easier, put an ORM or data-access layer between the Flask code and the database. SQLAlchemy is a common choice and can work with SQLite during development and PostgreSQL in production. It won’t eliminate every migration issue, especially if you rely on database-specific SQL, but it can keep the rest of the application from being tied directly to the SQLite file.
SQLite can absolutely work in production, especially for a small application or when each user has an essentially separate local database. The main limitation is concurrent access to the same database file. If your application starts receiving enough simultaneous reads and writes that locking becomes a problem, that’s a strong signal to move to PostgreSQL or another client-server database.
A practical trigger is when you need multiple application servers to share one database. SQLite is a file-based database, so it doesn’t naturally provide the network access and coordination that MySQL or PostgreSQL do. Slow queries, memory pressure on the application server, backups, or growing deployment requirements are other good reasons to migrate. Until those concerns appear, keeping SQLite can be perfectly reasonable.
That makes sense. I’m going to look at using SQLAlchemy so I can keep the current setup simple while making a future PostgreSQL migration less painful.