pantz.org banner
SQLite commands and general usage
Posted on 05-25-2009 19:13:35 UTC | Updated on 05-26-2009 01:40:57 UTC
Section: /software/sqlite/ | Permanent Link

SQLite is an embedded open source relational database (db). It is very portable, easy to use, compact, efficient, and reliable. Being an embedded database it becomes part of the program that hosts it. It is embedded in many popular programs used today. Most use the SQLite C API to interface with the SQLite db. Many scripting/programming languages use the API like the Perl module DBI::SQLite, PHP's data objects with the SQLite driver, or just straight C programs. Not only can these languages (and many more) use the SQLite C API to access the SQLite db but most OS's have a statically linked native binary that can also be controlled completely from the command line. How cool is that?

Installing

SQLite can be installed by going to the SQLite website and downloading a copy and installing it. They have binaries for most major platforms (Windows, MAC, or Linux). If your using a OS with a package manager then there is a good chance you can just install it by using your package manager.

There are 2 versions of SQLite. Version 2 and version 3. Version 2 is (as you may have guessed) is the older version. SQLite 3 databases are not compatible with SQLite 2 databases. Use SQLite 3 if at all possible. If your going to install the SQLite command line version then here is something to remember. The package for SQLite 2 is just called "SQLite". The package for SQLite version 3 is called "SQLite3". These are also the names for the command line binaries. You access a version 2 db with the "SQLite" binary (program). You access a version 3 db with the "SQLite3" binary.

Here are some examples of installing SQLite version 3 on some Linux and BSD's.

On Linux distros with the apt-get package manager (Ex: Debian or Ubuntu).

sudo apt-get install SQLite3

On Linux distros with the yum package manager (Ex: RedHat, CentOS, or Fedora).

yum install SQLite3

On OpenBSD 4.4.

pkg_add -v ftp://ftp.openbsd.org/pub/OpenBSD/4.4/packages/i386/SQLite3-3.5.9p0.tgz

You get the idea. Just install the SQLite3 package for whatever OS you have. If your OS does not have a package just go to the SQLite website then download and install their binary.

SQLite command line

The following commands will be using the statically linked command-line program. This is what was installed in the install section above. You can completely control an SQLite database with this program and it's special built in shell. Some commands in the following examples will be invoked from a Linux shell and and from the SQLite shell. The SQLite shell will consider any command with . (period) in front of it a SQLite command. You can get a complete list of valid SQLite commands by typing .help at the SQLite shell. The commands that don't begin with a period are just regular SQL commands. They should work with most databases.

The examples below are done from a Linux machine with version 3.5.9 of SQLite.

Below when you see user@host:~$ it means execute the command from the shell. When you see sqlite> it means from a SQLite prompt (shell) after selecting a db.

Making/accessing a SQLite database.

This command will not make the db (the file test.db) until you create something in it (Ex: table or view) or until you run a few select commands from the SQLite prompt (like .database). This is so you can change things like page size or character encoding before the database structure is written to the file. After executing the command you will see a SQLite prompt. Make a table or a view to have the file actually written to disk.

If you already have a database file called test.db then you can select and access it with the same command. In summary if the db file does not exist this command will create it and if it does exist then it will select it to be accessed.

user@host:~$ sqlite3 test.db

Make your output pretty

Before we start creating and looking at data we are going to change a few default settings to make the ouput of the commands look better. We will turn on column mode (.mode col). There are many other modes other than col. See .help for more output modes. We will also turn on column headers (.headers on). Both stay on until you exit the SQLite shell or change them to something else.

sqlite> .mode col
sqlite> .headers on

Creating tables

Let's make a table called test. After the table is made the database file will exist on disk. The table named test and have 2 columns. One called ids and one called value. The ids column is an integer primary key, SQLite will make this an auto increment column. Which means if there is no number then SQLite will generate a number for the column on the first insert. After the first insert it will auto increment the numbers from there. All of this is done with the SQL create command.

sqlite> create table test (ids integer primary key, value text);

Creating views

Let's create a view. Views are like virtual tables. People also call them derived tables. This is because their contents are derived from other tables. Views look like regular tables, but they are not. Views are dynamically generated. A view is made up of relational expressions and takes other tables to produce a new table.

The following example will create a view called testview and will select everything from the test table we just created. It's a silly example of what views are good for but it will work. Most views are good for queries you keep using over and over again. You create the view to use as a temporary table and then query the view just like a table. The view eliminates the need to keep using that same redundant query.

Creating views like creating tables is done with the SQL create command.

sqlite> create view testview AS select * from test;

