Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

It's not you, it's SQL

stack.convex.dev

Post reply on HN
Print view View original

31–40 of 84 posts

Re: It's not you, it's SQL

#31

Post by cfeduke »

Justsignedup wrote: post #25

My biggest issue with sql has and always will be the lack of definitions. There's no way to express data structure knowledge in sql, only relations and keys. Discoverability can be quite lacking. And every time I want to join table A to B I have to re-define everything, because SQL doesn't store that. ORMs help. They help because they encode relationships in meaningful ways. A has many Bs, so A.B works, and I don't n…

You can sort of fake the joins between two tables and avoid the ceremony with `natural join` assuming you've named the columns the same. (This doesn't work if your team has followed a normal pattern of "table1.id" with a FK in "table2.table1_id".) It's more like a hack and it falls apart as soon as someone starts mucking with column names [what evil person would ever do a thing like that...] or happens to share unrelated columns with the same name across two tables. So yeah, probably don't use it, except maybe to save yourself some time writing OLAP queries but increase your time spent debugging those same queries. :)

Re: It's not you, it's SQL

#32

Post by cube2222 »

If I understand correctly this is basically trying to solve a very similar set of issues as something like entgo[0] but in a very TypeScript-native way?

Also, regarding the transaction functions, are those run locally, or are they serialized and run remotely on the database server? Both have their caveats.

Number 1 (and that approach to retries) already works just fine with Postgres, you just need a proper library (which wraps your function and retries on transaction isolation errors). But you also have to keep in mind that if you interface with 3rd party services during your transactions, those calls will need to be idempotent as well. This is actually the proper way to do transactions in application code with a SQL database, esp. if you're running with serializable transaction isolation.

Number 2 is very limiting, as you can't have all those third party services and libraries used between different operations in a transaction, which is often (I'd even argue - usually) very useful.

Since you're citing stored procedures as a viable alternative to convex's take, while not listing the above number 1, it sounds like you're doing 2.

Anyhow, good luck!

[0]: https://entgo.io

Re: It's not you, it's SQL

#33

Post by jamwt »

jrochkind1 wrote: post #28
jamwt wrote: post #24

Earlier quoted context omitted.

Article author here. > It may actually be fine to just require everything to be typescript, but the idea that you'd require your application be written in the same language as your data store, and thus implement a different data store for each language you might want to write an app in (and not share between languages)... would formerly be thought of as pretty ridiculous? The vision is definitely aspirational, and is…

I see. So, when you say "the application and database types are automatically equivalent because the entire data pipeline from frontend to database uses the same exact types and definitions", can that apply somehow to python and rust client libraries too? What about "all write operations on the database are done in embedded functions"? For the python and rust client libraries, do you write these embedded functions in…

Folks for whom TypeScript is a big part of their project are going to be the most natural fit for Convex.

But no, not necessarily exclusively TypeScript. For example, the existence of the Python client library is due to developer demand. Some users had ML jobs that are triggered by Convex applications or reported outcomes into Convex.

The embedded functions are always TypeScript. The experience is pretty smooth in other languages as well-but the degree of this is largely dependent on how easily the type systems map from e.g. Python to TypeScript and how our client library can infer things in a way that feels native or requires little of your involvement.

Re: It's not you, it's SQL

#34

Post by MaxBarraclough »

Justsignedup wrote: post #25

My biggest issue with sql has and always will be the lack of definitions. There's no way to express data structure knowledge in sql, only relations and keys. Discoverability can be quite lacking. And every time I want to join table A to B I have to re-define everything, because SQL doesn't store that. ORMs help. They help because they encode relationships in meaningful ways. A has many Bs, so A.B works, and I don't n…

> Discoverability can be quite lacking.

A richer type system would help there, but to my knowledge this isn't offered by any major relational DBMS.

Re: It's not you, it's SQL

#35

Post by cfeduke »

treis wrote: post #16
DerArzt wrote: post #8

Man what a well written ad.

My reaction exactly. Specifically when they said what developers want is document DBs. That's not true. We (or a lot of us) want the equivalent of Typescript for SQL. Which people have tried to build but their efforts have ended up like CoffeeScript. Better but ultimately not worth using because they don't have the staying power.

> equivalent of Typescript for SQL

