Roadmap: Multiple Database Support

[denolfe]

Description

Support additional databases

Implementation Detail

• Implement an adapter in order to support additional databases

Potential Breaking Changes

None

Effort

High

Update 7/25/2023

We've made a ton of progress with a proof-of-concept that showcases how we are currently envisioning the translation of Payload field schemas to a relational database structure, and we're looking for feedback!

Give it a look:
https://payloadcms.com/blog/relational-database-table-structure-rfc

Update 5/23/2023

We believe we have the database adapter type determined. The idea is to expose a consistent adapter API so that any manner of database integration could be implemented. All interactions with the database within Payload should go through one of these adapter methods, so the idea is to be comprehensive.

Please share any feedback you have below!

export interface DatabaseAdapter<TSchema> {
  /**
   * Database specific properties
   */
  options: Record<string, unknown>

  /**
   * Open the connection to the database
   */
  connect?: ({ payload, config }: {payload: Payload, config: SanitizedConfig}) => Promise<void>

  /**
   * Perform startup tasks required to interact with the database such as building Schema and models
   */
  init?: ({ payload, config }: {payload: Payload, config: SanitizedConfig}) => Promise<void>

  /**
   * Terminate the connection with the database
   */
  destroy?: () => Promise<void>

  /**
   * Used to alias server only modules or make other changes to webpack configuration
   */
  webpack?: (config: Configuration) => Configuration

  // migrations
  /**
   * Output a migration file
   */
  createMigration: ({ payload }: { payload: Payload }) => Promise<void>

  /**
   * Run any migration up functions that have not yet been performed and update the status
   */
  migrate: ({ payload }: {payload: Payload}) => Promise<void>

  /**
   * Read the current state of migrations and output the result to show which have been run
   */
  migrateStatus: ({ payload }: {payload: Payload}) => Promise<void>

  /**
   * Run any migration down functions that have been performed
   */
  migrateDown: ({ payload }: {payload: Payload}) => Promise<void>

  /**
   * Run all migration down functions before running up
   */
  migrateRefresh: ({ payload }: {payload: Payload}) => Promise<void>

  /**
   * Run all migrate down functions
   */
  migrateReset: ({ payload }: {payload: Payload}) => Promise<void>

  /**
   * Drop the current database and run all migrate up functions
   */
  migrateFresh: ({ payload }: {payload: Payload}) => Promise<void>

  // transactions
  /**
   * Perform many database interactions in a single, all-or-nothing operation.
   */
  transaction?: ({ payload }: {payload: Payload}) => Promise<boolean>

  /**
   * Start a transaction, requiring commit() to be called for any changes to be made.
   */
  beginTransaction?: ({ payload }: {payload: Payload}) => Promise<boolean>

  /**
   * Cancel any changes since the beginning of the transaction.
   */
  rollbackTransaction?: ({ payload }: {payload: Payload}) => Promise<boolean>

  /**
   * Instruct the database to complete the changes made in the transaction.
   */
  commitTransaction?: ({ payload }: {payload: Payload}) => Promise<boolean>

  // versions
  queryDrafts: QueryDrafts<unknown>

  // operations
  find: Find
  findOne: FindOne
  create: Create
  update: Update
  updateOne: UpdateOne
  deleteOne: DeleteOne
  deleteMany: DeleteMany
}

type QueryDrafts<T> = (args: QueryDraftsArgs) => Promise<PaginatedDocs<T>>

type QueryDraftsArgs = {
  payload: Payload
  collection: SanitizedCollectionConfig
  where?: Where
  page?: number
  limit?: number
  sort?: string
  locale?: string
}

type FindArgs = {
  payload: Payload
  collection: SanitizedCollectionConfig
  where?: Where
  page?: number
  limit?: number
  sort?: string
  locale?: string
}

type Find = (args: FindArgs) => Promise<PaginatedDocs>

type FindOneArgs = {
  payload: Payload
  collection: SanitizedCollectionConfig
  where: Where
  locale?: string
  sort?: {
    [key: string]: string,
  }
}

type FindOne = (args: FindOneArgs) => Promise<PaginatedDocs>

type CreateArgs = {
  payload: Payload
  collection: SanitizedCollectionConfig
  data: Record<string, unknown>
  draft?: boolean
  locale?: string
}

type Create = (args: CreateArgs) => Promise<Document>

