thruflo
IntegrateDB - Database sharing toolkit for Postgres
Hi,
I’d like to introduce IntegrateDB – Intro — IntegrateDB v0.1.0 – a database sharing toolkit project that I’ve made in Elixir.
I’d love for some feedback and any thoughts on it. The idea is inspired by projects like React, Tailwind and LiveView, in that it aims to boost developer productivity by deliberately coupling concerns that “received wisdom” holds should be kept separate.
Basically integrating services directly through the database is usually seen as a smell, so we all build APIs and loosely coupled SOAs. But in many cases this can be overkill and actually if you could integrate through the database you could eliminate layers and go much faster. Especially if you’re making LiveView apps where you don’t need an API and you have Ecto at your disposal …
The problem is that having multiple services using the same data is a recipe for disaster – unless you have some way of declaring and controlling data dependencies whilst enabling schema evolution. Which (as you may have guessed!) is exactly what IntegrateDB does 
It’s a Phoenix-based web application that sits alongside a Postgres database. You interact with a JSON API to declare data dependencies and generate scoped db user credentials (which you then give to your applications for them to connect directly to the database — they don’t talk to IntegrateDB at all, it’s not a proxy or an extension).
It then adds a user defined function to your database which you can call from your migrations to validate that they don’t break any of your declared data dependencies. It also sets up and consumes a logical replication feed (hence being Postgres only for now), with the idea being to convert events that you register for into higher level notifications for your apps.
All of which is designed to allow you to break the rules and integrate straight through the database without shooting yourself in the foot.
The code is at GitHub - integratedb/integrate: Core IntegrateDB source code repository. · GitHub, the docs are at IntegrateDB v0.1.0 — Documentation and the website is https://integratedb.org
I’d love to get some feedback on the project.
Thanks,
James.
Most Liked
brightball
Here you go. I was in the car when I responded earlier.
“It’s not right for every situation, but several years ago I worked on a project with two separate code bases with recurring subscription systems. Due to a dispute with one of them and some limitations in services, we had to quickly request for every single user to manually update their subscriptions from one platform to the other. In order to make it as easy as possible, the new system needed to be able to know everything about the subscription on the old system so that users would only need to update their payment information and click a button. Our choices were to build an entire API on the old system with a legacy code base that nobody understood or to let the new database connect to the legacy database directly to perform joins. We ended up going with the second option and had a working version in production under two weeks with no issues. Everyone migrated successfully and the crisis was averted. Sometimes SQL is the best API.”
halostatue
This seems really good. The only comment I have on reading is that when you are able to move more toward security, it might be worth looking at what Hashicorp Vault does regarding temporary credentials for access so that the stakeholder bootstrapping process might be able to not have setup configurations that are wholly manual.
thruflo
Unless I misunderstood you want to give the user and password to a client in the web to access the database directly?
Yup, I think it’s a slight misunderstanding in that the user and password are meant to be given to an application, not to a client. I.e.: think a Phoenix app which you would typically provide a DATABASE_URL to, not a React client that might typically talk to an API endpoint.
Thanks for flagging up as I should make that clear. You definitely shouldn’t expose the db user credentials publicly 
Also, the cURL request can be done from a client in the internet?
IntegrateDB is an application you would deploy on your infrastructure (server / cloud / cluster) fairly close to / alongside your database. So you can lock down access as you prefer – network firewall, RBAC, reverse proxy, etc.
The API is also envisaged as something more for an operator to talk to (a CLI is on the roadmap) or to use as part of an provisioning workflow. It would be unusual for your application to talk to it.
Hope that makes sense
Last Post!
thruflo
@radiospiel you can totally use Integrate as is for this and the default behaviour with no claims is simply to provide each stakeholder application a db user that’s scoped to its own schema. However, you could also do this with a couple of auth related SQL statements.
I’m a bit snowed under at the moment but I’ll post here when I manage to get the time to restructure as planned
Popular in Discussions
Other popular topics
Latest Phoenix Threads
Chat & Discussions>Discussions
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
- #forms
- #api
- #metaprogramming
- #hex
- #security
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 for Elixir devs who love to ship. Start your free account.
Producing high quality Elixir screencasts since 2017.
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!"