hubertlepicki

hubertlepicki

Manually checking out and closing database (PostgreSQL) connection with Ecto

I have a fairly peculiar use case. Whenever a HTTP request is being handled in Phoenix app, I want to, from a plug mounted in router, set some session variables, create some temporary views, in a way that further down the road, my Phoenix/Ecto app does use those variables and views when making SQL queries.

There are in fact multiple reasons why I want to do the above, sufficient to say is that it involves auditing on database level, time-travel (with temporal_tables extension) and limiting the visibility of records on per-user basis that has to be done in database.

The obvious problem here is that Ecto maintains a pool of connections/sessions and whenever a SQL query is executed it checks out another connection. So, if I do 5 SQL queries during my request handling, I could very likely use 5 different DB connections/sessions.

As far as I can tell, these are also not cleaned up, i.e. the views and variables I set during one request would likely leak to another ones. I don’t want that.

I can, most likely, force the behavior I want by wrapping up all of my code in transaction, provide it function and within that function my database connection ID would be constant and my temporary views /variables would survive between database calls.

Is there a similar mechanism that would involve not using a transaction? I don’t want to wrap everything in a transaction, that can be possibly dangerous to the performance and I actually have multiple steps that I want to partially succeed.

Marked As Solved

wojtekmach

wojtekmach

Hex Core Team

Ecto 3.0 ships with Repo.checkout/2 function for this use case.

Also Liked

michalmuskala

michalmuskala

It’s a planned feature for Ecto 3.0 to checkout the connection without wrapping in a transaction (and also to have transaction support with explicit checkout/checking calls instead of just a closure which severely limits the control flow possibilities).

Right now, I think the only way to do that could be abusing some of the ecto internals and the fact that ecto stores the connection pid during transaction in pdict. But I didn’t say that, if anybody asks :wink:

Where Next?

Popular in Questions Top

Harrisonl
We have an ECS cluster with 4 services, where each task joins a single cluster, via discovery ECS discovery service. Currently when I de...
New
JulienCorb
I am trying to implement my new.html.eex file to create new posts on my website. new.html.eex: <h1>Create Post</h1> <%= ...
New
LegitStack
I’m trying to make a websocket server in Phoenix or raw Elixir. I heard about gun, I think I could use cowboy, but since I’m not that sma...
New
stefanchrobot
What’s the safe way to decode a JSON string into a struct? I want to avoid calling String.to_atom. Jason.decode can give me a map with st...
New
hariharasudhan94
lets say i have a sample like a = 20; b = 10; if (a > b) do {:ok, "a"} end if (a < b) do {:ok, b} end if (a == b) do {:ok, "equa...
New
vrod
I am using the Starship cross-shell prompt – it seems pretty nice, but I get some errors: [WARN] - (starship::utils): Executing command ...
New
fayddelight
I tried installing elixir 1.11.2 erlang 23.3.4 via asdf in my zsh shell. Enabled the versions locally and globally. When I list them ...
New
dblack
I’ve got an issue with an app and I’ve no idea of how to troubleshoot it. I’m hoping someone here might have seen something similar. I p...
New
chensan
I have a User schema with a :from_id field set to type :string: defmodule TweetBot.Repo.Migrations.CreateUsers do use Ecto.Migration ...
New
vonH
In asking this question I am more interested about the expressiveness of the language itself and less concerned about the availability of...
New

Other popular topics Top

sen
Hi All, I set a environment variables in dev.exs , like below code. when i start server, how can i set the ${enable} value? thanks. d...
New
Darmani72
If I have a post route which an argument: post /my_post_route/:my_param1, MyController.my_post_handler How would get the post params ...
New
senggen
Erlang/OTP 25 [erts-13.2.2] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] 15:22:35.803 [error] gen_event {lager_file_backend...
New
lastday4you
I wanted to check elixir version in phoenix because i found that my elixir is 1.5 but when i use Enum.chunk_by it said the function is un...
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
josevalim
Hi everyone, One of the features added to Elixir early on to help integration with Erlang code was the idea of overridable function defi...
New
nobody
Hi! In PHP: $_SERVER[‘SERVER_ADDR’] - in Elixir? Searched the docs for ip address and the web, no good results. Thanks!
New
KronicDeth
Elixir plugin for JetBrain’s IntelliJ Platform (including Rubymine) This is a plugin that adds support for Elixir to JetBrains IntelliJ...
289 36128 110
New
dblack
I’ve got an issue with an app and I’ve no idea of how to troubleshoot it. I’m hoping someone here might have seen something similar. I p...
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

We're in Beta

About us Mission Statement