Home » Core Java » sql » SQLite Tutorial

About Mohammad Meraj Zia

Senior Java Developer

SQLite Tutorial

1. Introduction

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The is free and you don’t need to buy any license to use it. It is one of the most commonly used DB. Unlike most SQL databases, it does not have a separate server process. It reads and writes directly to disk files. All the information which you need (schema, tables, indices, views, triggers, etc)is stored in a single file. The same fine can be used in a 32-bit or a 64-bit system because the file format is compatible with both. SQLite database files are a recommended storage format by the US Library of Congress.

SQLite is a compact library. With all features enabled, the library size can be less than 600KiB, depending on the target platform and compiler optimization settings. There is a tradeoff between memory usage and speed. SQLite responds gracefully to memory allocation failures and disk I/O errors. Transactions are ACID even if interrupted by system crashes or power failures.

2. Uses

You should not compare SQLite with traditional client/server SQL databases like MySQL, Oracle, PostgreSQL. Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

2.1 Embedded devices and IOT

SQLite database requires very little administration as compared to the traditional client/server SQL databases. That is why it is a good fit for use in cellphones, set-top boxes, televisions, game consoles, cameras, watches, kitchen appliances, thermostats, automobiles, machine tools, airplanes, remote sensors, drones, medical devices, robots etc.

2.2 Websites

SQLite works best for the website which has low traffic. The amount of web traffic the SQLite can handle depends on how much the website uses its database.

2.3 Data analysis

Various tools can be used to extract data from the CSV file. This data then can be analyzed using different languages – e.g. Python, R, etc. This can also be done in a traditional SQL database but SQLite is easier to install and use and it’s just one file that we will be processing for the analysis.

2.4 Data transfer

Because an SQLite database is a single compact file in a well-defined cross-platform format, it is often used as a container for transferring content from one system to another. The sender gathers content into an SQLite database file, transfers that one file to the receiver, then the receiver uses SQL to extract the content as needed.

2.5 Replacement for ad hoc disk files

Many programs use fopen(), fread(), and fwrite() to create and manage files of data in home-grown formats. SQLite works particularly well as a replacement for these ad hoc data files. Contrary to intuition, SQLite can be faster than the filesystem for reading and writing content to disk.

2.6 Education

Because of the simplicity of the SQLite database, it is a good candidate to use for education purposes. Students can create as many databases as they like and can send the DB file to the tutor for grading purposes.

3. Traditional SQL databases

In this section, we will discuss the cases where it is better to use the traditional client/server databases rather than SQLite.

3.1 Client server application

In a scenario where we have multiple clients calling the servers which require database access, it is better to use the traditional client/server SQL database. SQLite will work but if the load becomes more the performance will reduce drastically. Another issue is that SQLite uses one file – so if multiple threads/processes try to access this file we could see locking issues.

3.2 High volume websites

SQLite will not be the best option for websites that handle lots of transactions.

3.3 Lage datasets

An SQLite database is limited in size to 281 terabytes. Even if it could handle larger databases, SQLite stores the entire database in a single disk file, and many filesystems limit the maximum size of files to something less than this. So if you think that your data size could be more than this you should consider using the traditional client/server SQL database.

3.4 High concurrency

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. So if you are building an application that requires lots of writes in a quick interval of time then it is better to use the traditional client/server SQL database.

4. SQLite vs traditional client/server database

In this section, we will see the difference between SQLite and a traditional client/server database – e.g. MySQL.

4.1 Configuration

A traditional client/server database requires some setup and configuration before you can start using it. SQLite doesn’t need to be installed before it can be used. There is no set-up process for it. You don’t need to start/stop the server as you do in the case of a traditional database. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after a system crash or power failure. There is nothing to troubleshoot.

4.2 Client/Server

Most SQL database engines are implemented as a separate server process. Programs that want to access the database communicate with the server using some kind of interprocess communication (typically TCP/IP) to send requests to the server and to receive back results. SQLite does not work this way. With SQLite, the process that wants to access the database reads and writes directly from the database files on disk. There is no intermediary server process.

There are advantages and disadvantages to being serverless. The main advantage is that there is no separate server process to install, set up, configure, initialize, manage, and troubleshoot. This is one reason why SQLite is a “zero-configuration” database engine. Programs that use SQLite require no administrative support for setting up the database engine before they are run. Any program that is able to access the disk is able to use an SQLite database.

On the other hand, a database engine that uses a server can provide better protection from bugs in the client application – stray pointers in a client cannot corrupt memory on the server. And because a server is a single persistent process, it is able to control database access with more precision, allowing for finer grain locking and better concurrency.

4.3 Database file

An SQLite database is a single ordinary disk file that can be located anywhere in the directory hierarchy. If SQLite can read the disk file then it can read anything in the database. If the disk file and its directory are writable, then SQLite can change anything in the database. Database files can easily be copied onto a USB memory stick or emailed for sharing.

Other SQL database engines tend to store data as a large collection of files. Often these files are in a standard location that only the database engine itself can access. This makes the data more secure, but also makes it harder to access. Some SQL database engines provide the option of writing directly to disk and bypassing the filesystem altogether. This provides added performance, but at the cost of considerable setup and maintenance complexity.

5. Language

In this section, we will see how SQLite language differs from the traditional client/server databases.

5.1. Flexibility

SQLite is very flexible with regard to data types. SQLite is very forgiving of the type of data that you put into the database. For example, if a column has a datatype of INTEGER and the application inserts a text string into that column, SQLite will first try to convert the text string into an integer, just like every other SQL database engine. Thus, if one inserts ‘1234’ into an INTEGER column, that value is converted into an integer 1234 and stored. But, if you insert a non-numeric string like ‘wxyz’ into an INTEGER column, unlike other SQL databases, SQLite does not throw an error. Instead, SQLite stores the actual string value in the column.

5.2 Booleans

Unlike most other SQL implementations, SQLite does not have a separate BOOLEAN data type. Instead, TRUE and FALSE are (normally) represented as integers 1 and 0, respectively.

5.3 DATETIME

SQLite has no DATETIME datatype. Instead, dates and times can be stored in any of these ways:

  • As a TEXT string in the ISO-8601 format. Example: ‘2018-04-02 12:13:46‘.
  • As an INTEGER number of seconds since 1970 (also known as “unix time”).
  • As a REAL value that is the fractional Julian day number.

5.4 Datatype is optional

Because SQLite is flexible and forgiving with regard to data types, table columns can be created that have no specified datatype at all

6. Summary

In this article, we discussed the SQLite database. We discussed its use and how it is different from traditional client/server SQL databases. We looked at the scenarios where it will be good to use SQLite and also those where it will not perform better. In the end we looked at some of the language differences.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments