5 ms·

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 re

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.

kccqzy 2y ago
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/ https://haskell-beam.github.io/beam/tutorials/tutorial1/ or Esqueleto https://github.com/bitemyapp/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.
maweki 2y ago
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.
- dventimihasura 2y ago
  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/c8fb81fe8a7e5df038158bdfe0f06dbb https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f... https://supabase.com/ 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).
  - rqtwteye 2y ago
    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.
    - dventimihasura 2y ago
      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.
dventimihasura 2y ago
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."
- panstromek 2y ago
  This make sense to me logically, but whenever I try to actually do it, it becomes a disaster.
  - dventimihasura 2y ago
    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.
- deleted 2y ago
  [deleted]
- sethammons 2y ago
  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
  - dventimihasura 2y ago
    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.
wvenable 2y ago
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?
- rqtwteye 2y ago
  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.
  - wvenable 2y ago
    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.
deleted 2y ago
[deleted]

kccqzy 2y ago

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/ https://haskell-beam.github.io/beam/tutorials/tutorial1/ or Esqueleto https://github.com/bitemyapp/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.

maweki 2y ago

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.

dventimihasura 2y ago
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/c8fb81fe8a7e5df038158bdfe0f06dbb https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f... https://supabase.com/ 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).
- rqtwteye 2y ago
  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.
  - dventimihasura 2y ago
    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.

dventimihasura 2y ago

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."

panstromek 2y ago
This make sense to me logically, but whenever I try to actually do it, it becomes a disaster.
- dventimihasura 2y ago
  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.
deleted 2y ago
[deleted]
sethammons 2y ago
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
- dventimihasura 2y ago
  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.

wvenable 2y ago

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?

rqtwteye 2y ago
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.
- wvenable 2y ago
  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.

deleted 2y ago

[deleted]