Creating indexes

Indexes designed to speed up queries under certain conditions. If our test table had 100,000 entries and we wanted to find a small group of entries the database would have to scan all 100,000 entries looking for our small group. If we make an index of all these entries then we will be able to scan all the entries much faster.

The bad thing is that indexes increase the size of the database because they keep a copy of all the columns they index. One other bad thing is that if you insert, update or delete a record the database has to modify the record and the index of that table. This means that indexes can slow down inserts, updates, deletes, etc. If used wisely indexes can give you a huge performance boost. You will just have to test them for yourself. Here is how to make an index for our table test on column value.

Creating indexes like creating tables and views is done with the SQL create command.

sqlite> create index testindex on test (value);

Listing views, tables, and indexes

To see all the tables and views in the database we use the SQLite .tables command. You can also use the SQL like operator to find tables and views with a pattern. Using the % symbol in the example below we show all the tables and views beginning with the letter t.

sqlite> .tables

test      testview
sqlite> .tables t%
test

To view any indexes a table has we can use the SQLite .indices command. To see them for our test table we would do the following.

sqlite> .indices test

testindex

Listing schema information

To see database schema information we use the SQLite .schema command. This give you the create command that was used to create the tables. If you created an index on any columns then this will also show up.

sqlite> .schema test

CREATE TABLE test (ids integer primary key, value text);
CREATE INDEX testindex on test (value);

Inserting data into tables

Now let's put in some data in the db with some SQL insert statements.

sqlite> insert into test (value) values('value1');
sqlite> insert into test (value) values('value2');
sqlite> insert into test (value) values('value3');
sqlite> insert into test (value) values('value4');

Seeing the data

Let's use the SQL select command to look at the data we inserted. Remember we turned on column mode (.mode col) and column headers (.headers on) at the beginning of this page so it's easier to read the output.

sqlite> select * from test;

ids         value
----------  ----------
1           value1
2           value2
3           value3
4           value4

Seeing your SQLite databases

To see the databases that are currently open use the SQLite .databases command. It will show the main and temp databases and where they are on the system.

sqlite> .databases

seq  name             file
---  ---------------  ------------------------------------
0    main             /home/user/test.db
1    temp             /var/tmp/etilqs_oCUXLLbZO4TjUNI

Exporting your data

You can export all of your database objects to a file for backup using the SQLite .output and .dump commands. These commands will output all of the data from the database. The first .output line directs any output from now on to the file /tmp/test.sql. The second line .dump will export all of the data in the objects in the database. If the file is already there it will overwrite that file. The last command will set the output back to the screen like we had it.

sqlite> .output /tmp/test.sql
sqlite> .dump
sqlite> .output stdout

To look at what the file looks like open a new shell and cat that file.

user@host:~$ cat /tmp/test.sql

BEGIN TRANSACTION;
CREATE TABLE test (ids integer primary key, value text);
INSERT INTO "test" VALUES(1,'value1');
INSERT INTO "test" VALUES(2,'value2');
INSERT INTO "test" VALUES(3,'value3');
INSERT INTO "test" VALUES(4,'value4');
CREATE VIEW testview AS select * from test;
CREATE INDEX testindex on test (value);
COMMIT;

The file contains all the information you need to re-create your database. From creating the tables to inserting all the data. This is used mostly to backup databases or to export the data from one database type to another. For backing up purposes it is usually easier to just copy the SQLite3 db file to another location.

Importing external data

If you would like to import the data you exported using the SQLite .dump command it's as easy as using the SQLite .read command. In the example below the first command makes a new database from the system command line (shell). The second command imports the data we exported in the previous example. The last command shows all the data in the new database.

user@host:~$ sqlite3 testrecover.db
sqlite> .read /tmp/test.sql
sqlite> select * from test;

ids         value
----------  ----------
1           value1
2           value2
3           value3
4           value4

If you want to import data that is in csv (comma separated value) format into a table you can do it using the SQLite .import command. The csv file has to use separators that SQLite understands. To see the current separators you can use the .show command. SQLite uses the pipe | as the default separator.

sqlite> .show

     echo: off
  explain: off
  headers: on
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    width:

To change the separator type use the .separator command. This will change it for output mode and for .import.

sqlite> .separator ,
sqlite> .show

     echo: off
  explain: off
  headers: on
     mode: column
nullvalue: ""
   output: stdout
separator: ","
    width:

Now that the separator is changed to a comma we can import data from an external csv file. We will import more data into our current table (test) with the .import command. Make sure your csv file sections match up with your tables and that you have the same number of columns. Also, note that when you export a file now it will be exported with a comma as the separator.

