til/graph database in mysql
I was recently playing around with a database for a hobby project and wanted to design a database schema for it.
Lots of looking about, and stumbled upon
(TAO) — The Object Association.
There is a lot in there, but the main moment was the object
and association
tables.
Let’s go from a relational model to a graph model!
… and turns out a lot has changed in the last 7~ years since I last touched MySQL. It now supports JSON data types, which made this journey so much easier.
This is it here, just 2 tables for everything 🎉
# The nodes or entities in the system
CREATE TABLE object
(
id char(16) NOT NULL default (lower(hex(random_bytes(8)))),
otype int NOT NULL,
data JSON,
created timestamp default current_timestamp,
updated timestamp default current_timestamp on update current_timestamp,
PRIMARY KEY (id)
) ENGINE = InnoDB;
# and the relationship between them
CREATE TABLE assoc
(
id1 char(16) NOT NULL,
atype int NOT NULL,
id2 char(16) NOT NULL,
data JSON,
time timestamp default current_timestamp,
PRIMARY KEY (id1, atype, id2)
) ENGINE = InnoDB;
I picked an int
here to store the type, as it could be an index into an application tier enum. Cheap to store, and
query.
But what about joins?
Joins happen at the application level, not the database level. Typically you ask the database to join say the users and the comments tables and return a subset of data, this is not what is happening here. Far easier to apply access or privacy policies in the application tier (as that is where that context lives), than in the queries.
Delight
It just makes caching so much easier as you can just cache a whole row. No need to juggle cache keys on a coarse selection of fields.
💡 It’s easier to cache a whole row, than to cache a selection of fields.
Patterns
Some patterns of working with this data model, because does take some getting used to.
Typically a query is something like;
JOIN <table> ON <condition>
, the graph model this is now written asJOIN <table> WHERE <condition>
.
Get an object by its id
SELECT id, type, data FROM object WHERE id = ?
Validate that a relationship exists
SELECT true FROM assoc WHERE type = ? AND id1 = ? AND id2 = ?
# eg: SELECT true FROM assoc WHERE type = 'AUTHORED' AND ...
# see if a user has a authored a post
Get a connection of nodes
Something like getting all blog posts by a user, `id2`` here being the object ID (remember join at the application level).
SELECT id2 FROM assoc WHERE type = ? AND id1 = ?
…and that’s it. You only really need to get a node, check for the existence of nodes, and get a collection of nodes. The rest is built-up abstractions in the application tier.