4 ms·
JSONB is awesome, a lot of our tables end up being something like this: CREATE TABLE some_table ( id SERIAL PRIMARY KEY, uuid DEFAULT u
by mritchie712 6y ago
JSONB is awesome, a lot of our tables end up being something like this:
CREATE TABLE some_table (
id SERIAL PRIMARY KEY,
uuid DEFAULT uuid_generate_v1(),
user_id INTEGER,
details JSONB,
updated_on timestamp DEFAULT current_timestamp,
created_on timestamp DEFAULT current_timestamp
)
- nicwolff 6y agoI suggest adding `details_schema JSONB` and validating `details` against it. Now you can still add fields to `details` without deploying code changes, but every dev in the company won't throw random garbage in there – or expose that field as raw JSON in an admin panel somewhere.
- mritchie712 6y agoWe're still a pretty small so unlikely to happen, but great tip for a larger team.
- 10000truths 6y agoThe whole point of having a JSON type is to do away with needing a fixed schema, no? If you need to constrain the format of the JSON object, why not just represent it with more DB-friendly types in the first place?
- nicwolff 6y agoIn an enterprise production environment, it's much easier to update a JSON schema, which is stored as data, than to alter the DB schema. The point of having a JSON field isn't to let everyone with write access to the API stick whatever they want in there; it's to let the administrators of the service change what it can store without engineers deploying code changes and DB migrations.