simrayz
Combining postgresql and neo4j
I’m using a Neo4j database to supplement my Postgresql database. Postgres is used to store the entities themselves (e.g. users), and should always be the one source of truth. However, I am struggling with figuring out how I make sure the Neo4j database consistent with my postgres database. I am using Ecto and Bolt.Sips as my database adapters.
How can I make sure that a user is only created/deleted/changed if the action is successful in both databases? Both Ecto and Bolt.Sips has transaction/rollback functionality, but is there a way to combine them?
This is what I have so far, but this would cause problems if the postgres transaction fails after the neo4j transaction has finished.
def delete_user(%User{} = user) do
Ecto.Multi.new()
|> Ecto.Multi.delete(:delete, user)
|> Ecto.Multi.run(:delete_neo4j, fn _, _ ->
Neo4j.Accounts.delete_user(user)
|> Repo.transaction()
First Post!
lpil
There’s no simple way to have transactional behaviour across two database. You could look into implementing patterns such as the saga pattern but this is a lot of work and easy to get wrong.
The more straightforward solution may be to manually write some undo logic for the Neo4j database that runs after a postgresql transaction fails, but this will be error prone and imperfect.
I would be tempted to remove the Neo4j database if possible and move the graph queries into postgresql using recursive queries.
Most Liked
yourpalal
I’m going to be contrarian and suggest that yes, this is possible!
You need to use a 2-step transaction in postgres (PostgreSQL: Documentation: 9.3: PREPARE TRANSACTION)
Basically it goes like this:
postgres: BEGIN TRANSACTION
neo4j: BEGIN TRANSACTION
...
postgres: PREPARE TRANSACTION
if failed, neo4j: ROLLBACK
neo4j: COMMIT
if failed: postgres ROLLBACK PREPARED
postgres: postgres COMMIT PREPARED
the key being the PREPARE feature in postgres. This allows you to almost commit a transaction, with the possibility of rolling it back. The transaction is pretty much committed at that point, and any database checks such as constraints etc. have passed. Then you can commit neo4j knowing the postgres data is safe, and then finally finish up the postgres commit by telling postgres that it’s okay to finalize that transaction.
I’m not sure what the performance implications are but this is the safest way to do it.
Edit: It looks like postgrex doesn’t have native support for this but I feel like it would probably not be that hard to either:
- make a PR To support it
- hack it into your codebase in some way
OvermindDL1
Well if there is a neo4j FDW plugin for postgresql, then postgresql could do the cross database transaction as well as any is possible. 
FDW plugins let you use another database from inside pgsql itself, I use it and it works well, is not a touch slower but hey joining across makes up for it! 
sorentwo
Storing data in both Neo4J and Postgres isn’t something I recommend. As mentioned earlier in this thread, something should be the source of truth and then you can replicate to another data store for querying.
There is a great talk on this exact situation by Glen Vanderburg at RailsConf a few years back (this was a project I was initially involved in): https://m.youtube.com/watch?v=Nz-aU3vOFbw
Last Post!
tjdam
Ohh, tons of food for thought there…! I will begin my experiments this weekend, awesome to know someone around here battle testing it 
, will definitely be in contact!
Somewhat related, are you aware of Seraph project?
I found it recently and I was wondering if someone like yourself with some experience handling raw Neo4j queries in production would find it valuable. In particular the idea of an interface that resembles Ecto but it’s built around graph database “mechanics”.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Develop your skills with books, videos, and courses.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"