SQLite notes

install

macOS, Ubuntu, Fedora already includes SQLite3

  • Ubuntu

    # install SQLite3 development package
    # to access SQLite databases using C, Tcl, Java
    sudo apt install libsqlite3-dev
  • Fedora

    # install SQLite3 language bindings
    # to access SQLite databases using C, Tcl, Java
    sudo dnf install sqlite-devel sqlite-tcl sqlite-jdbc
  • Windows

    1. Go to SQLite Download Page and download the following under Precompiled Binaries for Windows:
    2. sqlite-dll-win32-x86-nnnnnnn.zip or sqlite-dll-win64-x64-nnnnnnn.zip (32-bit or 64-bit DLL)
    3. sqlite-tools-win32-x86-nnnnnnn.zip (command-line tools)
    4. Create sqlite directory in the C: drive (i.e., C:\sqlite).
    5. Extract sqlite3.def, sqlite3.dll and sqlite3.exe to C:\sqlite.
    6. Add C:\sqlite to PATH environment variable

compile from source

You can compile SQLite3 from source if you want more control. Here is how to do it on Ubuntu and Fedora. (Still need to do it for Mac and Windows)

Go to SQLite Download Page and download the following under Source Code:

  • sqlite-autoconf-nnnnnnn.tar.gz (C source code)
// download
curl -sSO https://www.sqlite.org/2019/sqlite-autoconf-3280000.tar.gz

// extract
tar xf sqlite-autoconf-3280000.tar.gz

// change directory
cd sqlite-autoconf-3280000/

// NOTE: read the INSTALL file for more info

// configure
./configure
./configure --enable-readline=yes --prefix=/usr/local

// build
make

// install
sudo make install

// uninstall
sudo make uninstall

enable autocompletion

Note: SQLite3 autocompletion might not be supported in Ubuntu and Fedora. You can to install rlwrap and create an alias in order to support SQLite3 autocompletion.

# install rlwrap for sqlite3 autocompletion
sudo apt install rlwrap // ubuntu
sudo dnf install rlwrap // fedora

# add an alias (.bash_aliases or .bashrc)
# for sqlite3 autocompletion
alias sqlite3='rlwrap -a -N -c -i sqlite3'

install DB Browser for SQLite

  • Mac

    # install DB Browser for SQLite
    brew cask install db-browser-for-sqlite
  • Ubuntu

    # install DB Browser for SQLite
    sudo apt install sqlitebrowser
  • Fedora

    # install DB Browser for SQLite
    sudo dnf install sqlitebrowser
  • Windows


create new database file

// create foobar.db in current directory
sqlite3 foobar.db
sqlite> .schema
sqlite> .exit

// create temp.db in $HOME/tmp/ directory
sqlite3 ~/tmp/temp.db
sqlite> .schema
sqlite> .exit

list names and files of attached databases

sqlite> .databases

list names of tables

// list names of all tables
sqlite> .tables

// list names of tables that contain 'title'
sqlite> .tables %title%

// list names of tables that start with 'title'
sqlite> .tables title%

// list names of tables that end with 'title'
sqlite> .tables %title

show names of indices

// show all indices
sqlite> .indices

// show indices of tables with name that contains 'title'
sqlite> .indices %title%

// show indices of tables with name that starts with 'title'
sqlite> .indices title%

// show indices of tables with name that ends with 'title'
sqlite> .indices %title

show the schema (i.e., the CREATE statement)

// show the schema for all tables
sqlite> .schema --indent

// show the schema for the 'sqlite_master' table (holds key info about your database tables)
sqlite> .schema --indent sqlite_master

// show the schema for a table named 'foo'
sqlite> .schema --indent foo

// show the schema for tables with name that contains 'title'
sqlite> .schema --indent %title%

// show the schema for tables with name that start with 'title'
sqlite> .schema --indent title%

// show the schema for tables with name that end with 'title'
sqlite> .schema --indent %title

execute SQL statements from a file

execute SQL in mySqlScript.sql

sqlite> .read mySqlScript.sql

import

  1. import csv to table

    sqlite3 foobar.db
    sqlite> .headers on
    sqlite> .mode csv
    sqlite> .import foo.csv Foo
  2. import tsv to table

    sqlite3 foobar.db
    sqlite> .headers on
    sqlite> .mode tabs
    sqlite> .import bar.tsv Bar

output

  1. output to stdout
sqlite> .output
sqlite> select * from tbl1;
sqlite> select * from tbl2;
  1. output to file
sqlite> .output test_file_1.txt
sqlite> select * from tbl1;
sqlite> select * from tbl2;
  1. output to command
sqlite> .output '|open -f'
sqlite> SELECT * FROM bigTable;

Note: If first character of the .output is a pipe symbol (|), then remaining characters are treated as a command and the output is sent to that command.

