SQLite Review

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


Valuable Features:

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.

Improvements to 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.

Room for 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.

Use of Solution:

Over four years.

Previous Solutions:

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

Other Solutions Considered:

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.

Other Advice:

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.
1 Comment
Andrew JacksonReal UserTOP 20

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.

04 February 15
Guest

Sign Up with Email