gbaird
Using Postgrex and SQL for bank transfers in PostgreSQL. epgsql erlang as alternative
I am trying to code a bank transfer service using Elixir/Erlang, Postgrex and PostgreSQL.
Erlang v 26
Elixir v 1.16.0
Postgrex v 0.17.4
The logic I am trying to build is the following, wrapped in a transaction BEGIN - COMMIT.
Party A seeks to transfer $50 from his account to Party B, her account. Think a Venmo transfer from Party A to Party B for $50.
Initialize
- Store the transfer amount in an Elixir variable *transfer_amount
- Store the from account UID in an Elixir variable from_account
- Store the to account UID in an Elixir Variable to_account
- Store the authorization token in an Elixir variable auth_token
BEGIN the transaction
- Read the master token value from the authorization data table and if the auth_token = master_token, then continue, else quit
- Read the balance from Party A’s account using the from_account UID
- If from_account** balance is >= than transfer_amount, then continue, else quit
- Update from_account balance by subtracting transfer_amount
- Update to_account balance by adding transfer_amount to balance
COMMIT the transaction
What I tried is wrapping the SQL statement built from the above into a query and then use Postgrex.query - see below. The code returns an error.
My question for the group is this the correct/best design pattern to try and accomplish the objective - a P2P bank transfer, to wrap the SQL logic into a SQL statement that has a series of “reads” and then a “read / write” using Postgrex.query! Does Postgres support “Transaction” syntax? Can the read, write, comparisons all be included into a single statement and then sent using Postgrex.query?
24)> Postgrex.query!(pid3, "BEGIN; SELECT token_masterid FROM token; IF token_id = master_token THEN SELECT from_account, balance_avail FROM main_account … [note not……; COMMIT", [])
** (Postgrex.Error) ERROR 42601
A more simple version produces this error
iex(14)> Postgrex.query!(pid3, "BEGIN transaction; SELECT * FROM account; COMMIT;", [])
** (Postgrex.Error) ERROR 42601 (syntax_error) cannot insert multiple commands into a prepared statement
iex(14)> Postgrex.query!(pid3, "BEGIN transaction; SELECT * FROM account; COMMIT;", [])
** (Postgrex.Error) ERROR 42601 (syntax_error) cannot insert multiple commands into a prepared statement
Thank you in advance any thoughts and help !!
Most Liked
benwilson512
Hi @gbaird this seems more to do with a general comfort level with how Elixir works. All functions will only return one value, that isn’t related to Postgrex. If you want to return the information from multiple queries, bind each result to a variable and return a tuple:
{:ok, res} = Postgrex.transaction(pid3, fn(pid3) ->
result1 = Postgrex.query!(pid3, "SELECT owner_id, balance_avail FROM account", [])
result2 = Postgrex.query!(pid3, "SELECT owner_id, balance_book FROM account", [])
{result1, result2}
end)
Keep in mind with Postgres that transactions are by default merely READ COMMITTED. Your use case may require stricter transaction modes.
sbuttgereit
Actually, should be possible to use a DO block (PostgreSQL: Documentation: 16: DO); this is effectively treated as a single SQL statement even though we may be doing more (possibly much more) work than that.
This is not so different than the stored procedure route, except that the DO block is implicitly ephemeral; no need to change the schema.
al2o3cr
I don’t think multistatement queries like the above are supported - see also:
If you’re absolutely 100% required to do it SQL-side, you could make a stored procedure and then call that with Postgrex.query in a single statement.
The more-idiomatic approach would be to use Postgrex.transaction and then do the steps you’ve described using Elixir (and more DB queries) inside that transaction block.
tj0
Ecto maintains a database pool and parses / creates queries. There is some overhead in casting and validating data, but it is unavoidable as you would have to do that in any homegrown solution anyway. Ecto does this via Ecto.ChangeSet.
The major difference would be that there is a cost in converting from an Ecto.Struct to raw sql. This would be on the order of 5-10us and should be heavily outweighed by any networking variance. These benchmarks are from the Ecto Sqlite repo:
- ecto_sqlite3/bench/scripts/micro/to_sql_bench.exs at main · elixir-sqlite/ecto_sqlite3 · GitHub
- ecto_sqlite3/bench/results/to_sql.md at main · elixir-sqlite/ecto_sqlite3 · GitHub
So, I don’t think there should be much of a practical negatives in performance difference in using Ecto over raw, but I have been surprised before.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Build Elixir applications with speed and confidence.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"