The best way to build a modern backend + admin UI. No black magic, all TypeScript, and fully open-source, Payload is both an app framework and a headless CMS.
dbStore field property - removes schema creation for a field. Currently using Virtual Fields with Postgres bloats the db with unused columns / tables, as well may join empty arrays / blocks that are meant to be "Virtual". Realted discussion - https://github.com/payloadcms/payload/discussions/6270
Field config - dbJsonColumn
dbJsonColumn field property - stores a field, that should create additional tables (hasMany Text / Number / Select, Array / Blocks, Polymorphic / hasMany relationships) as a simple jsonb column.
Advantages
Doesn't bloat a DB with too much tables for blocks (you can have them a lot).
Migrations are simpler. therefore, chances to screw up them are less.
0 Joins and so the performance should be better. While originally you can have them a _lot
Easier to work with the Payload nested data from DB driver, just SELECT blocks from table
Considerations:
Querying on a field with dbJsonColumn. A solution would be is to implement a universal JSON querying that will work on any field type, that stored as a json column. Still you can use payload.db.drizzle.query to query on JSON with SQL.
Foreign keys aren't created and so when relationship is deleted, setting to NULL isn't performed automatically. The same as with MongoDB.
My thoughts:
I haven't ever query on a blocks field, so i don't see a point of adding so much complexity just because of this.
ryanleecode
commented
4 days ago
Description
The PR adds:
Field config -
dbStoredbStorefield property - removes schema creation for a field. Currently using Virtual Fields with Postgres bloats the db with unused columns / tables, as well may join empty arrays / blocks that are meant to be "Virtual". Realted discussion - https://github.com/payloadcms/payload/discussions/6270Field config -
dbJsonColumndbJsonColumnfield property - stores a field, that should create additional tables (hasManyText / Number / Select, Array / Blocks, Polymorphic /hasManyrelationships) as a simplejsonbcolumn.Advantages
SELECT blocks from tableConsiderations:
dbJsonColumn. A solution would be is to implement a universal JSON querying that will work on any field type, that stored as a json column. Still you can usepayload.db.drizzle.queryto query on JSON with SQL.NULLisn't performed automatically. The same as with MongoDB.My thoughts: I haven't ever query on a blocks field, so i don't see a point of adding so much complexity just because of this.
Passing
defaultValueinto a DB schemaNon undefined and non function
defaultValueis passed into a column builder. This 100% syncsdefaultValuewith a database and resolves a problem when adding a required field into a collection with existing docs, currently you can't do that unless you: lose data / modify migration manually. Discussions: https://github.com/payloadcms/payload/discussions/6691 https://github.com/payloadcms/payload/discussions/6048dbTypefor Number fieldAccepts:
'integer' | 'bigint' | 'real' | 'numeric'Simply stores number field in a specified column, by defaultnumeric.According to Postgres documentation calculations on numeric values are very slow compared to the integer types, or to the floating-point types.
Adds default validation with
Number.isInteger.Checklist: