SQLite is provided by Apple with the current version of OS X; I don't know when
it was first included with OS X, but I know it has been present
from at least OS X 10.8.5
(Mountain Lion). If you open the
Terminal
application, which you can find in /Applications/Utilities,
and type which sqlite3, you should see the program.
$ which sqlite3 /usr/bin/sqlite3
If it isn't present on your Mac OS X system, download the precompiled binaries for Mac OS X (x86) from the SQLite Download Page. Currently the download file is a 1.06 MB .zip file. From the Apple OS X Finder, double-click on the downloaded ZIP file to extract its contents. You will then see 3 executable files in the folder in which the files have been extracted.
$ ls -lo ~/Downloads/sqlite-tools-osx-x86-3110100 total 4112 -rwxr-xr-x@ 1 jasmith1 580176 Mar 3 22:44 sqldiff -rwxr-xr-x@ 1 jasmith1 892296 Mar 3 22:47 sqlite3 -rwxr-xr-x@ 1 jasmith1 627324 Mar 3 22:45 sqlite3_analyzer $
You can obtain help information for the command by typing
sqlite3 -help at the Terminal
shell
prompt.
$ sqlite3 -help Usage: sqlite3 [OPTIONS] FILENAME [SQL] FILENAME is the name of an SQLite database. A new database is created if the file does not previously exist. OPTIONS include: -bail stop after hitting an error -batch force batch I/O -column set output mode to 'column' -cmd COMMAND run "COMMAND" before reading stdin -csv set output mode to 'csv' -echo print commands before execution -init FILENAME read/process named file -[no]header turn headers on or off -help show this message -html set output mode to HTML -interactive force interactive I/O -line set output mode to 'line' -list set output mode to 'list' -mmap N default mmap size set to N -nullvalue TEXT set text string for NULL values. Default '' -separator SEP set output field separator. Default: '|' -stats print memory stats before each finalize -version show SQLite version -vfs NAME use NAME as the default VFS $
You can obtain an sqlite prompt where you can issue
SQL commands by typing sqlite3. You can get help information
by typing .help at that prompt.
$ sqlite3
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
If TABLE specified, only dump tables matching
LIKE pattern TABLE.
.echo on|off Turn command echo on or off
.exit Exit this program
.explain ?on|off? Turn output mode suitable for EXPLAIN on or off.
With no args, it turns EXPLAIN on.
.headers on|off Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices ?TABLE? Show names of all indices
If TABLE specified, only show indices for tables
matching LIKE pattern TABLE.
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Use STRING in place of NULL values
.once FILENAME Output for the next SQL command only to FILENAME
.open ?FILENAME? Close existing database and reopen FILENAME
.output ?FILENAME? Send output to FILENAME or stdout
.print STRING... Print literal STRING
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILENAME Execute SQL in FILENAME
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.save FILE Write in-memory database into FILE
.schema ?TABLE? Show the CREATE statements
If TABLE specified, only show tables matching
LIKE pattern TABLE.
.separator STRING Change separator used by output mode and .import
.shell CMD ARGS... Run CMD ARGS... in a system shell
.show Show the current values for various settings
.stats on|off Turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables
If TABLE specified, only list tables matching
LIKE pattern TABLE.
.timeout MS Try opening locked tables for MS milliseconds
.timer on|off Turn SQL timer on or off
.trace FILE|off Output each SQL statement as it is run
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set column widths for "column" mode
Negative values right-justify
sqlite>You can see the current settings for SQLite by issuing the .show
command.
sqlite> .show
echo: off
eqp: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
stats: off
width:
sqlite>You can exit from the SQLite program by typing .exit
at the sqlite> prompt or by hitting Ctrl-D.
You can create a new database from the command line by entering
the command sqlite3 DatabaseName.db, where
DatabaseName.db is the name of the database you wish to create.
When creating a table within a database, you need to decide what data type
you will use for each column in the table - see
Datatypes In SQLite Version
3.
E.g., I needed to track a list of people authorized to request work be done for various projects. The data I need to track consists of the following:
You can create a new table with the CREATE TABLE table_name
command.
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype );
SQLite supports the following data types:
If you don't specify a datatype for a column, then SQLite assumes the "type affinity" is BLOB. If the delcared type of a column contains any of the strings "CHAR", "CLOB", or "TEXT", then that column has TEXT affinity. Since VARCHAR contains the string "CHAR", it is assigned the TEXT affinity1.
For each column, if I want to ensure that a value is always present for
the column, I can add "NOT NULL" to the column specification, e.g.,
Last_Name text NOT NULL. In SQL, a
NULL value
in a
column means that a
record, i.e. a row in the database, does not have any value stored in that
field. By using NOT NULL, I have ensured that SQLite won't
allow a record to be created with NULL for that column.
I can also specify that I want a column or columns to be a
primary key by adding that indicator to the column specification, e.g.,
UID text PRIMARY KEY NOT NULL. E.g., I can create a Work Requests
(WRS) dtabase with a table containing information on requesters as
shown below. I want all of the fields to be mandatory, except for
the note field. I can see what tables exist in the database with
the SQLite command .tables and I can see the layout
for the table I've created with the .schema command.
$ sqlite3 WRS.db SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints. sqlite> CREATE TABLE Requesters( ...> Last_Name text NOT NULL, ...> First_Name text NOT NULL, ...> UID text PRIMARY KEY NOT NULL, ...> Phone text NOT NULL, ...> Email text NOT NULL, ...> Note text ...> ); sqlite> .tables Requesters sqlite> .schema CREATE TABLE Requesters( Last_Name text NOT NULL, First_Name text NOT NULL, UID text PRIMARY KEY NOT NULL, Phone text NOT NULL, Email text NOT NULL, Note text );
As you enter the parts of a statement you can hit enter when you near the
end of a line. SQLite will then display a ...> prompt where
you can continue typing the rest of the statement, which you will terminate
with a semicolon (;), or you can just continue typing a very long
line and what you type will keep wrapping to the next line.
In this case, I also need to track the projects for which a requester is authorized to request work be done. An individual is often authorized to request work be done for multiple projects. Since I don't know beforehand how many projects a person may have to deal with and, since that number can grow over time, I will create another table which contains UIDs and projects for those UIDs.
sqlite> CREATE TABLE UID_Projects( ...> UID text PRIMARY KEY NOT NULL, ...> Project text NOT NULL ...> ); sqlite>
Now, if I issue the .tables command I can see I have two
tables in the database. If I issue the .schema command, I will
see the layout for both tables displayed. If I want to see just the layout
for a particular table, I can specify it with .schema table_name
where table_name is the name of a particular table.
sqlite> .tables UID_Projects Requesters sqlite> .schema UID_Projects CREATE TABLE UID_Projects( UID text PRIMARY KEY NOT NULL, Project text NOT NULL ); sqlite> .schema Requesters CREATE TABLE Requesters( Last_Name text NOT NULL, First_Name text NOT NULL, UID text PRIMARY KEY NOT NULL, Phone text NOT NULL, Email text NOT NULL, Note text ); sqlite> .schema CREATE TABLE Requesters( Last_Name text NOT NULL, First_Name text NOT NULL, UID text PRIMARY KEY NOT NULL, Phone text NOT NULL, Email text NOT NULL, Note text ); CREATE TABLE UID_Projects( UID text PRIMARY KEY NOT NULL, Project text NOT NULL );
When you want to exit from the SQLite program, you can hit the Ctrl-D keys simultaneously.
What do you do if, afer you've created a table, you want to add additional
columns? You can use the
ALTER
TABLE command in such instances. The format of a command to add a
column is ALTER TABLE table_name ADD COLUMN column_def
or you can use ALTER TABLE database_name.table_name
ADD COLUMN column_def.
The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:
- The column may not have a PRIMARY KEY or UNIQUE constraint.
- The column may not have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses.
- If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
- If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.
Note: After ADD COLUMN has been run on a database, that
database will not be readable by SQLite version 3.1.3 and earlier, if you
transfer the database to another system.
E.g., for the Requesters table I created above, I'd like to add three additional fields that will contain date values: the date when a person was authorized to submit requests, the date the last update was made for the person, e.g., when he/she was given aurhorization to submit requests for another existing project or new project, and a removal date, if the person is no longer authorized to submit any requests, e.g., he/she left for another job.
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values2:
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
I chose TEXT for this field, also. I don't need times, only dates and I don't need to perform any calculations involving the dates.
To add the three new fields, I can enter the ALTER TABLE
commands shown below:
$ sqlite3 WRS.db SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints. sqlite> .schema Requesters CREATE TABLE Requesters( Last_Name text NOT NULL, First_Name text NOT NULL, UID text PRIMARY KEY NOT NULL, Phone text NOT NULL, Email text NOT NULL, Note text ); sqlite> ALTER TABLE Requesters ADD COLUMN Added text; sqlite> ALTER TABLE Requesters ADD COLUMN Last_Modified text; sqlite> ALTER TABLE Requesters ADD COLUMN Removed text; sqlite> .schema Requesters CREATE TABLE Requesters( Last_Name text NOT NULL, First_Name text NOT NULL, UID text PRIMARY KEY NOT NULL, Phone text NOT NULL, Email text NOT NULL, Note text , Added text, Last_Modified text, Removed text); sqlite>
New columns are added at the end of the table structure as shown above. If
there are records in the table already, the new columns will have a value of
NULL assigned to them.
Once you have created a table, you can add records to it with a
INSERT INTO statement in the form below:
INSERT INTO table_name (column1, column2, column3, ... columnN) VALUES
(value1, value2, value3, ... valueN);
E.g. for the Requesters table I created, I could insert a record as shown below:
sqlite> INSERT INTO Requesters (Last_Name, First_Name, UID, Phone, Email,
...> Last_Modified) VALUES ("Wilson", "David", "dawilso1", "703.555.1234",
...> 2016-04-07);
Error: 5 values for 6 columns
sqlite> INSERT INTO Requesters (Last_Name, First_Name, UID, Phone, Email,
...> Last_Modified) VALUES ("Wilson", "David", "dawilso1", "703.555.1234",
...> "david.a.wilson-1@example.com", 2016-04-07);
sqlite>When I first entered the statement, I specified 6 columns, but only entered data for 5 columns; I forgot to add the email address as a value, so SQLite displayed the error message "5 values for 6 columns". If you enter the statement correctly, you will be returned to the sqlite prompt. I entered values for all of the fields that can not have a NULL value, but for others where I didn't specify "NOT NULL" for the column definition, I can omit a value and, in the example above, I omitted inserting any data for the "Note", "Added", and "Removed" fields.
Now I need to add projects for the person in the UID_Projects table.
sqlite> INSERT INTO UID_Projects (UID, Project) VALUES (dawilso1, ICE);
Error: no such column: dawilso1
sqlite> INSERT INTO UID_Projects (UID, Project) VALUES ("dawilso1", "ICE");
sqlite>Why did SQLite display a "no such column" error for the first attempt? I needed to enclose the text values I wanted to insert in the record within double or single quotes. When I enclosed the values in quotes, SQLite accepted the values.
If I want to see all of the records in a table, I can use a
SELECT * FROM table_name statement. E.g., though
so far I have only one record in the table:
sqlite> SELECT * FROM Requesters; Wilson|David|dawilso1|703.555.1234|david.a.wilson-1@example.com|||2005| sqlite>
For fields, where I didn't specify a value, I will see two
vertical
bars, with no value between them, i.e., || for
the "Note" and "Added" values, if the columns aren't at the end of
the table. For this table, the last column was Removed.
Since I didn't specify a value for that field, there is a vertical
bar, also referred to as a "pipe" character, with no value after it at the
end of the record.
Though I entered 2016-04-07 for the Last_Modified
date, I see 2005 displayed in that field. The values don't match,
because I didn't include the date value in quotes when I entered
it. But I can fix that problem by using an UPDATE statement.
sqlite> UPDATE Requesters SET Last_Modified = '2016-04-07' WHERE UID="dawilso1"; sqlite> SELECT * FROM Requesters; Wilson|David|dawilso1|703.555.1234|david.a.wilson-1@example.com|||2016-04-07| sqlite>
In this case since there was only one record, I could have omitted the
WHERE clause, but, if there were multiple records in the table,
I would want to stipulate which record(s) I wanted to alter using a
WHERE condition.
Now I'll add an additional project for David Wilson in the UID_Projects table.
sqlite> INSERT INTO UID_Projects (UID, Project) VALUES ("dawilso1", "TESS");
Error: UNIQUE constraint failed: UID_Projects.UID
sqlite>Why did SQLite display the error message "UNIQUE constraint failed"? The
error message was displayed because I specified that UID should
be a primary key for the table, which means it should be a unique value. If
I need multiple records in the table with the same UID, then I should not
have made it a primary key. Unfortunately, SQLite does not provide a capability
to easily alter a column or even delete a column. Instead, I'll need to copy
the data in the table to a new, similar table that doesn't have that constraint.
For the workaround of copying the data to a new table, I can rename a table and copy the contents of a table into another table. So, first I'll rename the exiting UID_Projects table and then I'll create a new, similar table using its prior name, but this time without the UID as a primary key. Then I'll copy the contents of the old table to the new one, though, granted, since I've only got one record in the table, it wouldn't be a substantial effort to just enter that same record in the new table by retyping the data.
sqlite> ALTER TABLE UID_Projects RENAME TO UID_Projects_Old; sqlite> CREATE TABLE UID_Projects (UID TEXT NOT NULL, Project TEXT NOT NULL); sqlite> INSERT INTO UID_Projects SELECT * FROM UID_Projects_Old; sqlite>
I can verify the existing data has been copied correctly, if I wish, and then I can "drop" the old table, i.e., remove the table from the database.
sqlite> SELECT * FROM UID_Projects; dawilso1|ICE sqlite> .tables UID_Projects UID_Projects_Old Requesters sqlite> DROP TABLE UID_Projects_Old; sqlite> .tables UID_Projects Requesters sqlite>
I can then add a new record with the same UID to the table.
sqlite> INSERT INTO UID_Projects VALUES ('dawilso1', 'TESS');
sqlite> SELECT * FROM UID_Projects;
dawilso1|ICE
dawilso1|TESS
sqlite>If I'm including all of the values with the INSERT statement,
I don't need to specify which columns they go into; I just need to put them
in the correct order. So, instead of using INSERT INTO table_name
(col1, col2, ... colN) VALUES (value1, value2, ... valueN), I can
just use a statement in the form INSERT INTO table_name
VALUES (value1, value2, ... valueN.
sqlite> prompt or
you can execute commands you have stored in a text file using .read
filename where filename is the name of the file where
you have stored the SQL statements. E.g., I realized that I needed to delete
a column from a table, so I renamed the existing table, since SQLite doesn't
provide a "delete column" statement. I then wanted to use the existing
schema, but with a modification for one of the columns. So I issued
the command .schema old_table to see the existing
table layout, then copied and pasted the schema information into a text file,
then altered the SQL so it would create a new table with the columns that
I needed. I was then able to import the table layout I needed to create
the new table from the createdb.txt file I had
created. I could then copy the records from the old to the new version of the
table and then "drop" the old table. You could also give the file the extension
.sql rather than .txt, if you wanted to make
it clear that the file contained SQL statements.
sqlite> .read createdb.txt sqlite>
.databases command. If you wish to backup
that database you can issue the command .backup to back it up to
a file3.
.backup DB FILE
Backup DB (default "main") to FILE
The backup file will contain the entire database with all its tables and records. E.g.:
$ sqlite3 MSGRS.db SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints. sqlite> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main /Users/jasmith1/Documents/Work/MSGRS/MSGRS.db sqlite> .backup MSGRS_Backup_20160412.db sqlite> .exit $ ls -lgh MSGRS*.db -rw-r--r-- 1 ABC\Domain Users 24K Apr 12 16:46 MSGRS.db -rw-r--r-- 1 ABC\Domain Users 24K Apr 12 19:17 MSGRS_Backup_20160412.db $
Or you can use the .save FILE command specifying
a new file name for the output file.
.save FILE Write in-memory database
into FILE
E.g., suppose I've opened the MSGRS.db database file, I
can save another copy of it to a different file name as shown below:.
$ ls -lgh MSGRS*.db -rw-r--r-- 1 ABC\Domain Users 24K Apr 12 16:46 MSGRS.db $ sqlite3 MSGRS.db SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints. sqlite> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main /Users/jasmith1/Documents/Work/MSGRS/MSGRS.db sqlite> .save MSGRS_Backup.db sqlite> .exit $ ls -lgh MSGRS*.db -rw-r--r-- 1 ABC\Domain Users 24K Apr 12 16:46 MSGRS.db -rw-r--r-- 1 ABC\Domain Users 24K Apr 12 19:07 MSGRS_Backup.db
Alternatively, you can simply copy the .db file to another location or file name at a shell prompt outside of SQLite, since all of the data is stored in the .db file.
References: