search

alexdesousa / ayesql

Library for using raw SQL in Elixir
MIT License
135 stars 13 forks source link

CRUD Design Patterns #19

Closed thdxr closed 3 years ago

thdxr commented 3 years ago

I was curious if you generally use ayesql alongside Ecto or if there's a good way to do inserts and updates using ayesql

alexdesousa commented 3 years ago

@thdxr Sorry for the late reply. I've been meaning to answer this for a while, but I haven't had the time.

I've used AyeSQL mainly for querying stuff that usually would be too hard to query using just Ecto.

There was a project where we had some limititations and plain Ecto wouldn't be enough, so we used Ecto.Changeset for inserts and updates mixing it with AyeSQL instead of Ecto.Repo.

In the end, Ecto is far superior for most use cases. However, mixing AyeSQL and Ecto might work when you need complex queries along with normal CRUD operations.

dvv commented 2 years ago

Wonder how do I do a bulk insert?

alexdesousa commented 2 years ago

@dvv I usually mix it with Ecto.Multi and run each query in a transaction.

dvv commented 2 years ago

I see. Just wanna explore how far can I reach without Ecto. The following doesn't work with values = [[[1, "a"]]]]:

insert into foo (col1, col2) values :values on conflict do nothing
-- ERROR 42601 (syntax_error) syntax error at or near "$1"
insert into foo (col1, col2) values (:values) on conflict do nothing
-- ERROR 42601 (syntax_error) INSERT has more target columns than expressions

Am I missing the way?