type UpdateArgs = {
  payload: Payload
  collection: SanitizedCollectionConfig
  data: Record<string, unknown>
  where: Where
  draft?: boolean
  locale?: string
}

type Update = (args: UpdateArgs) => Promise<Document>

type UpdateOneArgs = {
  payload: Payload,
  collection: SanitizedCollectionConfig,
  data: Record<string, unknown>,
  where: Where,
  draft?: boolean
  locale?: string
}

type UpdateOne = (args: UpdateOneArgs) => Promise<Document>

type DeleteOneArgs = {
  payload: Payload,
  collection: SanitizedCollectionConfig,
  data: Record<string, unknown>,
  where: Where,
}

type DeleteOne = (args: DeleteOneArgs) => Promise<Document>

type DeleteManyArgs = {
  payload: Payload,
  collection: SanitizedCollectionConfig,
  where: Where,
}

type DeleteMany = (args: DeleteManyArgs) => Promise<Document>

[SushyDev]

Not sure if this would be the right place to suggest but I would love for this to integrate with firebase firestore (and going off-topic, it would be cool if payload could integrate with more firebase features natively such as auth)

[gnusupport]

I would like it integrated with PostgreSQL

[fm-swe]

if the data inside the json column is just stored and retrieved there is no downside to it
There should be a slowdown if you want to filter based on the data inside a json column, although I remember postgres introducing some heavy patches in the past year(s) to handle data inside json column like any other column data

[gnusupport]

I have Website Revision System in PostgreSQL since decades. Tell me what you wish to design and I can help you.

1. Usually there are some types and statuses, I start with it. Maybe page has publishing status, maybe it is there, but not published, maybe published but hidden from sitemap. That is one table.

2. Page belongs to website areas. Thus there is table of website areas. Think of it as domains.
   Example:

                             ID   317
                           Note   "GNU Support"
                          Title   "GNU Support"
                            URL   "https://gnu.support"
               Default language   "English"
                        Company   nil
                         Author   "GNU Support"
                    Description   "GNU Support"
                       Keywords   "GNU Support"
                  Main category   "gnu.support"
                       Template   "RCD NEW: Free Liquid Templates for Discount Markdown and RCD Template Interpolation System"
          Default Main Filename   "index"
              Default File Type   ".html HTML File"
                Media directory   "/home/data1/protected/public_html/gnu.support/images"
                Files directory   "/home/data1/protected/public_html/gnu.support/files"
                 Links per page   10
                   Product Rows   4
                Product Columns   3
                     To publish   t
                   Virtual area   nil
                    Thumb Width   200
                   Thumb Height   160
            Maximal Image Width   600
                  Allow signups   nil
                   Allow logins   nil
              Default page type   "Markdown (Pandoc, espresso)"
                       Category   nil
                      Copyright   "All copyrights reserved"
                Managing Editor   nil
          Related pages heading   "Related pages"
          Include related pages   t

4. Page belongs to categories too, that is other table to be made.

5. Page may have tags. That is tagging table where pages are related to tags. There is table for tags only, and there is tag types table.

I gave you simple example to follow in designing tables.

Your program can be made so that it may access this or that database. It should be made in general manner while underlying driver is to obtain information from database.

[gnusupport]

It's all good guys!

We are excited about adding support specifically for Postgres as well. There are some pretty significant downsides though. For example, if we were to adopt Prisma, all of our "nested" field types like group, array, blocks, etc. would either need to be stored as JSON columns in Postgres, which is not ideal, and could mean that much of every single doc is simply stored as JSON in Postgres....

Alternatively, we could create different tables for every nested field type. Consider if you have a group field. In Mongo, boom, it just stores nested fields in the group. All on the same doc. In Prisma, that would ideally be done via a join on a different table, where the nested fields are placed within another table, and joined in. Same with Arrays, Blocks, etc. Not ideal as compared to Mongo.

Regardless, this is definitely something we're continuing to evaluate. Would love to know what you guys think about the above.

I consider that bad database design.

Each group shall be defined in groups' table. I understand that my thinking is SQL based, and not document based. But I do not agree that your way of planning the database belong to that type of database like Mongo, I actually see that your way of thinking rather belong to relational databases.

And I use rapid SQL development by using simple text templates (skeletons) in Emacs and it may be done with any editor to be quick.

[jmikrut]

