Free Pascal

SQLite Database Tutorial

SQL stands for Structured Query Language. SQL allows working with relational databases.

Almost SQL database system has its own dialect, but a large number of SQL statements are the same for all of them.

SQLite is for embedded (non-server) databases. It reads and writes directly to disk files.

SQLite is the most widely deployed database in the world. There are billions of SQLite database files in use daily, on smartphones and gadgets and in desktop applications. A few of the better-known users of SQLite are shown below:

  • Adobe, as the application file format for their Photoshop Lightroom.
  • Apple, in many of the native applications running on Mac OS-X desktops and servers and on iOS devices such as iPhones and iPods. SQLite is also used in iTunes.
  • BMW, in its IDrive Sat Nav system.
  • Bosch, in the multimedia systems installed on GM, Nissan, and Suzuki automobiles.
  • Evernote
  • Facebook
  • Google, in their Android cell-phone operating system, and in the Chrome Web Browser.
  • The United States Library of Congress recognizes SQLite as a recommended storage format for preservation of digital content.
  • McAfee, in its antivirus programs.
  • Microsoft, as a core component of Windows 10, and in other products.
  • Mozilla, in the Firefox Web Browser and in the Thunderbird email reader.
  • PHP programming language
  • Python distributions, since Python 2.5
  • Skype

...and many more. Since SQLite is in the public domain, most developers use it in their projects without ever telling us.

A complete SQLite database with multiple tables can be contained in a single disk file.

SQLite does not have any file naming requirements. You can use any custom file suffix, for example .SQLite, .slite, .db,... 

Field types, aka "storage classes":

  • INTEGER: for integers
  • REAL: for floating point values
  • TEXT: for text
  • BLOB: for "blobs" of data, stored exactly as they were was input.
There is no separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
There is no storage class for dates and/or times. Instead, dates and times are stored as TEXT, REAL, or INTEGER values.

Information held in an SQLite database file is easily accessible using commonly available open-source tools, such as DB Browser for SQLite.

SQLite and Lazarus

You find the built-in SQLDB components of Lazarus on the SQLdb tab of the Component Palette.

The advantage of using SQLDB is that it is fairly easy to change to a different database such as Firebird or PostgreSQL without changing your program too much.

Our project: The GuitarClub

We'll show you how to set up and program a Relational Database Management System (RDBMS) for our imaginary "GuitarClub". Its database will contain tables for the club members and for their collector items,  plus some helper files.