4 ms·

Thanks for sharing your experience. I've been meaning to try Slick, and YeSQL sounds like a nice way to reduce some boilerplate with no real downside. I go back

Thanks for sharing your experience. I've been meaning to try Slick, and YeSQL sounds like a nice way to reduce some boilerplate with no real downside. I go back and forth about how I feel about ORMs. I think everyone can agree you'll need to learn SQL for any non-trivial project, even if you end up using some abstraction on top of it. On a tangent: you mentioned Upserts in Postgres features. I thought Postgres didn't have any kind of Upsert. Was it added recently or something?

Xorlev 12y ago
Postgres still doesn't. I usually write a rule to do so. For instance, here's my "ON DUPLICATE KEY UPDATE eid=eid": CREATE RULE location_updates_on_duplicate_ignore AS ON INSERT TO location_updates WHERE (EXISTS (SELECT 1 FROM location_updates WHERE (eid = NEW.eid))) DO INSTEAD NOTHING; But you can also do much more advanced merge logic by replacing DO INSTEAD NOTHING with DO INSTEAD <statement>. Mine just needed to ignore already-submitted batches.
terhechte 12y ago
Postgres indeed doesn't have Upsert yet, so I'm going the default way of locking the table, and implementing it via a slightly more complex query. I was just too lazy to explain that in my earlier comment. The problem is the same: The syntax below can't really be represented well in a ORM. BEGIN; LOCK TABLE search_tracking IN SHARE ROW EXCLUSIVE MODE; WITH upsert AS (UPDATE search_tracking SET count=count+1 WHERE keyword = 'John Doe' RETURNING *) INSERT INTO search_tracking (keyword, count) SELECT 'John Doe', 1 WHERE NOT EXISTS(SELECT * FROM upsert); COMMIT;
- jimktrains2 12y ago
  You can use writable CTEs as an upsert in postgres. http://dba.stackexchange.com/questions/13468/most-idiomatic-way-to-implement-upsert-in-postgresql-nowadays/44412#44412 http://dba.stackexchange.com/questions/13468/most-idiomatic-...
- NicoJuicy 12y ago
  But this aint that hard, i suppose it could also be done in Postgres (query using MS SQL Server) Table1 SET (...) WHERE Column1='SomeValue' IF @@ROWCOUNT=0 INSERT INTO Table1 VALUES (...)
  - _ZeD_ 12y ago
    not atomic
    - sk5t 12y ago
      Thus transactions...
ssmoot 12y ago
Side Note: Slick has basic parameterized queries/statements/DDL. eg: val cachedPurchasesQuery = Q[String, (Int, AccountId, Timestamp)] + """ |SELECT pi.purchasable_item_id, u.account_id, pi.created_at |FROM purchased_items AS pi |INNER JOIN users u ON pi.user_id = u.id |WHERE purchasable_item_type = ? AND u.account_id IS NOT NULL """.stripMargin Then you call that like: val magazinePurchases = cachedPurchasesQuery("Magazine").list Or `foreach`, `firstOption`, etc. Most of the normal collection-y stuff. I prefer this over the Table mapping DSL and for-comprehension stuff personally. But I've only used it in production on smaller projects that are limited to under a dozen queries/statements or so.

Xorlev 12y ago

Postgres still doesn't. I usually write a rule to do so. For instance, here's my "ON DUPLICATE KEY UPDATE eid=eid": CREATE RULE location_updates_on_duplicate_ignore AS ON INSERT TO location_updates WHERE (EXISTS (SELECT 1 FROM location_updates WHERE (eid = NEW.eid))) DO INSTEAD NOTHING; But you can also do much more advanced merge logic by replacing DO INSTEAD NOTHING with DO INSTEAD <statement>. Mine just needed to ignore already-submitted batches.

terhechte 12y ago

Postgres indeed doesn't have Upsert yet, so I'm going the default way of locking the table, and implementing it via a slightly more complex query. I was just too lazy to explain that in my earlier comment. The problem is the same: The syntax below can't really be represented well in a ORM. BEGIN; LOCK TABLE search_tracking IN SHARE ROW EXCLUSIVE MODE; WITH upsert AS (UPDATE search_tracking SET count=count+1 WHERE keyword = 'John Doe' RETURNING *) INSERT INTO search_tracking (keyword, count) SELECT 'John Doe', 1 WHERE NOT EXISTS(SELECT * FROM upsert); COMMIT;

jimktrains2 12y ago
You can use writable CTEs as an upsert in postgres. http://dba.stackexchange.com/questions/13468/most-idiomatic-way-to-implement-upsert-in-postgresql-nowadays/44412#44412 http://dba.stackexchange.com/questions/13468/most-idiomatic-...
NicoJuicy 12y ago
But this aint that hard, i suppose it could also be done in Postgres (query using MS SQL Server) Table1 SET (...) WHERE Column1='SomeValue' IF @@ROWCOUNT=0 INSERT INTO Table1 VALUES (...)
- _ZeD_ 12y ago
  not atomic
  - sk5t 12y ago
    Thus transactions...

ssmoot 12y ago

Side Note: Slick has basic parameterized queries/statements/DDL. eg: val cachedPurchasesQuery = Q[String, (Int, AccountId, Timestamp)] + """ |SELECT pi.purchasable_item_id, u.account_id, pi.created_at |FROM purchased_items AS pi |INNER JOIN users u ON pi.user_id = u.id |WHERE purchasable_item_type = ? AND u.account_id IS NOT NULL """.stripMargin Then you call that like: val magazinePurchases = cachedPurchasesQuery("Magazine").list Or `foreach`, `firstOption`, etc. Most of the normal collection-y stuff. I prefer this over the Table mapping DSL and for-comprehension stuff personally. But I've only used it in production on smaller projects that are limited to under a dozen queries/statements or so.