My limited experience with LINQ to SQL was very positive in these regards - but one is not writing a type safe SQL at all, instead a language that is type safe and is ultimately interpreted as SQL.

Same experience with some libraries in Scala - where it's possible to get compile-time type safety for SQL - but required onerous setup or synchronization/code generation tools to achieve. (Sort of comes with the territory.)

Re: It's not you, it's SQL

#36

Post by richiebful1 »

Justsignedup wrote: post #25

My biggest issue with sql has and always will be the lack of definitions. There's no way to express data structure knowledge in sql, only relations and keys. Discoverability can be quite lacking. And every time I want to join table A to B I have to re-define everything, because SQL doesn't store that. ORMs help. They help because they encode relationships in meaningful ways. A has many Bs, so A.B works, and I don't n…

A database system could implement a feature that automatically uses the foreign key to join to a table. Maybe some RDBMS out there does this.

For example, you have a many to one relationship between posts and users. Instead of this:

  select *
  from user as u
  join post as p
  on p.user_id = u.user_id;
You could do:

  alter table post
  add constraint fk_user_id foreign key (user_id) 
  references user.user_id;

  select *
  from user as u
  left referent join post as p;
Any good sql auto-completer will also look up the foreign key information and auto-generate the on clause for you as well. Redgate SQL Prompt (mssql only) is one of the best tools out there for this reason

Re: It's not you, it's SQL

#37

Post by cfeduke »

jemmyw wrote: post #5
BitwiseFool wrote: post #3

Earlier quoted context omitted.

I wish SQL did not require a comma between items after the SELECT and before the FROM. We don't need commas in between joins. I feel like someone could write a way to parse queries so that it isn't needed. Can you imagine how much time and effort that would save people? Edit: You do need commas in ORDER BYs, that slipped my mind when typing out this pet peeve of mine.

I'd be happy if it just accepted a dangling comma so you can swap your columns around freely without causing a syntax error

When you see queries written like:

  select
  1
  ,field1
  ,field2
  ,field3
  from ...
you've hit peak SQL engineer comma frustration workaround.

Re: It's not you, it's SQL

#38

Post by isoprophlex »

MaxBarraclough wrote: post #34
Justsignedup wrote: post #25

My biggest issue with sql has and always will be the lack of definitions. There's no way to express data structure knowledge in sql, only relations and keys. Discoverability can be quite lacking. And every time I want to join table A to B I have to re-define everything, because SQL doesn't store that. ORMs help. They help because they encode relationships in meaningful ways. A has many Bs, so A.B works, and I don't n…

> Discoverability can be quite lacking. A richer type system would help there, but to my knowledge this isn't offered by any major relational DBMS.

https://www.postgresql.org/docs/current/sql-createtype.html

Postgres allows you to define custom types at least; making some enums or composites might give you some measure of sanity..?

Re: It's not you, it's SQL

#39

Post by nativecoinc »

jamwt wrote: post #24
jrochkind1 wrote: post #23

> In Convex, the application and database types are automatically equivalent because the entire data pipeline from frontend to database uses the same exact types and definitions... There is no adapter code between languages because everything we write is just TypeScript. It may actually be fine to just require everything to be typescript, but the idea that you'd require your application be written in the same languag…

Article author here. > It may actually be fine to just require everything to be typescript, but the idea that you'd require your application be written in the same language as your data store, and thus implement a different data store for each language you might want to write an app in (and not share between languages)... would formerly be thought of as pretty ridiculous? The vision is definitely aspirational, and is…

Article ostensibly about doing better than SQL: Ah, interesting

Underlying vision or assumption that we’ll all be using JavaScript/TypeScript in any case: Freaking really??

Re: It's not you, it's SQL

#40

Post by jrumbut »

civilized wrote: post #29
Justsignedup wrote: post #25

My biggest issue with sql has and always will be the lack of definitions. There's no way to express data structure knowledge in sql, only relations and keys. Discoverability can be quite lacking. And every time I want to join table A to B I have to re-define everything, because SQL doesn't store that. ORMs help. They help because they encode relationships in meaningful ways. A has many Bs, so A.B works, and I don't n…

Hi, just curious - could you or someone else be more specific about the way in which an ORM encodes relationships that SQL doesn't?

An ORM allows you to encode a relationship once then use it for many different queries, I think that's the idea.
Post reply on HN