4 ms·

Is this a thing? This feels like it throws separation of concerns out the window. When my business logic sits in a script, I get a lot of free tooling to help

Is this a thing? This feels like it throws separation of concerns out the window. When my business logic sits in a script, I get a lot of free tooling to help me (code editors, git, jira, etc.). If it sits in a db field, I lose all that along with the benefits (e.g., git blame) they provide.

atishay811 3y ago
You have to get it into the db with a a migration checked into your git repo.
devmor 3y ago
Yes, this reminds me of the horrible things WordPress did early on in my career - executing scripts found in a database, embedding HTML found in a database. Was also one of the most common vectors for vulnerability chaining.
bloaf 3y ago
https://www.dolthub.com/ https://www.dolthub.com/ So I get what you're saying, but at the same time that means your business data can't ever include formulas. I've worked in areas where the formulas were business data (i.e. modeling). You could hard-code all your models/correlations/aggregation functions. But then it becomes much harder to make UIs that do things like "find all formulas that involve this thermometer reading" or allow a technician to make temporary edits without risking them screwing up your entire application.
- BVegter 3y ago
  With our approach in Colbert, you can provide your table that holds a formula field with a corresponding tag field and apply formulas to your data using a WHERE clause on the tag field. This allows you to safely test different scenarios. Furthermore, in Colbert, you have the capability to inquire about the parameters used in each formula. For example, you can test the appearance of a specific parameter by querying: SELECT * FROM Employees WHERE params bonus contains 'Birthdate'
tyingq 3y ago
It seems to be a proposal for now. The article did throw in a couple of bullet points for that: - Security risks and best practices for handling user-defined formulas. - Is SQL injection a hazard when using formula fields?
PartiallyTyped 3y ago
You'd be surprised how common runtime SQL generation and execution is :')
phartenfeller 3y ago
You can store SQL queries in views that you can version in Git. In "enterprise" databases like Postgres, Oracle and MSSQL, you can also store statements in procedures/functions that you can also store in Git. We also do Git based migration scripts that create new / update existing views, tables, procedures, data, etc. This happens automatically on test servers and can be rolled out to production. It is somewhat tricky to build such an infrastructure and differs from "traditional" backend software, but it is what it is with Databases that heavily rely on state. The benefit is that you save a lot of bandwidth when you process the data on the machine where they live, and the architecture of the IT landscape is way simpler.
derefr 3y ago
That assumes your database cells aren't themselves revision-controlled (e.g. Dolt: https://www.dolthub.com/ https://www.dolthub.com/) But also, this is the same argument people generally give against using DB stored procedures. And, despite using sprocs often myself, I generally agree with them — the tooling around sprocs really sucks. Sprocs are "DB objects as state" (like rows in a table are state); but they could be a lot more than that. My secret dream, as an infrastructure engineer and DBA, is that, on boot, each version of each application that uses an RDBMS could "register" or "zero-install" a client schema with said RDBMS — i.e. a namespace of persistent virtual DB objects, unique to that build of that application — but where the underlying physical DB objects are not necessarily unique, instead being shared as needed between builds and even between applications. (Think: what would happen if you put the Plan9 designers in charge of the evolution of the SQL standard.) Such a "client schema" would specify, among other things, the full source code of any sprocs the application expects to be able to call. These would be able to be internally deduplicated — e.g. registered by content-hash into an sproc store on the DB (compare/contrast: Redis EVALSHA), and then "symbolically linked" to a particular name in the client schema. Ideally, in applications that use client schemas, every SQL query that's a static literal at app build time, would be compiled into a boot-time registration of, and runtime call to, such a registered virtual sproc definition. Think SQL "prepared statements" — but "prepared" at compile time on the client and ensure-bound to RDBMS-side equivalents at app boot time. (Why? Well, think about what RDBMSes could do to dynamically compile / optimize / JIT queries — and dynamically create/optimize indices to serve queries — if they could know the bounded set of in-use queries at any given time, and do long-term statistics collection on the behavior of those queries. No existing RDBMS is designed to do this, despite sprocs existing, only because they're so little used. Instead, almost all "app queries" to RDBMSes are just regular DML statements — which read to an RDBMS as one-offs needing to be freshly compiled and planned. RDBMSes could do so much better — if apps simply had the language to communicate their needs clearly!)

atishay811 3y ago

You have to get it into the db with a a migration checked into your git repo.

devmor 3y ago

Yes, this reminds me of the horrible things WordPress did early on in my career - executing scripts found in a database, embedding HTML found in a database. Was also one of the most common vectors for vulnerability chaining.

bloaf 3y ago

https://www.dolthub.com/ https://www.dolthub.com/ So I get what you're saying, but at the same time that means your business data can't ever include formulas. I've worked in areas where the formulas were business data (i.e. modeling). You could hard-code all your models/correlations/aggregation functions. But then it becomes much harder to make UIs that do things like "find all formulas that involve this thermometer reading" or allow a technician to make temporary edits without risking them screwing up your entire application.

BVegter 3y ago
With our approach in Colbert, you can provide your table that holds a formula field with a corresponding tag field and apply formulas to your data using a WHERE clause on the tag field. This allows you to safely test different scenarios. Furthermore, in Colbert, you have the capability to inquire about the parameters used in each formula. For example, you can test the appearance of a specific parameter by querying: SELECT * FROM Employees WHERE params bonus contains 'Birthdate'

tyingq 3y ago

It seems to be a proposal for now. The article did throw in a couple of bullet points for that: - Security risks and best practices for handling user-defined formulas. - Is SQL injection a hazard when using formula fields?

PartiallyTyped 3y ago

You'd be surprised how common runtime SQL generation and execution is :')

