Is it good idea to have SQLite database in Flask app?

3 years, 2 months ago FAQ, Python

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. When you’re gonna use SQLite as database for your Flask app, then you have to consider some things:

  1. It's slow.
  • Locks whole file for writing.
  • No caching mechanism of it's own.
  1. It’s limited.
  • Database size restricted to 2GB in most cases.
  • Not fully SQL92 compliant.
  • Not very scalable.

But you can overcome these issues. How?

For Websites

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Each dynamic page does roughly 200 SQL statements. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.

For Server-side database

Systems designers report success using SQLite as a data store on server applications running in the datacenter, or in other words, using SQLite as the underlying storage engine for an application-specific database server.

With this pattern, the overall system is still client/server: clients send requests to the server and get back replies over the network. But instead of sending generic SQL and getting back raw table content, the client requests and server responses are high-level and application-specific. The server translates requests into multiple SQL queries, gathers the results, does post-processing, filtering, and analysis, then constructs a high-level reply containing only the essential information.

Developers report that SQLite is often faster than a client/server SQL database engine in this scenario. Database requests are serialized by the server, so concurrency is not an issue. Concurrency is also improved by "database sharding": using separate database files for different subdomains. For example, the server might have a separate SQLite database for each user, so that the server can handle hundreds or thousands of simultaneous connections, but each SQLite database is only used by one connection.

Making Decision

SO we can see that we can use SQLite as database for Flask app. But only for small app where database is seldom needed. If you are gonna develop a medium app, then you should use MySQL/MariaDB. For large app as Bill Karwin said, you can use a client/server database like PostgreSQL allows many concurrent clients.

But surely, you can use SQLite as database for development purpose. Then you can move to any database you feel suitable. Just use Flask-SQLAlchemy to connect to database. Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application. It requires SQLAlchemy 0.6 or higher. It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks.

You can use any engine with Flask-SQLAlchemy. So chill bro!

Previous Next
Comments
Have you read the comment guideline?

Give me useful, constructive criticism. Spot a typo or an error? Let me know and I will correct it.