search

holdfenytolvaj / pogi

Javascript library for PostgreSQL and node.js
MIT License
140 stars 15 forks source link

support for read replica #18

Open tyriis opened 5 years ago

tyriis commented 5 years ago

For my daily work, it would be very nice to have an option to define read replicas. A possible way can be to add a flag to the query execution, to mark a query as read-only, so it is not needed to have a high amount of logic for this feature. On the other hand side, the developer is able to decide what queries to route to the replica.

holdfenytolvaj commented 5 years ago

This again, definitely sounds like an interesting feature, how would be the ideal api look like for you?

tyriis commented 5 years ago

This is a real good question :)

I use raw SQL queries all the time, so not sure if it fulfill all the needs. Currently, I have worked out a temporary solution by just creating a second instance, with a different host. As a developer i have to decide what query to send where, this is not really smart, but works for me so far.

To come back to the question, I think something inside the connection options, could add additional replica servers. F.e. replicas: [{connectionOptions}, ...] where all values from the parent connectionOptions are used and can be overwritten in the replicas[n].connectionOptions.

The other part would be a smart query detector, that is able to determine if a select is made or a write operation (so all read operations can be routed to the replica and all write to the default). As an easier to achieve, 1st iteration, you can let the developer decide when using raw queries, if its a read or write. F.e. with an optional flag (useReplica?: boolean). If no replica is set and Flag is set it should fall back to the master.

I hope it helps, and please let me know if you have a smarter solution/idea.

holdfenytolvaj commented 5 years ago

I'm going on vacation soon, but in June-July I will work on it.