phartenfeller 3y ago

You can store SQL queries in views that you can version in Git. In "enterprise" databases like Postgres, Oracle and MSSQL, you can also store statements in procedures/functions that you can also store in Git. We also do Git based migration scripts that create new / update existing views, tables, procedures, data, etc. This happens automatically on test servers and can be rolled out to production. It is somewhat tricky to build such an infrastructure and differs from "traditional" backend software, but it is what it is with Databases that heavily rely on state. The benefit is that you save a lot of bandwidth when you process the data on the machine where they live, and the architecture of the IT landscape is way simpler.

derefr 3y ago

That assumes your database cells aren't themselves revision-controlled (e.g. Dolt: https://www.dolthub.com/ https://www.dolthub.com/) But also, this is the same argument people generally give against using DB stored procedures. And, despite using sprocs often myself, I generally agree with them — the tooling around sprocs really sucks. Sprocs are "DB objects as state" (like rows in a table are state); but they could be a lot more than that. My secret dream, as an infrastructure engineer and DBA, is that, on boot, each version of each application that uses an RDBMS could "register" or "zero-install" a client schema with said RDBMS — i.e. a namespace of persistent virtual DB objects, unique to that build of that application — but where the underlying physical DB objects are not necessarily unique, instead being shared as needed between builds and even between applications. (Think: what would happen if you put the Plan9 designers in charge of the evolution of the SQL standard.) Such a "client schema" would specify, among other things, the full source code of any sprocs the application expects to be able to call. These would be able to be internally deduplicated — e.g. registered by content-hash into an sproc store on the DB (compare/contrast: Redis EVALSHA), and then "symbolically linked" to a particular name in the client schema. Ideally, in applications that use client schemas, every SQL query that's a static literal at app build time, would be compiled into a boot-time registration of, and runtime call to, such a registered virtual sproc definition. Think SQL "prepared statements" — but "prepared" at compile time on the client and ensure-bound to RDBMS-side equivalents at app boot time. (Why? Well, think about what RDBMSes could do to dynamically compile / optimize / JIT queries — and dynamically create/optimize indices to serve queries — if they could know the bounded set of in-use queries at any given time, and do long-term statistics collection on the behavior of those queries. No existing RDBMS is designed to do this, despite sprocs existing, only because they're so little used. Instead, almost all "app queries" to RDBMSes are just regular DML statements — which read to an RDBMS as one-offs needing to be freshly compiled and planned. RDBMSes could do so much better — if apps simply had the language to communicate their needs clearly!)