Here is what the test.csv file looks like.

5,value5
6,value6
7,value7

Here is the import from the csv file and what the test table looks like after import.

sqlite> .import /tmp/test.csv test
sqlite> select * from test;

ids         value
----------  ----------
1           value1
2           value2
3           value3
4           value4
5           value5
6           value6
7           value7

Removing tables, triggers, views, and indexes

To remove any index, table, trigger, or view you have made use the SQL drop command. The syntax is the word drop then what you want to drop and then the name of the thing you want to drop. Below is and example of dropping one of each kind.

drop index indexname
drop table tablename
drop trigger triggername
drop view viewname

Exiting

To exit out of the SQLite prompt use the SQLite .exit or .quit command. Both work the same way.

sqlite> .exit
user@host:~$

SQLite from the shell

Intro

The following commands will be using the statically linked SQLite command-line program. This is what was installed in the install section above. You can completely control an SQLite database with this program right from the Linux shell. No need to invoke the SQLite shell we introduced above. Being able to access SQLite databases from a shell lets you incorporate SQLite into your shell scripts without the need for external libraries.

We will use the same test.db database that we made in the examples above for this demo.

Usage options

The SQLite binary is for version 3 is called SQLite3. You can use this program from the command line just like any other UNIX program. If you want to look at the options the binary has just issue the --help option.

user@host:~$ 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:
   -init filename       read/process named file
   -echo                print commands before execution
   -[no]header          turn headers on or off
   -bail                stop after hitting an error
   -interactive         force interactive I/O
   -batch               force batch I/O
   -column              set output mode to 'column'
   -csv                 set output mode to 'csv'
   -html                set output mode to HTML
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -separator 'x'       set output field separator (|)
   -nullvalue 'text'    set text string for NULL values
   -version             show SQLite version

You can see syntax you need to use and the different options available.

NOTE THE ABOVE: If the database does not exist it will be created on any command given. Even on commands that fail.

Execute SQL statements

You can execute SQL statements right from the shell. Let's look at the data in our test database test.db. We are going to turn on headers and also turn on column mode so the ouput looks nice.

user@host:~$ sqlite3 -header -column test.db 'select * from test'

ids         value
----------  ----------
1           value1
2           value2
3           value3
4           value4
5           value5
6           value6
7           value7

You can execute any SQL statement against a database like this.

Execute SQLite commands

You can execute SQLite commands the same way you execute SQL commands. The example below uses the .schema SQLite command on the test.db file.

user@host:~$ sqlite3 -header -column test.db '.schema'

CREATE TABLE test (ids integer primary key, value text);
CREATE VIEW testview AS select * from test;
CREATE INDEX testindex on test (value);

Export a database

You can export (backup) your database using the command-line program and .dump. It works the same way it does from the SQLite shell. But we are going to want the output to go to a file and not the screen so we will redirect it to a file called dbbackup.

user@host:~$ sqlite3 test.db '.dump' > dbbackup

You could also compress the output before writing it disk by running .dump's ouput through gzip, bzip2, or whatever. The following is an example using bzip. It is highly recommended that you compress your dumps.

user@host:~$ sqlite3 test.db '.dump' | gzip -c > dbbackup.gz

Import a database

If you have used the SQLite .dump command we mentioned earlier to create a file with SQL statements you can input those statements into your database from the command line. This can be done with any SQL statements not just the ones from the .dump command. The following example will use a file called statements.sql and import them into our test.db file.

user@host:~$ sqlite3 test.db < statements.sql

To import that gzip compressed dump (backup) of SQL statements from above.

user@host:~$ gunzip -c dbbackup.gz | SQLite3 test.db

As a speed note. If your importing a lot of insert statements wrap those in a transaction to get a major speedup.

More information

Here are some great links from the SQLite website to help you further.

SQL commands understood by SQLite

SQLite understands most of the standard SQL language. But there is some syntax differences sometimes. There are also some SQL commands that SQLite has added that are unique. These are commands like VACUUM or PRAGMA. Make sure your look at the PRAGMA page as it is used to tweak settings in your SQLite database.

http://www.SQLite.org/lang.html

Functions

Core Functions, Aggregate functions, and Date time functions are all functions you can use with SQLite. You can also have an application define additional functions that are written in C and have them added to the database engine using the SQLite3_create_function() API.

http://www.sqlite.org/lang_corefunc.html

http://www.sqlite.org/lang_aggfunc.html

http://www.sqlite.org/lang_datefunc.html

