5 ms·
Show me your well-defined normalized tables, and I will know the rules for a valid state of the system. How, though, will I know the rules for a valid transitio
by cafard 10y ago
Show me your well-defined normalized tables, and I will know the rules for a valid state of the system. How, though, will I know the rules for a valid transition from one state to another? Have I seen some nasty stored procedures? Yes, a number of which I have written. But I have seen well thought out and useful ones also.
- scarface74 10y agoWith logic being in code: * it is easily unit tested * all source code is one place that can be branched, versioned, merged, deployed, rolled back, diffed, code reviewed, approved with pull request etc.
- adaml_623 10y agoWhy are your stored procedures not in the code repo?
- lmm 10y agoBecause deployment tooling for stored procedures sucks?
- jamra 10y agoStored procedures are easier to deploy than table schema. You just need to drop if exists and then create them. Table schema is the hard part since there is overhead associated with creating an index on an existing table and with renaming a column.
- spacemanmatt 10y agoSQL is code. Not sure how that escapes people.
- scarface74 10y agoHarder to unit test, harder to merge, harder to roll back...
- self-diversity 10y agoThat was true a decade ago. The tooling has caught up in most languages, so it's trivial to embed your SQL in application code and use it in an environmentally independent matter (eg to invoke in unit tests), provided you take the few minutes initially to set things up correctly instead of the all-too-common "fuck it we're a startup" manner people seem to love.
- agentultra 10y agoAnd you can unit test it too. http://pgtap.org http://pgtap.org
- scarface74 10y agoGuess which is faster for continuous testing - being able to mock out your Entity Framework context and use an in memory representation of your table via Lists or constantly running an ungodly stored proc against the database?
- agentultra 10y agoI can't say that our tests are slow. We can test our database continuously and independently from our various projects that utilize it. Our integration and smoke tests run continuously as you would expect.
- scarface74 10y agoYou can't tell me that you can run a full suite of database dependent tests as you can run a bunch of in memory tests that mock the database queries. How do you handle 5 - 10 devs working concurrently if they are all running tests?
- agentultra 10y agoIsolation I guess? I don't know what your experience is and why you think it cannot be anything but slow but... it's not? pgtap lets us unit test our SQL functions, triggers, views, etc. It's integrated with our change management system. It's fast enough and works well.
- jamra 10y agoYou work from local databases. You keep a migration script that sets up the schema and another migration script that sets up the data. If you create a feature, you must add to each script when merging. You can run unit tests by creating transactions, running the test, and then rolling back at the end. I do get your point, though. It's more convenient to be able to mock your database from your code.
- duozerk 10y agoOr you can simply implement it so that when your code runs it first checks that the stored procedures exist and have the same definition as the one stored in the source code; and to drop/recreate them if that's not the case. In which case the stored procedures are in your "main" code repo and "deploying" them simply means running the software. Though your point about unit tests stands.
- spacemanmatt 10y agoI like to use table-driven transition constraints. The basic design is, you have a table of valid transitions (begin state, end state) and a function that you can write a constraint with. It's the same as other code, but with better data locality.