SQLite is a lightweight library containing a database engine that lets you use a very large subset of SQL to store and retrieve data. As a user, you are most likely to be familiar with an application interface or the interface of a database management system (DBMS) that provides an interface to the database itself. Behind the scenes, your commands (whether text or graphical) are translated into SQLite syntax.

This chapter shows you the basics of creating a table, adding data to it, and retrieving that data. It’s a high-level overview to give you a taste of SQLite. The next chapter will drill down into some options and variations you can use. In this chapter, you’ll see both a graphical user interface (GUI) and the sqlite3 command-line interface that are part of SQLite. In subsequent chapters, you’ll usually see only the SQLite interface because it includes the SQLite commands. In most graphical editors for SQLite, you have an option to type in the SQLite code directly, so those commands should work for you in either environment.

Using sqlite3

sqlite3 is a command-line utility that is part of SQLite 3 (the sqlite2 version is no longer applicable). It runs on OS X (using Terminal) or as sqlite3.exe, which runs on Windows. It allows you to experiment with SQLite code directly, and it is used in this book as a touchstone for SQLite syntax. (When you use a third-party tool such as a graphical editor, you may encounter slight variations in the syntax such as whether or not a semicolon is required at the end of a statement—it is in SQLite.) You end your sqlite3 session with a .quit or .exit. Note the initial period because both are sqlite3 commands, but no semicolon at the end because they are not SQLite syntax (requiring a semicolon).

Most of the sqlite3 code in this book is shown with the prompt at the beginning of each line so that you can see which commands are multiline commands. Remember that you always need a semicolon at the end of a command—you can place it alone on the last line if you’ve forgotten to enter it before.

sqlite3 works with a temporary database that it creates for you, or alternatively, you can manage your own databases. These commands are shown here because they are sqlite3 commands and not SQLite syntax.

In the following subsections, we will review the basic sqlite3 commands that you need to use the most. You can find more information about the sqlite3 commands at www.sqlite.org/cli.html.

Run sqlite3 and Let It Create a New Database

Just type

sqlite3

When you are finished, type .exit or .quit. Here is a simple sample session.

Jesses-Mac-Pro:~ jessefeiler$ sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .exit

Create and Name a New sqlite3 Database

The command sqlite3 mydatabase will create a new database called mydatabase.

Jesses-Mac-Pro:~ jessefeiler$ sqlite3 mydatabase
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .quit

Delete the Database

By default, the database is created at the root level of the user (as long as that’s the directory set in your command-line editor). Therefore, you can delete it from the Finder if you’re on OS X. Just go to your root level (i.e., next to folders such as Desktop, Documents, Downloads, Library, Movies, Music, Pictures, Public, and Sites) and delete the database you have just created.

Run sqlite3 and Open an Existing Database

