When to use SQLite?


When should I and should not I use SQLite?

I have a project that I plan to do I have no idea how many tables are going to be, but I believe more than 15 with at least a thousand lines in some tables.

Would it be feasible to do it in SQLite?

asked by anonymous 26.11.2015 / 10:58

3 answers


SQLite uses if you have a need to have a data scenario in offline mode.

Example: You have your corporate system (15 tables with 1000 records each), but you must go to the field (disconnected environment) to use an application with data from your system.

So you create a SQLite base on the device that goes to the field (mobile, laptop, etc.) and brings to that database only data that will meet the need for this field trip. And as soon as that device returns to the corporate network, you create a synchronization logic to bring the SQLite data back to the main database.

26.11.2015 / 11:26

Before you start using a tool you should study it deeply. And after studying it will have information to decide on its own whether it is feasible for your case or not. Do not rely on random people on the internet to tell you what is good or not for you.

If you study SQLite you will certainly get on this page . It tells you where SQLite can be used. In summary:

  • Mobile and Embedded Devices
  • File Format
  • "Small" websites
  • Data Analysis
  • Caching for corporate data
  • Server-side only database
  • Data archiving
  • Substitute for operations with common files
  • Temporary or internal banks
  • Substitute for other SGDBs in test and demonstration tasks
  • Database training.

It says where no should use:

  • Client-server applications ( how to use it if you know how to do it )
  • High volume websites (there speaks 1% of sites, my experience shows it is rarer still)
  • Absurdly large data set (but it's rare to have this whole volume)
  • High competition (his biggest problem, but few have this need)

That is, SQLite is suitable for almost every case. And cases where it is not suitable, the person will know, because there the problem is so complex that someone inexperienced will not be involved. And if this happens, the wrong choice of database will be a small problem near the others it will commit.

Of course he may not please you, but there's another problem. I use SQLite whenever I can. It is not as complete in features as other banks. It is great for basic storage and relationship. In general, other database resources may look great in the beginning, but you have to learn everything and use it correctly. Often just having the basics makes the job simpler without losing important functionality. Most of the resources in more "powerful" products are just facilitators that can help or hinder. They are not there without charging a price.

Your case is extremely simple and should have no problems. But you need to look at how the data will be accessed. Will there be great competition? I doubt it, so I guess you do not have to worry about it. Will the access be by an application on the same machine? This is important. SQLite does not do well on hits being made by another machine.

It can not be reliably accessed from other machines. In practice it is rare to give a problem, but it can happen. The solution is to have an application on the machine that accesses it and allow other machines to communicate with this application (see the link above). This is why websites work well. The HTTP server and website code follow this model. Why do not people follow the same model for other types of applications? Probably because they follow cake recipes and do not think outside the box. Or maybe because they do not know how to do it.

This is the real decision whether to use it or not. The volume of data is secondary.

So study the product to understand the limitations it has on extra features, volume of (real) competition, and remote direct access. Knowing this will know where you can use it or not. The official website is very informative.

An example of using SQLite on a high scale . Some people do not use it, and they think it does not scale, which can not be used in almost all scenarios rather than more complete solutions. It just is not good with lots of writing competition and for auxiliary tools to the database. Curiously these people usually believe in NoSQL which are often worse than reading SQLite. People need to think outside the box, investigate before making claims that reach their credibility.

26.11.2015 / 12:51

SQLite can be used whenever you have to store data locally in a structured and corruption-resistant way. The SQLite code is considered to be one of the best in dealing with local files in order to minimize the chance of data loss, anyone who studies the topic is advised to study its code, so it is preferable to even use SQLite to try writing code manipulation files, even when dealing with simple data as an application configuration.

08.04.2018 / 10:35