Hey @gnusupport — I have also been around SQL for over a decade and I understand that storing lots of content in JSON columns is bad design. That is why I wrote what I wrote—describing how it is not ideal.

I think we are saying the exact same thing. But I really do appreciate you taking the time to describe this stuff in detail. It's awesome to see.

[gnusupport]

I use this GNU Emacs skeleton function: https://gnu.support/gnu-emacs/emacs-lisp/Emacs-Lisp-cf-sql-table.html

[gnusupport]

I am using table variables where I can insert any variable either as global variable or website or page variable. It is not hard to use "blocks", but I consider blocks also as variables. I also have templates, and template is interpolated with variables and text, what else. One can have small templates for HTML blocks. So one could have table blocks that references website domain (web area), including one could reference specific category inside of the web area, or even specific page. That way a block may be either global to area, or more specific to category and pages, so that other categories and pages may have also blocks with same name, but differently placed. Once you start creating first table with help of skeletons you will quickly understand it. I can help when you tell me you need it.

[t3hmrman]

I personally think the idea of how to structure the database can/should be punted for now.

IMHO the hard work here is abstracting your database such that different engines can be shimmed underneath, this enables you to implement some working version of Postgres and let the community contribute/suggest improvements over time.

Are you planning on making a wholesale switch from mongoose to prisma? Or are you willing to consider supporting both (or at least not disturbing the mongoose code)?

Personally I think the least impact way to do this is to:

1. Build an interface around what is expected of a backing store (this essentially ossifies and elucidates the contract between different parts of the app and the DB) -- lets say Database or Backend
2. Re-implement (mostly copy/make more references to) the current mongo implementation as the first implementation -- let's say MongoBackend
3. Allow for use of the new mongo backend (vs old straight mongoose backend) with an env var
4. Test the new MongoBackend with the built in mongoose usage side by side (great time to get a good complete DB-side test suite with the old vs/new backends which essentially run the same code)
5. Create RFC for PostgresBackend (architectural discussions, bikeshedding, etc all go in here)
6. Write v1 of the PostgresBackend
7. Write a one-time export script from Mongo -> Postgres (maybe even run it automatically if the right ENV is set?)

It's quite high-effort but that's how I would do it -- I think getting into the weeds on how to structure the Postgres tables might be a little bit premature.

This way, if someone comes along and says they want to support BigQuery or whatever for some reason -- you can easily tell them "sure, just write an engine and we'll consider including it", and see if they do it.

[stanlindsey-datatruth]

This is a very good approach. Iterative development, build towards the end state not just building the endstate, thinking about delivery and risk.

[gnusupport]

* James Mikrut ***@***.***> [2022-08-04 18:33]:

Hey @gnusupport — I have also been around SQL for over a decade and I understand that storing lots of content in JSON columns is bad design. That is why I wrote what I wrote—describing how it is **_not ideal_**. I think we are saying the exact same thing.

Yes, that is same thing I mean.

[taismassaro]

hey there, I was wondering if there are any updates on this topic.
my team is working on rewriting the legacy custom CMS (PHP + Postgres) that powers our product (medical content) and I found Payload today while researching headless CMS options. it sounds quite promising, but unfortunately MongoDB as a hard requirement could be seen as a big limitation for us to even try it out.

would appreciate if you can share what your timeline looks like so we can consider it. thanks!

[jmikrut]

Hey @taismassaro — we currently have this marked as roadmap-priority-3. We want to get through roadmap items with priority 1 and 2 before moving on to priority 3, so it will be a bit yet. It's certainly important to us, but we have some underlying stuff to do before getting to this work.

[taismassaro]

hey @jmikrut thanks a lot for the answer! I've been told the tech stack can be defined by the solution we find if it fits our requirements, so I'll keep looking into it! I just joined the Discord as well, will bring any further questions there!

[jmikrut]

Of course! If you have ANY questions at all, feel free to ping me directly on Discord. The team and I would be happy to help!

[bastiW]

I really would like to have the functionality of data migration in Payloadcms. I am not so much lacking in a wide range of different databases. I mostly miss an easy way to migrate data. E. G. There should be a default approach when renaming a field.

The lack of this functionality makes it hard to develop an enterprise product aside from POCs.

• This is what makes Django in the Python world strong.
• Prisma JS also handles this very effectively.

So I would love to see that Payload cms go all in for Prisma.

[jmikrut]

Prisma is 100% at the top of our radar for sure! Keep an eye out!

