SQL

The {exec} sql directive allows executing SQL in the browser. Each block is executed in a new, empty database.

select concat('SQLite ', sqlite_version()) as Database;

Database definition

A database can be defined as a named {exec} sql block, to be referenced in the :after: option of other blocks.

create table countries (
  country text not null,
  country_code text not null,
  dial_code text not null,
  capital text not null,
  population int not null,
  food text
);
insert into countries values
  ('Switzerland', 'CH', '+41', 'Bern', 8776000, 'fondue!'),
  ('France', 'FR', '+33', 'Paris', 67970000, null),
  ('Germany', 'DE', '+49', 'Berlin', 83800000, null),
  ('Italy', 'IT', '+39', 'Rome', 58940000, null),
  ('Austria', 'AT', '+43', 'Vienna', 9042000, 'Kaiserschmarrn'),
  ('Lichtenstein', 'LI', '+423', 'Vaduz', 39327, null);

Queries

The results of the first select statement in each {exec} sql block of the :after: and :then: sequence are displayed as tables.

select * from countries;

The SQL code can be hidden by adding :class: hidden.

select * from countries where country_code = 'CH';

Empty results

select * from countries where false;

Wide results

create table wide (
  column0 text,
  column1 text,
  column2 text,
  column3 text,
  column4 text,
  column5 text,
  column6 text,
  column7 text,
  column8 text,
  column9 text,
  column10 text,
  column11 text,
  column12 text,
  column13 text,
  column14 text,
  column15 text
);
insert into wide values (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

select * from wide;

Tall results

The height of large results tables can be limited with :output-style:.

create table tall (value integer);
insert into tall values (0), (1), (2), (3), (4), (5), (6), (7);

select t1.value, t2.value from tall as t1, tall as t2;

Mutations

update countries set food = 'baguette' where country_code = 'FR';
select * from countries where country_code = 'FR';

SQL errors

select * from unknown_table;

Foreign key constraint enforcement is enabled by default.

create table users (id integer primary key);
create table orders (user integer, foreign key (user) references users (id));
insert into orders (user) values (1);