Here's why SQLite is perfect for your day-to-day work. It doesn't matter if you are a developer, analyst, tester, admin or product manager.
For starters, a few known facts:
SQLite is the most widely used database management system in the world and is included in all popular operating systems.
Works without a server.
For developers, it is embedded directly into the application.
For everyone else - a convenient console (REPL) in one file (sqlite3.exe on Windows, sqlite3 on Linux / macOS).
Console, import and export
The console is a killer feature of SQLite: a more powerful data analysis tool than Excel, and much simpler than any pandas. Data from CSV is loaded with one command, the table is created automatically:
> .import --csv city.csv city
> select count(*) from city;
1117
Basic SQL features are supported, and the console shows the result in a nice table. There are also advanced SQL features, but more about them later.
select
century || '- ' as dates,
count(*) as city_count
from history
group by century
order by century desc;
┌──────────┬────────────┐
│ dates │ city_count │
├──────────┼────────────┤
│ 21- │ 1 │
│ 20- │ 263 │
│ 19- │ 189 │
│ 18- │ 191 │
│ 17- │ 137 │
│ 16- │ 79 │
│ 15- │ 39 │
│ 14- │ 38 │
│ 13- │ 27 │
│ 12- │ 44 │
│ 11- │ 8 │
│ 10- │ 6 │
│ 9- │ 4 │
│ 5- │ 1 │
│ 3- │ 1 │
└──────────┴────────────┘
A bunch of data export formats: sql, csv, json, even markdown and html. Everything is done with a couple of commands:
.mode json
.output city.json
select city, foundation_year, timezone from city limit 10;
.shell cat city.json
[{"city":"","foundation_year":1969,"timezone":"UTC+3"},
{"city":"","foundation_year":1857,"timezone":"UTC+3"},
{"city":"-","foundation_year":1830,"timezone":"UTC+7"},
{"city":"","foundation_year":1913,"timezone":"UTC+7"},
{"city":"","foundation_year":1730,"timezone":"UTC+7"},
{"city":"","foundation_year":1846,"timezone":"UTC+7"},
{"city":"","foundation_year":1709,"timezone":"UTC+7"},
{"city":"","foundation_year":1942,"timezone":"UTC+7"},
{"city":"","foundation_year":1748,"timezone":"UTC+7"},
{"city":"","foundation_year":1736,"timezone":"UTC+7"}]
Working with JSON native
There is nothing more convenient than SQLite for parsing and transforming JSON. You can select data directly from the file, as if it were a regular table. Or load it into a table and select from there - as you prefer.
select
json_extract(value, '$.code') as code,
json_extract(value, '$.name') as name,
json_extract(value, '$.rate') as rate,
json_extract(value, '$.default') as "default"
from
json_each(readfile('currency.sample.json'))
;
┌──────┬───────────────────┬────────────┬─────────┐ │ code │ name │ rate │ default │ ├──────┼───────────────────┼────────────┼─────────┤ │ AZN │ │ 0.023107 │ 0 │ │ BYR │ │ 0.034966 │ 0 │ │ EUR │ │ 0.011138 │ 0 │ │ GEL │ │ 0.0344 │ 0 │ │ KGS │ │ 1.131738 │ 0 │ │ KZT │ │ 5.699857 │ 0 │ │ RUR │ │ 1.0 │ 1 │ │ UAH │ │ 0.380539 │ 0 │ │ USD │ │ 0.013601 │ 0 │ │ UZS │ │ 142.441417 │ 0 │ └──────┴───────────────────┴────────────┴─────────┘
It doesn't matter how sprawling the JSON is, you can choose the attributes of any nesting:
select
json_extract(value, '$.id') as id,
json_extract(value, '$.name') as name
from
json_tree(readfile('industry.sample.json'))
where
path like '$[%].industries'
;
┌────────┬──────────────────────┐ │ id │ name │ ├────────┼──────────────────────┤ │ 7.538 │ - │ │ 7.539 │ - │ │ 7.540 │ │ │ 9.399 │ │ │ 9.400 │ │ │ 9.401 │ │ │ 43.641 │ │ │ 43.646 │ │ │ 43.647 │ │ └────────┴──────────────────────┘
CTE and set operations
, Common Table Expressions ( WITH
) , . ( parent_id
) — WITH
. «» .
with recursive tmp(id, name, level) as (
select id, name, 1 as level
from area
where parent_id is null
union all
select
area.id,
tmp.name || ', ' || area.name as name,
tmp.level + 1 as level
from area
join tmp on area.parent_id = tmp.id
)
select * from tmp;
┌──────┬─────────────────────────────────────┬───────┐ │ id │ name │ level │ ├──────┼─────────────────────────────────────┼───────┤ │ 113 │ │ 1 │ │ 1 │ , │ 2 │ │ 1586 │ , │ 2 │ │ 1588 │ , , │ 3 │ │ 78 │ , , │ 3 │ │ 212 │ , , │ 3 │ │ ... │ ... │ ... │ └──────┴─────────────────────────────────────┴───────┘
? : UNION
, INTERSECT
, EXCEPT
.
select employer_id
from employer_area
where area_id = 1
except
select employer_id
from employer_area
where area_id = 2;
? — :
alter table vacancy
add column salary_net integer as (
case when salary_gross = true then
round(salary_from/1.13)
else
salary_from
end
);
, :
select
substr(name, 1, 40) as name,
salary_net
from vacancy
where
salary_currency = 'RUR'
and salary_net is not null
limit 10;
┌──────────────────────────────────────────┬────────────┐
│ name │ salary_net │
├──────────────────────────────────────────┼────────────┤
│ - (Delphi) │ 40000 │
│ Scala / Java ( Senio │ 60000 │
│ Java / Kotlin Developer │ 150000 │
│ 1 │ 150000 │
│ C# │ 53097 │
│ 1 │ 80000 │
│ Java - (Middle, Senior) │ 100000 │
│ C#/ .NET │ 70796 │
│ / QA engineer ( │ 45000 │
│ │ 17699 │
└──────────────────────────────────────────┴────────────┘
.
? : , , , . , . , ( ).
.load sqlite3-stats select count(*) as book_count, cast(avg(num_pages) as integer) as mean, cast(median(num_pages) as integer) as median, mode(num_pages) as mode, percentile_90(num_pages) as p90, percentile_95(num_pages) as p95, percentile_99(num_pages) as p99 from books;
┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐
│ book_count │ mean │ median │ mode │ p90 │ p95 │ p99 │
├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤
│ 1483 │ 349 │ 295 │ 256 │ 640 │ 817 │ 1199 │
└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘
. SQLite - . , — . .
, . , :
. . , :
with slots as (
select
num_pages/100 as slot,
count(*) as book_count
from books
group by slot
),
max as (
select max(book_count) as value
from slots
)
select
slot,
book_count,
printf('%.' || (book_count * 30 / max.value) || 'c', '*') as bar
from slots, max
order by slot;
┌──────┬────────────┬────────────────────────────────┐
│ slot │ book_count │ bar │
├──────┼────────────┼────────────────────────────────┤
│ 0 │ 116 │ ********* │
│ 1 │ 254 │ ******************** │
│ 2 │ 376 │ ****************************** │
│ 3 │ 285 │ ********************** │
│ 4 │ 184 │ ************** │
│ 5 │ 90 │ ******* │
│ 6 │ 54 │ **** │
│ 7 │ 41 │ *** │
│ 8 │ 31 │ ** │
│ 9 │ 15 │ * │
│ 10 │ 11 │ * │
│ 11 │ 12 │ * │
│ 12 │ 2 │ * │
│ 13 │ 5 │ * │
│ 14 │ 3 │ * │
│ 15 │ 1 │ * │
│ 17 │ 1 │ * │
│ 18 │ 2 │ * │
└──────┴────────────┴────────────────────────────────┘
SQLite ( — ). INSERT
240 . CSV ( ) — 2 .
.load sqlite3-vsv create virtual table temp.blocks_csv using vsv( filename="ipblocks.csv", schema="create table x(network text, geoname_id integer, registered_country_geoname_id integer, represented_country_geoname_id integer, is_anonymous_proxy integer, is_satellite_provider integer, postal_code text, latitude real, longitude real, accuracy_radius integer)", columns=10, header=on, nulls=on );
.timer on
insert into blocks
select * from blocks_csv;
Run Time: real 5.176 user 4.716420 sys 0.403866
select count(*) from blocks;
3386629
Run Time: real 0.095 user 0.021972 sys 0.063716
, SQLite , . . write-ahead log ( ) . — , .
SQLite . , sqlite.org SQLite , (~200 ). 700 , 95% .
,
, «» . .
SQLite : json json_extract()
:
create table currency(
body text,
code text as (json_extract(body, '$.code')),
name text as (json_extract(body, '$.name'))
);
create index currency_code_idx on currency(code);
insert into currency
select value
from json_each(readfile('currency.sample.json'));
explain query plan
select name from currency where code = 'RUR';
QUERY PLAN
`--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)
. WITH RECURSIVE
, :
:
create virtual table books_fts
using fts5(title, author, publisher);
insert into books_fts
select title, author, publisher from books;
select
author,
substr(title, 1, 30) as title,
substr(publisher, 1, 10) as publisher
from books_fts
where books_fts match 'ann'
limit 5;
┌─────────────────────┬────────────────────────────────┬────────────┐ │ author │ title │ publisher │ ├─────────────────────┼────────────────────────────────┼────────────┤ │ Ruby Ann Boxcar │ Ruby Ann's Down Home Trailer P │ Citadel │ │ Ruby Ann Boxcar │ Ruby Ann's Down Home Trailer P │ Citadel │ │ Lynne Ann DeSpelder │ The Last Dance: Encountering D │ McGraw-Hil │ │ Daniel Defoe │ Robinson Crusoe │ Ann Arbor │ │ Ann Thwaite │ Waiting for the Party: The Lif │ David R. G │ └─────────────────────┴────────────────────────────────┴────────────┘
in-memory ? :
db = sqlite3.connect(":memory:")
:
db = sqlite3.connect("file::memory:?cache=shared")
( PostgreSQL). UPSERT
, UPDATE FROM
generate_series()
. R-Tree . , fuzzy- . SQLite «» .
, SQLite . , !
, SQLite — @sqliter