Types & NULL

SQLite's type system is… chill. Sometimes too chill. NULL is where FE brains get hurt.

SQLite type affinity (the short version)

In Postgres/MySQL, columns have strict types. Put a string in an INTEGER column and you get an error (or a cast).

SQLite has type affinity - it prefers a type but will often store what you give it:

Declared	Affinity vibe
INTEGER	whole numbers
REAL	floats
TEXT	strings
BLOB	binary
NUMERIC	numbers / dates-ish

CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  amount REAL,
  label TEXT,
  flag INTEGER  -- often used as 0/1 boolean
);

Practical advice: still declare types. Your future self (and any ORM) will thank you. Just know SQLite won't babysit as hard as Postgres.

Booleans

There's no real BOOLEAN type in SQLite. People use INTEGER with 0 / 1, or sometimes TEXT. In app code, map to JS boolean.

Postgres has proper boolean. MySQL has TINYINT(1) traditions. Same idea, different costumes.

NULL is not a value

NULL means "unknown / missing". It is not 0, not '', not false.

SELECT * FROM contacts WHERE city = NULL;      -- never matches
SELECT * FROM contacts WHERE city IS NULL;     -- correct
SELECT * FROM contacts WHERE city IS NOT NULL;

Comparisons with NULL yield "unknown", which WHERE treats as not true.

-- if city is NULL, this row disappears from the result
WHERE city != 'London'

Need "not London, including missing cities"?

WHERE city IS NULL OR city != 'London'
-- or
WHERE COALESCE(city, '') != 'London'

COALESCE ≈ nullish coalescing

SELECT name, COALESCE(city, 'Unknown') AS city
FROM contacts;

FE: city ?? 'Unknown'.

Empty string vs NULL

CRMs love this bug.

Stored	Meaning people intend
NULL	we don't know / not set
''	explicitly empty

Pick a convention. I prefer NULL for "unknown" and avoid storing '' unless the product really wants a blank field. Mixing both makes filters sad.

Dates and times

SQLite has no dedicated date type. Common pattern: store ISO-8601 text:

created_at TEXT NOT NULL DEFAULT (datetime('now'))

Or unix integers. Just be consistent.

SELECT datetime('now');
SELECT date('now', '-7 days');

Postgres: timestamptz is the grown-up choice. Different syntax, same product need.

Why FE people should care

Your TypeScript type says companyId: number | null. The column allows NULL. The API returns null. The UI shows "-". That's one pipeline.

When someone stores 0 or '' instead of NULL, your if (contact.companyId) checks lie to you. Schema discipline prevents UI bugs. 🙌

Next: dashboard brain - Aggregates.