Database:Getting started with SQLite
- 1 Meta
- 2 Full frontal - Code examples up-front!
- 3 Introduction
- 3.1 Starting SQLite3
- 3.2 Interactive mode
- 3.3 SQLite3-specific commands
- 3.4 SQL statements
- 3.5 Non-interactive mode
- 3.6 Reading commands and statements from a file
- 3.7 Giving commands as arguments
- 3.8 Sending commands via a Bash pipe
- 3.9 Selecting a database
- 3.10 Some SQLite3-specific commands
- 4 Links
Expand using link to the right to the meta section with instructor's notes.
This chapter gets the student started with using the SQLite DBMS. We recommend that you, the teacher, watch the videos and review the exercises. This chapter (and all following chapters) assumes that the student has a working environment with a bash shell and SQLite3 installed.
The lecture shows how you can access the SQLite interactive shell from the command line, as well as gives some short examples on how to also run SQL statements from the command line, e.g. using echo and pipes. Another topic of the lecture, is to show some SQLite specific instructions (those starting with a dot, like .schema, .tables etc) for investigating tables or opening a database from within the SQLite shell (.open).
The online exercises train the student in using the SQLite software both from the command line and interactively, dumping tables as a backup, restoring from backup, etc. They touch upon a simple CREATE TABLE statement, and discuss the difference between a text file with SQL statements and the binary file with a complete database.
Topics addressed in the online exercises
There are 15 tasks with instructions and suggested solutions for this topic. If you would print out the exercises on paper, that would be 10 A4 pages, to give you an idea of the multitude of the exercises. The things trained (and explained when needed) in the exercises include:
- How to create a database, giving the name as an argument to sqlite3
- How to create a simple table inside a database
- How to log out of the interactive sqlite3 shell
- Using Ctrl-D
- Using .exit
- How to enter the interactive shell
- Without argument to sqlite3 - will not select any database as the main database
- Choosing a named database as the main database using .open
- With an argument of the name of the database - will select it as the main database right away
- How to investigate what database is selected as the main database using .databases
- How to investigate what tables exist in the currently selected database using .tables
- How to investigate how a table was created using .schema
- How to create a text file with SQL commands to optionally create a table and insert a lot of rows into it
- How to run sqlite3 from the command line and have it read all the SQL statements from such a file, using redirection of standard in
- How to select all rows and columns from a table using SELECT
- How to list the SQLite3 specific commands using .help
- How to redirect output from the interactive session
- Using .output
- Using .once
- How to create a backup (as a text file with create table and all necessary insert statements) using .dump (in combination with redirecting the output to a file)
- That SQL statements like SELECT end with a semicolon
- That SQLite3-specific commands start with a dot
Expect this lecture to take around 25 minutes. As usual, we recommend watching the video lecture and looking at the online exercises (and their suggested solutions).
Topics in this lecture are:
- Reminding students to install SQLite3 (and possibly also bash)
- Starting SQLite3 from the shell with an argument of the name of a new database to be created
- Simple example of a CREATE TABLE statement
- In the interactive SQLite shell, with no database active, you can open an existing database using the .open command
- You can execute SQL from the shell using redirection or a pipe
.schema .tables .opencommands (SQLite3 specific, not part of SQL)
After the lecture, you may encourage the students to do the exercises online from the SQL SELECT chapter again, this time using the SQLite database and getting their hands dirty. After that, there are exercises also for this particular topic (see above).
- Most examples in lectures and in particular in exercises in this course material assume that the student is using the command line and the bash shell
- Point out that SQL is a standard (albeit with small dialectic differences between DBMSes) but the commands starting with a dot (.open, .schema etc) are not part of SQL but rather comes with SQLite and only works there
Full frontal - Code examples up-front!
Here's some code, so you know what to expect from this chapter!
$ sqlite3 my_books < my_books.sql $ sqlite3 my_books
SQLite version 3.11.0 2016-02-15 17:29:24 Enter ".help" for usage hints. sqlite> .tables book sqlite>
This chapter gets you started with using the SQLite DBMS. See the videos and presentation, read the pages linked to below (under External links) and move on to the exercises in the next chapter.
There are various ways to start the SQLite3 client. First of all, you need to understand that there are two modes in which the SQLite3 client can operate in. You can run it interactively, which means that you enter the program
sqlite3 and start a conversation with it, pretty much as when you are running Bash in a terminal.
Interactive programs are typically line-based, which means that you are presented with a prompt (in this case
sqlite> ) which means "OK, talk to me", that is, that the program is accepting your input.
There are two types of commands you can send to the sqlite3 client. The first types is SQLite-specific directives, all starting with a dot, sometimes accepting also arguments. Those commands don't end with a semicolon and they are specific to the SQLite database management system. With those commands you can affect various aspects of the database engine, like how results are presented to you. You can also use such commands to query the database mangement system for what databases exist, what the schemas (table definitions) exists in a database etc. Some commands also let you load commands and databases from the file system, or save a backup etc.
The other type of commands you can issue to the SQLite database, are in the form of SQL statements. SQL (structured query language) is a standardized language for querying databases for data, manipulating data in databases and even creating new databases with new tables of data. We will learn the basics of the SQL language throughout this course material.
Both sqlite-specific commands (those starting with a dot) and SQL statements work in both interactive and non-interactive mode (see below).
The other way in which to engage with the
sqlite3 program, is non-interactive. That means that you can send lines of text to it from Bash in the terminal, and get the results printed directly in the terminal. We are going to look at both modes.
In order to communicate with sqlite3 non-interactively, you must first remember that the program is line-based. That means that the program - pretty much like Bash - operates on lines of input. If you want to issue a command or an SQL statement to the database engine, you need to send it as a line of text ending with a newline.
Reading commands and statements from a file
One way to communicate with the sqlite3 program non-interactively is to redirect input from a file, using the shell's redirection operator
< (a less-than character). That instructs the shell to start the program non-interactively by connecting the standard input channel of the sqlite3 program to the lines of a file:
$ sqlite3 < file-with-commands
Giving commands as arguments
Another way to communicate non-interactively with the sqlite3 program, is to provide the commands and SQL statements as arguments to the program:
$ sqlite3 testdb "SELECT DATE('now');" 2019-01-13
In the example above, we gave two arguments to the sqlite3 command, first the name of a database,
testdb and second, the SQL statement
SELECT DATE('now'); which calls a function for dates in a way that makes the database print out the current date.
Sending commands via a Bash pipe
Yet another way to communicate with sqlite3 non-interactively, is to use the shell's pipe operator,
|. A pipe works like this: You issue a command which prints lines of text to its standard out stream. The pipe catches the output from the command and sends it as the standard input stream to the next command. Let's look at an example:
$ sqlite3 testdb "select date('now')" 2019-01-13
Note that we end SQL statements with a semicolon.
If you need to read up on redirection, pipes or even arguments to programs, please review our course materials on Bash (see the left-hand navigation on the wiki).
Selecting a database
You can start SQLite3 without an active database. You will then get an in-memory execution of SQLite3. That means that you can do everything you normally can do with SQLite3 like creating tables, adding data, manipulating data etc, but it will be done in the RAM memory which means that everything will disappear when you exit the program. Normally, you would want the data to be persistently stored in a file instead.
When you start
sqlite3 without any arguments, you start it in this in-memory mode.
You probably want to start
sqlite3 with a persistent database, stored in a file instead. In order to do that, you give one argument to
sqlite3, the name of the database file (actually the path to the file). If such a database file exists, you will use that database in your communication with SQLite3. If such a database file doesn't exist, SQLite3 will create it for you, once you create a table in the database.
A database file is a binary file whose contents only
sqlite3 can understand. It is of great importance that you understand the difference between such a database file and e.g. a file with commands or SQL statements.
A file with SQL statements must be a regular plain-text file whose contents is lines of semicolon-separated SQL statements. Such a file can be redirected to
sqlite3 or sent via a pipe to the program (see above).
A database file, on the other hand, is a file created by
sqlite3 which we cannot print or look at in an editor. The
sqlite3 program saves all databases and data in a database file, using a file format only
sqlite3 can understand. It is convenient, but not necessary, to name a database file with a file suffix of e.g.
my_first_database.db. It doesn't matter what you call your databse file, but giving it the
.db file suffix might help you remember that it is a database file, and not a text file with commands and/or SQL statements.
Some SQLite3-specific commands
If you have opened
sqlite3 in the in-memory mode, you can actually open a database from a database file, using the command
.open with an argument of the (path to) database file. If the file
my_first_database.db is in current directory, you could open it like this:
$ sqlite3 SQLite version 3.11.0 2016-02-15 17:29:24 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open my_first_database.db sqlite>
A useful command is the
.databases command. You can use it to see what databases are connected:
sqlite> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main /home/rikard/my_first_database.db sqlite>
The above tells you that you have connected to the
my_first_database.db database as the main database (the active database).
When you have opened the database you want to work with, you might have forgotten what tables exist in the database. You can then use the
sqlite> .tables colors fruits sqlite>
Once you have connected to a database, either by giving the database file as the argument to
sqlite3, or by using the
.open command, you can investigate how a table (or all tables) was defined:
sqlite> .schema colors CREATE TABLE colors(id INTEGER DEFAULT 0 NOT NULL PRIMARY KEY, name TEXT NOT NULL); sqlite>
The above queries the database engine for the definition of the table
colors of the active database. The argument to
.schema is the name of the table to investigate. If no argument is provided to
.schema the database engine shows all table definitions of the active database:
sqlite> .schema CREATE TABLE colors(id INTEGER DEFAULT 0 NOT NULL PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE fruits(id INTEGER DEFAULT 0 NOT NULL PRIMARY KEY, name TEXT NOT NULL, is_poisonous BOOLEAN);
You can use the
.help command to get a list of sqlite3-specific commands. You should also read the documentation at https://www.sqlite.org/cli.html. And of course, you should watch our video lectures linked below, as well as do our exercises on the next page!
Note that the videos are in Swedish but the presentation is in English for now. We aim to add English videos in the future ™
- Kom igång med SQLite3 (Swe)
- Kom igång med SQLite3 Live-kodning (Swe) Download my_books.sql used in the live video
- Presentation (PDF) Getting started with SQLite3 (Eng)
- Databases - Getting started with SQLite3 (Full playlist) | Getting started with sqlite - 1/4 | 2/4 | 3/4 | 4/4 | Getting started with SQLite3 (PDF)
- No files for this chapter
We expect you to read the following pages:
- sqlite.org - Command Line Shell For SQLite
- Zetcode - SQLite introduction
- Zetcode - The sqlite3 command line tool