gbaird

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

  1. Store the transfer amount in an Elixir variable *transfer_amount
  2. Store the from account UID in an Elixir variable from_account
  3. Store the to account UID in an Elixir Variable to_account
  4. Store the authorization token in an Elixir variable auth_token

BEGIN the transaction

  1. Read the master token value from the authorization data table and if the auth_token = master_token, then continue, else quit
  2. Read the balance from Party A’s account using the from_account UID
  3. If from_account** balance is >= than transfer_amount, then continue, else quit
  4. Update from_account balance by subtracting transfer_amount
  5. 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

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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

sbuttgereit

Actually, should be possible to use a DO block (PostgreSQL: Documentation: 18: 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

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.

Where Next?

Popular in Questions Top

sergio
In Ruby, I can go: User.find_by(email: "foobar@email.com").update(email: "hello@email.com") How can I do something similar in Elixir? ...
New
WestKeys
Currently suffering from paralysis by [HTTP client] analysis. This is rather unusual in Elixirland as there tends to be consensus on the ...
New
mcarvalho
What is the difference between System.get_env and Application.get_env? For example, what are best practices to use one versus another.
New
greenz1
I have a phoenix application from which a user can download multiple(5-6) files of size 1MB. I couldn’t find anything related to sending ...
New
mgjohns61585
Could someone help me? I’m making my first elixir program, number guessing game. I can’t figure out how to convert the user’s guess from ...
New
shahryarjb
Hello, I get Persian date from my client and convert it to normal calendar like this: def jalali_string_to_miladi_english_number(persi...
New
ovidiubadita
Hey all, I discovered Elixir and I love it. I always wanted to learn a functional programming and I intended to go for Haskell, but afte...
New
joeerl
Hello again - after a longish gap I’ve decided I really must dig into Elixir and see what’s been happening here - so I have a few questio...
New
SoCreat
i’m a new one to elixir which editor can i use vs code? or atom? Thanks! :smiley:
New
PeterCarter
There are pre-rolled solutions for other frameworks that do work. However, Phoenix does not seem to have these. Have people had good expe...
New

Other popular topics Top

TunkShif
This post is an instruction guide to help you setup your Neovim for Elixir development from scratch. It includes general information on h...
274 41989 114
New
albydarned
Hello all! I am typing this post from my new MacBook Pro with the M1 chip. I’m loving it so far, and will probably use it as my daily dr...
New
JeremM34
Hello, how can I check the Phoenix version ? Thanks !
New
Fl4m3Ph03n1x
About me? ( if you have nothing better to do than reading about some random guy in the internet :stuck_out_tongue: ) Hello all, this is ...
New
Lily
In templates/appointment/index.html.eex: <%= for appointment <- @appointments do %> <tr> <td><%= appoi...
New
Emily
I have VueJS GUIs with the project generated using Webpack. I have Elixir modules that will need to be used by the VueJS GUIs. I forese...
New
sergio_101
I am VERY much an elixir newbie. I have taken one elixir course and one phoenix course on Udemy. During that course, I saw the instructor...
New
nsuchy
Hi. I’ve noticed that Windows Powershell has it’s own IEX command and you cannot access Elixir’s IEX due to the conflict. This isn’t a cr...
New
PeterCarter
There are pre-rolled solutions for other frameworks that do work. However, Phoenix does not seem to have these. Have people had good expe...
New
JorisKok
I have a server on AWS, and was running a load test using artillery. When looking at the Phoenix dashboard I see the Ports going to 100% ...
New

Latest on Elixir Forum

Elixir Forum

We're in Beta

About us Mission Statement