A brief introduction to SQLite and usage scenarios

Time:2019-10-8

A brief introduction to SQLite and usage scenarios

What is SQLite?

SQLite is a lightweight embedded database. Its database is a file.

Small embedded systems, similar to mysql, are smaller and have relatively few functions. They occupy very low resources and may require only a few hundred kilograms of memory.

It is a truly open source, unlimited database, cross-platform, supporting Linux, Mac, Android, iOS and Windows, etc. It is mainly used in embedded development.

What are the characteristics of SQLite?

Advantages of SQLite

  • Source code is not subject to copyright restrictions, true freedom, open source and free.
  • No server, no single server process or operating system
  • A SQLite database is a disk file stored in a single cross-platform
  • Zero configuration, because it is a file itself, no installation or management, easy to carry
  • Without any external dependency, all the functions of operation are integrated by themselves.
  • Lightweight, SQLite itself is written in C, small size, often integrated into various applications.

Defects of SQLite

  • Lack of user management and security functions
  • It can only be embedded locally and can not be accessed by remote clients. It needs upper application to deal with these matters.
  • Not suitable for big data
  • It is suitable for single-threaded access, but not for multi-threaded high concurrent scenarios.
  • It does not support advanced features of various databases, such as management tools, analysis tools, maintenance and so on.

Application scenario of SQLite

Small website

SQLite is suitable for small and medium-sized traffic websites.

Daily visits to websites under 100,000 can be very good support, suitable for reading more and writing less operations, such as administrators add data in the background, other visitors are mostly browsing.

100,000 per day is a critical value. In fact, under the data volume of 1 million, the performance of SQLite is still possible, but it is not suitable in the upward direction.

It does not need to purchase database services alone, does not need server processes, the cost of configuration is almost zero, and the import and export of data are duplicated files, and the maintenance difficulty is almost zero. Migrating to other servers can be supported without any configuration. Moreover, the speed of reading is very fast, and the link of remote database is omitted, which can greatly improve the speed of website access.

Embedded devices

SQLite is suitable for mobile phones, PDA, set-top boxes, and other embedded devices. As an embedded database, it can also be well applied to client programs.

Because it is lightweight, compact, does not occupy much memory, data read and write performance is good, and embedded equipment data volume is not large, do not need frequent maintenance, so it is more suitable.

Database Teaching

SQLite supports most query language functions of the SQL92 (SQL2) standard.

Its configuration-free, dependency-free, compact, single file features make its installation and use very simple, very suitable for explaining SQL statements.

Students can use and operate SQLite in a very short time, not affected by system and business constraints. The results of learning can be sent to teachers by mail or cloud files for grading.

It can quickly realize a minimized application, which is suitable for students to quickly understand SQLite and SQL syntax, so as to realize the analogy bypass of database and understand the design and implementation principles of other database systems.

Local applications

Its single disk file features, and does not support remote connections, make it suitable for local applications, such as PC client software.

Commonly used applications are financial analysis tools, CAD packages, file management procedures and so on.

No remote means that it is suitable for internal or temporary databases to process some data and make the program more flexible.

Not applicable to scenarios

Obviously, it is suitable for small websites, relatively not suitable for high traffic websites, nor for large data sets, and its shortcomings are also mentioned, not suitable for high concurrent access.

Specific usage scenarios are not absolutely limited, depending on the situation, we should distinguish the needs and business scenarios, there are other similar or other types of databases in the market, referring to the use cost, performance and other factors to determine which database is the best choice.