I have been extremely successful at 2 jobs and several freelance contracts utilizing Microsoft SSDT database projects. It’s allowed us to completely store our database schema + stored procedures in the same solution as our code that uses it (plus any migration scripts that may need to be applied as well), and both are checked in and out of source control at the same time.
I actually saw a great implementation for handling them in rails with source control and I’d love to see this model ported elsewhere:
Source control / versioning is really the key.
This was essentially my solution. My problem implementing it stemmed from DBAs that didn’t want anyone else telling them how to do things.
Luckily now we have “DevOps”. Of course in my experience this has led to companies “having a devops person” which is a trendy way of saying “having a sysadmin with some external roles that we don’t pay as well and that has less experience.”
I’m not old enough to be as bitter as I am about software development practices 
Running a consultancy compresses many years of interaction with stupid processes into a short time, it turns out.
Still pretty new to Phoenix, so still have much to learn. I do find myself being somewhat disappointed with Ecto, though. It gives me a way of mapping external data to Elixir data structures, which is great and I also like being able to step up and down through database versions with the migrate features. What I don’t get is why ecto presents itself as a query builder for server side execution, unlike Linq which is client side. In any case, what I want client side in Phoenix, in addition to mapping, is anything that helps me do things like map reduce and other horizontally scalable things. Where Erlang/Phoenix seems to offer something new is the ability to parallelise multiple, simpler, safer, queries on data streams, something that SQL might be able to do more efficiently but in a more complex, less safe way. However, I want to be able to choose which approach I take. As it is, Ecto seems like a version of Esperanto; nice idea but why would you? Is it really that much more readable than SQL? It seems to me that you can only answer yes, by sticking to simpler queries. I may realise as I learn more that I have firmly grasped the wrong end off the stick, but these are my current thoughts.
Ecto does have a lot of limitations that I keep hitting on repeatedly (especially prefix borkness, that bugs the ever living crap out of me), but it is a safe way of accessing SQL and converting to/from native structures, and it excels at that.
And to be honest, SQL is far more expressive and is safer (especially in ecto form) than client processing, while also pulling down only the data you need instead of a ton more.
Tbh, if you need map reduce and all, neither RDBMS nor Ecto is the good choice. RDBMS tried to provide some stuff around that, but it tends to needs to basically store the data two time in different schemas, one adapted to the relational access and one to the “computation” access.
So it is not strange that Ecto does not try to solve that problem. It is a completely different problem. I would even argue that it should be handled by a completely different tool.
To go back on stored procedure, i got the feeling that people forget one thing : they mean that if you have more than one “service” using the same data schema/data base, you end up with an internal hidden coupling. If one need to change the stored procedure, you will need to evolve it in the next. In a world where database and data tend to outlive all the applications that uses it or the contrary, you may need to change database “on the fly”, store procedure is an additional hidden coupling that is going to bite you one day.
1 Like
It’s not that hidden and is way smaller burden compared to code directly being dependent on db schema.
In my experience, it is the contrary. It is hidden because it is not in source control nor deployment alongisde your code. And that makes it a bigger burden.
Also, i have to work with that type of stuff everyday in “more than a decade old” setup. You know how we use these db ? We find a way to SQLi through their stored procedure and all. It is easier and work better than spending two to three years trying to decypher it.
But who knows. Maybe we missed something important. Would not be the first time.
That can very easily be rectified by putting it into migrations. If anybody ever really gets into SP it would be great to see the approach taken by the squirm_rails library ported.
I ended up tinkering with a Postgres function recently when I ran into an issue with Ecto error trying to pass in raw SQL. The PG function was a simple way around the issue. Documented everything here.
EDIT: Forgot that this was a much older discussion and that those links were already included earlier.
My unease is partly to do with borders and technological alliances. Ecto offers several things, but in defining its own server-side query language it redraws the boundary between a web technology and a database one, rather than simply mediating between the two. I’m not sure that that is sustainable or a good use of energy. My experience of databases is that they are often a bit of a mess, for whatever reason, so you need all the tools you can get to get the data into into the shape you want.
It can also be that much data does not arrive via a UI, so you end up managing code in different places with different syntaxes. Report writers, such as Crystal Reports, are often going to rely on stored procedures. Who’s in charge when multiple web apps access the same databases?
Again, is it a good use case for MongoDB to treat it as a relational database? What of the graph databases that are becoming more popular? Can they all be treated as relational?
I suspect that we are going to end up with drivers for each database. It would be a pity to lose Ecto because it has a lot that is very handy. However, it seems (at this still early point in my education) far from being a no-brainer to keep it. A preferred model that comes to mind is the Elm one where you define client side data structures and provide decoders for a stream of json data to populate them.
The coupling issue that DianaOlympos mentions is a real one. But, I still want my database back, dude.
Fortunately my immediate needs are simple enough that I can comfortably use Ecto for my next project, which I am looking forward to, and will hopefully understand more about at the end. For the job I have just finished, though, it’s the guys from the last century that are in charge, and Ecto, in its current form, would be a pretty tough sell.
+1
We have them in migrations. Comparing between projects that use sps vs those that don’t making schema changes and various optimizations is way easier in the ones that do. Plus in reality none really switches dbs that often you pretty much can use PG unless you have very specific reasons not to. Whatever you use if the project is of decent size you really should have a very good idea of how the underlying Db works as it’s the only thing in your stack that is hard to scale horizontally.
In current project we basically have schema/functions folder that contains SPs and a custom migrator that updates function definitions when migrations are applied. Very convenient for version control.
1 Like
I am doing something similar my latest application, not to bad. I still believe in stored procedures. crosses-fingers
1 Like
You give up some things and gain others. I began my career building almost exclusively NoSQL apps (before the term existed) and can think of only one specific occasion where I had any need for an RDBMS. (Since I was in the IBM universe at the time, I used DB/2.)
However, Postgres is really amazing, and is one reason (along with Rails and Phoenix) I would now by default use an RDBMS first, if I didn’t know which sort of database would fit the eventual needs of my app. Postgres has some of the flexibility of a NoSQL db, and is rock-solid as a relational database. Unless you have a particular need that can only be handled by one of the other options, it’s hard to go wrong with it.
1 Like
How does it redraw the boundaries? I don’t understand how this is possible.
The responsibility for creating tables and shaping queries, is now all client side, and the language is all different. Database technology evolves, and ecto may or may not keep in step. Most databases , relational or not,as well as APIs, supply json data directly, so I would prefer to just deal with JSON at the boundaries of my app. It is a much simpler model than ecto. The only good use case for ecto that I see is the ability to create dynamic queries, which may in some cases also reduce the amount of data that you need to return from the server. Pretty good use cases perhaps, but for me not often required, and an esoteric complication that I think should be the exception, not the norm. It is a big learning curve to find yourself in a niche space. Apart from that, though, I am a big fan of Elixir/Phoenix.
The filesystem was the first major NoSQL store, it’s a hierarchical key/value storage system! ^.^
1 Like
