DEV Community: Daniel Kukula

Simplifying Ecto Fragments with Defrag: A Better Way to Write Database Queries

Daniel Kukula — Fri, 07 Mar 2025 22:40:41 +0000

Simplifying Ecto Fragments with Defrag: A Better Way to Write Database Queries

Working with complex database queries in Elixir's Ecto can sometimes feel like wrestling with syntax. If you've ever found yourself counting question marks in a fragment to figure out which parameter goes where, the Defrag library might be just what you need.

The Problem with Traditional Fragments

Anyone who's worked with Ecto has likely written code like this:

fragment("date_part('year', ?) BETWEEN ? AND ?", user.birth_date, start_year, end_year)

As your queries grow more complex, tracking which parameter corresponds to which placeholder becomes increasingly error-prone. With 18 variables in a query (yes, I've seen this in production code!), good luck finding where each one belongs without careful counting.

Enter Defrag: String Interpolation for Ecto Fragments

Defrag offers a more intuitive approach by allowing you to use Elixir's familiar string interpolation syntax in your database queries:

defrag("date_part('year', #{user.birth_date}) BETWEEN #{start_year} AND #{end_year}")

The library transforms this readable syntax into proper Ecto fragments at compile time, maintaining all the SQL injection protection of regular fragments while making your code significantly easier to understand.

How It Works

Under the hood, Defrag is a simple macro that:

Parses your string with interpolation syntax
Replaces each interpolated expression with a question mark
Collects all the interpolated values as parameters
Builds a standard Ecto fragment with these components

Real-World Usage

Incorporating Defrag into your Ecto queries is straightforward:

import Ecto.Query
import Defrag

def complex_search(query, params) do
  from u in query,
    where: defrag("""
      #{u.status} = 'active' AND
      date_part('year', #{u.registered_at}) >= #{params.min_year} AND
      #{u.points} BETWEEN #{params.min_points} AND #{params.max_points} AND
      lower(#{u.email}) LIKE '%#{^params.email_search}%'
    """)
end

The query above would be nearly impossible to read and maintain with traditional fragments, but with Defrag, the relationship between your SQL and parameters is crystal clear.

Benefits

Self-documenting queries: See exactly where each parameter fits in the SQL statement
Reduced errors: No more counting question marks or parameter positions
Maintainability: Easier to modify queries when you can see the full context
Familiar syntax: Uses the string interpolation you already know from Elixir

Getting Started

Add Defrag to your dependencies in mix.exs:

def deps do
  [
    {:defrag, "~> 0.1.0"}
  ]
end

Then import it in your query modules:

import Defrag

Final Thoughts

If you've struggled with complex Ecto fragments, Defrag offers a simple yet powerful solution. By bringing familiar string interpolation to your database queries, it makes even the most complex fragments manageable and maintainable.

For those working with data-intensive applications, this small library can make a significant difference in your codebase clarity.

The Journey of Optimization

Daniel Kukula — Thu, 02 Jan 2025 22:50:45 +0000

Recently, I watched some Elixir vs Go comparison videos on YouTube. After the first comparison, José Valim made a PR to make the comparison more accurate. One key difference was that the Elixir version used Ecto and serialized datetime multiple times, while the Go version used raw SQL and single datetime serialization.

While these micro-benchmarks might not reflect real-world scenarios (where database operations typically account for 90% of response time), they sparked my curiosity about how Elixir handles datetime serialization internally.

Initial Investigation: The String Concatenation Problem

Looking into Elixir's source code, specifically the calendar/iso.ex module, I noticed frequent use of the <> operator for string concatenation. Here's a simple example of how string concatenation works:

iex(1)> time = "10:10:10"
"10:10:10"
iex(2)> date = "2025-01-02"
"2025-01-02"
iex(3)> datetime = date <> " " <> time
"2025-01-02 10:10:10"

The problem? Each concatenation creates a new string, which is inefficient when we don't actually need intermediate strings. Erlang VM provides better tools for this: IOLists.

Enter IOLists: A Better Approach

Instead of concatenating strings, we can use IOLists - a powerful BEAM feature that allows us to work with strings, charlists, and bytes without creating intermediate strings:

iex(4)> IO.puts([date, " ", time])
2025-01-02 10:10:10

You can even use IOLists in string interpolation:

iex(5)> "the time is #{[date, 32, time]}"
"the time is 2025-01-02 10:10:10"

The number 32 here represents a space character in ASCII - IOLists can contain integers (0-255) representing bytes.

The Original Implementation

Here's a simplified version of the original Calendar module:

defmodule LegacyCalendar do
  def datetime_to_string(dt) do
    %{
      year: year,
      month: month,
      day: day,
      hour: hour,
      minute: minute,
      second: second,
      microsecond: microsecond
    } = dt

    date_to_string(year, month, day) <>
      " " <>
      time_to_string(hour, minute, second, microsecond) <>
      "Z"
  end

  defp time_to_string(hour, minute, second, {microsecond, precision}) do
    time_to_string_format(hour, minute, second) <>
      "." <> (microsecond |> zero_pad(6) |> binary_part(0, precision))
  end

  defp time_to_string_format(hour, minute, second) do
    zero_pad(hour, 2) <> ":" <> zero_pad(minute, 2) <> ":" <> zero_pad(second, 2)
  end

  defp date_to_string(year, month, day) do
    zero_pad(year, 4) <> "-" <> zero_pad(month, 2) <> "-" <> zero_pad(day, 2)
  end

  defp zero_pad(val, count) when val >= 0 do
    num = Integer.to_string(val)
    :binary.copy("0", max(count - byte_size(num), 0)) <> num
  end

  defp zero_pad(val, count) do
    "-" <> zero_pad(-val, count)
  end
end

Running this 1000 times takes about 3ms:

mix run -e ":timer.tc(fn -> for _ <- 1..1000 do LegacyCalendar.datetime_to_string(~U[2025-01-02T21:22:23.012345Z]) end end) |> elem(0) |> IO.puts"
# Output: 3146ms

Step 1: Converting to IOLists

The first optimization replaced string concatenations with IOLists:

def datetime_to_string(dt) do
  [
    date_to_string(year, month, day),
    ?\s,
    time_to_string(hour, minute, second, microsecond),
    ?Z
  ]
  |> IO.iodata_to_binary()
end

This change reduced execution time to 2700ms.

Step 2: Optimizing Microseconds

The next optimization targeted microsecond formatting:

def microseconds_to_iodata(_microsecond, 0), do: []
def microseconds_to_iodata(microsecond, 6), do: zero_pad(microsecond, 6)

def microseconds_to_iodata(microsecond, precision) do
  num = div(microsecond, div_factor(precision))
  zero_pad(num, precision)
end

This brought the time down to 2500ms.

Step 3: Optimizing Zero Padding

We optimized the zero padding function by precomputing binary parts:

defp zero_pad(val, count) when val >= 0 and count <= 6 do
  num = Integer.to_string(val)
  case max(count - byte_size(num), 0) do
    0 -> num
    1 -> ["0", num]
    2 -> ["00", num]
    3 -> ["000", num]
    4 -> ["0000", num]
    5 -> ["00000", num]
  end
end

This reduced execution time to around 1900ms.

Step 4: Improper Lists

Following a suggestion from @sabiwara, we implemented improper lists - a technique where the last element isn't an empty list but a string:

[
  date_to_string(year, month, day),
  ?\s,
  time_to_string(hour, minute, second, microsecond)
  | "Z"
]

This optimization brought us down to 1800ms.

Final Optimization: Special Cases

The last optimization added special cases for two-digit numbers, which are used frequently in time formatting:

defp zero_pad(ones, 2) when ones >= 0 and ones < 10 do
  [?0, ones + ?0]
end

defp zero_pad(val, 2) when val >= 10 and val < 100 do
  tens = div(val, 10)
  ones = rem(val, 10)
  [tens + ?0, ones + ?0]
end

This brought our final execution time down to around 1714ms.

Memory Benefits

Beyond performance improvements, returning IOLists directly (without converting to binary) can save memory when working with libraries that accept IOData (like Phoenix or JSON encoders). The string doesn't need to be created and later garbage collected.

Conclusion

Through this optimization journey, we improved datetime serialization performance by:

Replacing string concatenation with IOLists
Optimizing microsecond handling
Using improper lists
Adding special cases for common patterns

The final result is not just faster execution (from 3146μs to 1714μs) but also more memory-efficient when used with IOData-aware libraries.

This exercise shows that even in a well-optimized language like Elixir, understanding the underlying BEAM features and applying them appropriately can lead to significant improvements.

Benchee Output

Here are the Benchee outputs for each step of the optimization process:

Initial Implementation

Name                     ips        average  deviation         median         99th %
LegacyCalendar        1.19 M      842.19 ns  ±2153.46%         551 ns        1362 ns

Extended statistics:

Name                   minimum        maximum    sample size                     mode
LegacyCalendar          470 ns    10294513 ns         1.81 M                   541 ns

Memory usage statistics:

Name              Memory usage
LegacyCalendar           896 B

**All measurements for memory usage were the same**

Profiling LegacyCalendar with eprof...

Profile results of #PID<0.488555.0>
#                                           CALLS     % TIME µS/CALL
Total                                          27 100.0   15    0.56
LegacyCalendar.time_to_string_format/3          1  0.00    0    0.00
LegacyCalendar.datetime_to_string/1             1  0.00    0    0.00
LegacyCalendar.date_to_string/3                 1  0.00    0    0.00
anonymous fn/0 in CalendarStringBench.run/0     1  0.00    0    0.00
:binary.copy/2                                  7  6.67    1    0.14
LegacyCalendar.time_to_string/4                 1 13.33    2    2.00
:erlang.integer_to_binary/1                     7 13.33    2    0.29
:erlang.apply/2                                 1 20.00    3    3.00
LegacyCalendar.zero_pad/2                       7 46.67    7    1.00

Profile done over 9 matching functions

Optimized Implementation

Name                     ips        average  deviation         median         99th %
LegacyCalendar        3.75 M      266.66 ns  ±4073.37%         231 ns         361 ns

Extended statistics:

Name                   minimum        maximum    sample size                     mode
LegacyCalendar          200 ns    15489642 ns         4.68 M                   230 ns

Memory usage statistics:

Name              Memory usage
LegacyCalendar           440 B

**All measurements for memory usage were the same**

Profiling LegacyCalendar with eprof...

Profile results of #PID<0.561770.0>
#                                           CALLS     % TIME µS/CALL
Total                                          22 100.0    6    0.27
LegacyCalendar.time_to_string_format/3          1  0.00    0    0.00
LegacyCalendar.time_to_string/4                 1  0.00    0    0.00
LegacyCalendar.microseconds_to_iodata/2         1  0.00    0    0.00
LegacyCalendar.datetime_to_string/1             1  0.00    0    0.00
LegacyCalendar.date_to_string/3                 1  0.00    0    0.00
anonymous fn/0 in CalendarStringBench.run/0     1  0.00    0    0.00
LegacyCalendar.zero_pad/2                       7 16.67    1    0.14
:erlang.iolist_to_binary/1                      1 16.67    1    1.00
:erlang.integer_to_binary/1                     7 16.67    1    0.14
:erlang.apply/2                                 1 50.00    3    3.00

Profile done over 10 matching functions

Final Optimized Implementation

Name                     ips        average  deviation         median         99th %
LegacyCalendar        9.15 M      109.30 ns ±12941.66%          80 ns         140 ns

Extended statistics:

Name                   minimum        maximum    sample size                     mode
LegacyCalendar           70 ns    23697039 ns         7.68 M                    80 ns

Memory usage statistics:

Name              Memory usage
LegacyCalendar           432 B

**All measurements for memory usage were the same**

Profiling LegacyCalendar with eprof...

Profile results of #PID<0.546908.0>
#                                           CALLS     % TIME µS/CALL
Total                                          16 100.0    6    0.38
LegacyCalendar.time_to_string_format/3          1  0.00    0    0.00
LegacyCalendar.time_to_string/4                 1  0.00    0    0.00
LegacyCalendar.microseconds_to_iodata/2         1  0.00    0    0.00
LegacyCalendar.datetime_to_string/1             1  0.00    0    0.00
LegacyCalendar.date_to_string/3                 1  0.00    0    0.00
anonymous fn/0 in CalendarStringBench.run/0     1  0.00    0    0.00
:erlang.integer_to_binary/1                     2 16.67    1    0.50
LegacyCalendar.zero_pad/2                       7 33.33    2    0.29
:erlang.apply/2                                 1 50.00    3    3.00

Profile done over 10 matching functions

Final Source Code

Here's the complete optimized version that's similar to Elixir's master branch:

defmodule LegacyCalendar do
  def datetime_to_string(dt) do
    %{
      year: year,
      month: month,
      day: day,
      hour: hour,
      minute: minute,
      second: second,
      microsecond: microsecond
    } = dt

    [
      date_to_string(year, month, day),
      ?\s,
      time_to_string(hour, minute, second, microsecond)
      | "Z"
    ]
  end

  defp time_to_string(hour, minute, second, {microsecond, precision}) do
    [
      time_to_string_format(hour, minute, second),
      ?.
      | microseconds_to_iodata(microsecond, precision)
    ]
  end

  def microseconds_to_iodata(_microsecond, 0), do: []
  def microseconds_to_iodata(microsecond, 6), do: zero_pad(microsecond, 6)

  def microseconds_to_iodata(microsecond, precision) do
    num = div(microsecond, div_factor(precision))
    zero_pad(num, precision)
  end

  defp div_factor(1), do: 100_000
  defp div_factor(2), do: 10_000
  defp div_factor(3), do: 1_000
  defp div_factor(4), do: 100
  defp div_factor(5), do: 10

  defp time_to_string_format(hour, minute, second) do
    [zero_pad(hour, 2), ?:, zero_pad(minute, 2), ?: | zero_pad(second, 2)]
  end

  defp date_to_string(year, month, day) do
    [zero_pad(year, 4), ?-, zero_pad(month, 2), ?- | zero_pad(day, 2)]
  end

  defp zero_pad(val, count) when val >= 0 do
    num = Integer.to_string(val)

    case max(count - byte_size(num), 0) do
      0 -> num
      1 -> ["0" | num]
      2 -> ["00" | num]
      3 -> ["000" | num]
      4 -> ["0000" | num]
      5 -> ["00000" | num]
    end
  end

  defp zero_pad(val, count) do
    [?- | zero_pad(-val, count)]
  end
end

Key Differences and Further Optimization Potential

The final version includes several important characteristics:

Returns IOList Instead of String
- The function now returns an IOList directly instead of converting to a string
- This saves memory when used with IOData-aware libraries like Phoenix or JSON encoders
- No need for final string creation and garbage collection
Improper Lists Throughout
- Uses improper lists (|) at the end of lists instead of regular list construction
- Improves performance by reducing list traversal overhead
- Example: [zero_pad(hour, 2), ?:, zero_pad(minute, 2), ?: | zero_pad(second, 2)]

Further Optimization Possibilities

Special case for two-digit numbers (used frequently in time formatting):

 defp zero_pad(ones, 2) when ones >= 0 and ones < 10 do
   [?0, ones + ?0]
 end

 defp zero_pad(val, 2) when val >= 10 and val < 100 do
   tens = div(val, 10)
   ones = rem(val, 10)
   [tens + ?0, ones + ?0]
 end

Special case for years 2020-2029, this one is less spectacular, because we build the year only a single time:

 defp zero_pad(twentytwenty, 4) when twentytwenty >= 2020 and twentytwenty < 2030 do
   ones = twentytwenty - 2020
   ["202", ones + ?0]
 end

We could generalize this to be valid for the whole century:

 defp zero_pad(twenty_first_century, 4) when twenty_first_century >= 2000 and twenty_first_century < 2100 do
   ["20", zero_pad(rem(twenty_century, 2000), 2)]
 end

These optimizations could further improve performance for common cases

Trade-offs
- Adding more special cases increases code complexity
- The performance gain might not justify the maintenance cost

Final Thoughts

The optimization journey took us from a simple string concatenation approach to a highly optimized implementation using BEAM-specific features. The final version in Elixir's master branch represents a careful balance between performance and maintainability.

While there's room for further optimization through special cases, the current implementation already provides excellent performance with reasonable code complexity. The decision to return IOLists directly also provides flexibility for consumers of this API, allowing them to choose between string conversion and direct IOList usage based on their needs. The new JSON module already benefits from this.

A Note on Performance Measurements

When measuring performance, it's essential to understand the difference between simple timing tools like :timer.tc and comprehensive benchmarking frameworks like Benchee. In our initial measurements using :timer.tc, we saw around 3,150 microseconds per iteration, while Benchee showed a median of 551 nanoseconds per operation. This significant difference occurs because :timer.tc includes the overhead of the for loop and doesn't account for VM optimizations that happen during Benchee's warm-up phase.

For accurate performance comparisons, it's crucial to:

Use proper benchmarking tools that account for VM warm-up
Run multiple iterations to get statistically significant results
Consider the median and deviation, not just the average
Be aware of the measurement units (microseconds vs nanoseconds)

Pseudolocalization in Phoenix with gettext_pseudolocalize

Daniel Kukula — Fri, 27 Dec 2024 15:51:49 +0000

Understanding Gettext in Elixir Applications

Gettext is a widely adopted internationalization (i18n) system that helps developers make their applications available in multiple languages. Originally developed for GNU projects, it has become a standard solution across many programming languages and frameworks, including Elixir and Phoenix.

At its core, Gettext works by replacing hardcoded strings in your code with function calls that look up translations from a translation list. Instead of writing:

def hello do
  "Hello, World!"
end

You write:

def hello do
  gettext("Hello, World!")
end

This small change enables your application to display different text based on the user's locale. The translations themselves are stored in PO (Portable Object) files, typically organized by language:

priv/gettext/en/LC_MESSAGES/default.po
priv/gettext/es/LC_MESSAGES/default.po
priv/gettext/fr/LC_MESSAGES/default.po

Phoenix applications come with Gettext support out of the box, making it straightforward to build multilingual web applications. However, during development, it's common to face challenges like:

Ensuring UI elements can accommodate longer translations
Identifying hardcoded strings that should be translated
Testing how the application behaves with different languages
Verifying that all strings are properly marked for translation

This is where pseudolocalization comes in...

Pseudolocalization: Making Translation Issues Visible

When developing international applications, it's crucial to identify potential issues with translations before sending text to actual translators. This is where pseudolocalization comes in - a technique that transforms your English text into a modified version that simulates characteristics of other languages while remaining somewhat readable.

The gettext_pseudolocalize task transforms your Gettext strings into a special format that helps identify several common internationalization issues:

"Account Settings" → ⟦Åċċøüñť Șêťťíñğš~~~~~~~⟧

Let's break down what this transformation does:

Diacritical Marks: Letters are replaced with accented versions (A → Å, c → ċ). This helps identify font and character encoding issues.
Text Expansion: The ~~~~~ padding simulates how translations might be longer than the original text. Many languages, like German or Finnish, can be 30-40% longer than English.
Brackets: The ⟦ and ⟧ markers help identify string truncation and concatenation issues. If you see a broken bracket, you know something's wrong with string handling.

For example, if you have a UI element that looks fine with "Email" but breaks with ⟦Ȅɱàíĺ~~~~~~~~~~⟧, you know you need to adjust your layout to accommodate longer translations.

Common issues this helps identify:

Hard-coded string width assumptions
Improper text wrapping
Font compatibility problems
String concatenation issues
Missing translations

The beauty of pseudolocalization is that the text remains somewhat readable for developers while still testing the internationalization infrastructure of your application.

Implementing Pseudolocalization in Your Phoenix Application

Let's walk through setting up gettext_pseudolocalize in a new Phoenix project and see it in action.

First, create a new Phoenix project:

mix phx.new gtx
cd gtx
mix ecto.setup

Adding the Dependency

Add gettext_pseudolocalize to your dependencies in mix.exs:

def deps do
  [
    # ... other deps ...
    {:gettext_pseudolocalize, "~> 0.1"}
  ]
end

Then fetch the dependency:

mix deps.get

Configuration

We need to configure two things:

Tell Gettext to use our pseudolocalization plural forms handler for xx language in config/config.exs:

config :gettext, :plural_forms, GettextPseudolocalize.Plural

Set the default locale to "xx" (our pseudolocale) in development in config/dev.exs:

config :gtx, GtxWeb.Gettext, default_locale: "xx"

Generating Pseudolocalized Translations

Now we can extract all translatable strings and create pseudolocalized versions:

mix gettext.extract
mix gettext.pseudolocalize

This will:

Extract all gettext calls into PO template files
Generate a pseudolocalized version in the "xx" locale

Seeing It in Action

When you first run your Phoenix application, you might notice something interesting - not all strings are pseudolocalized. This actually helps us identify strings that aren't properly set up for internationalization!

For example, in the default Phoenix homepage, you'll see this string in its original form:

Peace of mind from prototype to production.

This tells us that this string isn't using Gettext and therefore won't be translatable in production. Let's fix that!

Edit lib/gtx_web/controllers/page_html/home.html.heex and change the hardcoded string to use gettext:

- Peace of mind from prototype to production.
+ {gettext("Peace of mind from prototype to production.")}

Now run the extraction and pseudolocalization commands:

mix gettext.extract
mix gettext.pseudolocalize

Looking at priv/gettext/xx/LC_MESSAGES/default.po, you'll see our string has been added and pseudolocalized:

#: lib/gtx_web/controllers/page_html/home.html.heex:57
#, elixir-autogen, elixir-format
msgid "Peace of mind from prototype to production."
msgstr "⟦Ƥêàċê øƒ ɱíñđ ƒȓøɱ ƥȓøťøťÿƥê ťø ƥȓøđüċťíøñ.~~~~~~~~~~~~~~~~~~⟧"

When you refresh your page, you'll now see the pseudolocalized version of the string. This visual difference makes it easy to spot which strings in your application are properly internationalized (pseudolocalized) and which ones are still hardcoded (original English).

This is one of the key benefits of using pseudolocalization during development - it helps you identify missed internationalization opportunities before they become problems in production.

Best Practices and Tips

When to Use Pseudolocalization

Early Development
- Enable pseudolocalization from the start of development
- Makes internationalization issues visible before they become deeply embedded
- Helps establish good i18n habits in your team
UI Reviews
- Use pseudolocalization during UI reviews to spot potential layout issues
- Pay special attention to:
  - Buttons and navigation elements
  - Forms and labels
  - Table headers
  - Alert messages

Common Issues to Watch For

Text Truncation
- If you see broken brackets ⟦ or ⟧, your UI is cutting off text
- Look for CSS text-overflow, fixed widths, or other constraints
Layout Breaking
- Watch for elements that jump to new lines or overlap
- The ~~~~~ padding helps simulate longer translations
- If it breaks with pseudo-text, it will likely break with real translations
Hardcoded Strings
- Any text that appears in regular English is likely missing gettext
- Common places to check:
  - Error messages
  - Flash messages
  - Email templates
  - Dynamic content

Conclusion

Internationalization is often treated as an afterthought, leading to rushed implementations, broken layouts, and frustrated users. The gettext_pseudolocalize library helps tackle these challenges early in the development cycle by making translation-related issues immediately visible.

Key Benefits

Early Detection: Spot internationalization issues before they reach production
Visual Feedback: Instantly see which strings are properly internationalized
Layout Testing: Identify UI components that won't scale with different language lengths
Developer Awareness: Builds good i18n habits in your development team

Real-world Impact

Instead of waiting for translations or testing with actual languages, pseudolocalization provides immediate feedback during development. This means:

Fewer emergency fixes when adding new languages
More consistent user experience across all locales
Reduced cost of internationalization
Better preparation for global markets

Next Steps

The library is open source and available on GitHub. You can:

Star the repository
Report issues or suggest improvements
Contribute to the codebase
Share your experience with the community

Remember, good internationalization isn't about just translating strings—it's about building applications that gracefully adapt to different languages and cultures. gettext_pseudolocalize is one tool to help achieve that goal.

Happy internationalizing! 🌍

Enhancements to dbg in elixir 1.18

Daniel Kukula — Sun, 22 Dec 2024 08:28:30 +0000

Elixir 1.18 added some interesting, features but one that went under the radar was extended support for dbg. In 1.17 when you had this code

a = 1
b = 2

if a + b == 3 do
  :equal
else
  :non_equal
end
|> dbg

It evaluated to:

if a + b == 3 do
  :equal
else
  :non_equal
end #=> :equal

In 1.18 this was aligned with what case and cond does:

Case argument:
1 + 2 #=> 3

Case expression (clause #1 matched):
case 1 + 2 do
  3 -> :equal
  _ -> :non_equal
end #=> :equal

if result in 1.18:

If condition:
a + b == 3 #=> true

If expression:
if a + b == 3 do
  :equal
else
  :non_equal
end #=> :equal

if this is not enough you can wrap your code in brackets and pass it to dbg

(
  input = %{a: 1, b: 2}
  c = Map.get(input, :c, 20)

  if input[:a] + c == 3 do
    :equal
  else
    :non_equal
  end
)
|> dbg

results in displaying results for every expression

Code block:
(
  input = %{a: 1, b: 2} #=> %{a: 1, b: 2}
  c = Map.get(input, :c, 20) #=> 20
  if input[:a] + c == 3 do
  :equal
else
  :non_equal
end #=> :non_equal
)

Last missing piece is the with macro which is sometimes a nightmare to debug, in elixir 1.18 we have support for that:

with input = %{a: 1, b: 2},
     {:ok, c} <- Map.fetch(input, :c),
     3 <- input[:a] + c do
  :equal
else
  _ -> :non_equal
end
|> dbg

The result here is:

With clauses:
%{a: 1, b: 2} #=> %{a: 1, b: 2}
Map.fetch(input, :c) #=> :error

With expression:
with input = %{a: 1, b: 2},
     {:ok, c} <- Map.fetch(input, :c),
     3 <- input[:a] + c do
  :equal
else
  _ -> :non_equal
end #=> :non_equal

This should make your debugging journey easier.
If you are stuck in an older elixir versions you can install a package that backports this functionality dbg_mate
Thanks for your attention.

Sql commenter with postgrex

Daniel Kukula — Sun, 15 Dec 2024 21:24:29 +0000

A few years ago, I discovered sqlcommenter, a tool that enables adding trace context to SQL queries. This feature makes it possible to match database calls visible in PostgreSQL logs or any observability tool. At the time, I found several questions about this topic scattered across the internet, but no concrete solutions. I attempted to solve this myself and experimented with various approaches, but none of them proved viable for implementation in Elixir, particularly with Ecto.

Since then, I've revisited this idea several times. Recently, I've taken a deeper dive into the subject and gained a better understanding of how the Ecto machinery works under the hood.

I created a pull request that added the ability to include comments in Postgrex queries, which was released in version 0.19.3. Now you can add comment: "this is the comment that will be added to query", and it will appear in the logs like this:

2024-12-15 20:02:33.328 GMT,"postgres",
"connector_test",29538,"127.0.0.1:42156",675f35d4.7362,57,
"SELECT",2024-12-15 20:02:28 GMT,33/50,240124,LOG,00000,
"execute <unnamed>: SELECT c0.""id"" FROM ""conversation"" AS c0
 WHERE (c0.""id"" = $1)/*this is the comment that will be added to query*/","parameters: $1 = '0193cbea-58ee-7f1c-8250-dd25c8e9f6cf'",
,,,,,,,"","client backend",,0

To add comments to every query, we can use Ecto.Repo's callback functions. Specifically, the prepare_query/3 callback:

@callback prepare_query(operation, query :: Ecto.Query.t(), opts :: Keyword.t()) ::
  {Ecto.Query.t(), Keyword.t()}

Where operation can be :all | :update_all | :delete_all | :stream | :insert_all.

We can use this callback to modify both Ecto and Postgrex options and add comments:

def prepare_query(_operation, query, opts) do
  comment = "comment that will be appended"
  {query, [comment: comment, prepare: :unnamed] ++ opts}
end

This is the basic implementation, but you can enhance it by copying the telemetry context. Note the prepare: :unnamed option - this disables prepared statements. Adding a comment always disables prepared statements, but I included it here explicitly for clarity. This is due that PostgreSQL uses the actual query string for prepared statements, but there are some important nuances to understand:

Query String as a Key: In PostgreSQL, the prepared statement is associated with a specific query string. This means if you prepare a query like SELECT * FROM table WHERE id = $1, PostgreSQL uses that exact query string as a reference.
Query Parsing: When a prepared statement is created, PostgreSQL parses the query string to generate a query plan. This query plan is then stored and reused when you execute the statement.
String Variations Matter: Small variations in the query string, such as extra spaces, case changes, or different formatting, result in PostgreSQL treating them as different queries. For example:

SELECT * FROM table WHERE id = $1/*comment1*/
SELECT * FROM table WHERE id = $1/*comment2*/

These would be treated as distinct prepared statements due to their string differences.

Sqlcommenter requires a specific format of the message.
The sqlcommenter format follows a specific convention where each key-value pair is URL-encoded and joined with commas. For example, app='connector',caller='MyModule.function' becomes app='connector',caller='MyModule%2Efunction'. This standardized format ensures consistency across different programming languages and frameworks while making the comments both human-readable and parseable by observability tools. Special characters and spaces are encoded to prevent SQL injection and maintain valid query syntax.
I created previously a package that you can use to encode your queries.
Full repo module that uses this package looks like this:

defmodule Connector.Repo do
  use Ecto.Repo,
    otp_app: :connector,
    adapter: Ecto.Adapters.Postgres

  @impl true
  def default_options(_operation) do
    [stacktrace: true]
  end

  @impl true
  def prepare_query(_operation, query, opts) do
    caller = Sqlcommenter.extract_repo_caller(opts, __MODULE__)
    node = if node() == :nonode@nohost, do: "connector", else: Atom.to_string(node())

    sqlcommenter = [
      app: "connector",
      caller: caller,
      node: node
    ]

    comment = Sqlcommenter.to_str(sqlcommenter)
    {query, [comment: comment, prepare: :unnamed] ++ opts}
  end
end

The default_options/1 is another Ecto.Repo callback that allows you to inject options at an earlier stage. Using this callback gives us access to the stacktrace in the options passed to prepare_query, which I used to identify where our query was called from. Another thing I added here is the node name.
Here's how it appears in the PostgreSQL log (I added few newlines for visibillity):

2024-12-15 20:48:19.541 GMT,"postgres",
"connector_test",35894,"127.0.0.1:52118",675f408e.8c36,96,
"SELECT",2024-12-15 20:48:14 GMT,21/61,240492,LOG,00000,
"execute <unnamed>: 
SELECT m0.""id"", m0.""role"" AS m0 WHERE (m0.""id"" = $1)
/*app='connector',caller='Connector.ChatTest.test%20messages%20get_message%21%2F1%20returns%20the%20message%20with%20given%20id%2F1',node='connector'*/",
"parameters: $1 ='0193cc14-4054-7dd5aaad-5943c073e8e6'",
,,,,,,,"","client backend",,0

I hope you found this information useful and that it adds another tool to your debugging toolkit. For cases where the comment remains static, like the one mentioned above, I'm currently working on another example that uses an experimental Ecto feature. If you're interested in learning more, please leave a like to let me know that you find this content valuable.
I wrapped this also as a package - contribute when you have ideas how to extend it.

Running Livebook as a Systemd Service for Your User

Daniel Kukula — Sun, 30 Jun 2024 07:32:15 +0000

Livebook is a powerful tool for creating and sharing interactive notebooks with Elixir. To make it even more convenient, you can set it up to run as a systemd service for your user. This ensures that Livebook starts automatically whenever you log in, and runs in the background without requiring root permissions. Here’s a step-by-step guide to help you get started.

Step 1: Install Livebook

First, ensure you have Livebook installed. You can install it via Elixir’s package manager, mix, by running:

mix escript.install hex livebook

Step 2: Create a User Systemd Service File

Systemd allows users to manage their own services. User-specific service files are stored in ~/.config/systemd/user/.

mkdir -p ~/.config/systemd/user
nano ~/.config/systemd/user/livebook.service

Step 3: Define the Service Configuration

Add the following content to the livebook.service file (replace USER and PASSWORD):

[Unit]
Description=Livebook Service

[Service]
Type=simple
ExecStart=/home/__USER__/.asdf/shims/livebook server
Restart=on-failure
Environment=HOME=%h
Environment=MIX_ENV=prod
Environment=PATH=/home/USER/.asdf/shims:/usr/local/bin:/usr/bin:/bin
Environment=LIVEBOOK_HOME=%h/livemd
Environment=LIVEBOOK_PASSWORD=__PASSWORD__
Environment=LIVEBOOK_PORT=8090
Environment=LIVEBOOK_IFRAME_PORT=8091


[Install]
WantedBy=default.target

Explanation:

ExecStart: The command to start Livebook. Ensure the path to the Livebook binary is correct.
Environment: Sets necessary environment variables. %h will expand to the user’s home directory.

Step 4: Reload User Systemd Configuration

After creating the service file, reload the systemd user configuration.

systemctl --user daemon-reload

Step 5: Enable the Service for Your User

Enable the service to start on user login.

systemctl --user enable livebook.service

Step 6: Start the Service for Your User

Start the Livebook service.

systemctl --user start livebook.service

Step 7: Check the Service Status

Verify that the service is running.

systemctl --user status livebook.service

Step 8: Enable User Systemd Services on Boot

Ensure that user services are started automatically at boot. You can enable lingering for your user:

sudo loginctl enable-linger USER

Replace USER with your actual username.

Conclusion

By following these steps, you can set up Livebook to run as a systemd service for your user. This setup ensures that Livebook starts automatically when you log in and runs smoothly in the background. Enjoy your interactive notebooks with the convenience of a managed service!

Machine learning on ryzen 7840hs

Daniel Kukula — Thu, 15 Feb 2024 19:50:19 +0000

I bought an amd laptop with 7840hs and 32GB ram with the hope that I can play with machine learning models. At the beginning it was problematic, I could not get anything bigger to work but after some time spent reading and experimenting here are some of my findings.

I'm using linux and I have the rocm packages installed.
I have these env variables exported:

export HSA_OVERRIDE_GFX_VERSION=11.0.0
export PYTORCH_ROCM_ARCH="gfx1100"

The integrated graphics on this processor has a unified memory architecture. It means that the memory is shared between cpu and gpu.

The gpu memory can be set in bios under the UMA size option.
But this is the minimal memory amount. When a game needs more memory it can request more memory. Can a model take advantage of that? It turns out that it can, at least it's implemented in llama.cpp, but it's not enabled by default. I managed to load mixtral quantized to 4bits fully loaded into memory and run interference on that. You can search the readme for hipblas.

it is also possible to use unified memory architecture (UMA) to share main memory between the CPU and integrated GPU by setting -DLLAMA_HIP_UMA=ON". However, this hurts performance for non-integrated GPUs (but enables working with integrated GPUs).

But what with other frameworks?
Most of the frameworks support rocm somehow. For example pytorch has dedicated builds that you install with this command:

pip3 uninstall torch torchvision torchaudio
pip3 install --pre torch torchvision torchaudio --index-url https://download.pytorch.org/whl/nightly/rocm5.6/

The problem here is that pytorch does not support the shared memory yet and we are limited by the maximum memory that you can set in bios. My bios only allows to set 4GB of total 32GB my machine has.

I found a workaround to this by using Smokeless_UMAF that allows to access settings that may not be exposed in normal bios.
Thanks to it I could set 16GB of ram to iGPU.

Now when I load comfyUI I'm greeted with this text:

Total VRAM 16384 MB, total RAM 15677 MB                                         
Set vram state to: NORMAL_VRAM                                                  
Device: cuda:0 AMD Radeon Graphics : native

This may be risky. There is a known problems section in the readme. My bios loads but the page where I can set the size of shared memory hangs so there is also a problem with my laptop.
On the other hand I don't change it very often so I can live with that.

Enhancing SQL Traceability with Sqlcommenter in Elixir

Daniel Kukula — Tue, 03 Oct 2023 18:38:45 +0000

This post is outdated - there is a solution now: https://dev.to/dkuku/sql-commenter-with-postgrex-2bfd

For anyone who prefers to read he code - both ways are here: TLDR

In the world of database operations, traceability is a crucial aspect. It's essential to know which piece of code generated a specific SQL query. This knowledge becomes even more important when working with data mapping frameworks. That's where Sqlcommenter for Elixir comes into play.

Sqlcommenter is an Elixir library designed for SQL comment management, enabling you to securely attach metadata as comments to SQL statements generated within your application.
The metadata undergoes an escape process before being transformed into a comment, ensuring that it remains safeguarded against SQL injection risks.
In this blog post, we'll explore how to use Sqlcommenter to enhance your SQL traceability in Elixir.

Sqlcommenter is built based on the specification provided by https://google.github.io/sqlcommenter/. This specification serves as the foundation for Sqlcommenter's functionality and ensures its compatibility with industry standards.

Installation

Getting started with Sqlcommenter in your Elixir project is straightforward. You need to add it as a dependency in your mix.exs file:

def deps do
  [{:sqlcommenter, "~> 0.1"}]
end

After adding the dependency, run mix deps.get.

Usage

Once Sqlcommenter is installed, you can start using it to trace your SQL queries. Currently Ecto does not have any way to do this automatically, there is no comment option in ecto. I saw multiple questions about this already and no proper plan to implement it. We need to work around this limitation. I found 2 ways of doing this both with own limitations. Here's a step-by-step guide on how to do it:

Generate raw sql and attach the trace infor

First, you'll need to modify your MyApp.Repo module by adding a new function called all_traced. This function will be responsible for attaching SQL comments to your SQL queries:

  def all_traced(queryable, opts \\ []) do
    {metadata, opts} = Keyword.pop(opts, :metadata, %{})
    {:current_stacktrace, stacktrace} = Process.info(self(), :current_stacktrace)

    stacktrace =
      stacktrace |> Enum.drop(2) |> Enum.take(1) |> Exception.format_stacktrace() |> String.trim()

    queryable = Ecto.Queryable.to_query(queryable)
    {query, params} = __MODULE__.to_sql(:all, queryable)
    query = Sqlcommenter.append_to_query(query, put_in(metadata, [:stacktrace], stacktrace))

    {:ok, result} =
      __MODULE__.query(query, params, opts)

    struct =
      case queryable.from do
        # maybe other load types
        %{source: {_, struct}} -> struct
      end

    Enum.map(
      result.rows,
      &__MODULE__.load(struct, {result.columns, &1})
    )
  end

The all_traced function is a custom function you can add to your Elixir application when using the Sqlcommenter library. This function serves the purpose of enhancing traceability for SQL queries generated by your application when interacting with a database. Let's break down what this function does and why it's useful:

Function Signature: The all_traced function takes two arguments:

queryable: This argument represents the ecto query you want to execute
opts: This is an optional argument that allows you to pass additional options to the function. In the context of Sqlcommenter, it can include metadata that you want to attach as SQL comments to the generated query.

Metadata: Within the function, the opts argument is processed to extract metadata. Metadata consists of key-value pairs that provide context or information about the query. For example, in the blog post's code snippet, the request_id is extracted from the metadata.
Additionally the metadata will be enhanced by the function name that executed the query. This is extracted from the stacktrace.

SQL Query Generation: The function uses Repo.to_sql to convert the queryable into an SQL query and its parameters. This step is necessary to prepare the query for execution.

Sqlcommenter Integration: The key role of the all_traced function is to integrate Sqlcommenter into the query. It calls Sqlcommenter.append_to_query and passes the original query and metadata params as arguments. Sqlcommenter then appends the metadata as an SQL comment to the query.

Query Execution: Finally, the modified query is passed to Repo.query for execution. This is where the actual database query is executed, but now it includes the SQL comment added by Sqlcommenter.

Loading Data: Repo.query returns raw values from the database, we have to load the values back to Ecto.Schmas - this happens at the end of the function where we extract the struct name and use Repo.load to load the data.

In essence, the all_traced function is a middleware or wrapper around your database queries. It extends the query with SQL comments that contain metadata, making it easier to trace the origin of the query. This traceability is particularly valuable in scenarios where you need to correlate your query with web request id.

By using all_traced and Sqlcommenter, you can associate SQL queries with specific context information, like the request_id in the example, which can be immensely helpful for debugging, monitoring, and auditing your database interactions. You can also load the trace_id, span_id when using OpenTelemetry or Spandex. A disadvantage here is that it has to be modified to use more complicated queries with preloads or schemaless queries

2. Using the `all_traced` Function

Now that you've defined the all_traced function, you can use it for querying your database. Here's an example:

Schemas.Person
|> Repo.all_traced(metadata: %{request_id: Ecto.UUID.generate()})

In this example, we're querying the Person schema and attaching a request_id to it. This request_id is added as an SQL comment, making it easier to trace the query later.

SQL Query Traceability

When you execute the above code, you'll receive your data as usual. However, behind the scenes, Sqlcommenter has added a comment to your SQL query. Here's what it might look like:

SELECT p0."id", p0."first_name" FROM "person"."person" AS p0 /*request_id='fa2af7b2-d8e1-4e8f-8820-3fd648b73187'*/ []

As you can see, the request_id is embedded as an SQL comment within the query. This simple addition makes it much easier to correlate the SQL statement with the originating code.

Using Ecto.Query.lock

Lock is the last part appended to the query in postgres. Ecto accepts a string as this param, so we can just insert or append our Sqlcommenter data into this field. This change is very simple:

Person
|> where([p], p.id == ^ person_id)
|> lock(Sqlcommenter.to_str(metadata))

It could be as simple as that but ecto is checking and makes sure there is no way of SQL injection attacks.

** (Ecto.Query.CompileError) `Sqlcommenter.to_str(a: :b)`
 is not a valid lock. For security reasons,
 a lock must always be a literal string or a fragment

Also ecto is caching queries in ets, This way it does not have to build the query every time is executed, having every time a different lock makes it impossible to cache.
That being said, we can get around it by modifying the function and swapping the data in the actual query to skip this validation:

  def all_traced_lock(queryable, opts \\ []) do
    {metadata, opts} = Keyword.pop(opts, :metadata, %{})
    {:current_stacktrace, stacktrace} = Process.info(self(), :current_stacktrace)

    stacktrace =
      stacktrace |> Enum.drop(2) |> Enum.take(1) |> Exception.format_stacktrace() |> String.trim()

    metadata = put_in(metadata, [:stacktrace], stacktrace)

    queryable =
      queryable
      |> Ecto.Queryable.to_query()
      |> Map.update(:lock, nil, fn
        nil -> Sqlcommenter.to_str(metadata)
        lock -> Sqlcommenter.append_to_query(lock, metadata)
      end)

    __MODULE__.all(queryable, opts)
  end

This change either creates the lock entry or appends to it.
This makes it easier to load the data, you can also use schemaless queries and preloads.

Conclusion

It would be great when ecto would support this out of the box, sqlcommenter is compatible with opentelemetry which becomes the de facto standard for tracing elixir code.
I know that both of my proposal are hacks but any tool in your toolbox that helps with debugging may come in handy at some point.

Livebook on steam deck

Daniel Kukula — Thu, 19 Jan 2023 20:23:25 +0000

This post may be updated in the future.

Steam deck is a modern machine. I wanted to test how long it takes to generate images using stable diffusion on it.
Thanks to livebook this becomes trivial. The biggest problem is to install the package. I did it using ssh but it can also be done directly on deck.
You need first switch to desktop mode.
From here you need to open Konsole and setup password for your user:

passwd

next thing is to enable sshd server:

sudo systemctl start sshd

having ssh enabled you can use your laptop to connect

ssh deck@steamdeck

next you need to install docker:

# enables the root partition to be rw
sudo steamos-readonly disable
# syncs the system
sudo pacman -Syyu
# updates stored signing keys
sudo pacman-key --refresh-keys
sudo pacman -S archlinux-keyring
# syncs the system again with new keys
sudo pacman -Syu
# installs docker
sudo pacman -S docker
# optional will start docker every time the deck starts
sudo systemctl enable docker
 # starts docker daemon for current session
sudo systemctl start docker

Having docker installed we can run any docker image - so lets process with docker - it's basically the same command as from the livebook read me - just the port is changed, the suggested one is already in use.

sudo docker run -p 8888:8080 -p 8081:8081 --pull always -u $(id -u):$(id -g) -v $(pwd):/data livebook/livebook

After running this command you'll get an url printed in the terminal. I connected to it using my browser but I had to change the host from http://0.0.0.0:8080 to http://192.168.1.123:8888 - your steam deck ip may be different - I had to get mine from my router settings.
Next you can follow the official video from Jose:

https://news.livebook.dev/announcing-bumblebee-gpt2-stable-diffusion-and-more-in-elixir-3Op73O
The initial model download takes 5-20min depending on your internet connection. Stable diffusion runs on the steam deck but it's not very performant because it's running on the cpu - image generation takes around 8 minutes.
You can try other models also.

Reverse engineer unknown data format using elixir

Daniel Kukula — Tue, 10 Jan 2023 18:37:35 +0000

I recently worked on porting the csv file converter for AdventureWorks database from ruby to elixir. This ended successful and I have all the data in my local postgresql to play with. By browsing the tables I found spatial_location column in person.address table and I wanted to decode it.

|address_line1       |city   |spatial_location                            |
|--------------------|-------|--------------------------------------------|
|1970 Napa Ct.       |Bothell|E6100000010CAE8BFC28BCE4474067A89189898A5EC0|
|9833 Mt. Dias Blv.  |Bothell|E6100000010CD6FA851AE6D74740BC262A0A03905EC0|
|7484 Roundtree Drive|Bothell|E6100000010C18E304C4ADE14740DA930C7893915EC0|
|9539 Glenside Dr    |Bothell|E6100000010C813A0D5F9FDE474011A5C28A7C955EC0|
|1226 Shoe St.       |Bothell|E6100000010C61C64D8ABBD94740C460EA3FD8855EC0|
|1399 Firestone Drive|Bothell|E6100000010CE0B4E50458DA47402F12A5F80C975EC0|
|5672 Hale Dr.       |Bothell|E6100000010C18E304C4ADE1474011A5C28A7C955EC0|
|6387 Scenic Avenue  |Bothell|E6100000010C0029A5D93BDF4740E248962FD5975EC0|
|8713 Yosemite Ct.   |Bothell|E6100000010C6A80AD742DDC4740851574F7198C5EC0|

As you can see the data looks like binary data.
I found even some documentation from microsoft that may be useful here but in the end I could not use it with luck.
So what I end up doing to decode it.

First what I know and may be useful:

I got the city and address
I know that the data is binary encoded as string because there is nothing highier than F.
The string is 44 letters long - every byte is encoded using 2 letters so I have 22 bytes of data

Lets jump to iex:

iex(1)> encoded = "E6100000010C6A80AD742DDC4740851574F7198C5EC0"
"E6100000010C6A80AD742DDC4740851574F7198C5EC0"
iex(2)> String.length(encoded)
44
iex(3)> decoded = Base.decode16!(encoded)
<<230, 16, 0, 0, 1, 12, 106, 128, 173, 116, 45, 220, 71, 64, 133, 21, 116, 247,
  25, 140, 94, 192>>

Next thing that I did was to find what exactly can be hidden in my data:
The beginning and end look the same between the first lines I found - it may be some kind of wrapper and I may need to skip it.
I also searched for the actual coordinates of Bothell to see if I can see some similarities:

It looks like the data encoded is around 47.5 and -122.5
so let's move back to iex.

iex(3)> decoded = Base.decode16!(encoded)
<<230, 16, 0, 0, 1, 12, 106, 128, 173, 116, 45, 220, 71, 64, 133, 21, 116, 247,
  25, 140, 94, 192>>
iex(4)> <<47.0::float>>
<<64, 71, 128, 0, 0, 0, 0, 0>>
iex(5)> <<47.99::float>>
<<64, 71, 254, 184, 81, 235, 133, 31>>

I tried how float 47 looks like in binary form. It starts with
64, 71 and has 8 bytes. Looking at the encoded data I have 64 and even 71 - just in reverse order, is it a coincidence??
Let's try with -122

<<230, 16, 0, 0, 1, 12, 106, 128, 173, 116, 45, 220, 71, 64, 133, 21, 116, 247,
  25, 140, 94, 192>>
iex(7)> <<-122.0::float>>                
<<192, 94, 128, 0, 0, 0, 0, 0>>
iex(8)> <<-122.9::float>>
<<192, 94, 185, 153, 153, 153, 153, 154>>

192, 94 - again I can see similar pattern but in reverse order at the end.
It turn's out that I can use the little endian modifier to create similar looking data:

iex(9)> <<-122.9::little-float>>
<<154, 153, 153, 153, 153, 185, 94, 192>>

Hmm - definitely looks similar. Especially that it's 8 numbers from the end and If I look at the encoded data then on the 9th position I have 64 which is the possible beginning of 47.0 encoded as binary. When removing this data I end up with 6 bits at the beginning. Back to iex:

iex(10)> <<_::binary-size(6), x::little-float, y::little-float>> = decoded
<<230, 16, 0, 0, 1, 12, 106, 128, 173, 116, 45, 220, 71, 64, 133, 21, 116, 247,
  25, 140, 94, 192>>
iex(11)> x
47.7201372000862
iex(12)> y
-122.189084876407

Was it really so easy??
Close but I can't see the street 8713 Yosemite Ct.

Lets try with another one:
6387 Scenic Avenue, Bothell

iex(15)> <<_::binary-size(6), x::little-float, y::little-float>> = 
iex(..)> Base.decode16!("E6100000010C0029A5D93BDF4740E248962FD5975EC0")  
<<230, 16, 0, 0, 1, 12, 0, 41, 165, 217, 59, 223, 71, 64, 226, 72, 150, 47, 213,
  151, 94, 192>>
iex(16)> x
47.7440139824339
iex(17)> y
-122.372386833918

Again it's not a complete hit. Let's se if we can be more specific?
From the ms docs I found that the encoded data starts with SRID

SRID (4 bytes): (32 bit integer) The SRID for the geography. GEOGRAPHY structures MUST use
SRID values in the range of 4120 through 4999, inclusive, with the exception of null geographies.
A value of -1 indicates a null geography. When a null geography is indicated, all other fields are
omitted. Default SRID for GEOGRAPHY instances is 4326. Default SRID for GEOMETRY instances is
zero (0). For GEOMETRY instance, SRID can be any value: SRID is not constrained.

Let's againg try to decode it, this time it's an integer.

iex(35)> <<srid::little-integer-size(32), _::binary>> = Base.decode16!("E6100000010C0029A5D93BDF4740E248962FD5975EC0")
<<230, 16, 0, 0, 1, 12, 0, 41, 165, 217, 59, 223, 71, 64, 226, 72, 150, 47, 213,
  151, 94, 192>>
iex(36)> srid
4326

This is in the 4120..4999 range and after short google search it turns out that it's a standard encoding that is used by openstreetmap.
Let's check how far are we off at least?

Ohh - maybe that's the problem? Let's try another one:

🤦🤦🤦🤦🤦🤦🤦
It turn's out that the data is synthetic so the exact location won't be shown. But it does not mean that it was not worth checking out how to do this. I definitely learned something along the way and I hope so are you when reading it.

TIL: Non standard postgres types in livebook

Daniel Kukula — Sun, 08 Jan 2023 13:01:43 +0000

Livebook has a new, nice feature that allows you to connect to a database. It works with basic data types.

But I found a problem when the data type is not supported by postgrex.

In this case, you can either have to write a custom postgrex extension like the example here. In postgrex GitHub repo, there are many examples for common data types, but XML is missing there.

You can also cast your field to a supported data type:

SELECT demographics::text from person.person

To play with this example, you can install the example Adventureworks database provided by Microsoft. I made an elixir converter that converts some of the files to a format accepted by PostgreSQL, and also updated the previous version to properly name the columns with underscores.

Visualize explain results from postgresql in pgcli

Daniel Kukula — Tue, 05 Apr 2022 19:55:05 +0000

Recently I wrote a visualizer for explain query responses from postgresql. I also integrated it into pgcli and it got merged few days ago. You can install it currently with:

pip install git+https://github.com/dbcli/pgcli@main

or wait for 3.4.2 release and update.
To switch between normal mode and explain mode you can press F5

DEV Community: Daniel Kukula

Simplifying Ecto Fragments with Defrag: A Better Way to Write Database Queries

Simplifying Ecto Fragments with Defrag: A Better Way to Write Database Queries

The Problem with Traditional Fragments

Enter Defrag: String Interpolation for Ecto Fragments

How It Works

Real-World Usage

Benefits

Getting Started

Final Thoughts

The Journey of Optimization

Initial Investigation: The String Concatenation Problem

Enter IOLists: A Better Approach

The Original Implementation

Step 1: Converting to IOLists

Step 2: Optimizing Microseconds

Step 3: Optimizing Zero Padding

Step 4: Improper Lists

Final Optimization: Special Cases

Memory Benefits

Conclusion

Benchee Output

Initial Implementation

Optimized Implementation

Final Optimized Implementation

Final Source Code

Key Differences and Further Optimization Potential

Final Thoughts

A Note on Performance Measurements

Pseudolocalization in Phoenix with gettext_pseudolocalize

Understanding Gettext in Elixir Applications

Pseudolocalization: Making Translation Issues Visible

Implementing Pseudolocalization in Your Phoenix Application

Adding the Dependency

Configuration

Generating Pseudolocalized Translations

Seeing It in Action

Best Practices and Tips

When to Use Pseudolocalization

Common Issues to Watch For

Conclusion

Key Benefits

Real-world Impact

Next Steps

Enhancements to dbg in elixir 1.18

Sql commenter with postgrex

Running Livebook as a Systemd Service for Your User

Step 1: Install Livebook

Step 2: Create a User Systemd Service File

Step 3: Define the Service Configuration

Step 4: Reload User Systemd Configuration

Step 5: Enable the Service for Your User

Step 6: Start the Service for Your User

Step 7: Check the Service Status

Step 8: Enable User Systemd Services on Boot

Conclusion

Machine learning on ryzen 7840hs

Enhancing SQL Traceability with Sqlcommenter in Elixir

Installation

Usage

Generate raw sql and attach the trace infor

2. Using the all_traced Function

SQL Query Traceability

Using Ecto.Query.lock

Conclusion

Livebook on steam deck

Reverse engineer unknown data format using elixir

TIL: Non standard postgres types in livebook

Visualize explain results from postgresql in pgcli

2. Using the `all_traced` Function