To open an existing database use the .open command. If you want to change your directory in Terminal (or whatever your command-line editor is, use your standard command (it often is cd). In Terminal on OS X, after you type cd, just drag the folder into which you want to place your files into Terminal: it will pick up the path and insert it into your code.

Jesses-Mac-Pro:~ jessefeiler$ sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open testdb
sqlite> .quit

Experimenting with SQLite Syntax

The SQLPro for SQLite editor is useful to demonstrate SQLite syntax because, like a number of other SQLite editors, you can use a higher-level interface such as a graphical user interface (GUI) to manipulate your database, but you can also see the underlying SQLite code that is generated. It is that SQLite code with which you’ll be working in the rest of this book.

This section lets you explore SQLite syntax, but remember that most of the SQLite code you’ll be creating is going to be embedded in an app that you write (or within a framework, DBMS, or library that is included in your app).

There are a number of lightweight SQL editors available on Web (just search for “SQLite editor”). Because sqlite files are cross-platform, you can use these editors to work with any sqlite file that you have (subject, of course, to security constraints implemented by the operating system). In this chapter, SQLPro for SQLite is used as an example of a simple GUI placed on top of SQLite. It is available at www.sqlitepro.com. This will show you how Table 3-1 (shown previously in Chapter 1 and repeated here) is created from a user’s point of view.

Because a graphical editor provides you with a view of what you’re doing to your database, it may be easier to use than a command-line interface where there is no guidance beyond the command-line prompt. If you prefer to start out with the command line, rest assured that in this chapter you’ll see the graphical representation of the table as you create it, but you’ll also see the SQLite syntax that is generated at each step.

SQLite uses a subset of SQL (a very large subset at that). In addition, there are some minor modifications to standard SQL syntax (and, to be quite blunt, just about every DBMS makes some minor modifications of one kind or another). Rest assured, the SQL shown in this chapter applies in almost every environment. If you download a SQL editor from the Web (i.e., a standard SQL editor and not a SQLite editor), you may actually be using syntax that differs very slightly from SQLite.

In particular, the very common MySQL DBMS has several popular editors—many of them free—but you might find a few minor differences. Most people (including me) function quite well without worrying about these distinctions: if they do crop up they’re simple to solve. What may be the most important point to remember is that SQLite syntax as it is implemented in SQLite is available at sqlite.org. SQL itself is not a standard in the way the HTML is, and that’s why you may encounter these variations.

Exploring Your sqlite3 Database with a Graphical SQLite Editor

When you first open your graphical SQLite editor, you’ll probably see an empty database (or even no database at all). As is the case with any modern app, you may be given the opportunity to reopen the last opened database or to navigate to an existing database.

In your database window, you’ll usually see a list of the items in your database (if any exist). The main items that you usually care about are

• Tables that contain data. You can retrieve the data using a query. Queries return tables—they may have no rows or columns and therefore be empty, but they are tables nonetheless.
• Views are saved queries that, like all queries, produce a table as their result. Thus, a view is not just a saved query: it also can be used much like a table (the result of the query).
• System tables are SQLite tables that are created automatically for you within each database. You may think you’re creating a new table, and you are, but, in addition, you are updating the sqlite_master, which keeps track of each table in the database.

If there are no tables yet—that is the case if you have just created a new database—you’ll typically see a list such as the one at the left of Figure 3-1. There are headers for tables and views (none are present as this is a new database). Under the System Tables header, you can see the single sqlite_master table, which is present in each SQLite database.

If you open sqlite_master, you can see the columns in that table as shown at the left of Figure 3-1. Here they are.

• type is TABLE in the case of tables. (Other values will be discussed as they are encountered.)
• name is the table name.
• tbl_name is a short table name but may be the same as name.
• rootpage is used to identify the location in the database where the table is found. You don’t often need to refer to this.
• sql is the sql code that is used to create the item. It will be updated with modifications so that it is the code to be used to create the item as it is currently configured (not as it was originally configured).

Creating a Table

Here are ways to create a table with a graphical SQLite editor or with the command-line and sqlite3.

Using a Graphical SQLite Editor

At the bottom of the list of tables shown in Figure 3-1 (and near to it in other editors), you’ll find a + that lets you add another table to the list. Figure 3-2 shows the result of clicking +.

You see a form into which you can type your table name. A single column is placed in the window. The first thing you usually do is rename both the table and the first column.

Creating Table Columns

You can use + to create additional columns. In this case, you know what the table should look like (you’ve seen it in Table 3-1), so it’s not difficult to set up the columns in Figure 3-3.

The details of the data type and size attributes should not be too difficult to set because you have pop-up menus to use. The possible values for Data Type in SQLite Pro for SQLite are

• blob
• char
• double
• float
• integer
• varchar, nvarchar
• text

These have the same meanings they have in most languages, but in SQLite, the types are relatively unimportant because SQLite is not strongly typed. It actually uses five basic storage classes.

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

These storage classes are useful in the implementation of SQLite. As noted in the documentation at http://sqlite.org/datatype3.html

This doesn’t mean that you should ignore typing. In fact, when SQLite is embedded in a framework, DBMS, or language, stricter typing may well be enforced. However, it is the framework, DBMS, or language that enforces that typing.

NULL values are represented by a special code internally that represents nonexistent—NULL. This is far preferable to using an actual value (typically 0 or -1) to represent missing data. As soon as you provide any value for missing data, you run the risk of accidentally using that missing-data value as a real data value. (Ask anyone who worked on the Year 2000 problem what the consequences of this are.) When NULL is not an acceptable value, this means that the relevant column must have some value or other. It can’t be totally empty.

In the table as shown in Figure 3-3, PK and Name cannot be NULL, but Origin can be blank.

When you have finished specifying your table name and its columns, you click Accept (or whatever your editor calls its button), and the table is created. Remember that inside sqlite_master you have a field called sql for each table. The field contains the code that generates the table (not its data). You can see that code in SQLPro for SQLite by clicking the Structure button at the top of Figure 3-4.

Using SQLite3

Here’s the syntax for sqlite3. Note that this syntax creates both the table and its columns. The ALTER TABLE command lets you come back later to modify the table’s columns.

sqlite> CREATE TABLE "SimpleTable" (
   ...>   PK integer PRIMARY KEY NOT NULL,
   ...>   Name char(128) NOT NULL,
   ...>   Origin char(128)
   ...> )WITHOUT ROWID;

Inserting Data into a Table

As in the previous section, you’ll see how to do this with a GUI as well as with the command line.

Using a Graphical User Interface

In SQLPro for SQLite, the Data button at the top of the window shown in Figure 3-5 lets you add more rows to the column. You can type in the data that you want. Figure 3-5 shows three new rows created, but the only data typed in is located in the first row. You’ll notice that the PK primary key is automatically filled in, even in the third row: SQLite has taken care of this because it is the primary key.

If you want to enter data with a query (here is how you would do it from a command-line interface), you can enter it using the Query button as shown in Figure 3-6.

Using SQLite3

Following is the sqlite3code to enter all three records:

INSERT INTO SimpleTable (PK, Name, Origin) VALUES (1, "Cecelia", "Australia");
INSERT INTO SimpleTable (PK, Name, Origin) VALUES (2, "Leif", "Iceland");
sqlite> INSERT INTO SimpleTable (PK, Name, Origin) VALUES (5, "Charlotte",
"United States");

Retrieving Data

If you want to retrieve data from the table, you use a SELECT query. Here is an example:

SELECT * FROM SimpleTable WHERE Name = "Leif"

Using a Graphical User Interface

You can type it into the Query pane as shown in Figure 3-7.

Using sqlite3

Here’s the code in sqlite3. Note how the data is formatted on return with vertical lines between the columns.

sqlite> SELECT * FROM SimpleTable WHERE Name = "Leif";
2|Leif|Iceland
sqlite>

Deleting Data

If you are playing around with data, you may wind up with data you don’t want. In particular, you may be encountering errors because the PK field must be unique so you can’t re-enter data you’ve already entered.

Here’s how to delete a row with PK value 1.

DELETE FROM SimpleTable WHERE PK = 1;

Summary

This chapter has shown you the basics of creating a table, inserting data into it, and then retrieving it. Remember that these are only the basics: in later chapters, you’ll see more features of SQLite, but, throughout, the same basic SQLite commands you’ve seen in this chapter will recur with variations and options. (As a reminder, those commands are CREATE, INSERT, and SELECT.)