Hacker News Reader

Comment by Fishkins | Hacker News Reader

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?

View on HN

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;

You can use writable CTEs as an upsert in postgres. http://dba.stackexchange.com/questions/13468/most-idiomatic-...

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 (...)

not atomic

Thus transactions...

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.

Reply on news.ycombinator.com