SQLite findings (on JSON and others)

JSON

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

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.)

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

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")

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).

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?

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.

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

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, and INT is not working, must be INTEGER).

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,

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).

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).

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);