Insert multiple row in a single SQL query

[tpimh]

Is it possible to Model:create() not just a single row, but several? How do I do it?

[leafo]

It's not possible to do it with model:create, the easiest way would be to manually write a query. You can also do a begin commit transaction to bundle separate inserts done with model:create together, which should speed it up significantly. Just be careful you don't leave the connection in a transaction state, since it will be reused later.

[tpimh]

Maybe it's a good idea for create to also accept array as first argument? Would a pull request with such improvement be useful?

[leafo]

Maybe it's a good idea for create to also accept array as first argument? Would a pull request with such improvement be useful?

It's pretty common to override create for custom initialization, and changing the interface could have impact on that. If this was a method It would probably make sense have a separate method, especially since lua does not have separate array types. That aside, I'm not sure the additional API is worth it. Lapis' orm gives you what you need for most common queries, and I recommend people trying to do more complex things to write queries themselves, using the primitive query building functions provided in the db module.

[turbo]

@tpimh Something along the lines of this should work:

-- model is just the class
-- directive example: "ON CONFLICT DO NOTHING"
bulk_insert = (model, tvals, directive = "", tx_size = 500) ->
  table_name = model\table_name!
  columns = [ n.column_name for n in *model\columns! ]
  columns_str = table.concat(columns, ",")

  DB.query "BEGIN"

  for n = 1, #tvals, tx_size
    batch_size = math.min(tx_size - 1, #tvals - n)
    query = "INSERT INTO #{table_name} (#{columns_str}) VALUES "
    query_tail = {}

    for n_offset = 0, batch_size
      col_vals = table.concat([ DB.escape_literal(
        tvals[n + n_offset][col]
      ) for col in *columns ], ",")
      table.insert(query_tail, "\n(#{col_vals})")

    query ..= table.concat(query_tail, ",")
    if directive
      query ..= " #{directive}"
    query ..= ";"


    DB.query query

  DB.query "COMMIT"

Of course you also need to error-check and ROLLBACK to prevent connection orphaning.

[tpimh]

@turbo Thanks! I'll give it a try.

[turbo]

Don't run it blindly though ;). E.g. if your create table contains subqueries, this will double-escape them. Customize it to your needs.