3 ms·

> but perhaps not at the expense of keeping business logic in separate places Perhaps the answer is to consolidate business logic in the database with constrai

> but perhaps not at the expense of keeping business logic in separate places Perhaps the answer is to consolidate business logic in the database with constraints. This also protects data from manipulation outside of an app, like with scripts or ETL. Putting more logic in the db often results in terse error messages though, so the app needs to deal with that and make them nicer for the end user. I wrote about one possible approach here - https://begriffs.com/posts/2017-10-21-sql-domain-integrity.html#improved-error-messages https://begriffs.com/posts/2017-10-21-sql-domain-integrity.h... > weak version control/deploy solutions There are solutions that allow you to store migrations in version control, apply them to different database targets, and run consistency checks. For instance http://sqitch.org/ http://sqitch.org/ but maybe you're familiar with it already and regard it as one of the weak tools. Thought I'd point it out though. > a new programming language to the stack plpgsql is somewhat gnarly, but it is well adapted for the database. You know what it's going to do, compared with managing mappings from some other language. I never tried LINQ though so maybe the mapping would be more pleasant than I'm imagining.

arkh 8y ago
> plpgsql is somewhat gnarly, but it is well adapted for the database. You can use python or perl to define your postgres functions. https://www.postgresql.org/docs/10/static/plpython-funcs.html https://www.postgresql.org/docs/10/static/plpython-funcs.htm...
- rleigh 8y ago
  They are slower (by orders of magnitude) than writing them in C or C++, or even PL/pgSQL. Calling into the interpreter for millions of trigger events can quickly become a major bottleneck. When I originally implemented https://pgxn.org/dist/debversion/ https://pgxn.org/dist/debversion/ for version numbering, I originally implemented it in Perl, then Python. The implementations were clean, but the performance of both was abysmal. After reimplementing it in C++ with a C interface, it runs like greased lightning. While this is a custom datatype with operators implemented as C functions, the same concerns apply to triggers which are invoked on every affected row.
combatentropy 8y ago
> plpgsql is somewhat gnarly I agree it doesn't look like Python or whatever, but it does look a lot like SQL. So if you're familiar with SQL, PL/pgSQL is easy to learn. To me it's just SQL with if-statements and loops.

arkh 8y ago

> plpgsql is somewhat gnarly, but it is well adapted for the database. You can use python or perl to define your postgres functions. https://www.postgresql.org/docs/10/static/plpython-funcs.html https://www.postgresql.org/docs/10/static/plpython-funcs.htm...

rleigh 8y ago
They are slower (by orders of magnitude) than writing them in C or C++, or even PL/pgSQL. Calling into the interpreter for millions of trigger events can quickly become a major bottleneck. When I originally implemented https://pgxn.org/dist/debversion/ https://pgxn.org/dist/debversion/ for version numbering, I originally implemented it in Perl, then Python. The implementations were clean, but the performance of both was abysmal. After reimplementing it in C++ with a C interface, it runs like greased lightning. While this is a custom datatype with operators implemented as C functions, the same concerns apply to triggers which are invoked on every affected row.

combatentropy 8y ago

> plpgsql is somewhat gnarly I agree it doesn't look like Python or whatever, but it does look a lot like SQL. So if you're familiar with SQL, PL/pgSQL is easy to learn. To me it's just SQL with if-statements and loops.