[UsVfxArtia]

Lol, it's funny that this is second in priority, and upload progress bar is first ))))))))

[jmikrut]

It's easy to come in and evaluate our prioritization efforts at a very surface level but there are many, many reasons why this is marked as priority-2 at the moment.

[kaitwalla]

Just throwing a note in - totally understand about prioritization but wanted to raise my hand as another "I love everything about this CMS but MongoDB is a hard pass" user here!

[charkour]

Very well said, @kaitwalla

[hades200082]

I may be ignorant of something here, but what is it about Mongo that makes you pass on it?

[ssyberg]

FWIW - I took the plunge on Payload for a project that had highly relational data and I kinda regret it - not because of Payload at all, but because hacking mongo to deal with complex relationships that would be dead simple in a SQL db has become a huge huge time sink. For a simple CMS I think there's not a ton of downside.

[TechSynthesis]

Can you tell us more about the type of project and what were you trying to accomplish.

[ssyberg]

More details here: #287 (comment)

[matthijs166]

It is for every project also always a pain in the ass to set up MongoDB especial for junior developers. We have some docker configs for it, but adds more complexity to the project.

We are now looking into https://github.com/nodkz/mongodb-memory-server for local development. Maybe an idea to include this standard in payload? This makes the barrier to enter for hobby projects lower and the setup for local development less complex.

We also use N8N in a lot of projects (they use Prisma) and there we have the option to use SQLite, it's one command, and you got it a n8n setup running.

[hades200082]

I've been using a docker compose file for new projects for some time and it makes it so simple for new devs to get up and running quickly.

What is it about mongo that is such a pain?

[aleksei-a-savitski]

The usage of the NoSql database is an advantage of Payload CMS. It simplifies deep object nesting a lot. As a user who switched from SQL-based CMS, I am delighted with this design choice. SQL-based CMS should abstract away the need to think about relations, but it is impossible. You still should assume the database structure for complex content types. So NoSql is a better choice for a CMS that allows developers to create custom content types.

I also did not have trouble setting up MongoDB for local development. The free remote instance is a perfect choice for the first try.

[fm-swe]

I think you spelled botch wrong 😜

[CanRau]

Hmm I still think Postgres gives you better relations while also giving you jsonb columns for deeply nested & indexable structures so still don't (yet) see the downside of relational here

[ssyberg]

Just want to throw two precedents in the mix here in case people are not aware of them. I've not done a deep dive in either of these, but they seem closely related to the potential paths so might be worth taking a closer look at these projects and learning what we can...

1. KeystoneJS has a lot of similarities with Payload (though we are much more excited by Payload for a number of reasons) and it may be worth taking some time looking at how they've integrated Prisma (the little tinkering I did was very promising)

2. This is probably much more interesting for this thread - a review of the decisions Statamic made when they implemented a database driver for their flatfile based content model has a lot of parallels with some of the paths suggested above (Statamic effectively goes the put it all in a JSON field route). From my pretty limited use of the Statamic database driver, I might consider their implementation an anti-pattern. Though it solves the issue of a large number of records that the flatfile solution just can't support, it doesn't address most of the underlying reasons why many of us prefer a RDBMS. The main things that come to mind that make us very nervous with Mongo: relationships and the associated complex queries, and search (and relatedly usage of opensearch/elasticsearch).

[ssyberg]

The advantage is leaving each driver to determine the best way to handle persistence. The less core Payload has to define, the better.

My point is the persistence structure itself has actual feature implications in the core of Payload, and I think core features being driver dependent is probably an anti-pattern for a framework.

[hades200082]

The advantage is leaving each driver to determine the best way to handle persistence. The less core Payload has to define, the better.

My point is the persistence structure itself has actual feature implications in the core of Payload, and I think core features being driver dependent is probably an anti-pattern for a framework.

If that's the case then it smells like a deeper issue.

The application core shouldn't care how the data is persisted as long as the driver delivers it back to the core in the same format as it was given it.

[ssyberg]

If that's the case then it smells like a deeper issue.

Yes! It's a deep issue and I'd suggest somewhat inherent to complex CMS development, especially with respect to dynamically nested fields - it's kind of the crux of the discussions above (e.g. #287 (reply in thread)).

The application core shouldn't care how the data is persisted as long as the driver delivers it back to the core in the same format as it was given it.

