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.


  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 !!

I think it’s probably easier if you use Ecto which can create queries that are casted, etc.

Within Ecto, you can create a transaction.

Are you using Phoenix or are you trying to do this standalone? Phoenix has a lot of auxiliary functions that help put things together easily.

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.

1 Like

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.


Thank you for the responses. Great suggestions. I am trying to avoid if possible using stored procedures - to not have code level logic incorporated within the database application - to preserve flexibility if we want to swap out the datastore at a future date. However, the resiliency and reliability of the code is also of the highest priority given the nature of the application. We will try both methods and report back the findings for those interested.

Also, if anybody has a good reference for Postrgrex.transaction examples, please share (any additional documentation would be helpful). Thank you for the suggestion for using Ecto. We will explore this as well - since this is a very simple, single use case scenarrio - where we need high performance at scale, one question we have is since Ecto is a more robust and fulsome package - will the performance be slower since not using many of the features… or is this a false worry? we might want to containerize the service for easy replication - build a network of interconnected nodes - so trying to keep the code base as streamlined and compact as possible.

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:

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.

1 Like

We have been trying to run the multiple queried within the transaction block using Postgrex.transaction But we can’t get the queries to execute within a single transaction. The function only return the last query executed. Is there more details anywhere on how to build the queries and assign variables within a transaction block using this function? Any assistance much appreciated !

{:ok, res} = Postgrex.transaction(pid3, fn(pid3) ->
  Postgrex.query!(pid3, "SELECT owner_id, balance_avail FROM account", [])
  Postgrex.query!(pid3, "SELECT owner_id, balance_book FROM account", [])
iex(16)> res
  command: :select,
  columns: ["owner_id", "balance_book"],
  rows: [
    ["f245cb8bd2df8391281a3606e1841ba4", 7594777],
    ["3d58a9bd15d268b4ccd335d5c1a9fe1b", 9735911],
    ["3f6fbdbdfeffd3a5fe16f6235d3115fd", 6308644],

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}

Keep in mind with Postgres that transactions are by default merely READ COMMITTED. Your use case may require stricter transaction modes.