What is SQLite
SQLite was born as a very lightweight database engine, open source and written in C, where we can save all kinds of information related to a program or an app. Its main advantage is that, unlike what happens with other database engines, SQLite works as its own and independent server, avoiding having to perform external queries in separate processes. Or what is the same, the database and the engine are included within the program and the data is consulted (or saved) from itself, eliminating the need to have other services open in the background.
SQLite makes use of SQL, a specific language for working with databases. Therefore, queries and orders can be sent to this server directly in this language. In addition, it allows you to combine these instructions with Python scripts to be able to analyze complex data.
Some of the most common uses of these databases are:
- Desktop programs and mobile applications.
- Very lightweight websites, such as static pages.
- Sites or systems that have a relatively low number of users, around 100,000.
Features and use
Among the main characteristics of this language we can highlight several of them. The first, and one of the most important, is that its most recent versions allow you to work seamlessly with databases up to 2 TB of size. In addition, its databases have most of the SQL-92 standard, so they work without problems with other programs that work with SQL statements. It also has an unusual type system, assigning each type to individual values. Another feature is that it allows several processes or threads to consult the same database without problems, which translates into a considerable performance improvement compared to other alternatives (although, yes, the writes cannot be simultaneous).
Some of the programming languages that these lightweight databases use are:
- Programs written in C / C ++.
- Software programmed in REALbasic, since SQLite is embedded within the framework.
- Programs written in Perl and Python.
- Using plug-ins, other languages are supported, such as Visual Basic 6, Delphi, Java, or .NET.
- Since version 5, PHP includes SQLite. Before, it was necessary to resort to addons.
- macOS includes the language.
- Other languages and tools, such as Bennu, AutoIt, Go (Google’s language) or BitzMax.
There are many programs that we usually use on a daily basis that have this type of database. Some of the best known are the following:
- Adobe Photoshop Elements, the simple, AI-assisted photo editor.
- Firefox, which uses SQLite to save cookies, favorites, history, etc.
- OpenOffice, using it as a Base database model.
- Various Apple applications, such as Apple Mail.
- Opera, for managing WebSQL databases.
- Skype, the popular video calling program.
- Kodi, the multimedia center.
Finally, it should be noted that, due to its small size, it is perfect to be included in programs and operating systems, such as Android, iOS, Google Chrome or WebOS, among others.
Differences with MySQL
Even though both are database management systems, there are quite a few differences between MySQL and SQLite. The first one is that MySQL is based on a client / server model, where a query is generated, and a response is obtained through the network. Also, although it is open source, it is not free like SQLite, but is owned by Oracle.
MySQL, for its part, is also more compatible with other data types and languages. For example, SQLite supports only data of the type Integer, Real, Text and Blob, while MySQL supports, among others, data such as Tinyint, Timestamp, MediumBlob, MediumText and Datetime.
SQLite can be a bit slower with smaller libraries and no functionality such as concurrent queries. Oracle databases, by supporting this functionality, do support it, and this is essential when we want to ensure the best performance.
Finally, MySQL databases are much more secure by having user authentication and control systems. A user who wants to access the data will have to specify a username and password, while in SQLite the access is direct and without restrictions.
In short, SQLite advantages are:
- It offers great stability, is multiplatform and backward compatible.
- Completely free, and open source.
- It is not installed or configured.
- Save all databases in the same file.
On the other hand, pros of its rival, MySQL, they are summarized in:
- Save and relate larger capacity databases.
- It is based on a client / server model, with more bandwidth and better performance.
- It can be run in the cloud.
How to program in SQLite
As we said, SQLite databases use the SQL language. Therefore, any SQL editor should serve us without problems to create, query and edit databases in this language. Of course, there are always some programs that are more complete or more specialized than others when it comes to performing certain tasks. For example, some of the best IDEs to work with these databases are:
- Visual Studio Code (with the SQLTools or SQLite extensions).
- DBVisualizer
- SQLite Editor
- DBeaver
- SQLiteExpert
- SQLite Studio
- SQLite Query
What we must take into account when choosing one program or another is basically the platform from which we are going to work (for example, a PC with Windows or Linux, a Mac, or a mobile phone), the support and assistance of the programs, and whether it is free and unrestricted software, or limited.
