Our great sponsors
-
JDBI
The Jdbi library provides convenient, idiomatic access to relational databases in Java and other JVM technologies such as Kotlin, Clojure or Scala.
-
-
InfluxDB
Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.
-
-
-
graphile-engine
Monorepo home of graphile-build, graphile-build-pg, graphile-utils, postgraphile-core and graphql-parse-resolve-info. Build a high-performance easily-extensible GraphQL schema by combining plugins!
-
WorkOS
The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.
-
-
Hasura
Blazing fast, instant realtime GraphQL APIs on your DB with fine grained access control, also trigger webhooks on database events.
-
safeql
Composable / async / functional / type-safe / parallel-pipelined queries and relations without SQL injection or N+1s. (by karmakaze)
-
moja
Composable computation pipelines for Java: Async, Lazy, Option, Try, Result, Multi (List), Stated, Reader, Logger, Writer.
-
-
Slick
Slick (Scala Language Integrated Connection Kit) is a modern database query and access library for Scala (by slick)
-
SaaSHub
SaaSHub - Software Alternatives and Reviews. SaaSHub helps you find the best software and product alternatives
I found JDBi[1] to be a really nice balance between ORM and raw SQL. It gives me the flexibility I need but takes care of a lot of the boilerplate. It's almost like a third category.
1. http://jdbi.org
To me this looks a lot like ecto https://github.com/elixir-ecto/ecto
Is there a significant difference?
The future isn't writing queries by hand or using ORM's. The future is reflection or code generation tools like https://sqlc.dev
Whether you use traditional queries or ORM's you still have to write all the model query code by hand in traditional systems. Projects like sqlc generate all the shapes/objects, validation, and everything else for you.
This is just not liking SQL, which is fine, but it's not 'the future of querying databases'. It's just a simplified language that is under-specified and doesn't really support even a fraction of the use cases of SQL, and therefore looks 'cleaner'. It's a bad abstraction.
Here is my attempt at a 'post orm' if anyone is interested, as a bonus it is fully implemented and some people actually use it: https://github.com/justinvanwinkle/Norm
- Hasura Remote Schema (https://hasura.io/blog/tagged/remote-schemas/)
If you want more control over the web API and you were going to fetch the data within your Python back-end and process it there, for some use-cases (not all, but some), there are options:
- pg_http (https://github.com/pramsey/pgsql-http)
Life is about trade-offs. Doing the work in SQL is not without its drawbacks, but it's also not without its benefits, and that's true for doing the work in a general-purpose language as well. Whatever the drawbacks of doing it in SQL, one of the benefits has got to be eliminating the impedance mismatch (for people who regard that mismatch as a problem, and the OP seems to be one such person). What I claim is that doing the work directly in the database shouldn't be ruled out in general (the specifics of a given use-case may rule it out in particular) any more the the other common patterns (API hand-written in Python, for instance) shouldn't be ruled out in general.
The OP didn't write about a UI and we don't know what their needs are, so for all we know they're writing a Python-based back-end API in REST or GraphQL which is being consumed by a mobile UI or a SPA UI in Reactor or Vue.js or whatever, such that that back-end API doesn't have to provide a UI. If the existing Python back-end doesn't provide a UI then any proposed substitute--including one in SQL--shouldn't have to provide a UI either, just an API. In that case, there are some ready-made solutions already available:
- PostgREST (https://postgrest.org/): REST API for PostgreSQL
- Hasura Remote Schema (https://hasura.io/blog/tagged/remote-schemas/)
If you want more control over the web API and you were going to fetch the data within your Python back-end and process it there, for some use-cases (not all, but some), there are options:
- pg_http (https://github.com/pramsey/pgsql-http)
Life is about trade-offs. Doing the work in SQL is not without its drawbacks, but it's also not without its benefits, and that's true for doing the work in a general-purpose language as well. Whatever the drawbacks of doing it in SQL, one of the benefits has got to be eliminating the impedance mismatch (for people who regard that mismatch as a problem, and the OP seems to be one such person). What I claim is that doing the work directly in the database shouldn't be ruled out in general (the specifics of a given use-case may rule it out in particular) any more the the other common patterns (API hand-written in Python, for instance) shouldn't be ruled out in general.
I want sum types.
I want a statically-typed way of constructing composable queries that follow SQL rather than reinvent a different thing. It doesn't have to be the same syntax but it has to be the same structuring.
I started writing one[0] and stopped before doing all the boilerplate code generation, having moved on from the JVM ecosystem for the time being. One thing it does is treat most things like sets so we don't end up with N+1 queries. Another trick it uses is collapsing constant expressions via an expression evaluation library[1].
[0] https://github.com/karmakaze/safeql
[1] https://github.com/karmakaze/moja
I want sum types.
I want a statically-typed way of constructing composable queries that follow SQL rather than reinvent a different thing. It doesn't have to be the same syntax but it has to be the same structuring.
I started writing one[0] and stopped before doing all the boilerplate code generation, having moved on from the JVM ecosystem for the time being. One thing it does is treat most things like sets so we don't end up with N+1 queries. Another trick it uses is collapsing constant expressions via an expression evaluation library[1].
[0] https://github.com/karmakaze/safeql
[1] https://github.com/karmakaze/moja
The Scala ecosystem has a few ways to do composable type-safe query building, e.g. Slick[0] or more recently Quill[1]. . I believe both also have ways to do compile-time string interpolation (e.g. sql"""select * from users where id = ${user.id}""") which generate prepared statements (I know Slick does prepared statements. Quill has similar macros but I haven't looked into how safe they are to use).
[0] https://scala-slick.org/
If you want a db tool which can be an ORM for your app, and drop down to a lower level dsl, while targeting specific features of the databases it supports, + having a "composable superset for building queries", there's [ruby sequel](http://sequel.jeremyevans.net/), which is the best tool of the kind you'll get for any proglang. Everything the author wants, minus the typrchecking perhaps, which is IMO shooting at the stars.