I wrote a gem to help test that unique indices match uniqueness validations in rails https://github.com/emma-borhanian/schema_expectations
If you only do rails-side validation, you have an (exploitable) race condition.
Simplify: move code into database functions
61–70 of 77 posts
Re: Simplify: move code into database functions
#62I'm currently reading Martin Fowler's "Patterns of Enterprise Application Architecture"[0] and I think that one of the main points that stuck with me is his recommendation for separation between the various layers and functions of a software system. Basically the whole system should be a layer cake of smaller modules/systems, as "opaque" as needed to one another, but interacting with each other solely through a well…
Putting more intelligence in the data layer doesn't break the modularity of the system, it also doesn't obviate the need for middle layers. The biggest thing it does enforce is a semantically clean gateway to the underlying data store. Databases can do a whole lot declaratively in way that is correct by inspection. Six lines of stored procedure can save hundreds in the middle tier while allowing other tools to access the database directly, w/o forcing everyone through the middle tiers.
Re: Simplify: move code into database functions
#63The reasoning always offered for treating the database as a dumb store with no intelligence has been about portability. Which is fine if you are selling third party software meant to be installed onto an existing customer's database. But for all other use cases, not using the features of the platform is a mistake. PostgreSQL offers so much! * Stored procedures in Java, Python, Lua, Perl, JavaScript etc [1] * Multicor…
Yea, I've never really understood the portability argument. How often do people switch between sql dbs?
Back in the dawn of time, customers bought big machines to run all the databases for an organization. So software sold into those orgs needed to support Oracle or MSSQL. Now with machines being much faster and whole app stacks being virtualized the impetus for having an application support multiple databases is greatly reduced.
Re: Simplify: move code into database functions
#64Earlier quoted context omitted.
Yes, this is a very bad idea. Due to the way that query planners work, it's not even a good idea to vary the shape of the dataset between testing and production. If you have a different (bigger, higher stddev, etc) distribution of data in column x, the planner will often complete a query differently
Agree, unless your need for a DB is quite trivial, then perhaps something like SQLLite for the devs and whatever else for production. However this breaks down quickly once you grow beyond needing very simple INSERT and SELECT type operations.
At which point one should just run SQLite everywhere, It is often excellent for datasets up to about a gig.
Re: Simplify: move code into database functions
#65The article makes some valid points but anyone who thinks databases should be used for everything and the kitchen sink should come fill out an application where I work. They could then spend the next 10 years debugging hard to troubleshoot database issues instead of easy to troubleshoot code issues. Jamming too much logic in the database is a recipe for disaster in my experience.
Re: Simplify: move code into database functions
#66Historyically, I've been against putting logic inside (MySQL) database since it's a bitch to version and maintain (especially cross-database-user) Maybe Postgres is a lot better, but for MySQL I'm staying the hell away from it
It does mean you need to treat your data schema and functions as code. My short checklist for doing database centric development (postgresql centered): * Database schema is kept under version control ( essentially all the DDL for a given database is kept in one place and managed as a unit ). You can use `pg_dump -s` to extract the schema from an extant database. * data types and functions are kept in separate files u…
> pg_dump -s
I wholehartedly agree that the schema should be versioned. But the schema dump is imho unreadable. I edit it manually instead, which has its own drawbacks.
Since I referr to the schema a lot, I think it is worth optimizing for readability.
Re: Simplify: move code into database functions
#67Earlier quoted context omitted.
> A serious problem with this approach is scaling. Stack overflow runs off one SqlServer. Do you think your app is going beyond that? If yes, maybe this approach will not work for you. If no, you are in the 99,99999% of all other sites/apps that are not as big as SO, FB or google and you can use this simple way to make your site or app.
StackOverflow's database doesn't need to work that hard because the vast majority of traffic is read requests for public, essentially static webpages that get re-rendered and updated a handful of times in their lifecycle. I'd guess 90% or more could be served by a reverse proxy without ever touching SQL Server or even the app tier. It would be easy to exceed its DB load with an application that serves unique content…
> It would be easy to exceed its DB load with an application that serves unique content to each user, is write-heavy, etc.
Just curious: did YOU ever reach that point? I mean where a well optimized db engine reached it's limit? Can I ask you on what website?
Re: Simplify: move code into database functions
#68Stored procedures, functions, triggers, etc, are very useful tools. They are used regularly in Microsoft SQL Server and Oracle systems. But, one has to be very careful to use them when warranted. The out-of-the-box tooling for debugging, maintaining, versioning, and testing code that lives in your database is not nearly as robust as what you have for most other development environments. The key here is to use the too…
Nice write-up.
> This code has to be maintained, versioned, etc.
Specifically to a Microsoft stack this specific pain point is outright negated, in fact, Microsoft's answer (Visual Studio SQLPROJ/Data Dude) exposes migrations for the horrible metahistory hack that they are. SQL projects are stored in source control as though they were C++/C#/what-have-you. Just like the rest of our code, the 347kloc SQL portion is handled on by CI and the migration (as well as the creation) scripts are generated for us - based on the schema differences (that it determines for us) between our last release and the current release.
So far as SQL Server goes, maintenance and versioning woes are an outright myth.
More pros:
4. A policy of using SPROCs only is a good way to significantly reduce the risk of SQL-related vulnerabilities. If developers are required to justify why they are sending raw queries to SQL they will have a hard time introducing e.g. an injection vulnerability.
5. A lot of the overhead of query execution (parsing, resolving objects, etc.) is done when you CREATE or ALTER the procedure, this can results in a performance benefit (especially if the application is chatty).
More cons:
5. Query plans for SPROCs are aggressively cached, in extremely rare circumstances this can play havoc with performance (direct contradiction of my own pro 5).
6. Triggers+family are invisible logic. They can cause confusion during debugging.
Re: Simplify: move code into database functions
#69Stored procedures, functions, triggers, etc, are very useful tools. They are used regularly in Microsoft SQL Server and Oracle systems. But, one has to be very careful to use them when warranted. The out-of-the-box tooling for debugging, maintaining, versioning, and testing code that lives in your database is not nearly as robust as what you have for most other development environments. The key here is to use the too…
The out-of-the-box tooling for debugging, maintaining, versioning, and testing code that lives in your database is not nearly as robust as what you have for most other development environments. This is the key point for me. I've seen SQL databases with 60k+ LOC in stored procedures, and functions (thankfully they avoided triggers). This code wasn't versioned, tested, or commented. While it's technically true that you…
> I've never seen it done.
See my comment[1]. We're doing it and it works beautifully.
Re: Simplify: move code into database functions
#70The reasoning always offered for treating the database as a dumb store with no intelligence has been about portability. Which is fine if you are selling third party software meant to be installed onto an existing customer's database. But for all other use cases, not using the features of the platform is a mistake. PostgreSQL offers so much! * Stored procedures in Java, Python, Lua, Perl, JavaScript etc [1] * Multicor…
Yea, I've never really understood the portability argument. How often do people switch between sql dbs?
At my first major PostgreSQL job, I got there shortly after they had abandoned MySQL. So there's that.