SQLite 3.53

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select sqlite_version();


sqlite_version()


3.53.4


create table customer (id integer primary key, name text not null);
create table product (id integer primary key, name text not null, price decimal(6,2) not null);
create table orders (id integer primary key, customer_id int not null references customer, ordered_on date not null);
create table order_line (order_id int references orders, product_id int references product, qty int not null, primary key (order_id, product_id));
insert into customer values (1,'ann'), (2,'bob'), (3,'cat');
insert into product values (1,'widget',2.50), (2,'gadget',7.00), (3,'gizmo',12.00);
insert into orders with recursive s (n) as (select 0 union all select n+1 from s where n < 51) select n, 1+n%3, date('2026-01-01', '+' || (n*7) || ' days') from s;
insert into order_line select o.id, p.id, 1+(o.id*p.id)%5 from orders o cross join product p where (o.id+p.id)%2=0 or p.id=1;

3 rows affected



3 rows affected



52 rows affected



104 rows affected


-- new in SQLite 3.53: json_array_insert()
select json_array_insert('["ann","cat"]', '$[1]', 'bob') as inserted
     , json_array_insert('[1,2,3]', '$[#]', 4) as appended;


inserted
appended


["ann","bob","cat"]
[1,2,3,4]


# python demo
import sqlite3
c = sqlite3.connect("/data/fiddle.db")
sql = """select c.name, sum(l.qty * p.price)
         from customer c join orders o on o.customer_id = c.id join order_line l on l.order_id = o.id join product p on p.id = l.product_id
         group by c.name order by 2 desc"""
for name, revenue in c.execute(sql):
    print(f"{name:>4} {revenue:8.2f}  {chr(9608) * int(revenue // 40)}")
 ann   648.00  ████████████████
 bob   639.00  ███████████████
 cat   575.50  ██████████████

-- echarts demo
with recursive
  mon (m) as (select date('2026-01-01') union all select date(m, '+1 month') from mon where m < '2026-12-01')
, rev as
    ( select p.name, mon.m, coalesce(sum(l.qty*p.price),0) r
      from
        product p cross join
        mon left join
        (orders o join order_line l on l.order_id = o.id) on strftime('%Y-%m-01', o.ordered_on) = mon.m and l.product_id = p.id
      group by p.name, mon.m )
select
  json_object
  ( 'tooltip', json_object('trigger','axis')
  , 'legend', json_object()
  , 'xAxis', json_object('type','category','data',json((select json_group_array(substr('JanFebMarAprMayJunJulAugSepOctNovDec', 3*strftime('%m',m)-2, 3) order by m) from mon)))
  , 'yAxis', json_object('type','value')
  , 'series', json((select json_group_array(json_object('name',name,'type','bar','stack','revenue','data',json(data)) order by name) from (select name, json_group_array(r order by m) data from rev group by name)))
  ) as option;


option


{"tooltip":{"trigger":"axis"},"legend":{},"xAxis":{"type":"category","data":["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]},"yAxis":{"type":"value"},"series":[{"name":"gadget","type":"bar","stack":"revenue","data":[70,35,42,49,21,63,42,63,35,42,49,21]},{"name":"gizmo","type":"bar","stack":"revenue","data":[108,36,84,96,84,72,60,108,36,144,36,84]},{"name":"widget","type":"bar","stack":"revenue","data":[37.5,25.0,27.5,37.5,30.0,32.5,37.5,35.0,25.0,37.5,27.5,30.0]}]}


-- mermaid demo
select
  concat_ws
  ( char(10)
  , 'erDiagram'
  , ( select group_concat('  ' || t.name || ' {' || char(10) || x.cols || char(10) || '  }', char(10) order by t.name)
      from
        sqlite_schema t join
        ( select
            m.name
          , group_concat(concat_ws(' ', '   ', lower(iif(instr(c.type, '('), substr(c.type, 1, instr(c.type, '(') - 1), c.type)), c.name, nullif(concat_ws(', ', iif(c.pk > 0, 'PK', null), iif(exists(select * from pragma_foreign_key_list(m.name) f where f."from" = c.name), 'FK', null)), '')), char(10) order by c.cid) cols
          from sqlite_schema m, pragma_table_info(m.name) c
          where m.type = 'table'
          group by m.name
        ) x on x.name = t.name
      where t.type = 'table' and t.name not like 'sqlite%' )
  , ( select group_concat('  ' || m.name || ' }o--|| ' || f."table" || ' : "' || f."from" || '"', char(10) order by m.name, f."table")
      from sqlite_schema m, pragma_foreign_key_list(m.name) f
      where m.type = 'table' )
  ) as diagram;


diagram


erDiagram
  customer {
    integer id PK
    text name
  }
  order_line {
    int order_id PK, FK
    int product_id PK, FK
    int qty
  }
  orders {
    integer id PK
    int customer_id FK
    date ordered_on
  }
  product {
    integer id PK
    text name
    decimal price
  }
  order_line }o--|| orders : "order_id"
  order_line }o--|| product : "product_id"
  orders }o--|| customer : "customer_id"