Choosing the Right Database: When SQLite is the Better Option

When building data-driven applications, there is a common assumption that production systems require a database such as Oracle, PostgreSQL or MySQL. This is often sound advice, particularly for multi-user systems or applications with complex operational requirements.

However, it is not universally true.

In one application developed for a professional golfer, the requirement was to capture and analyse shot data recorded after each round. The data could only be entered by the player, as they are the only person with sufficient knowledge of each shot for the information to be meaningful. The usage model was therefore simple: a single user entering data over time, with no concurrent access and no requirement for multi-user interaction.

Given this, the choice of database was not driven by scale or concurrency, but by correctness, simplicity, and reliability.

SQLite was selected deliberately.

A key consideration was transactional integrity. As an experienced DBA, it is essential that the system behaves predictably in the event of failure. SQLite provides full ACID compliance, ensuring that incomplete transactions are not left in an inconsistent state. This was verified against the official documentation and confirmed to meet the requirements of the system.

Performance was not a concern in this context. For a single-user application with modest data volumes, SQLite is extremely fast. It supports standard SQL and integrates cleanly with frameworks such as Django via its ORM, allowing the application to be developed without compromise.

Operational simplicity was another important factor. The entire database is contained within a single file. This makes deployment and maintenance straightforward, and allows the production dataset to be copied and inspected easily when required. While this is primarily a convenience from a development perspective, it reflects a broader principle: fewer moving parts reduce operational overhead.

It is common to see guidance suggesting that SQLite should only be used for development, with a more “serious” database required for production. There is some truth in this, particularly for applications with concurrent users, scaling requirements, or complex infrastructure.

However, this example illustrates that the correct choice depends on the problem being solved.

For a single-user, data-entry-driven application, SQLite provides:

• reliable transactional behaviour
  
• sufficient performance
  
• minimal operational complexity
  

without introducing unnecessary infrastructure.

The broader point is not about SQLite itself, but about selecting technology based on actual requirements. In this case, introducing PostgreSQL or another server-based database would have added complexity without delivering any meaningful benefit.

Choosing the simplest solution that meets the requirement is often the most effective approach.