SQLite Review

SQLite does not have spatial extensions but a project called SpatiaLite takes care of this.


What is most valuable?

Whenever I need a single file database that works server-less, cross-platform and has acid properties, I use SQLite. It also supports triggers which can be used for many things like handling data integrity. One more important point is that there is a Firefox extension called SQLite Manager which is a free client that helps you to do almost anything that you expect from client software.

How has it helped my organization?

I mostly used it at university in a Database Systems class where students are first introduced to SQL. They need something easy to use in their projects, so SQLite is the perfect candidate. As SQLite truly is cross platform some students were able to build Android applications for their final projects.  

We can share sample databases as files so no one has to populate their own. People are no longer needed to setup a server which saves time. As it is server-less you can just pop-up a browser window and start experimenting on your SQLite DB, or create one without installing any drivers whatsoever.

What needs improvement?

SQLite does not have user defined functions like other database management systems do, and you have to write C code, which is a complication that you may not get into for a user defined function.

For how long have I used the solution?

Over four years.

Which solution did I use previously and why did I switch?

I previously used the H2 database and I changed because I had problems while connecting with the PHP server.

Which other solutions did I evaluate?

I also looked at using an embedded MySQL DB but it was not single file and there are problems using it on a PHP server as it needs some effort to run on a Windows machine.

What other advice do I have?

In my experience, I have never used SQLite in a large project, but I used it teach and create proof of concept applications, and it has served me well. For one case, I needed spatial extensions which SQLite does not have but someone else created a project which extends SQLite called SpatiaLite.

**Disclosure: I am a real user, and this review is based on my own experience and opinions.
Add a Comment
Guest
1 Comment

author avatarSQL DBA at a software R&D company with 51-200 employees
Vendor

SQLite database is based on relational database management system. It supports .db file to store its database and it is easy to use as well. As it supports RDBMS so there are many things which user can implement objects in tabular form and get back data easily on their front end. SQLite database also supports file locking and concurrency mechanism which makes its database consistent. SpatiaLite is not based on client-server architecture. Its simply a ordinary file in which database engine is build in its application.