Hacker News Reader

Comment by rqtwteye | Hacker News Reader

I think SQL is pretty good but I would like to see a tighter integration with the application code and the database. Like managing the database source in the repository, integrate the database into tests easier, maybe express queries in the application language instead of SQL. You can do all of these to to some degree with things like ORM and LINQ but the database still feels very separate from the rest of the project.

I think SQL as a language could also be improved a lot to make it easier to understand. It looks and feels like FORTRAN 77.

View on HN

One way to have tighter integration between the database and the application logic is to abandon the application code altogether. Express the application logic right in the database using (custom) data types, domains, defaults, constraints, views, triggers, and procedures. Then you can delete much or all of the "application tier", delete the ORM, and simplify the entire stack. That's one way to avoid having "the database [feel] very separate from the rest of the project."

This make sense to me logically, but whenever I try to actually do it, it becomes a disaster.

Fair. I've only ever had success with this strategy as a solo hacker. It was once claimed to me that this would be a disaster on large teams and I had to admit that I had no evidence one way or the other. Don't know if that resonates with your experience. Just anecdotal.

application logic needs to live with application code in source control. I've not seen a solution that leverages the database the way you suggest and achieves that. It leads to people yeeting things into production and hoping they work because half the logic is not in front of them

The customers I work with tend to use database migration tools like Flyway or Liquibase, have their DDL in source control, run tests, and have fairly conventional CI/CD. As I said above, I've never personally worked on a large team doing this, but our customers seem to make it work.

Absolutely no reason you cannot keep your application logic in source control with this approach.

This idea is largely regarded as making the codebase less maintainable since it can be more difficult to test discrete logical units of the SQL code than doing so for the application code.

I'm aware of that belief, but I do not share it.

Sounds like we should be adding good testing frameworks to SQL then

pgTAP is pretty good for PostgreSQL, but I don't know what if any options are available for other databases.

https://pgtap.org/

This is what I like to do.

I agree with everything you said except putting business logic in procedures. That should be in application code instead in my experience. I have worked with too many vendors that put all their business logic in the DB. That always makes things more difficult for maintenance IMO. It may be easy to get something up and running for the first time, but it makes things 10x more difficult to change in the future.

That depends. I worked with customers 25 years ago who put loads of business logic in very long and involved T-SQL stored procedures in MSSQL or PL/SQL stored procedures in Oracle. That may have made maintenance more difficult for the database developers who wrote them. It did make maintenance more difficult for the Java and .Net developers when they had to look at them, but that may have been a skill issue. These days, between (custom) data types, domains, defaults, constraints, views, materialized views, and triggers, I think there's more opportunity to regard "stored procedures" as a measure-of-last-resort. When that's invoked, I think application of the "Single Responsibility Principle" can take you a long way.

This was the way things were done 20 years ago as far as I understand. Shame I mostly missed that (MySQL was flavor of the day, being free and fast and throwing a way a load of features, only to be repeated to an even greater extent by Mongo a decade later). I could see a lot of benefits to working like that.

That's a good point about MySQL. PostgreSQL wasn't quite ready for primetime at that time, and Oracle and MSSQL aren't free. That left MySQL to fill the gap, but it's not really built for this style of development.

You are really discussing an improvement in programming languages rather than in databases. Innovation in programming languages themselves tend to be in languages like Haskell which give you much more abstraction power. You should take a look at Haskell beam https://haskell-beam.github.io/beam/tutorials/tutorial1/ or Esqueleto https://github.com/bitemyapp/esqueleto and you will find that these libraries achieve much tighter integration between the database and the rest of the language. I'm especially fond of Beam: the way to define data types and tables, the way to write queries just feel incredibly intuitive to me.

> managing the database source in the repository, integrate the database into tests easier

These are much easier problems to solve. Many people have already solved it.

As I see it, we haven't really made any progress in terms of impedance mismatch.

Otherwise, jooq is a fine addition to the Java world, as it generates a type-safe DSL from your ddl file where every statically correct jooq/Java statement is also a statically correct SQL query.

I see it differently. In the 25 years I've been working in this industry I see a welcome trend toward doing more in the database, such that the impedance mismatch dissipates. This is from a low-water mark 20 years ago or so, when most Java developers I knew kept the database at arm's length, insisting it met them on their terms rather than the other way around.

https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...

https://supabase.com/

One way to eliminate the Java-SQL impedance (for example) mismatch is to delete Java altogether, along with JOOQ, Hibernate, and Spring (for example).

I think more code in the database could work but then the database should have better programming languages. The current SQL dialects are terrible for writing regular code.

That's a matter of taste. I find most of the business logic I need to express involves finding and transforming relational data, a task to which SQL is better suited than any general purpose programming language. Often, SQL is all that's needed. Less often, I have to resort to PLpgSQL, PL/SQL, or T-SQL. When that happens, it's not that bad.

Fundamentally the database is separate from the rest of the project. It's quite often a completely separate service hosted on a completely separate machine.

Do you feel the same about HTML, CSS, JavaScript, REST APIs, etc?

HTML, CSS, JavaScript, REST APIs are usually stored in a repository like the application code. Much easier to manage. I understand that the database is usually hosted separately but in most cases they are tightly coupled so should be managed together.

I store my database structure in the repository either as scripts or as migration code (and sometimes both)

I'm not sure what else you need. This seems like a basic tooling problem.

Reply on news.ycombinator.com