use postgres/mysql/mongodb as index data source

[erfanium]

in our company, we decided to keep our postgres database as the only single source of truth. so

• other databases like meilisearch should be synced with the primary source of truth database (in our case, the postgres db).
• our backend apps should never write into meilisearch directly, meilisearch data should be kept sync with postgres data.

i think this approach is already popular within other teams, so why not make the DX better for this approach?

How we fixed this problem

we have created an proxy docker app, that executes a select query every 30 seconds and gets the updated data for a specific meilisearch index data.

so by executing this query and manage the changed rows, we keep our meilisearch data in sync with our primary db.

MVP Idea

this feature can be a built-in meilisearch feature. so we can eliminate the need for a postgres-to-meili sync docker container

let's say we could create a index in meilisearch like this.

meilisearch.createIndex('users', {
  dataSource: {
    uri: 'postgres://user:pass@host/dbname',
    query: `
      select id, full_name, address, updated_at from "my_db"
      where updated_at >= $last_sync
    `
    syncInterval: 30, // sync every 30 seconds
  }
})

[macraig]

Hi @erfanium , thanks for taking the time to leave your feedback and explaining your current workaround.

Your feature request is interesting and could be helpful to other users. We'll keep this discussion open, allowing others who might be facing similar challenges to jump in and share their thoughts :)

However, we want to be transparent about our current priorities. Our team is presently occupied with other pressing commitments and projects, so we may not be able to address this specific request right away. Nevertheless, we really appreciate your suggestion and we're always on the lookout for ways to make our product even better based on what our users tell us.

We hope you understand our position and we'll certainly keep your idea in mind for the future. Please feel free to continue to provide input and share your thoughts - your input is extremely valuable to us!

[derekperkins]

This would be a great idea, but rather than polling, it should be implemented with CDC (change data capture)

• https://debezium.io/
• https://docs.airbyte.com/integrations/sources/mysql/#change-data-capture-cdc
• https://docs.airbyte.com/integrations/sources/postgres#configuring-postgres-connector-with-change-data-capture-cdc
• https://cloud.google.com/datastream

[macraig]

Thanks for the feedback @derekperkins !