It's not you, it's SQL

Post by dragonwriter » Mon, Jan 30, 2023, 10:09 PM UTC

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…

Post by dragonwriter » Mon, Jan 30, 2023, 10:27 PM UTC

sgk284 wrote: post #46

richiebful1 wrote: post #36

Earlier quoted context omitted.

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…

SQL already covers this use case with the `using` keyword. But you need to specify the shared column name. If you didn't need to specify the column then adding a new foreign key between the tables would make existing queries ambiguous and break backwards compatibility. See: https://www.postgresql.org/docs/current/queries-table-expres... .

  CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,’
    name VARCHAR,
    manager_id BIGINT CONSTRAINT manager REFERENCES employees
  );

  SELECT name as employee_name, manager.name as manager_name
  FROM employees

  SELECT ee.name as employee_name, mgr.name as manager_name
  FROM employees ee 
  LEFT JOIN employees mgr ON (
    employees.manager_id = mgr.id
  )

Post by vlunkr » Mon, Jan 30, 2023, 10:34 PM UTC

jamwt wrote: post #54

vlunkr wrote: post #51

I agree with lots of the points here. However one of the great strengths of the SQL family is the FOSS history. I doubt something proprietary is ever going win everyone over. At least I hope not.

Article author here. Agree this is a necessary part of the change. Convex is working on our OSS strategy this year. Thanks for the feedback!

Post by dragonwriter » Mon, Jan 30, 2023, 10:43 PM UTC

jamwt wrote: post #62

dragonwriter wrote: post #60

Earlier quoted context omitted.

It vaguely references it, doesn’t explain what the data model it is proposing actually is in concrete terms. It gives some API examples, but nothing that tells me what is different between it and the data model of any of the major (Object) Relational DBMS’s with an appropriate host-language client adaptor except that the client library exists only for TS and the type system is designed to be 1:1 with TypeScript. And…

> doesn’t explain what the data model it is proposing actually is in concrete terms. Fair enough. The goal of the post wasn't necessarily to comprehensively describe the product–so the specifics of what we provide are found elsewhere in our docs, etc. > Really, the big thing that I see here is the transaction retry logic, These transactions run as embedded functions inside Convex in a runtime where it is impossible t…

Post by dragonwriter » Mon, Jan 30, 2023, 10:54 PM UTC

Justsignedup wrote: post #64

jrumbut wrote: post #59

Earlier quoted context omitted.

Sure, but people never use those. Also it gets back to the discoverability issue. The ORM documents relationships with the rest of the model code, if you have a poorly named view in a large, complex schema it may be hard to find. You could reinvent the wheel easily. Like everything with SQL, you can solve the problem but sometimes the solution isn't elegant. People want elegance.

This is an accurate description of how I think of this problem. And it would be tough to describe all this: - table a joins b yay a view - table b joins c yay a view - table c join d yay a view - table a joins to d. well technically it can, but are you really gonna write all the permutations of views for every possible join? ORMs encode that nicely so I can easily walk the relationships and get to the query I need.