Reddit!

Related stories

Fixing Sqlite error "unable to open database file"
Posted on 03-12-2008 16:13:00 UTC | Updated on 03-12-2008 16:13:00 UTC
Section: /software/sqlite/ | Permanent Link

I was using Sqlite for the first time and kept getting the error "unable to open database file(1) at dbdimp.c line 398". I knew the permissions on the db file where correct. It had to be writeable by the user accessing the file. Which in is this case was a webserver so whatever the webserver was running as. I could execute the code on the command line and there was no problem. So I knew it was not the code. It had to be a permissions problem. But where? Well, Sqlite creates temporary file for tables and indices in /var/tmp. Thing is /var/tmp had the correct permissions (with the sticky bit) for users to create files temp files. So that was not it. After more searching I found the answer. Sqlite also keeps a journal file when a transaction is started. This is called file is called "dbfilename-journal". I figured if it creates it's journal file in the /var/tmp dir then what's the problem? Problem is it does not try to create this temporary file in /var/tmp. If it did I would not have wasted all this damn time. The software devs decided to create this temporary file in the same dir the database file is kept in. So now the user that wants to write to that database has to have write permissions on the directory the database is in. After chowning that dir to the user opening the db it worked great.

The error message this gives is just horrible. It's way to vague. It makes you think your permissons are wrong on the database file itself. It could also lead you to think of 100 other reasons why it can't open the db file. You keep thinking there is something wrong with the db file itself when it's a temp file creation problem. If the code knows the error is in trying to create a temporary file then say that in the error! You can get a return value that would tell you this after trying to create it. I really like sqlite and thank them for giving it away, but they need a little more detail in the error messages.

Reddit!

Related stories


RSS Feed RSS feed logo

About


3com

3ware

alsa

alsactl

alsamixer

amd

android

apache

areca

arm

ati

auditd

awk

badblocks

bash

bind

bios

bonnie

cable

carp

cat5

cdrom

cellphone

centos

chart

chrome

chromebook

cifs

cisco

cloudera

comcast

commands

comodo

compiz-fusion

corsair

cpufreq

cpufrequtils

cpuspeed

cron

crontab

crossover

cu

cups

cvs

database

dbus

dd

dd_rescue

ddclient

debian

decimal

dhclient

dhcp

diagnostic

diskexplorer

disks

dkim

dns

dos

dovecot

drac

dsniff

dvdauthor

e-mail

echo

editor

emerald

encryption

ethernet

expect

ext3

ext4

fat32

fedora

fetchmail

fiber

filesystems

firefox

firewall

flac

flexlm

floppy

flowtools

fonts

format

freebsd

ftp

gdm

gmail

gnome

google

gpg

greasemonkey

greylisting

growisofs

grub

hacking

hadoop

harddrive

hba

hex

hfsc

html

html5

http

https

hulu

idl

ie

ilo

intel

ios

iperf

ipmi

iptables

ipv6

irix

javascript

kde

kernel

kickstart

kmail

kprinter

krecord

kubuntu

kvm

lame

ldap

linux

logfile

lp

lpq

lpr

maradns

matlab

memory

mencoder

mhdd

mkinitrd

mkisofs

moinmoin

motherboard

mouse

movemail

mplayer

multitail

mutt

myodbc

mysql

mythtv

nagios

nameserver

netflix

netflow

nginx

nic

ntfs

ntp

nvidia

odbc

openbsd

openntpd

openoffice

openssh

openssl

openvpn

opteron

parted

partimage

patch

perl

pf

pfflowd

pfsync

photorec

php

pop3

pop3s

ports

postfix

power

procmail

proftpd

proxy

pulseaudio

putty

pxe

python

qemu

r-studio

raid

recovery

redhat

router

rpc

rsync

ruby

saltstack

samba

schedule

screen

scsi

seagate

seatools

sed

sendmail

sgi

shell

siw

smtp

snort

solaris

soundcard

sox

spam

spamd

spf

spotify

sql

sqlite

squid

srs

ssh

ssh.com

ssl

su

subnet

subversion

sudo

sun

supermicro

switches

symbols

syslinux

syslog

systemd

systemrescuecd

t1

tcpip

tcpwrappers

telnet

terminal

testdisk

tftp

thttpd

thunderbird

timezone

ting

tls

tools

tr

trac

tuning

tunnel

ubuntu

unbound

vi

vpn

wget

wiki

windows

windowsxp

wireless

wpa_supplicant

x

xauth

xfree86

xfs

xinearama

xmms

youtube

zdump

zeromq

zic

zlib