1. Introduction
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is free and you don’t need to buy any license to use it. It is one of the most commonly used DB.
Unlike most SQL databases, it does not have a separate server process. It reads and writes directly to disk files. All the information which you need (schema, tables, indices, views, triggers, etc)is stored in a single file. The same fine can be used in a 32-bit or a 64-bit system because the file format is compatible with both. SQLite database files are a recommended storage format by the US Library of Congress.
You can also check this tutorial in the following video:
SQLite is a compact library. With all features enabled, the library size can be less than 600KiB, depending on the target platform and compiler optimization settings. There is a tradeoff between memory usage and speed. SQLite responds gracefully to memory allocation failures and disk I/O errors. Transactions are ACID even if interrupted by system crashes or power failures.
2. SQLite Installation
You can download the installable from this link. You can also build the sqlite3 executable by downloading the source code from SQLite downloads. You can use the command below extract the tar from gzipped file. You can make and make install after extracting the tar file. The output is shown below:
building sqlite3
apples-MacBook-Air:downloads bhagvan.kommadi$ cd sqlite-autoconf-3380200
apples-MacBook-Air:sqlite-autoconf-3380200 bhagvan.kommadi$ ls
INSTALL config.guess sqlite3.1
Makefile.am config.sub sqlite3.c
Makefile.fallback configure sqlite3.h
Makefile.in configure.ac sqlite3.pc.in
Makefile.msc depcomp sqlite3.rc
README.txt install-sh sqlite3ext.h
Replace.cs ltmain.sh sqlite3rc.h
aclocal.m4 missing tea
compile shell.c
apples-MacBook-Air:sqlite-autoconf-3380200 bhagvan.kommadi$ ./configure --prefix=/usr/local
checking for a BSD-compatible install... /usr/local/bin/ginstall -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /usr/local/bin/gmkdir -p
checking for gawk... no
checking for mawk... no
checking for nawk... no
checking for awk... awk
checking whether make sets $(MAKE)... make
yes
checking whether make supports nested variables... yes
checking for style of include used by make... GNU
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking whether gcc understands -c and -o together... yes
checking dependency style of gcc... gcc3
checking for special C compiler options needed for large files... no
checking for _FILE_OFFSET_BITS value needed for large files... no
checking for gcc... (cached) gcc
checking whether we are using the GNU C compiler... (cached) yes
checking whether gcc accepts -g... (cached) yes
checking for gcc option to accept ISO C89... (cached) none needed
checking whether gcc understands -c and -o together... (cached) yes
checking dependency style of gcc... (cached) gcc3
checking build system type... x86_64-apple-darwin20.6.0
checking host system type... x86_64-apple-darwin20.6.0
checking how to print strings... printf
checking for a sed that does not truncate output... /usr/bin/sed
checking for grep that handles long lines and -e... /usr/bin/grep
checking for egrep... /usr/bin/grep -E
checking for fgrep... /usr/bin/grep -F
checking for ld used by gcc... /Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/ld
checking if the linker (/Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/ld) is GNU ld... no
checking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm -B
checking the name lister (/usr/bin/nm -B) interface... BSD nm
checking whether ln -s works... yes
checking the maximum length of command line arguments... 786432
checking how to convert x86_64-apple-darwin20.6.0 file names to x86_64-apple-darwin20.6.0 format... func_convert_file_noop
checking how to convert x86_64-apple-darwin20.6.0 file names to toolchain format... func_convert_file_noop
checking for /Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/ld option to reload object files... -r
checking for objdump... objdump
checking how to recognize dependent libraries... pass_all
checking for dlltool... no
checking how to associate runtime and link libraries... printf %s\n
checking for ar... ar
checking for archiver @FILE support... no
checking for strip... strip
checking for ranlib... ranlib
checking command to parse /usr/bin/nm -B output from gcc object... ok
checking for sysroot... no
checking for a working dd... /bin/dd
checking how to truncate binary pipes... /bin/dd bs=4096 count=1
checking for mt... no
checking if : is a manifest tool... no
checking for dsymutil... dsymutil
checking for nmedit... nmedit
checking for lipo... lipo
checking for otool... otool
checking for otool64... no
checking for -single_module linker flag... yes
checking for -exported_symbols_list linker flag... yes
checking for -force_load linker flag... yes
checking how to run the C preprocessor... gcc -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for dlfcn.h... yes
checking for objdir... .libs
checking if gcc supports -fno-rtti -fno-exceptions... yes
checking for gcc option to produce PIC... -fno-common -DPIC
checking if gcc PIC flag -fno-common -DPIC works... yes
checking if gcc static flag -static works... no
checking if gcc supports -c -o file.o... yes
checking if gcc supports -c -o file.o... (cached) yes
checking whether the gcc linker (/Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/ld) supports shared libraries... yes
checking dynamic linker characteristics... darwin20.6.0 dyld
checking how to hardcode library paths into programs... immediate
checking whether stripping libraries is possible... yes
checking if libtool supports shared libraries... yes
checking whether to build shared libraries... yes
checking whether to build static libraries... yes
checking for fdatasync... yes
checking for usleep... yes
checking for fullfsync... no
checking for localtime_r... yes
checking for gmtime_r... yes
checking whether strerror_r is declared... yes
checking for strerror_r... yes
checking whether strerror_r returns char *... no
checking editline/readline.h usability... yes
checking editline/readline.h presence... yes
checking for editline/readline.h... yes
checking for library containing readline... no
checking readline/readline.h usability... yes
checking readline/readline.h presence... yes
checking for readline/readline.h... yes
checking for library containing tgetent... -ltermcap
checking for library containing readline... -lreadline
checking for library containing pthread_create... none required
checking for library containing pthread_mutexattr_init... none required
checking for library containing dlopen... none required
checking for whether to support dynamic extensions... yes
checking SQL math functions... enabled
checking for library containing ceil... none required
checking FTS4 extension... enabled
checking FTS3 extension... disabled
checking FTS5 extension... enabled
checking for library containing log... none required
checking RTREE extension... enabled
checking Session extension... disabled
checking Build type... release
checking for posix_fallocate... no
checking zlib.h usability... yes
checking zlib.h presence... yes
checking for zlib.h... yes
checking for library containing deflate... -lz
checking for library containing system... none required
checking that generated files are newer than configure... done
configure: creating ./config.status
config.status: creating Makefile
config.status: creating sqlite3.pc
config.status: executing depfiles commands
config.status: executing libtool commands
apples-MacBook-Air:sqlite-autoconf-3380200 bhagvan.kommadi$ make
/bin/sh ./libtool --tag=CC --mode=compile gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.38.2\" -DPACKAGE_STRING=\"sqlite\ 3.38.2\" -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\" -DPACKAGE_URL=\"\" -DPACKAGE=\"sqlite\" -DVERSION=\"3.38.2\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_EDITLINE_READLINE_H=1 -DHAVE_READLINE_READLINE_H=1 -DHAVE_READLINE=1 -DHAVE_ZLIB_H=1 -I. -I/usr/local/opt/krb5/include -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_GEOPOLY -DSQLITE_HAVE_ZLIB -g -O2 -MT sqlite3.lo -MD -MP -MF .deps/sqlite3.Tpo -c -o sqlite3.lo sqlite3.c
libtool: compile: gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.38.2\" "-DPACKAGE_STRING=\"sqlite 3.38.2\"" -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\" -DPACKAGE_URL=\"\" -DPACKAGE=\"sqlite\" -DVERSION=\"3.38.2\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_EDITLINE_READLINE_H=1 -DHAVE_READLINE_READLINE_H=1 -DHAVE_READLINE=1 -DHAVE_ZLIB_H=1 -I. -I/usr/local/opt/krb5/include -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_GEOPOLY -DSQLITE_HAVE_ZLIB -g -O2 -MT sqlite3.lo -MD -MP -MF .deps/sqlite3.Tpo -c sqlite3.c -fno-common -DPIC -o .libs/sqlite3.o
libtool: compile: gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.38.2\" "-DPACKAGE_STRING=\"sqlite 3.38.2\"" -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\" -DPACKAGE_URL=\"\" -DPACKAGE=\"sqlite\" -DVERSION=\"3.38.2\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_EDITLINE_READLINE_H=1 -DHAVE_READLINE_READLINE_H=1 -DHAVE_READLINE=1 -DHAVE_ZLIB_H=1 -I. -I/usr/local/opt/krb5/include -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_GEOPOLY -DSQLITE_HAVE_ZLIB -g -O2 -MT sqlite3.lo -MD -MP -MF .deps/sqlite3.Tpo -c sqlite3.c -o sqlite3.o >/dev/null 2>&1
mv -f .deps/sqlite3.Tpo .deps/sqlite3.Plo
/bin/sh ./libtool --tag=CC --mode=link gcc -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_GEOPOLY -DSQLITE_HAVE_ZLIB -g -O2 -no-undefined -version-info 8:6:8 -L/usr/local/opt/krb5/lib -o libsqlite3.la -rpath /usr/local/lib sqlite3.lo -lz
libtool: link: gcc -dynamiclib -o .libs/libsqlite3.0.dylib .libs/sqlite3.o -L/usr/local/opt/krb5/lib -lz -g -O2 -install_name /usr/local/lib/libsqlite3.0.dylib -compatibility_version 9 -current_version 9.6 -Wl,-single_module
libtool: link: (cd ".libs" && rm -f "libsqlite3.dylib" && ln -s "libsqlite3.0.dylib" "libsqlite3.dylib")
libtool: link: ar cru .libs/libsqlite3.a sqlite3.o
libtool: link: ranlib .libs/libsqlite3.a
libtool: link: ( cd ".libs" && rm -f "libsqlite3.la" && ln -s "../libsqlite3.la" "libsqlite3.la" )
gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.38.2\" -DPACKAGE_STRING=\"sqlite\ 3.38.2\" -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\" -DPACKAGE_URL=\"\" -DPACKAGE=\"sqlite\" -DVERSION=\"3.38.2\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_EDITLINE_READLINE_H=1 -DHAVE_READLINE_READLINE_H=1 -DHAVE_READLINE=1 -DHAVE_ZLIB_H=1 -I. -I/usr/local/opt/krb5/include -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_GEOPOLY -DSQLITE_HAVE_ZLIB -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_DBPAGE_VTAB -DSQLITE_ENABLE_STMTVTAB -DSQLITE_ENABLE_DBSTAT_VTAB -g -O2 -MT sqlite3-shell.o -MD -MP -MF .deps/sqlite3-shell.Tpo -c -o sqlite3-shell.o `test -f 'shell.c' || echo './'`shell.c
mv -f .deps/sqlite3-shell.Tpo .deps/sqlite3-shell.Po
gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.38.2\" -DPACKAGE_STRING=\"sqlite\ 3.38.2\" -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\" -DPACKAGE_URL=\"\" -DPACKAGE=\"sqlite\" -DVERSION=\"3.38.2\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_EDITLINE_READLINE_H=1 -DHAVE_READLINE_READLINE_H=1 -DHAVE_READLINE=1 -DHAVE_ZLIB_H=1 -I. -I/usr/local/opt/krb5/include -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_GEOPOLY -DSQLITE_HAVE_ZLIB -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_DBPAGE_VTAB -DSQLITE_ENABLE_STMTVTAB -DSQLITE_ENABLE_DBSTAT_VTAB -g -O2 -MT sqlite3-sqlite3.o -MD -MP -MF .deps/sqlite3-sqlite3.Tpo -c -o sqlite3-sqlite3.o `test -f 'sqlite3.c' || echo './'`sqlite3.c
mv -f .deps/sqlite3-sqlite3.Tpo .deps/sqlite3-sqlite3.Po
/bin/sh ./libtool --tag=CC --mode=link gcc -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_GEOPOLY -DSQLITE_HAVE_ZLIB -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_DBPAGE_VTAB -DSQLITE_ENABLE_STMTVTAB -DSQLITE_ENABLE_DBSTAT_VTAB -g -O2 -L/usr/local/opt/krb5/lib -o sqlite3 sqlite3-shell.o sqlite3-sqlite3.o -lreadline -ltermcap -lz
libtool: link: gcc -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_GEOPOLY -DSQLITE_HAVE_ZLIB -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_DBPAGE_VTAB -DSQLITE_ENABLE_STMTVTAB -DSQLITE_ENABLE_DBSTAT_VTAB -g -O2 -o sqlite3 sqlite3-shell.o sqlite3-sqlite3.o -L/usr/local/opt/krb5/lib -lreadline -ltermcap -lz
apples-MacBook-Air:sqlite-autoconf-3380200 bhagvan.kommadi$ make install
/usr/local/bin/gmkdir -p '/usr/local/lib'
/bin/sh ./libtool --mode=install /usr/local/bin/ginstall -c libsqlite3.la '/usr/local/lib'
libtool: install: /usr/local/bin/ginstall -c .libs/libsqlite3.0.dylib /usr/local/lib/libsqlite3.0.dylib
libtool: install: (cd /usr/local/lib && { ln -s -f libsqlite3.0.dylib libsqlite3.dylib || { rm -f libsqlite3.dylib && ln -s libsqlite3.0.dylib libsqlite3.dylib; }; })
libtool: install: /usr/local/bin/ginstall -c .libs/libsqlite3.lai /usr/local/lib/libsqlite3.la
libtool: install: /usr/local/bin/ginstall -c .libs/libsqlite3.a /usr/local/lib/libsqlite3.a
libtool: install: chmod 644 /usr/local/lib/libsqlite3.a
libtool: install: ranlib /usr/local/lib/libsqlite3.a
/usr/local/bin/gmkdir -p '/usr/local/bin'
/bin/sh ./libtool --mode=install /usr/local/bin/ginstall -c sqlite3 '/usr/local/bin'
libtool: install: /usr/local/bin/ginstall -c sqlite3 /usr/local/bin/sqlite3
/usr/local/bin/gmkdir -p '/usr/local/include'
/usr/local/bin/ginstall -c -m 644 sqlite3.h sqlite3ext.h '/usr/local/include'
/usr/local/bin/gmkdir -p '/usr/local/share/man/man1'
/usr/local/bin/ginstall -c -m 644 sqlite3.1 '/usr/local/share/man/man1'
/usr/local/bin/gmkdir -p '/usr/local/lib/pkgconfig'
/usr/local/bin/ginstall -c -m 644 sqlite3.pc '/usr/local/lib/pkgconfig'
You can run the sqlite3 using the executable and try .help command. The output is shown below.
help command
apples-MacBook-Air:sqlite-autoconf-3380200 bhagvan.kommadi$ ./sqlite3 SQLite version 3.38.2 2022-03-26 13:51:10 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .help .archive ... Manage SQL archives .auth ON|OFF Show authorizer callbacks .backup ?DB? FILE Backup DB (default "main") to FILE .bail on|off Stop after hitting an error. Default OFF .binary on|off Turn binary output on or off. Default OFF .cd DIRECTORY Change the working directory to DIRECTORY .changes on|off Show number of rows changed by SQL .check GLOB Fail if output since .testcase does not match .clone NEWDB Clone data into NEWDB from the existing database .connection [close] [#] Open or close an auxiliary database connection .databases List names and files of attached databases .dbconfig ?op? ?val? List or change sqlite3_db_config() options .dbinfo ?DB? Show status information about the database .dump ?OBJECTS? Render database content as SQL .echo on|off Turn command echo on or off .eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN .excel Display the output of next command in spreadsheet .exit ?CODE? Exit this program with return-code CODE .expert EXPERIMENTAL. Suggest indexes for queries .explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto .filectrl CMD ... Run various sqlite3_file_control() operations .fullschema ?--indent? Show schema and the content of sqlite_stat tables .headers on|off Turn display of headers on or off .help ?-all? ?PATTERN? Show help text for PATTERN .import FILE TABLE Import data from FILE into TABLE .imposter INDEX TABLE Create imposter table TABLE on index INDEX .indexes ?TABLE? Show names of indexes .limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT .lint OPTIONS Report potential schema issues. .load FILE ?ENTRY? Load an extension library .log FILE|off Turn logging on or off. FILE can be stderr/stdout .mode MODE ?OPTIONS? Set output mode .nonce STRING Suspend safe mode for one command if nonce matches .nullvalue STRING Use STRING in place of NULL values .once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE .open ?OPTIONS? ?FILE? Close existing database and reopen FILE .output ?FILE? Send output to FILE or stdout if FILE is omitted .parameter CMD ... Manage SQL parameter bindings .print STRING... Print literal STRING .progress N Invoke progress handler after every N opcodes .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILE Read input from FILE or command output .recover Recover as much data as possible from corrupt db. .restore ?DB? FILE Restore content of DB (default "main") from FILE .save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...) .scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off .schema ?PATTERN? Show the CREATE statements matching PATTERN .selftest ?OPTIONS? Run tests defined in the SELFTEST table .separator COL ?ROW? Change the column and row separators .sha3sum ... Compute a SHA3 hash of database content .shell CMD ARGS... Run CMD ARGS... in a system shell .show Show the current values for various settings .stats ?ARG? Show stats or turn stats on or off .system CMD ARGS... Run CMD ARGS... in a system shell .tables ?TABLE? List names of tables matching LIKE pattern TABLE .testcase NAME Begin redirecting output to 'testcase-out.txt' .testctrl CMD ... Run various sqlite3_test_control() operations .timeout MS Try opening locked tables for MS milliseconds .timer on|off Turn SQL timer on or off .trace ?OPTIONS? Output each SQL statement as it is run .vfsinfo ?AUX? Information about the top-level VFS .vfslist List all available VFSes .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set minimum column widths for columnar output sqlite>
You can try .databases command to show the databases (default) in sqlite. The output is shown below:
Default databases command
sqlite> .databases main: "" r/w sqlite> .schema sqlite_master CREATE TABLE sqlite_master ( type text, name text, tbl_name text, rootpage integer, sql text ); sqlite>
3. Uses of SQLite
You should not compare SQLite with traditional client/server SQL databases like MySQL, Oracle, PostgreSQL. Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.
3.1 Embedded devices and IOT
SQLite database requires very little administration as compared to the traditional client/server SQL databases. That is why it is a good fit for use in cellphones, set-top boxes, televisions, game consoles, cameras, watches, kitchen appliances, thermostats, automobiles, machine tools, airplanes, remote sensors, drones, medical devices, robots etc.
3.2 Websites
SQLite works best for the website which has low traffic. The amount of web traffic the SQLite can handle depends on how much the website uses its database.
3.3 Data analysis
Various tools can be used to extract data from the CSV file. This data then can be analyzed using different languages – e.g. Python, R, etc. This can also be done in a traditional SQL database but SQLite is easier to install and use and it’s just one file that we will be processing for the analysis.
3.4 Data transfer
Because an SQLite database is a single compact file in a well-defined cross-platform format, it is often used as a container for transferring content from one system to another. The sender gathers content into an SQLite database file, transfers that one file to the receiver, then the receiver uses SQL to extract the content as needed.
3.5 Replacement for ad hoc disk files
Many programs use fopen(), fread(), and fwrite() to create and manage files of data in home-grown formats. SQLite works particularly well as a replacement for these ad hoc data files. Contrary to intuition, SQLite can be faster than the filesystem for reading and writing content to disk.
3.6 Education
Because of the simplicity of the SQLite database, it is a good candidate to use for education purposes. Students can create as many databases as they like and can send the DB file to the tutor for grading purposes.
4. Traditional SQL databases
In this section, we will discuss the cases where it is better to use the traditional client/server databases rather than SQLite.
4.1 Client server application
In a scenario where we have multiple clients calling the servers which require database access, it is better to use the traditional client/server SQL database. SQLite will work but if the load becomes more the performance will reduce drastically. Another issue is that SQLite uses one file – so if multiple threads/processes try to access this file we could see locking issues.
4.2 High volume websites
SQLite will not be the best option for websites that handle lots of transactions.
4.3 Lage datasets
An SQLite database is limited in size to 281 terabytes. Even if it could handle larger databases, SQLite stores the entire database in a single disk file, and many filesystems limit the maximum size of files to something less than this. So if you think that your data size could be more than this you should consider using the traditional client/server SQL database.
4.4 High concurrency
SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. So if you are building an application that requires lots of writes in a quick interval of time then it is better to use the traditional client/server SQL database.
5. SQLite vs traditional client/server database
In this section, we will see the difference between SQLite and a traditional client/server database – e.g. MySQL.
5.1 Configuration
A traditional client/server database requires some setup and configuration before you can start using it. SQLite doesn’t need to be installed before it can be used. There is no set-up process for it. You don’t need to start/stop the server as you do in the case of a traditional database. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after a system crash or power failure. There is nothing to troubleshoot.
5.2 Client/Server
Most SQL database engines are implemented as a separate server process. Programs that want to access the database communicate with the server using some kind of interprocess communication (typically TCP/IP) to send requests to the server and to receive back results. SQLite does not work this way. With SQLite, the process that wants to access the database reads and writes directly from the database files on disk. There is no intermediary server process.
There are advantages and disadvantages to being serverless. The main advantage is that there is no separate server process to install, set up, configure, initialize, manage, and troubleshoot. This is one reason why SQLite is a “zero-configuration” database engine. Programs that use SQLite require no administrative support for setting up the database engine before they are run. Any program that is able to access the disk is able to use an SQLite database.
On the other hand, a database engine that uses a server can provide better protection from bugs in the client application – stray pointers in a client cannot corrupt memory on the server. And because a server is a single persistent process, it is able to control database access with more precision, allowing for finer grain locking and better concurrency.
5.3 Database file
An SQLite database is a single ordinary disk file that can be located anywhere in the directory hierarchy. If SQLite can read the disk file then it can read anything in the database. If the disk file and its directory are writable, then SQLite can change anything in the database. Database files can easily be copied onto a USB memory stick or emailed for sharing.
Other SQL database engines tend to store data as a large collection of files. Often these files are in a standard location that only the database engine itself can access. This makes the data more secure, but also makes it harder to access. Some SQL database engines provide the option of writing directly to disk and bypassing the filesystem altogether. This provides added performance, but at the cost of considerable setup and maintenance complexity.
6. Language
In this section, we will see how SQLite language differs from the traditional client/server databases.
6.1. Flexibility
SQLite is very flexible with regard to data types. SQLite is very forgiving of the type of data that you put into the database. For example, if a column has a datatype of INTEGER and the application inserts a text string into that column, SQLite will first try to convert the text string into an integer, just like every other SQL database engine. Thus, if one inserts ‘1234’ into an INTEGER column, that value is converted into an integer 1234 and stored. But, if you insert a non-numeric string like ‘wxyz’ into an INTEGER column, unlike other SQL databases, SQLite does not throw an error. Instead, SQLite stores the actual string value in the column.
6.2 Booleans
Unlike most other SQL implementations, SQLite does not have a separate BOOLEAN datatype. Instead, TRUE and FALSE are (normally) represented as integers 1 and 0, respectively.
6.3 DATETIME
SQLite has no DATETIME datatype. Instead, dates and times can be stored in any of these ways:
- As a
TEXTstring in the ISO-8601 format. Example: ‘2018-04-02 12:13:46‘. - As an
INTEGERnumber of seconds since 1970 (also known as “unix time”). - As a
REALvalue that is the fractional Julian day number.
6.4 Datatype is optional
Because SQLite is flexible and forgiving with regard to data types, table columns can be created that have no specified datatype at all
7. Summary
In this article, we discussed the SQLite database. We discussed its use and how it is different from traditional client/server SQL databases. We looked at the scenarios where it will be good to use SQLite and also those where it will not perform better. In the end, we looked at some of the language differences.
Last updated on Apr. 5th, 2022