output once

  1. output once to file

    sqlite> .once schema.sql
    sqlite> .schema --indent
  2. output once to text editor

    sqlite> .once -e
    sqlite3> SELECT * FROM bigTable;
  3. output once to spreadsheet program

    sqlite3> .once -x
    sqlite3> SELECT \* FROM bigTable;
    
    sqlite3> .excel
    sqlite3> SELECT * FROM bigTable;

    Note: The .excel command is an alias for .once -x.

  4. output once to command raw sqlite> .once '|open -f' sqlite> SELECT * FROM bigTable; Note: If first character of the .once is a pipe symbol (|), then remaining characters are treated as a command and the output is sent to that command.

backup

// backup default "main" database to mybackup.db file
sqlite> .backup mybackup.db

// backup default "main" database to /path/to/mybackup.db file
sqlite> .backup /path/to/mybackup.db

// attach backed up database
sqlite> ATTACH DATABASE 'mybackup.db' AS MyBackup;

clone

// clone data into myclone.db file from existing database
sqlite> .clone myclone.db

// clone data into path/to/myclone.db file from existing database
sqlite> .clone /path/to/myclone.db

// attach cloned database
sqlite> ATTACH DATABASE 'myclone.db' as MyClone;

dump

// dump database in a SQL text format
sqlite3 mydata.db .dump > mydata.sql

list available meta-commands

sqlite> .help

exit SQLite interactive shell

sqlite> .exit
sqlite> .quit

show current settings

// show current settings
sqlite> .show

execute SQL statements from terminal

// default mode is list
$ sqlite3 imdb.db 'SELECT nconst, primaryName FROM nameBasics LIMIT 5;'
nm0000001|Fred Astaire
nm0000002|Lauren Bacall
nm0000003|Brigitte Bardot
nm0000004|John Belushi
nm0000005|Ingmar Bergman

// you can change the mode to csv, column, html, line, list, quote
$ sqlite3 -csv imdb.db 'SELECT nconst, primaryName FROM nameBasics LIMIT 5;'
$ sqlite3 -column imdb.db 'SELECT nconst, primaryName FROM nameBasics LIMIT 5;'
$ sqlite3 -html imdb.db 'SELECT nconst, primaryName FROM nameBasics LIMIT 5;'
$ sqlite3 -line imdb.db 'SELECT nconst, primaryName FROM nameBasics LIMIT 5;'
$ sqlite3 -list imdb.db 'SELECT nconst, primaryName FROM nameBasics LIMIT 5;'
$ sqlite3 -quote imdb.db 'SELECT nconst, primaryName FROM nameBasics LIMIT 5;'

// you can set the separator (default is |) when the mode is list (default mode is list)
$ sqlite3 -separator ',' imdb.db 'SELECT nconst, primaryName FROM nameBasics LIMIT 5;'
nm0000001,Fred Astaire
nm0000002,Lauren Bacall
nm0000003,Brigitte Bardot
nm0000004,John Belushi
nm0000005,Ingmar Bergman

// you can turn on the header (default is off)
$ sqlite3 -header imdb.db 'SELECT nconst, primaryName FROM nameBasics limit 5;'
nconst|primaryName
nm0000001|Fred Astaire
nm0000002|Lauren Bacall
nm0000003|Brigitte Bardot
nm0000004|John Belushi
nm0000005|Ingmar Bergman

// you can set the nullvalue string (default is '')
$ sqlite3 -nullvalue 'NULL' imdb.db 'SELECT primaryName, birthYear, deathYear FROM nameBasics WHERE deathYear IS NULL LIMIT 5;'
Brigitte Bardot|1934|NULL
Olivia de Havilland|1916|NULL
Kirk Douglas|1916|NULL
Sophia Loren|1934|NULL
Raquel Welch|1940|NULL

execute SQL statements from SQLite interactive shell

sqlite3 mydata.db

sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .headers on
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .headers off
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .nullvalue 'NULL'
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .mode csv
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .mode column
sqlite> .width -30 -10
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .mode html
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .mode insert new_table
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .mode line
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .mode list
sqlite> .separator ','
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .mode quote
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .mode tabs
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

sqlite> .mode tcl
sqlite> SELECT nconst, primaryName FROM nameBasics LIMIT 5;

SQLite meta-commands for debugging

// turn on/off echo command - default off
sqlite> .echo on
sqlite> .echo off

// enable/disable EXPLAIN QUERY PLAN - default off
sqlite> .eqp on
sqlite> .eqp off
sqlite> .eqp full
sqlite> .eqp trace

// turn on/off stats - default off
sqlite> stats on
sqlite> stats off

Create a new database file, create a table and insert some records into table:

sqlite3 mydata.db
sqlite> create table memos(text, priority INTEGER);
sqlite> insert into memos values('deliver project description', 10);
sqlite> insert into memos values('lunch with Christine', 100);
sqlite> select * from memos;
sqlite> .exit

Source

© 2020 | Paul Kim