I think this ignores the fact that some things are easy in some database types and hard (or effectively impossible) in others. Mongo and MySQL are obviously far from interchangeable in most circumstances.

[DanRibbens]

I've been thinking about this problem and wondering if we could release a Payload database adapter test package. This could be used as a devDependency in each adapter and would make it easy for package maintainers to gaurentee feature support of core features in their own packages with a unified test suite that everyone can implement. It will also be hugely beneficial when Payload releases new field types or adds more features to existing ones in which the DB is concerned.

I agree @ssyberg, there will be some inevitable feature compability differences on edge cases. This is already true for how Payload has had to navigate some MongoDB API differences on AWS DocumentDB and Azure CosmosDB.

We have our work cut out for sure!

[hades200082]

The application core shouldn't care how the data is persisted as long as the driver delivers it back to the core in the same format as it was given it.

I think this ignores the fact that some things are easy in some database types and hard (or effectively impossible) in others. Mongo and MySQL are obviously far from interchangeable in most circumstances.

Nothing is impossible. Sub-optimal maybe but not impossible.

Ultimately though, leaving it up to the data persistence driver to decide how best to store a given structure in its database system is the best approach since the maintainer(s) of that driver would (presumably) be experts in that system.

[ssyberg]

The latest wagtail minor release adds dynamic field migration utilities: https://docs.wagtail.org/en/stable/advanced_topics/streamfield_migrations.html#making-structural-changes

I think this direction makes a lot of sense overall and really addresses some of the major pain points with dynamic fields.

[CanRau]

I think planetscale.com makes MySQL a very interesting target as well.
We're just migrating from Supabase to Planetscale, especially for branches, zero-downtime migrations, more standard serverless SDK etc.

[fm-swe]

I fail to see how outsourcing the grunt work of setting up the database(cluster) makes defining the data structures any easier.
Using services like planetscale might work for your own project, but ultimately you are just lying to yourself about the complexity of your setup by paying someone else

[CanRau]

I didn't imply planetscale or MySQL would make the data structures easier 🤔

Just wanted to add MySQL as an alternative db option along Postgres.

Also not sure why I'm lying to myself about complexity by paying someone and what this has to do with alternative db options for payload?

Maybe my comment came in as a little to sales pitchy for planetscale, sorry about that 🙏

[fm-swe]

yes I was reading it as an advertisement for planetscale, not mysql, sorry
The discussion in the past weeks here resolved around wether relational databases being feasible at all and wether to use json-columns or joined tables for complex objects.
I like json columns in postgresql, because you can just access values in json with ->> operator.
As far as I know accessing json data in mysql requires many different functions, so it felt really clunky and I dropped it quickly
Maybe you had a different experience?

[enijar]

You can also use the same syntax for JSON columns in MySQL, e.g.

select `json_column`->>"$.json_attribute"
from `table`

[glettl]

As @aleksei-a-savitski stated, it's true that NoSQL databases like MongoDB offer several advantages over traditional relational databases, such as the ability to store evolving data and complex data structures. For projects that require these features, MongoDB is the best fit.
I agree with the perspective that Payload CMS should continue to focus on supporting MongoDB. While there are many alternative CMSs that support relational databases, there may not be as many options for developers, like me, who require the unique features of NoSQL databases. By sticking to MongoDB, Payload CMS can provide a more specialized solution for these use cases - this is also an USP.

[ssyberg]

I don't think NoSQL and SQL support has to be mutually exclusive. Statamic has managed to support both flat file and SQL with different storage drivers. Of course that's non-trivial but it can be explored.

That said, I've worked on a lot of CMS projects in my time and basically every single complex project has lots of relational data. I would argue NoSQL / flatfile CMS' are best suited for relatively simple informational sites / blogs. IMO PaylaodCMS is built to be a robust framework to tackle complex problems - if I had to pick one I'd pick SQL. There are already plenty of good NoSQL CMS' out there for simpler projects, but there are no good modern CMS' that support SQL and highly complex applications.

[glettl]

Using MongoDB does not exclude the concept of relations in a CMS.
One notable benefit of using a NoSQL database like MongoDB, concerning PayloadCMS, is the ability to easily link fields to different collections, which can be quite useful. This is not always possible or as straightforward to achieve with SQL databases. Also the nested field can't be achieved in such an elegant way as it can be done with a NoSQL DB.

I'm afraid that we have to sacrifice such features because it's not as straight forward to implement these features in a SQL DB.

