luckyframework / luckyframework/avram

Bulk updates

Open
#1,075 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

feature request help wanted
Dominant language
Crystal
Stars
183
Forks
67
PR merge metrics
No merged PRs in 30d

Description

Related: https://github.com/luckyframework/avram/issues/662

We technically have a bulk update already through the query objects.

```crystal
# Updates all profiles locked value to false
ProfileQuery.new.update(locked: false)
```

This is handy, but if you're looking to update a bunch of records and each one potentially will get different values, that gets a little more tricky.

In this case, you'll want to pass in an array of all of the updates you want to make. I do have this "working" in my app, but it's very messy and brittle, so I'd like to try and figure out a better way that can also conform to Avram's principals of type-safety.

```crystal
class Test < BaseModel
enum State
Good
Bad
end
table do
primary_key id : UUID
column foo : Test::State
column bar : String
column baz : Array(String)
end
end

def self.update_all(params : Array(Hash(String, String)), &)
sql = <<-SQL
UPDATE tests SET foo = data_table.foo, bar = data_table.bar, baz = string_to_array(data_table.baz, ',')
FROM (
SELECT unnest($1::uuid[]) AS id, unnest($2::integer[]) AS foo, unnest($3::text[]) AS bar, unnest($4::text[]) AS baz
) AS data_table
WHERE tests.id = data_table.id
RETURNING tests.*
SQL

ids = params.map(&.["id"])
foos = params.map(&.["foo"].to_i)
bars = params.map(&.["bar"])
# should look like an array of CSV strings
bazs = params.map(&.["baz"])
inputs = [ids, foos, bars, bazs] # Can we even guarantee that these will always be in the correct order here?
# if they're not all the same size, this will fail
inputs_are_same_size = inputs.map(&.size).uniq!.first == ids.size

if inputs_are_same_size
args = inputs.map { |group| PQ::Param.encode_array(group) }
records = AppDatabase.query_all(sql, args: args, as: Test)
yield self.new, records
else
yield nil, nil
end
end
```

Some of the questions/issues I have here
* Enums have to be passed in as stringified integers here because `params` in Lucky is `Hash(String, String)`
* I couldn't figure out how to cast the `baz` to array of array of strings...
* Each arg to be an array of that column's data, so we can't do things like `[id, foo, bar, baz], [id, foo, bar, baz]`
* How do you handle errors? We'd have to some how map the error to the specific column that failed so you're not digging through 50k rows to figure out where waldo is.
* Notice the arrays have to be pre-encoded before being passed in. That's a whole thing....

I'm sure there's a lot more, so if anyone has some insight in to how this could work reliably, I'm open to ideas.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the existing query-object bulk update API shown through ProfileQuery.new.update and read related issue #662. Evaluate the proposed typed API against the PostgreSQL update example, including enum values, array encoding, parameter ordering, and error reporting; done means a clearly agreed design with type-safe behavior and defined failure handling.

Written by the indexing model from the issue text.

Assessment

Tech stack
crystal, postgresql
Domain
database
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.