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?
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;
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.
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.
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?