Nov 1, 2018

SQLite

SQLite is an amazing library that gets embedded inside the application that makes use of. As a self-contained, file-based database, SQLite offers an amazing set of tools to handle all sorts of data with much less constraint and ease compared to hosted, process based (server) relational databases.

When an application uses SQLite, the integration works with functional and direct calls made to a file holding the data (i.e. SQLite database) instead of communicating through an interface of sorts (i.e. ports, sockets). This makes SQLite extremely fast and efficient, and also powerful thanks to the library's underlying technology.

Advantages of SQLite

File based:
The entire database consists of a single file on the disk, which makes it extremely portable.

Standards-aware:
Although it might appear like a "simple" DB implementation, SQLite uses SQL. It has some features omitted (RIGHT OUTER JOIN or FOR EACH STATEMENT), however, some additional ones are baked in.

Great for developing and even testing:
During the development phase of most applications, for a majority of people it is extremely likely to need a solution that can scale for concurrency. SQLite, with its rich feature base, can offer more than what is needed for development with the simplicity of working with a single file and a linked C based library.

Disadvantages of SQLite

No user management:
Advanced databases come with the support for users, i.e. managed connections with set access privileges to the database and tables. Given the purpose and nature of SQLite (no higher-levels of multi-client concurrency), this feature does not exist.

Lack of possibility to tinker with for additional performance:
Again by design, SQLite is not possible to tinker with to obtain a great deal of additional performance. The library is simple to tune and simple to use. Since it is not complicated, it is technically not possible to make it more performant than it already, amazingly is.

When To Use SQLite
Embedded applications:
All applications that need portability, that do not require expansion, e.g. single-user local applications, mobile applications or games.

Disk access replacement:
In many cases, applications that need to read/write files to disk directly can benefit from switching to SQLite for additional functionality and simplicity that comes from using the Structured Query Language (SQL).

Testing:
It is an overkill for a large portion of applications to use an additional process for testing the business-logic (i.e. the application's main purpose: functionality).

When Not To Use SQLite
Multi-user applications:
If you are working on an application whereby multiple clients need to access and use the same database, a fully-featured RDBM (e.g. MySQL) is probably better to choose over SQLite.

Applications requiring high write volumes:
One of the limitations of SQLite is the write operations. This DBMS allows only one single write operating to take place at any given time, hence allowing a limited throughput.