Elixir/Ecto - update_all with join

When I first began using Ecto it was only to directly issue SQL since the PostgreSQL database that I was working on was so far outside the norm (tables stored in different schemas, different naming conventions on primary keys, character fields being used to store foreign keys that pointed at multiple tables). The second project that I worked on was a database that I controlled so I got to use the more general features of the Ecto query language. The way queries are constructed is - for the most part - easy to grasp but there are definitely some cases that are harder to understand and that could benefit from more examples. The Ecto update_all is one of those.

Read More

Monitor Postgres pgbouncer

Pgbouncer is widely used with Postgres to provide connection pooling. Its an easy-to-use and easy-to-install piece of software. The general idea is to specify n number of connections allowed to pgbouncer and m connections allowed to Postgres itself where m is much less than n. A typical configuration is to set pgbouncer to transaction mode. This allows pgbouncer to multiplex the “real” connections to Postgres as transactions are committed or rolled back.

Read More

Using Postgres COPY with Elixir/Ecto

This post is about using Postgres COPY to retrieve data from the database into memory when using Elixir and Ecto. Let’s assume you want to read all the data from a table ‘accounts’ using Postgres COPY. Lets assume the table definition in Postgres is:

Read More

Elixir, Phoenix and Ecto fragment and SIMILAR TO

I had a part of a Phoenix web app where users wanted to save filters to the database and then be able to select and apply them to narrow a dataset. One of the key features that they wanted was the ability to wildcard search the fqdn column value (fqdn is fully qualified domain name in this case). Creating the filter and saving the data was fairly straightfoward but how could I use Ecto to apply “n” number of LIKE operators?

Read More