SQLite findings (on JSON and others)
Tags: sqlite, sql, nosql, database
Published: 2021-01-12 11:32:33

## JSON

Not that intuitive, but can be as powerful than MongoDB; although I am not sure how indexing work... (but it does work)

```sql
sqlite> CREATE TABLE user (phone);
sqlite> INSERT INTO user VALUES ('{}');
sqlite> CREATE UNIQUE INDEX idx_user_phone_x_y ON user(json_extract(phone, '$.x.y'));
sqlite> UPDATE user SET phone = json_set(phone, '$.x.y', 'b');
sqlite> INSERT INTO user VALUES ('{"a":"b","x":{"y":"b"}}');
Error: UNIQUE constraint failed: index 'idx_user_phone_x_y'
sqlite> UPDATE user SET phone = json_set(phone, '$.x', json('{"z":"c"}'));
```

You can also declare column type as JSON, but it will not check invalid JSON for you. (Unless you put a check constraint.)

```sql
sqlite> CREATE TABLE user (name JSON);
sqlite> INSERT INTO user VALUES ('{');
sqlite> CREATE TABLE user (name CHECK(json_valid(name)));
sqlite> INSERT INTO user VALUES ('{');
Error: CHECK constraint failed: user
sqlite> INSERT INTO user VALUES ('a');
Error: CHECK constraint failed: user
sqlite> INSERT INTO user VALUES ('1');
sqlite> INSERT INTO user VALUES (1);
sqlite> INSERT INTO user VALUES ('{}');
```

## SQLITE_MAX_VARIABLE_NUMBER

This used to be 999 by default in older versions, but it is now increased.

It was a big limiter for me, for Python, Node.js, and Android; where I don't know how to compile the SQLite driver myself.

In Golang's go-sqlite, it is as easy as

```sh
export CGO_CFLAGS='-DSQLITE_MAX_VARIABLE_NAME=1000000'
```

## Type checking vs Type coercion

It does not happen, unless you create CONSTRAINT. I know there is _affinity_, but I am not sure what that means. (especially for "NUMERIC")

```sql
sqlite> CREATE TABLE user (name INT);
sqlite> INSERT INTO user VALUES ('a');
sqlite> INSERT INTO user VALUES (1);
sqlite> PRAGMA integrity_check;
ok
sqlite> CREATE TABLE user1 (name CHECK(TYPEOF(name) = 'integer'));
sqlite> INSERT INTO user1 VALUES ('a');
Error: CHECK contraint failed: user1
```

Interestingly, SQLite have type coercion (which I believe, should be kept to minimum).

```sql
sqlite> CREATE TABLE user (name INT);
sqlite> INSERT INTO user VALUES ('1');
sqlite> INSERT INTO user VALUES (1);
sqlite> SELECT COUNT(*) FROM user WHERE name = 1;
2
sqlite> CREATE TABLE user (name);
sqlite> INSERT INTO user VALUES ('1');
sqlite> INSERT INTO user VALUES (1);
sqlite> SELECT COUNT(*) FROM user WHERE name = 1;
1
```

Maybe it is similar to COLLATION?

```sql
sqlite> CREATE TABLE user (name COLLATE NOCASE);
sqlite> INSERT INTO user VALUES ('a');
sqlite> INSERT INTO user VALUES ('A');
sqlite> SELECT COUNT(*) FROM user WHERE name = 'a';
2
```

## TIMESTAMP

I can make `updatedAt` column via trigger as well, but I will tell you, what if I put type as TIMESTAMP first.

```sql
sqlite> CREATE TABLE user (name TIMESTAMP);
sqlite> INSERT INTO user VALUES (strftime('%s','now'));
sqlite> SELECT * FROM user WHERE name = 1610445191;
1610445191
sqlite> SELECT * FROM user WHERE name = '1610445191';
1610445191
sqlite> SELECT * FROM user WHERE name LIKE '161%';
1610445191
```

About the trigger that I use, it's

```sql
CREATE TABLE user (name, updatedAt TIMESTAMP DEFAULT (strftime('%s','now')));
CREATE TRIGGER t_user_updatedAt BEFORE UPDATE ON user
FOR EACH ROW
WHEN NEW.updatedAt = OLD.updatedAt
BEGIN
  UPDATE user SET updatedAt = strftime('%s', 'now') WHERE ROWID = NEW.ROWID;
END;
```

## PRIMARY KEY

The only that is auto is `INTEGER PRIMARY KEY` ([don't need autoincrement](https://sqlite.org/autoinc.html), and `INT` is not working, must be `INTEGER`).

```sql
sqlite> CREATE TABLE user (id INT PRIMARY KEY, name);
sqlite> INSERT INTO user VALUES (1, 1);
sqlite> INSERT INTO user VALUES (1, 1);
Error: UNIQUE constraint failed: user.id
sqlite> INSERT INTO user VALUES (null, 1);
sqlite> INSERT INTO user VALUES (null, 1);
```

I have never known that PRIMARY KEY can be repeatedly NULL...

Of course,

```sql
sqlite> CREATE TABLE user (id INTEGER PRIMARY KEY, name);
sqlite> INSERT INTO user VALUES (null, 1);
sqlite> INSERT INTO user VALUES (1, 1);
Error: UNIQUE constraint failed: user.id
```

BTW, you don't need explicit PRIMARY KEY. ROWID is already autogenerated. (If there is no `INTEGER PRIMARY KEY ROW`).

```sql
sqlite> CREATE TABLE user (id INT PRIMARY KEY, name);
sqlite> INSERT INTO user VALUES (null, 1);
sqlite> INSERT INTO user VALUES (1, 1);
sqlite> .header 1
sqlite> SELECT *, oid FROM user;
id|name|rowid
|1|1
1|1|2
sqlite> SELECT *, oid oid FROM user;
id|name|oid
|1|1
1|1|2
```

This looks interesting...

- https://stackoverflow.com/questions/3906811/null-permitted-in-primary-key-why-and-in-which-dbms
- https://www.sqlite.org/lang_createtable.html#primkeyconst

Nonetheless, PRIMARY KEY is automatically unique (but accept NULL).

```sql
sqlite> CREATE TABLE user (id TEXT PRIMARY KEY, name);
sqlite> INSERT INTO user VALUES ('a', 1);
sqlite> INSERT INTO user VALUES ('a', 1);
Error: UNIQUE constraint failed: user.id
sqlite> INSERT INTO user VALUES (null, 1);
sqlite> INSERT INTO user VALUES (null, 1);
```