[ClearedFram3]

Supporting more databases seems like an easy way to really increase complexity. Bringing in Prisma to make that happen would really do so (using NextJS is the same—simplifies things upfront with tons* of complexity elsewhere). My thought is to either double down Mongo and have a big "Why MongoDB" call out OR say no to most requests (CosmoDB, MySql, etc) and only add support for Postgres/Sqlite. Drizzle could come in handy here. Wish yall the best!

[ssyberg]

I don't think it's an outrageous comment in the least. I think as has been discussed at length elsewhere in this thread, the upshot is supporting two extremely different approaches to data persistence means you lose out on most of the features of both (any feature they don't share... which is most of them). I think the end game here ends up being just using postgres/sql as a document database and no one who is asking for sql support is asking for that.

[debatz]

I think your points are very valid @ssyberg, and yet I still think it is an outrageous comment.

I'm not dismissing any of the concerns, they're very real even though I believe a project like Payload should offer deep support for both Mongo and Postgres.

I'm opposed to the rationale of the comment, and especially how it shows in the Nextjs dig. This way of thinking only works for startups and sidehustles, not for production.

[dominikzogg]

@debatz choosing mongodb is more than choosing another database, its about choosing a mindset: do i see my data as structured or as tables which i can combine as i want. Both have their pro and cons, but they aren't different tools for them same purpose.

[ssyberg]

Yes exactly ☝🏼 I'm sure we could come up with 100s of examples where supporting both mindsets gets hairy, but just a trivial example: how do you support cascading deletes in Mongo? In SQL it's trivial, in Mongo you'd have a pretty big lift to implement it properly (and I might add you end up building relational constraints into mongo which kinda defeats the purpose and you already get that for free in SQL so just use it). The more I think about it I honestly don't see the point in supporting both. Maybe someone smarter than me can help me see the light.

[debatz]

@dominikzogg I'm not advocating for one tool or another, or even mindsets, which I agree with.

I'm taking exception to the approach of crusading for a certain tech or mindset.

I've used Mongo and Postgres in both personal projects and work. There are companies and projects where you have both at the same time.

Pretending to favor one or another in the case of a project such as Payload is a bad mindset, like I said, it only works for reddit/youtube/sidehustles/startups. It's not serious to talk this way for production.

A case could be made for servicing a niche, focusing on that niche, and doing it well. But are we seriously going to posit that the Payload team cannot service the two biggest database "mindsets"? They'd just be burning a significant share of the market for no reason, and crippling the opportunity for developers using Payload on their personal projects to introduce Payload at their company.

This kind of thinking is why I've seen a financial institution using Mongo in the most ham-fisted way possible, just because it was the system architect's new fetish. It never ends well.

So to finish, I agree with you @dominikzogg, which is why I think a project like Payload should invest in deeply supporting both.

[marklonquist]

I would love to have postgres support. Even if it just uses jsonb as the underlying structure (so basically, mongodb in postgres)

[dominikzogg]

What ever will be done, hopefully it will stay mongodb first, there are already other projects at some point moved away from mongodb for the worse.

[DanRibbens]

Nothing to worry about with this! We can promise that MongoDB isn't going anywhere.

[JesseRigon]

This might be further afield on roadmaps. The implementation of the adapters is obviously first to accommodate multiple databases.

My question is if there has been any interests or questions about using multiple databases within one payload server. I don't think this would be feasible at the field level though that would be amazing. I'm imagining at the collection level.

for example, I might want to use postgres for transactions on an ecommerce shop or an invoices collection; mongodb for user profiles and theming; and even theoretically neo4j for storing crm and supply chain data.
All accessible from the same admin ui.

Would this be possible to use one adapter that connects to multiple databases? Or import multiple adapters in the config and be able to call them for different collections separately.

If this is a stupid question let me know. Still getting ahold of CMS systems in general and Payload specifically.

[buzzeins]

please stay focused on mongodb only.

[leikoilja]

Do we have any updates on the progress for postgres support? 🙄 💙

[DanRibbens]

Here is a status update since it has been too long.

We've done a ton of work to make Payload's database interaction completely agnostic in it's core. At the same time we are packing in new database features that give us feature parity with most backend frameworks. The timing of doing this all at once is a huge lift, but also clears a path for any new DB adapters to be complete and not require changes for a long time.

Here is an overview of the work & progress so far:
[x] Database interface and new pattern for db config into Payload
[x] Abstract Mongoose/MongoDB out of core, no imports from mongoose in core
[x] Changes to Payload init (non-breaking) to start, connect and assign the db to payload.db
[x] Build migration commands to manage DB changes (adapter agnostic)
[x] Add transactions for all operations, passing the same req
[ ] started Build drizzle database adapter to implement the new database interface, starting with sqlite
[ ] Handle dev workflow to migrate automatically while building (something Drizzle offers that we can call)
[ ] Drizzle postgres - handle any implementation differences from sqlite (supporting more features, ie: querying geo, json, etc.)
[ ] Update test config to run all officially supported adapters in CI
[ ] Write database docs
[ ] Release Mongoose plugin
[ ] Release Payload 2.0
[ ] Release Drizzle adapter (assuming we get past the PoC successfully)
[ ] Exhale

[DanRibbens]

@coxmi We're working through some prototypes right now. The goal is to not lean too heavily on JSON. As long as we can get the level of normalization, query performance and dev experience, we're going to make it work like a traditional relational database. More on this later.

[sannajammeh]

@DanRibbens Great stuff! Really excited for this. I hope schemas will be supported for Postgres. It would be awesome to be able to keep a full database of public schema for app related records and a content schema for the CMS. This way we can also manually query between the two without cluttering the public schema.

[acomagu]

Great work!
I know Drizzle is a cool ORM, can I just ask why it wasn't Prisma? Just for reference.

[acomagu]

Oh I found the reason in your blog.

No forced filesystem reliance

We want the Payload config to remain as the single source-of-truth for your schema design. We don't want you to have to maintain a Payload config, and then also deal with an auto-generated Prisma schema file or similar. Other headless CMS require you to design a schema within the CMS itself, but they turn around and dynamically generate a schema configuration for their used ORM which you also have to store in your repo, but NEVER touch. This is duplicative, messy, and error-prone.

-- Relational Database Table Structure RFC | Blog | Payload CMS

Thank you for detailed writing!

[MoSattler]

really pumped for SQLite!

[jmikrut]

Update from the Payload team - we've got a ton of stuff cooking, and I just published a blog post regarding how we might map our Payload field schemas (and their features) over to a relational database structure. We're now looking for community feedback!

https://payloadcms.com/blog/relational-database-table-structure-rfc

Take a look and let us know what you think. And thank you!

[jmikrut]

Hey @ssyberg this is all top of mind for us as well, and it's something that we are considering very deeply. We plan to do some significant performance testing when we have everything wired up within an actual Payload adapter as well. One area where we've had success limiting the amount of tables created is by just relying on a single table for all relationships of a given collection type, which is a win, but that leaves arrays and blocks as the two field types that could drive excessive tables.

being much more explicit about our set of fields and blocks, not defining them with flexibility, inline in a collection, but building them intentionally for reuse, outside of the collection context. That is the more "relational" way

Totally - it's worth echoing that this is entirely up to your schema design, and you could always leverage the Relationship field straight to another table instead of an array or blocks field if you want to keep the amount of array / blocks tables required to a minimum. That would work with Payload out-of-the box as-is.

Another thing to note is that in Directus / Strapi, they have some pretty serious limitations when it comes to using their equivalent of our more complex field types, including the array field.

For example, Directus goes straight to a JSON column for their array field, which means that you can't have relationships or localized content within array rows. We would never let that fly - in Payload, we use relationships within array rows constantly. Relations within a JSON column might be possible, but it could be argued that it would be more complex to join relations on JSON array column data.

Being that Payload arrays support things like relationships, localized fields within array rows, groups, etc, if we switched to JSON from that point on, then the way we store locales, groups, etc. would all be done in JSON at that point. So then we'd need to maintain separate approaches for fetching data and then "transforming" it into the shape that Payload APIs expect.

Example: is the relationship field on the top level of the document? OK, join it in. Is it nested in an array? OK, can't join it in that case so we have to load it in separately in the application layer. That could be a lot more complex internally as well as mentally for the engineer working on Payload projects as well.

At this point it's worth noting that we do plan to explore JSON column usage in Postgres, in specific, and may opt to leverage it significantly if it ends up being more performant. This is one thing that we really want feedback on. At this stage, we are doing SQLite as a "lowest common denominator" type approach, to make sure that all of the different structures Payload offers are theoretically possible to reflect in a "best practices" manner in SQL. From there, we'll opt in to more and more DB-specific features.

I can't promise that we will jump right to JSON columns, though, and the problems above are just a small snippet of what we are foreseeing now.

Really the biggest thing that we would need to determine where to "draw the line" between when to use a JSON column, and when to use a traditional table approach. Would we jump to JSON for group fields? Array fields? Block fields? Locales? We'd quickly get to a spot where we're basically replicating what we have in MongoDB, but in Postgres JSON columns. That feels like bad practice to me but as I said earlier, this is basically the #1 area where we're looking for feedback.

Lastly, one good note regarding table hell is that what we've seen from Drizzle thus far in terms of making a single query for all joins has made us pleasantly surprised. And they have some even further performance optimizations here, so we won't be manually populating separate tables - it will all be one query.

What do you think?

[ssyberg]

Sorry I never responded here @jmikrut - got very buried since this was posted. I'm eager to see where this landed in the next couple weeks! At the very least I think our project will be a pretty good guinea pig for worst case scenario in potential table hell 😆

[AshtarCodes]

@jmikrut Above you mentioned that SQLite would be supported as a "lowest common denominator". Any updates on SQLite support?

I'm trying to build a PoC for my employer using Payload (to convince them to use this instead of AEM or other CMS) but there's an issue preventing me from using the Postgres adapter (license issue), so I'm hoping to use SQLite for the PoC.

[marklonquist]

@jmikrut Above you mentioned that SQLite would be supported as a "lowest common denominator". Any updates on SQLite support?

I'm trying to build a PoC for my employer using Payload (to convince them to use this instead of AEM or other CMS) but there's an issue preventing me from using the Postgres adapter (license issue), so I'm hoping to use SQLite for the PoC.

I'm wondering what license issue you are talking about? Postgres is open source and has no paid license.

[AshtarCodes]

@marklonquist #4454 - a dependency of the postgres adapter is missing a license

[topmask]

I don't know how to use PayloadCMS because I'm not familiar with MongoDB. I only know how to use SQL. If PayloadCMS supports SQL, then I will use PayloadCMS.

[isaackoz]

I must say, I was in the same boat as you until I caved in. And let me tell you, I have had zero regrets. PayloadCMS has been lovely to work with and I have had zero issues using MongoDB in both development and production. As @dominikzogg said below, MongoDB is independent of Payload. In other words, you don't need to learn or understand how MongoDB works— Payload takes care of that under the hood.

Certain tech influencers cough cough theo will certainly give you the impression that using MongoDB is "bad" and that you should use some other fancy database solution instead. But unless you have strong reason not to use MongoDB, there isn't really a downside to using it. Being afraid of learning MongoDB is not a reason to avoid using Payload though.

[dominikzogg]

@topmask completely independ of payload i highly suggest to get used to mongodb. For basics it's much easier than sql databases. And for complex uses cases there are so called aggregations. Most of us developers wouldn't store data in table like structures if it wasn't fir thats what we once get teached.

[nothingnesses]

Does anyone know if Payload works with FerretDB? Supposedly, it's a drop-in alternative to MongoDB backed by PostgreSQL.

[dmythro]

Oh, thanks, gonna check it out

[nothingnesses]

I tried it out and there's still a few unimplemented MongoDB functions. That said, there's apparently already an existing issue in the FerretDB repo tracking support for Payload.

[DanRibbens]

🎉 Postgres support is here! Check out the 2.0 release.

[jaschaio]

With Payload using Drizzle, does this also mean it will support Cloudflare D1 like Drizzle? I don't see it mentioned anywhere in this discussion yet.

[zanhk]

Hi is there any news on this?

[MoSattler]

also super pumped for this.

[joevingracien]

Looking forward it

[BrianJM]

Can't wait to see D1 and SQLite support.

[janus-reith]

Anyway, it might not look like it but there has been progress and this will come out early January.

@DanRibbens I'm very interested in this, is there any more recent branch or any other discussion or issue that contains some details about what has been done or still needs to be done for SQLite to work?

[tyteen4a03]

Adding to this - currently I need to use TinaCMS because the requirements for the project meant that syncing content between repositories happens very often, which makes it unmanageable with Payload.

It would be awesome if at some point PayloadCMS can consider adding a Markdown/Git backend.

[MoSattler]

Really excited for SQLite! Any update yet?