About

For our classes we will be using SQLite3.

SQLite is a lightweight Database Management System (DBMS) which allows its users to implement a relational schema and run SQL queries over it, without the need for a standalone database server. This is convenient because the setup is minimal and the database can be easily bundled with application files.

SQlite is portable, which means that you can install it on any computer, even when you do not have Administration privileges. It is also quite interesting for mobile development in platforms such as Android or iOS.

SQLite implements ACID (Atomic, Consistent, Isolated, and Durable) transactions. This makes it possible to rollback all changes made during transactions in the event of a system crash or power loss.

SQLite does not, however, fully implement the SQL Standard.

Installing SQLite3

All FEUP computers have sqlite3 already installed, so you should not need to carry out this step in those machines. If you want to install it in your personal laptop for home work or study, read on.

  • Windows
    1. Go to the official website
    2. Find the ZIP file that starts with sqlite-tools-win and download it.
    3. Right-click the file and select Extract all to extract the content of the ZIP file to a folder of your choosing (the only file you need is sqlite3.exe, so you can remove the other two).
    4. Continue to the next section to see how to start SQLite.
  • macOS
    1. Install HomeBrew
    2. Open a terminal.
    3. Type brew install sqlite3 and press Enter.
    4. Continue to the next section to see how to start SQLite.
  • Linux (Debian-based distros)
    1. Open terminal.
    2. Type sudo apt-get install sqlite3 and press Enter.
    3. Continue to the next section to see how to start SQLite.

Starting SQLite

  • On a FEUP machine
    1. Press the Windows Key + R. A small window will pop up.
    2. Type powershell.exe and press Enter. A command line window will pop up.
    3. Type sqlite3 and press Enter.
    4. You will now be in the SQLite3 command line.
  • On your own computer
    • Windows
      1. Go to the folder where your sqlite3.exe is.
      2. Right-click on the empty white space and select Open in a terminal.
      3. Type .\sqlite3.exe and press Enter.
      4. You will now be in the SQLite3 command line.
    • macOS / Linux
      1. Open a terminal.
      2. Type sqlite3 and press Enter.
      3. You will now be in the SQLite3 command line.

Useful SQLite Commands

Command Description
.databases List names and files of attached databases
.dump Dump the database or a specific table in an SQL text format
.exit / .quit Exit the SQLite shell
.headers Turn display of headers on or off, when displaying output of SQL
statements
.help Show available commands
.import Import data from a file into a table
.mode Set output mode
.open Open a database from a file
.output Redirect the output to a file
.read Execute SQL statements from a file
.save Write in-memory database into a file
.schema Show the CREATE statements used for the whole database or for a specific table
.show Show current values for various settings
.show List names of tables

Creating a Database

When you simply run sqlite3, any changes will be saved in memory only. This means that if you close the window, all changes will be lost.

SQLite Transient Database

You can see the list of all commands by typing .help:

SQLite Help

  • Executing SQL Statements

    There are two main ways of running SQL in the prompt; the first is to write code directly, the other is to create a .sql file and read it in the prompt.

    Here is a piece of code to get you started:

      -- will present the results in a neat organized table, but will take more horizontal space. Default mode is 'list', which is more compact but harder to read
  .mode COLUMNS
  -- shows headers at the top of the columns when running queries
  .headers ON

  -- delete table T if it exists (good for testing purposes)
  DROP TABLE IF EXISTS T;

  -- create table T
  CREATE TABLE T (
      a TEXT,
      b TEXT
  );

  -- insert Hello World into table T
  INSERT INTO T VALUES ('Hello,', 'world!');

  -- get all rows of table T
  SELECT * FROM T;

    You should see this result:

    SQLite Hello World

  • Reading From a File Another, more powerful way is to save the sequence of commands and statements in a text file (say, helloworld.sql), thus creating a script that you can run by using .read. This works as long as you have the sql file in the same folder where you are running sqlite3:

      .read helloworld.sql

    SQLite Read Command Result

  • Keeping the Database State in a File The state of the database can be maintained persistently in a file, avoiding the need to store SQL statements that can be later read (using .read as shown above).
    In order to do that, you simply need to add an extra argument when you start SQLite. The extra argument is the name of the file where the database will be saved:

      sqlite my_database.db

    Note: the .db extension is not required, but it is a convention.

Pragma Statements

The PRAGMA statement is an SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data.

  • Syntax A PRAGMA statement is formed by the keyword PRAGMA followed by the actual “pragma” that is being defined. A pragma has a name and can take one argument. The argument may be in parentheses or separated from the pragma name by an equal sign. The two syntaxes yield identical results. In many pragmas, the argument is a Boolean and can be one of: 1/yes/true/on; 0/no/false/off.

  • PRAGMA foreign_keys This is a very important pragma, as it must be used to enable support for foreign key constraints.
    Consider the following SQL statements, which violate a foreign key constraint:

      CREATE TABLE T1 (
      id INTEGER PRIMARY KEY,
      id2 INTEGER REFERENCES T2
  );
  CREATE TABLE T2 (
      id INTEGER PRIMARY KEY
  );

  INSERT INTO T2 VALUES (111);
  INSERT INTO T2 VALUES (222);
  INSERT INTO T1 VALUES (1, 111);
  INSERT INTO T1 VALUES (2, 333);

    Since in the current version of SQLite foreign key constraint enforcement is disabled by default, loading these statements from a file (say fk.sql), does not raise any error:

    SQLite Disabled Foreign Key Result

    If we turn on foreign key constraint enforcement, we get an error detecting the constraint violation:

    SQLite Enabled Foreign Key Result

References and Tutorials