about summary refs log tree commit diff
path: root/playbooks/sqlite3.md
blob: aec87f0b59ee984088e45be80ec3f89c00b12f89 (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
# SQLite3

Creating a reference for SQLite that I can access when I'm offline
(e.g. traveling in an airplane).

## Benefits

I enjoy using SQLite because it's lightweight and simple. Instead of networking
microservices, I can oftentimes just create a simple `db.sqlite3` file and get
significant mileage without much tooling overhead.

## Limitations

SQLite has some limitations; here are some of the limitations that I have encountered.

- SQLite **disables** support for `FOREIGN KEY` by default. Enable it with:

```
sqlite> PRAGMA foreign_keys = ON;
```

- SQLite has no `BOOLEAN` type; it uses 0 and 1 instead.

```
sqlite> SELECT TRUE;
TRUE
----------
1
sqlite> SELECT FALSE;
FALSE
----------
0
```

- SQLite has no `DATETIME` type; it uses `TEXT` instead.

```
sqlite> SELECT datetime('now');
datetime('now')
-------------------
2020-07-26 09:52:32
```

## Reference

The following should serve as a useful reference for working with SQLite.

### Schema

```sql
CREATE TABLE IF NOT EXISTS Movies (
  title TEXT NOT NULL,
  year INTEGER,
  PRIMARY KEY (title)
);

ALTER TABLE Movies ADD COLUMN rating DEFAULT 0.0;

DROP TABLE Movies;
```

### Queries

The following queries should come in handy as a reference:

```
sqlite> -- I'm using an intentionally incorrect date here for the subsequent UPDATE.
sqlite> INSERT INTO Movies (title, year) VALUES ('Toy Story 3', 2100);
sqlite> SELECT * FROM Movies WHERE year IS NULL;
sqlite> UPDATE Movies SET year = 2010 WHERE title = 'Toy Story 3';
sqlite> -- % is like .* in a regex
sqlite> DELETE FROM Movies WHERE title LIKE 'Toy Story%';
```

## Command Line

- Create a `~/.sqliterc` file with the following contents:

```
.mode column
.headers on
```

- To start an interactive session:

```shell
$ sqlite3 db.sqlite3
```

- To create a SQLite database from a `.sql` file:

```shell
$ sqlite3 db.sqlite3 <db.sql
```

- To reload changes to a `.sql` file while in an interactive session:

```
sqlite> .read db.sql
```

## Miscellaneous

- For a web-browser-based SQLite viewer, run the following:

```shell
$ sqlite_web db.sqlite3
```

- To import a CSV:

```
sqlite> .mode csv <table-name>
sqlite> .import path/to/file.csv <table-name>
```