DocsLite for SQLite

You can install SQLite using your package manager or download it from the SQLite website (https://www.sqlite.org/).

You open a SQLite database by opening a command window and passing the database name as a parameter, for example, sqlite3 mydatabase.sqlite. If the database does not exist, a database named mydatabase.sqlite is created in the current directory. Running this command opens the SQLite shell and shows the prompt, sqlite>.

The SQLite shell is used for executing SQL statements but there are also "dot commands" that simplify a number of operations. A list of commands used in this article follows:

If you find that you constantly use specific commands you can specify them in a .sqliterc configuration file stored in your home directory. For example, if you regularly import and export comma-separated values (CSV) files, create a configuration file with the contents:

.mode csv

This mode uses a comma separator when you issue SELECT statements and expects CSV files with the .import command.

SQLite makes it very easy to create a table and populate it with data. Suppose you have the following CSV file where the first row contains the field names:

type,url,username,password,notes
online,http://www.example.com,myusername,mypassword,comments about this record
local,,username,password,login for laptop
...

If this file is named file.csv, you can create a table and import your data by opening SQLite and entering the following dot commands:

sqlite> .mode csv
sqlite> .import file.csv tblpasswords

Importing this file loads your data and creates a bare-bones table defined in the following way:

CREATE TABLE tblpasswords(
  "type" TEXT,
  "url" TEXT,
  "username" TEXT,
  "password" TEXT,
  "notes" TEXT
);

Such a table may be perfectly suitable for your purposes but if you need something a bit more sophisticated you can explicitly create a table by using a SQL statement such as the following:

CREATE TABLE tblpasswords(
  type TEXT DEFAULT "online",
  url TEXT NOT NULL,
  username TEXT DEFAULT "username@email.com",
  password TEXT,
  notes TEXT,
  PRIMARY KEY (url),
  CHECK(type = "local" OR type = "online" OR type = "other")
);

This creates a table with default values for the type and username fields, a primary key and constraints on the values that can be assigned to the type field. You can import data into this table using the exact same dot commands as shown above. Just make sure that the first row doesn't contain the field names.

If you know exactly what you want then create a table with constraints but otherwise create a bare-bones table. It's easy enough to alter your table after the fact; doing this is described in Section 8, “Restructuring your table”.

If you have comma-separated or tab-separated data as shown in Section 2, “Creating a table” you can also insert additional data using the dot command, .import. Be sure to set the appropriate mode first and make sure that your data file doesn't include headers. This can be especially useful if you are importing data from a spreadsheet.

To insert a record using SQL, execute the following statement:

INSERT INTO
  tblpasswords 
  VALUES(
    'online',
    'http://example.com',
    'myusername',
    'password',
    'some notes'
  );

If you want to take advantage of default values for fields you must use the following syntax:

INSERT INTO 
  tblpasswords (
    url, 
    password 
  ) 
  VALUES(
    'http://example.com',
    'password'
  );

If you created a table with the defaults shown in Section 2, “Creating a table”, the type field will default to online and the username field will be username@email.com. The notes field is not specified and has no default value so will be NULL. Note that when you load data using the .import command you cannot take advantage of default values.

The simplest way to view your data is to use a SELECT statement. You can do this from the SQLite prompt or in batch mode but, without too much effort, you can view your data as a web page by using the -html option. The following script (for Unix-like operating systems) creates an HTML page of all your records:

#!/bin/bash
echo "<html><body><h1>Passwords</h1><table>" > /path/to/passwords.html
sqlite3 -html /path/to/passwords.sqlite \
  "SELECT * FROM tblpasswords ORDER BY url;" >> /path/to/passwords.html
echo "</table></body></html>" >> /path/to/passwords.html

To create a script for Windows save the following text file:

echo ^<html^>^<body^>^<h1^>Passwords^</h1^>^<table^> > C:\path\passwords.html
sqlite3 -html C:\path\passwords.sqlite ^
  "SELECT * FROM tblpasswords ORDER BY url;" >> C:\path\passwords.html
echo ^</table^>^</body^>^</html^> >> C:\path\passwords.html

Select specific fields or add to the ORDER BY clause to change how your data is displayed. You can also add a command to open the output file in your favorite browser.

To look up a specific record use a LIKE clause with the wild card character '%':

sqlite3 passwords.sqlite "SELECT * FROM tblpasswords WHERE url LIKE '%amazon%';"

Since this is a command that you will often repeat, you can create a script for Linux or Mac OS X by saving the following:

#!/bin/bash
sqlite3 -line passwords.sqlite \
"SELECT * FROM tblpasswords WHERE url LIKE '%${1}%';"

This script file uses the -line option to output each field on a separate line, improving readability. The use of the SQLite wild card character, '%', means that you can look for a partial rather than a complete word. You can also search additional fields by adding an OR clause such as OR notes LIKE '%${1}%';. The parameter ${1} is passed from the command line by invoking this script as follows: scriptname.sh amazon, where 'amazon' is the value you are looking for in the url field.

The equivalent script for Windows is:

sqlite3 -line passwords.sqlite ^
"SELECT * FROM tblpasswords WHERE url LIKE '%%%1%%';"

Creating a Windows batch file is a bit messier because the '%' character identifies a parameter passed to a batch file and must be escaped (by doubling it) when used as a wild card.

You should always have a backup of your database and with SQLite you can simply use the operating system to make a copy.

If you'd rather have the SQL needed to reconstruct your tables and data you can go to the command line and issue the command:

sqlite3 password.sqlite .dump > outfile.sql

This command dumps the contents of your database to a file named outfile.sql. This file includes INSERT statements needed to recreate all your data and the CREATE statements for all database objects.

Use the output file, outfile.sql, to create a new database as follows:

sqlite3 newpassword.sqlite ".read outfile.sql"

If newpassword.sqlite doesn't already exist, this command will create it.

There are no dot commands for updating records; you must use a SQL statement. For example, you can update a password in the following way:

UPDATE
  tblpasswords 
  SET
    password = 'newpassword'
  WHERE 
    url = 'http://www.amazon.com';

You don't always know the exact structure of a table right from the start so you may have to make alterations. SQLite's ALTER TABLE syntax is limited; you can rename tables and you can only add columns following the last column.

Nevertheless, the dot commands make an iterative approach to table design relatively simple. Follow these steps to change your table design:

Peter Lavin is a technical writer who has been published in a number of print and online magazines. He is the author of "Object Oriented PHP", published by No Starch Press, "DocBook for Writers", published by softcoded.com and a contributor to "PHP Hacks" by O'Reilly Media. His latest book, "HTML to MadCap Flare with Dynamic Content", will be published shortly by XML Press.