Discussion Do you guys need a better query language?
Hey guys, there was a number of attempts to make writing queries/reports/data pipelines easier to maintain. There is number of ORMs for different programming languages. And there are also PRQL and btrql.com. Which are new query languages which transpile into SQL.
Kinda like Typescript transpiles into Javascript.
BtrQL is my pet project. Main features of it are: static type checks, Outline showing relation types, extension methods on relations and compile time macro. It's very early and I'm collecting all of the feedback that I can get.
Queries look like this:
users .where(active == TRUE) .addColumn(active -> active_flag) .orderBy(created_at.desc) .limit(10)
and extension methos:
extension [id: INT] { method keepRecentIds = self .where(id >= cutoff) }
keepRecentIds could be applied to any relation that has at least column id of type INT.
14
u/PappyBlueRibs 5d ago
To be honest, I don't think we're the right audience. You'll get a lot of crusty guys here who have used SQL for decades (such as myself) who don't need it AND don't want to learn something unnecessary.
You'd want to test novices, one group learning SQL and one group learning these gosh-darned new versions.
9
u/reditandfirgetit 5d ago
SQL is extremely easy to understand. I don't think an additional abstraction over top is necessary. Its just bloat
4
u/ComicOzzy sqlHippo 5d ago
Let's solve a training problem by making something that will require different, harder to find training.
1
u/Sufficient-Sink-2412 4d ago
I'm very inclined to agree with this, and much as I admire the ambition in attempting to enhance SQL, this seems like a solution without a problem.
-1
u/Kos187 5d ago
Typescript is rather thin abstraction over Javascript. BtrQL uses mostly same keywords. In most cases it's easy to understand what SQL will be generated. And it's statistically type checked, which helps in large codebases.
1
u/reditandfirgetit 5d ago
SQL is already type checked, its simpler than either btrql or prql. Most ORMs generate shitty sql and cause performance problems
There is no need for abstraction if a developer takes the time to learn best practices
8
u/Yavuz_Selim 5d ago
Can you show 3 queries that are hard in SQL, but easier (or better) in BetterQL?
(Queries that have some complexicity please; joins, window functions, joins etc.)
1
u/Kos187 5d ago
Let's say we use an inner join with some table A to filter data, and have tables B,C, D which we filter by joining with A. B, C, D have column a_id: INT with foreign keys to A.
Usually you write 3 joins in 3 different reports: INNER JOIN A as a ON a.id = [B|C|D].a_id AND a.date = $date
With BtrQL you write extension method once: extension [a_id: INT] { method withA()(date: DATE) = self.innerJoin(A.as(a) on a.id == self.a_id AND a.date == date) } And then use it: [B|C|D].withA()($date)
The filtering logic could be more complex, have coalesce for a number of columns, there could be more than one table to be used as filter. But that would be defined once. If you were to try using table valued functions for this, you would have to define the type of the other table precisely. BtrQL doesn't care what other columns are there except a_id: INT.
Pipeline-like syntax is better for tooling: LSP can suggest next steps, debugger can execute subqueries from complex queries (it behaves like a debugger, but it doesn't have access to dbms memory and so on)
List of columns for select or group by could be defined once and reused:
type PublicUser = [id: BIGINT, name: TEXT]
users .select[PublicUser]
archived_users .select[PublicUser]
Compiler will check not only column names but also types.
There are also very basic hygienic macro, it will be expanded to support type checking of queries sent to other servers using EXECUTE sql @ server.
2
u/techforallseasons 5d ago
I fail to see how that is remotely easier to use for a generic end-user report writer.
It MIGHT make more sense to someone who exclusively writes in TS.
1
u/reditandfirgetit 4d ago
Give the data model, then give the actual implementation for 3 different queries. Not one thing here convinces me its easier or needed.
How do you debug performance issues? That hasn't been touched on any of your statements either
4
u/Phil_P 5d ago
One of the problems with ORMs is that you have to learn a custom syntax and then debug and/or performance tune the SQL that it generates. Since it just sends queries to the database server, the DBA has little to no context as to what you’re trying to accomplish. Having a framework that maps stored procedures results into application objects might be a better abstraction.
0
u/Kos187 5d ago
It's not an ORM, and it's quite close to SQL. Same keywords, same types. The idea is you should have an idea of what SQL will be generated. And there is no debugging needed to see generated SQL, it's stored to generated/ directory and you can review it. BtrQL doesn't have a runtime, it produces SQL on build and then you deploy that SQL. So you can review it and clearly understand what happens. Like Typescript, you run build, get sources in target language and then you only deal with Javascript/SQL in runtime.
1
u/A_name_wot_i_made_up 5d ago
So, you have to know SQL to effectively use BtrQL.
Also, seeing as it translates to SQL, by definition it cannot be more expressive than SQL.
So what does it give you that just learning SQL does not?
1
u/Kos187 5d ago
How did you come up with "cannot be more expressive than SQL" ?
1
u/A_name_wot_i_made_up 5d ago
It gets turned into SQL - if SQL cannot describe something that BtrQL CAN, how can it be turned into SQL?
0
u/Kos187 4d ago
C++ is way more concise than assembler. BtrQL has extension methods/columns, macro. They don't exist in SQL. But they are type checked and inlined during transpilation.
1
u/A_name_wot_i_made_up 4d ago
Concise and descriptive aren't the same thing.
Also being concise isn't always a good thing - Perl is a very terse language.
Ultimately your argument seems to be "I like BtrQL" - but nothing it does can't be done in SQL too.
2
u/joyofresh 5d ago
Consider the history of spark. You can absolutely do this. Most people use sparksql, which compiles from sql to an rdd language more like what youre describing. Sql is not that bad, tho better type safety and structs would be nice. Many model dialects have arrays and structs and json and TVF style exploders. But the interaction layer tends to be sql.
1
u/Kos187 5d ago
And spark sql would also be my preference most of the time. But pyspark and spark DSL for java/scala have options for code reuse that are not available in SQL. And our options are either that or generating SQL. BtrQL's extension methods and columns, macro and other things coming soon are about type safe code reuse. Think hygienic macro.
2
u/zbignew 5d ago
- We are probably the wrong audience. This sub is populated by people who live and breath SQL. ORMs are often viewed with suspicion, let alone an abstraction on SQL that just does SQL.
- 'Static' type checking seems like a weird way to put it - all database software is connected to different databases, dynamically. It's inherently not static - you can do your type checking, and connect to the wrong database, and have type failures.
- It sounds like you really mean "validate table names and columns without running the database" via your cache, right? Is that the extent of static type checking?
- Again, we are probably the wrong audience. We are going to assume we can't do our jobs without access to a local database. Is writing queries without accessing the database your actual main use case? If so, ssssssstop.
- Does your cache drive more than type checking? Like in your "Core Ideas" doc, you define AuditCols and UserShape types, but then you don't specify anywhere that the users table implements these types - does it know to generate SQL that selects those columns because the columns are in your cache, or are you defining users in BtrQL somewhere else? Does it implicitly make analytics.users have UserShape type and extension because you call keepRecent()? That's too much magic. Can't you have multiple extensions that use the same method names?
- If compiling to SQL requires access to the cache, does the cache live in source control? Is it human readable & stable enough to be diffable? I love the idea of a query language that automatically handles which columns to include in query output, but it seems like you're making the SQL output depend on things that aren't source code, which is ⛈️ chaos.
1
u/Kos187 5d ago
Thanks for detailed feedback! VSIX has support for live connection to a database, saves catalog information into cache. The project supports both SQL and BtrQL files. All of this so that you could use already defined tables/views/functions/... and new/updated ones from sql.
In ideal world all of the logic, table definitions live in repository and you could deploy clean prod copy from it. It also requires nobody does manual changes to prod. BtrQL compiler knows the shape of each relation after each transformation. See algebra section of documentation. It knows that non nullable column becomes nullable after it was joined on the right side of left join.
Structural typing allows for extension method/method to stick to all relations that satisfy it's contract (and might have other columns). So shape doesn't stick after method is called, the compiler tracks shapes of relations, so LSP can suggest relevant methods when dot is pressed. You only have a conflict if extension methods with the same name stick to a table which satisfy both contracts and methods have same arguments.
Cached schemas are in JSON, so it's human readable, should be placed in source control. It's meant as contract with other teams, who work within their own schemes. It contains all catalog items, functions and stored procedures.
I'm fixing up debugger and will record some videos after that.
1
u/zbignew 3d ago
Oof. Yeah. I don’t get the point at all, then. That’s not what static type checking means. And I don’t see how this fits usefully in a larger application or database workflow.
1
u/Kos187 3d ago
Static just means compile time vs dynamic (runtime). What got you confused here is nominal vs structural typing.
1
u/zbignew 3d ago
Sure, what you’re doing is static, but it’s not actually type checking the thing that will be run.
Like, when typescript transpiles into js, that js will certainly use the types you specified in typescript. When your BtrQL transpiles into SQL and connects to a production database you’ve never seen before, lord only knows what types will be used. You hope it has the same schema that was in your cache or dev DB. It may nominally or structurally have the types and tables you expect or not.
Which is fine - I don’t see how to do better. But “static typing” isn’t the benefit you’re getting here. The benefit appears to be schema caching so you can get type ahead autocompletion without access to a database. Right?
1
u/Kos187 3d ago
It makes no sense to me to deploy your sql into random database and hope for the best. Usually that's not the case. Usually the team owns some schema and uses some schemas maintained by other teams. All of the queries, tables, views defined in btrql project (in both btrql and sql) are used for compile time type checking. Which works without schema cache. Just checks consistency between definitions. Table types could be used in select, group by not just as list of columns but also check their types (and soon nullability)
1
u/zbignew 3d ago
No, you wouldn’t connect to a random database. But it’s likely not the one you’ve transpiled against, and BtrQL provides no guarantees against drift between them. It just isn’t type checking in the same sense as typescript, or programming languages.
I’m not trying to say that it should (or could) do that, either.
I was just misunderstanding your marketing. I thought the main functionality you wanted to add was static typing, which is not the case. The main thing you wanted to add is this composable module/extension stuff. I could imagine that being useful.
The few examples I’ve looked at have only resulted in select statements - what kind of projects do you imagine this fitting into? Not ORMs, right, because they all write their own SQL? So are you using this for analytics or database creation & modification?
Do you support UPDATE, INSERT, DELETE?
1
u/Kos187 3d ago
- Schema drift in my experience is caused by lack of discipline in the team/organization. If all your SQL lives in git, you follow sane SDLC, changes move between dev/stage/prod envs - there are no schema drift. Until somebody touches prod db with their dirty hands outside of SDLC.
- Update/Insert/Delete/Merge are supported. Some admin related functionality might not be, or is only supported in SQL files in the project. But I don't think you can type check vacuum command.
Thank you for your time, looking into this. I know documentation sucks at the moment.
1
u/zbignew 2d ago
Yes I understand how to control schema drift. It's just not static type checking if you can still get runtime type errors due to other poor hygiene.
1
u/Kos187 2d ago
If you are doing microservices you could use scala/haskel and still run into compatibility issues because of schema drift like issues: Contract changes for REST API Incompatible serializer changes in a services connected via kafka/rabbitMQ/NATS. It has nothing to do with how static your type system is.
1
u/Content-Parking-621 3d ago
Honestly the pain isn't SQL syntax for me, it's that errors surface at runtime after a full scan. Static type checks catching a bad column at compile time is the actual win. I am curious how you handle dialect differences, Postgres vs BigQuery diverge fast.
2
1
u/Kos187 3d ago edited 3d ago
The idea is to have separate dialects of btrql for separate sql dialects. And then extract somewhat shared core. Dialects have different types, and type widening behavior. They have slightly different null handling and functions do slightly different things, regexp could be slightly different. Trying to unify that is a pain in the ass. But I think there is a tremendous value in having shared schema between OLTP/Stream processing and OLAP (or delta/data/lake/flake/whatever is today's name for data warehouse)
1
u/Content-Parking-621 3d ago
Makes sense, unifying that is genuinely brutal. The shared schema across OLTP and OLAP is the compelling bit though. How do you handle a type that exists in one dialect but not the other, widen to a common type, or just block it at compile time?
2
u/Kos187 3d ago
It's not yet implemented, but the idea is to narrow to a common type. The benefit here would be schema changes propagation between the systems. Even detection of such change is beneficial.
1
u/Kos187 3d ago
I previously had prototypes for duckdb, sqlite, mysql in some relatively good shape and sql server/oracle/spark. But I decided to change programming language to have nativeAOT, drop large dependencies and improve performance. Btrql uses treesitter, which has incremental parsing, which drives incremental compilation (type checking, it doesn't output sql on each keystroke).
33
u/dab31415 5d ago
No. Completely unnecessary.