Jaco Pretorius

How to Use Variables in PostgreSQL psql Scripts (with Examples)

Wed, 04 Jun 2025 00:00:00 +0000

The psql command-line interface for PostgreSQL lets you pass parameters into your SQL scripts using variables. The psql --help command explains how to accomplish this:

psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -f, --file=FILENAME      execute commands from file, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)

If I create this simple script:

SELECT
  *
FROM
  customers
WHERE
  id = :ID
;

I can invoke it via psql:

psql --file=script.sql --variable=ID=2

 id | name
----+------
  2 | Bob
(1 row)

We immediately run into problems if we don’t consider how variables are interpolated.

psql --file=script.sql --variable=ID="Roger Wilco"
psql:script.sql:7: ERROR:  syntax error at or near "Wilco"
LINE 6:   id = Roger Wilco
                     ^

Note that the variable is not being interpreted as a string, but rather directly substituted in the script itself. This is quite powerful, since it means we can pass in variables to substitute as the name of a table or column. It also means it’s extremely prone to SQL injection attacks.

psql --file=script.sql --variable=ID="42; SELECT VERSION();"

 id | name
----+------
(0 rows)

          version
----------------------------
 PostgreSQL 15.13 (Homebrew)
(1 row)

Quoting Variables as Literals vs. Identifiers in psql

We need to tell psql to quote the variables. There are two ways to quote variables:

For quoting variables as a literal, use :'VARIABLE_NAME', for example SELECT * FROM customers WHERE id = :'USER_ID';
For quoting variables as an identifier, use :"VARIABLE_NAME", for example SELECT * FROM customers ORDER BY :"ORDER_BY_FIELD";

SELECT
  *
FROM
  customers
ORDER BY
  :"ORDER_BY_FIELD"
LIMIT
  :'LIMIT'
;

psql --file=script.sql --variable=ORDER_BY_FIELD=name --variable=LIMIT=5

 id |  name
----+---------
  1 | Alice
  2 | Bob
  3 | Charlie
  4 | Diana
  5 | Ethan
(5 rows)

One very important caveat is that psql actually parses the SQL in order to do the substitution, and cannot replace variables inside of a string. This becomes very important if you are trying to substitute variables inside of a pl/PgSQL function or DO block, since these are executed server-side and parsed as a string, making psql substitution impossible.

DO
$$
DECLARE
  customer_name TEXT;
BEGIN
  SELECT
    name
  INTO
    customer_name
  FROM
    customers
  WHERE
    id = :'CUSTOMER_ID'
  ;

  RAISE NOTICE 'Customer % is named %', :'CUSTOMER_ID', customer_name;
END
$$;

Trying to pass the variable into this script will fail:

psql --file=script.sql --variable=CUSTOMER_ID=2
psql:script.sql:18: ERROR:  syntax error at or near ":"
LINE 13:     id = :'CUSTOMER_ID'
                  ^

Unfortunately there doesn’t seem to be a standard way to pass variables into DO blocks, the best workaround I’ve seen is to use current_setting. The downside is that your variable is stored as a string, which means you need to consider casting it to the appropriate type.

SET custom_settings.customer_id = :'CUSTOMER_ID';

DO
$$
DECLARE
  customer_name TEXT;
BEGIN
  SELECT
    name
  INTO
    customer_name
  FROM
    customers
  WHERE
    id = current_setting('custom_settings.customer_id')::INTEGER
  ;

  RAISE NOTICE 'Customer % is named %', current_setting('custom_settings.customer_id'), customer_name;
END
$$;

psql --file=script.sql --variable=CUSTOMER_ID=2
psql:script.sql:20: NOTICE:  Customer 2 is named Bob

Note that you need to prefix/namespace your setting - as I did above with custom_settings - otherwise PostgreSQL will blow up thinking you are trying to access system configuration settings.

One last note - you can’t use variables with inline commands, since --command doesn’t parse variables unless input comes via stdin (e.g., from <<< or |), as it doesn’t treat SQL the same way as file or piped input.

psql --variable=NAME=Bob --command="SELECT :'NAME';"
ERROR:  syntax error at or near ":"
LINE 1: SELECT :'NAME';
               ^

Instead, we would need to use piping:

psql --variable=NAME=Bob <<< "SELECT :'NAME';"
 ?column?
----------
 Bob
(1 row)

echo "SELECT :'NAME';" | psql --variable=NAME=Bob
 ?column?
----------
 Bob
(1 row)

Most of my knowledge in this post comes from this post by depesz. Variables in psql are powerful for dynamic scripting and automation — but they come with some gotchas. By understanding quoting rules and limitations (especially around DO blocks and inline commands), you can write safer, more maintainable SQL scripts. Happy scripting!

Avoiding N+1 Queries with Rails Strict Loading

Wed, 28 May 2025 00:00:00 +0000

Active Record makes it easy to introduce N+1 queries into your Rails applications. The convenience of easily adding associations to your models means that great care is needed to avoid N+1 queries. I was therefore excited to see that Rails 6.1 introduced strict_loading to help alleviate some of these problems. I had previously used the bullet gem which adds visibility around these issues but built-in guardrails are a great addition to Rails. I came across this deep dive by Jordan Hollinger while researching strict loading, and it helped clarify some of the finer points—especially around n_plus_one_only.

Strict loading can be enabled at various levels: model, association, individual record, query, or across the entire application. Strict loading can be enabled in two modes - all and n_plus_one_only. You can also configure the action that is taken when a strict loading violation occurs, either raising an error or logging a warning.

Individual Queries

customers = Customer
  .preload(:addresses)
  .where(id: ...)
  .strict_loading

customers.each { |customer| customer.addresses.each(&:inspect) }
# No errors, since addresses are pre-loaded

customers.each { |customer| customer.statements.each(&:inspect) }
# ActiveRecord::StrictLoadingViolationError

You can also disable strict loading, but there doesn’t seem to be an option to specify the mode.

customers = Customer
  .strict_loading(false)
  .where(id: ...)

customers.each { |customer| customer.statements.each(&:inspect) }
# No errors (despite N+1 queries)

Individual Records

customer = Customer.find(...)
customer.strict_loading!
customer.statements.first
# ActiveRecord::StrictLoadingViolationError

customer.strict_loading!(mode: :n_plus_one_only)
customer.statements.first
# No errors

Model Level

class Customer < ApplicationRecord
  self.strict_loading_by_default = true

  # New option in Rails 8.0
  self.strict_loading_mode = :n_plus_one_only # or :all
end

customer = Customer.find(...)
customer.addresses.each(&:inspect) # ActiveRecord::StrictLoadingViolationError

Association Level

class Customer < ApplicationRecord
  has_many :addresses, strict_loading: true
end

customer = Customer.find(...)
customer.addresses.each(&:inspect) # ActiveRecord::StrictLoadingViolationError

Application Level

# config/application.rb
config.active_record.strict_loading_by_default = true # defaults to false
config.active_record.action_on_strict_loading_violation = :log # defaults to :raise

# New option in Rails 8.0
config.active_record.strict_loading_mode = :n_plus_one_only # defaults to :all

The different ways to enable strict loading make sense to me - if you’re creating a new application you will probably want to opt-in by default, but if you’re working on an existing application you might want to enable it by default and then selectively opt-out models, individual queries, or records. The association level doesn’t really make sense to me. I can’t see a compelling use case for it.

n_plus_one_only Mode

The n_plus_one_only mode can be confusing in practice. To illustrate how it works I have to create a few different associations.

class Customer < ApplicationRecord
  has_many :addresses
  has_many :statements
end

class Statement < ApplicationRecord
  belongs_to :customer
  has_many :entries, class_name: 'StatementEntry'
end

class StatementEntry < ApplicationRecord
  belongs_to :statement
end

Then I have also enabled strict loading across the entire application in n_plus_one_only mode.

statements = Statement.where(id: ...)
statements.each { |statement| statement.customer.name }
  Statement Load (2.0ms)  SELECT "statements".* FROM "statements" WHERE "statements"."id" BETWEEN ? AND ?  [["id", 1], ["id", 5]]
  Customer Load (0.1ms)  SELECT "customers".* FROM "customers" WHERE "customers"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Customer Load (0.1ms)  SELECT "customers".* FROM "customers" WHERE "customers"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Customer Load (0.0ms)  SELECT "customers".* FROM "customers" WHERE "customers"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Customer Load (0.0ms)  SELECT "customers".* FROM "customers" WHERE "customers"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Customer Load (0.0ms)  SELECT "customers".* FROM "customers" WHERE "customers"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]

This is a textbook example of when you’d expect strict loading to raise an error. Similarly, let’s load all the entries for each statement (since that’s a has_many association).

statements = Statement.where(id: ...)
statements.each { |statement| statement.entries.map(&:description).join(', ') }
  Statement Load (1.1ms)  SELECT "statements".* FROM "statements" WHERE "statements"."id" BETWEEN ? AND ?  [["id", 1], ["id", 5]]
  StatementEntry Load (0.0ms)  SELECT "statement_entries".* FROM "statement_entries" WHERE "statement_entries"."statement_id" = ?  [["statement_id", 1]]
  StatementEntry Load (0.0ms)  SELECT "statement_entries".* FROM "statement_entries" WHERE "statement_entries"."statement_id" = ?  [["statement_id", 2]]
  StatementEntry Load (0.0ms)  SELECT "statement_entries".* FROM "statement_entries" WHERE "statement_entries"."statement_id" = ?  [["statement_id", 3]]
  StatementEntry Load (0.1ms)  SELECT "statement_entries".* FROM "statement_entries" WHERE "statement_entries"."statement_id" = ?  [["statement_id", 4]]
  StatementEntry Load (0.0ms)  SELECT "statement_entries".* FROM "statement_entries" WHERE "statement_entries"."statement_id" = ?  [["statement_id", 5]]

Note that both of these snippets correctly raise an error when I change the strict_loading_mode to :all. I’m not the first one to notice this behavior, there is a Rails Github Issue that funnily enough flags Strict Loading n_plus_one_only doesn't catch N+1 problem. The underlying idea behind n_plus_one_only appears to be avoiding errors when working with a single record, since eager-loading in that scenario could mean that you are loading data unnecessarily.

Consider the following snippet:

statement = Statement.find(...)
statement.entries.map(&:description).join(', ')

This will raise an error if strict_loading_mode is set to :all, but not if it is set to :n_plus_one_only. That makes sense to me - loading the data later on doesn’t make the query worse, but always pre-loading it could mean we’re loading unnecessary data - for example, if we pass the statement to a view that conditionally renders the entries. However, given how :n_plus_one_only behaves in other scenarios I don’t see how it makes sense to enable it globally. The problem seems to be that Rails simply doesn’t know whether you are accessing associations from within the context of a single record or a collection of records.

Conclusion

If you’re starting a new Rails application I think the only sensible options are

Ignore strict loading and continue to use the bullet gem (and be diligent about avoiding N+1 queries)
Enable strict loading globally in :all mode

I can maybe see a use case for enabling it globally and then selectively disabling it for individual records where you explicitly know that you don’t want to pre-load unnecessary data. I also prefer using the raise option for violations instead of simply logging, but I can also understand that this might seem too risky for some.

A completely different option might be to use Occams Record which avoids N+1 queries by default. I haven’t tried this in a real Rails application yet, but I am eager to do so.

Understanding PostgreSQL LATERAL Joins with Practical Examples

Fri, 23 May 2025 00:00:00 +0000

PostgreSQL has a powerful feature called Lateral joins, which lets you reference columns from earlier tables in the FROM clause. This becomes especially useful when working with data that changes over time, like historical addresses or versioned records.

For example, consider a domain where we keep track of customers and statements. Customers can change their addresses over time, but it’s important that a statement has the appropriate historical address for a customer.

CREATE TABLE addresses (
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  address TEXT NOT NULL,
  effective_on DATE NOT NULL,
  PRIMARY KEY (customer_id, effective_on)
);

CREATE TABLE statements (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  statement_date DATE NOT NULL,
  balance NUMERIC(10, 2) NOT NULL
);

I have created some sample data:

SELECT
  customers.id
, customers.name
, addresses.address
, addresses.effective_on
FROM
  customers
INNER JOIN
  addresses
ON
  customers.id = addresses.customer_id
;

 id | name  |   address   | effective_on
----+-------+-------------+--------------
  1 | Alice | 123 Main St | 2024-01-01
  1 | Alice | 456 Oak Ave | 2024-04-01
  2 | Bob   | 789 Pine Rd | 2024-02-15

Think of the Lateral join as a kind of “for-each loop” inside SQL: for each row in statements, it runs a subquery on addresses using the statement_date. Without LATERAL, this would require a more verbose join with DISTINCT ON, a window function, or a nested subquery.

SELECT
  statements.id
, statements.statement_date
, customers.name
, statement_addresses.address
FROM
  statements
JOIN LATERAL (
  SELECT
    addresses.address
  FROM
    addresses
  WHERE
    addresses.customer_id = statements.customer_id
  AND
    addresses.effective_on <= statements.statement_date
  ORDER BY
    addresses.effective_on DESC
  LIMIT 1
 ) statement_addresses
ON
  TRUE
JOIN
  customers
ON
  customers.id = statements.customer_id
;

 id | statement_date | name  |   address
----+----------------+-------+-------------
  1 | 2024-02-01     | Alice | 123 Main St
  2 | 2024-05-01     | Alice | 456 Oak Ave
  3 | 2024-03-01     | Bob   | 789 Pine Rd

Over the past few years, I’ve become more of an advocate for tracking changes over time and using SQL to reconstruct the state of data at any given point. PostgreSQL’s LATERAL joins make this approach even more powerful, enabling elegant, performant queries that access related, time-sensitive records with minimal fuss.

rails new: Complete Guide to All Options

Tue, 13 May 2025 00:00:00 +0000

Every time I run the rails new command I try to remember what options I prefer - of course, you can change things later, but if you’re looking to get up and running quickly it’s nice to get it mostly correct on the first try. I did an audit of all the options as of rails 8.0.2 and grouped them in a way that made sense to me, since the default output provided by rails new --help can be difficult to parse through.

Options You Definitely Want to Specify

--database=DATABASE specifies the database adapter to use. The options are sqlite3, postgresql, mysql, oracle, sqlserver, jdbc, and none. The default is sqlite3.

--javascript=JAVASCRIPT tells Rails which JavaScript bundler or integration to set up for your new application. The possible values are importmap, bun, webpack, esbuild, and rollup. The default is importmap.

--css=CSS tells Rails which CSS processor to use for your new application. The possible values are tailwind, bootstrap, bulma, postcss, and sass. The default is tailwind.

Options To Skip Components You Don’t Need

--skip-action-mailer tells Rails not to include Action Mailer in your new app. This means Rails will not generate any email-related folders like app/mailers, not configure default mailer settings in config/environments/* and not include action_mailer in config/application.rb.

--skip-action-mailbox tells Rails not to include Action Mailbox in your new application. Action Mailbox is a Rails framework that lets your application receive inbound emails and process them as part of your business logic.

--skip-action-text tells Rails not to include Action Text in your new application. Action Text is a built-in Rails framework for rich text content, powered by the Trix editor. It allows users to write formatted text and embed images and attachments.

--skip-active-record tells Rails not to include Active Record, the built-in ORM (Object-Relational Mapping) framework. This is probably the trickiest option to add later on if you decide to skip it at first.

--skip-active-job tells Rails not to include Active Job, the framework Rails provides for background job abstraction.

--skip-active-storage tells Rails not to include Active Storage, the built-in framework for file uploads and attachments. If you choose to skip it at first you can add it later with bin/rails active_storage:install.

--skip-action-cable tells Rails not to include Action Cable, which is Rails’ built-in framework for WebSockets and real-time communication.

--skip-asset-pipeline tells Rails not to include any asset pipeline, meaning it won’t set up tools to manage and compile JavaScript, CSS, or images.

--skip-javascript tells Rails not to set up any JavaScript tooling or files in your new application. By default Rails adds package.json, app/javascript/application.js, JavaScript helpers like @hotwired/turbo-rails and @rails/ujs and the relevant config based on your JavaScript approach (see --javascript=JAVASCRIPT).

--skip-hotwire tells Rails not to include Hotwire, the default real-time frontend stack introduced in Rails 7. This includes Turbo and Stimulus.

--skip-jbuilder tells Rails not to include Jbuilder, which is the default JSON response templating library in Rails. Jbuilder lets you build JSON responses using Ruby in .json.jbuilder templates. To add it later you simply add it to your Gemfile and run bundle install.

--skip-test tells Rails not to generate the default test framework, which is Minitest, and to skip creating test files entirely. This will exclude minitest from the Gemfile, skip creating the test folder, and also not generate test files if you’re using rails generate controller/model. Use it if you’re using RSpec or another test framework

--skip-system-test tells Rails not to set up system tests, which are end-to-end browser-based tests using Capybara. This option skips generating the test/system folder, application_system_test_case.rb, and does not configure Capybara or install any system test drivers. --skip-test implies that system tests will also be skipped, so --skip-system-test is redundant unless used alone.

--skip-bootsnap tells Rails not to include Bootsnap, which is a performance optimization library that speeds up boot time by caching expensive operations.

--skip-dev-gems is a new option in Rails 8 and tells Rails to omit development-specific gems from the generated application’s Gemfile. These gems are typically included to enhance the development experience but are not necessary for production environments, e.g. web-console and listen.

--skip-thruster is a new option in Rails 8 and tells Rails to exclude the setup for Thruster, a new HTTP/2 proxy.

--skip-rubocop tells Rails not to include the RuboCop gem and .rubocop.yml configuration file. In Rails 7.2, the rails new command began including RuboCop by default in newly generated applications.

--skip-brakeman tells Rails not to include Brakeman, a static analysis security scanner for Ruby on Rails applications. Brakeman scans your Rails codebase for potential security vulnerabilities — without needing to run the app or its tests. It’s commonly used in CI pipelines or local development to catch problems early.

--skip-ci tells Rails not to create the GitHub Actions CI workflow in .github/workflows/ci.yml. By default this Workflow runs tests and Rubocop.

--skip-kamal tells Rails to exclude the default setup for Kamal, a deployment tool integrated into Rails to simplify application deployment. To add it later you add it to your Gemfile, run bundle install, and then bin/kamal init.

--skip-solid allows you to exclude the default setup for Solid components, which include Solid Cache (A caching backend that stores cached data in the database), Solid Queue (A database-backed job queue system that serves as the default Active Job backend), and Solid Cable (A database-backed Action Cable adapter for real-time features).

--skip-docker tells Rails to skip generating the docker config like Dockerfile and bin/docker-entrypoint. By default Rails will not generate the docker config, but this guards against a template or future config change enabling it by default. There is also a --docker option, but it’s not listed in rails new --help, because it’s considered an internal or ‘hidden’ option right now.

--devcontainer, --no-devcontainer, and --skip-devcontainer control whether a Dev Container configuration is generated for Visual Studio Code’s Remote - Containers / Dev Containers feature. --no-devcontainer and --skip-devcontainer are synonyms. By default Rails will not generate Dev Containers.

Options For the Generator Itself

--skip-collision-check tells Rails to overwrite existing files. By default, Rails will refuse to overwrite existing files like Gemfile, .gitignore, etc.

--ruby=PATH changes the shebang (#!) at the top of generated scripts like bin/rails and bin/rake. The default is #!/usr/bin/env ruby.

template=TEMPLATE allows you to specify a custom Ruby script that will run during project generation. It allows you to automate additional setup steps, like adding gems that you always include (devise, rubocop, standardrb, etc) and run generators. TEMPLATE can be a path to a file or a remote URL.

--skip-git tells Rails to skip all the git commands after generating the new app. By default Rails will run git init, stage all the files with git add . and make an initial commit. This option also skips the .gitignore file.

--skip-keeps means Rails won’t create any .keep files — so empty folders will truly be empty. That means they may be missing from Git until something is added.

--rc=RC lets you specify a file path for load additional options for the rails new command. By default, Rails looks for a .railsrc file in your home directory (~/.railsrc). If you want to prevent Rails from loading any .railsrc file, you can use the --no-rc option.

--skip-bundle tells Rails not to run bundle install automatically after generating the new app.

--skip-decrypted-diffs tells Rails not to setup a Git filter to show decrypted diffs for credential files. Rails uses encrypted credentials (like config/credentials.yml.enc) to securely store sensitive information. By default, Rails can configure Git to automatically show the decrypted contents of these files when viewing diffs, making it easier to see what has changed.

Grouped Options

--api, --no-api, and --skip-api control whether the generated application is a full-stack Rails app or a lightweight API-only app. API-only apps are optimized for serving JSON and exclude views, helpers, assets, and various other behaviors not typically needed in API-only applications. By default Rails assumes you are building a full-stack application, so equivalent to --non-api.

--minimal creates a lightweight Rails application by excluding several default frameworks and tools. This applies several of the --skip-* options and excludes components such as Active Job, Action Mailer, Action Mailbox, Active Storage, Action Text, Action Cable, JavaScript, Hotwire, Jbuilder, System tests, Bootsnap, Development gems, Brakeman, Rubocop, CI configuration files, Docker setup, Kamal, Solid components, and Thruster. By default Rails assumes you are building a non-minimal application, so equivalent to --non-minimal.

Options You Will Probably Never Use

--dev, --no-dev, and --skip-dev are special internal flags meant for Rails contributors or advanced users working on Rails itself. This allows you to tell Rails to generate the app using the local checkout of the Rails framework, instead of pulling gems from rubygems.org.

--edge, --no-edge, and --skip-edge control whether the newly generated application uses the edge branch/version of Rails through configuration in the Gemfile. --no-edge / --skip-edge are aliases that explicitly say: ‘don’t use edge Rails.’ They’re rarely needed unless you’re overriding an inherited or default behavior. Similarly, the --master, --main, --no-main, and --skip-main control the same behavior, but for pointing at the main branch.

Options for Engines and Plugins

--skip-namespace is used when generating engines or plugins, not during normal app creation. So if you’re doing rails new myapp then it does nothing. If you’re generating a plugin, i.e. rails plugin new my_plugin by default, Rails namespaces everything under the plugin name — so MyPlugin::Engine, MyPlugin::ApplicationController, etc. This options allows you to skip that.

--name=NAME is used with the rails plugin new and rails engine new generators - and not with normal app creation. It sets the internal Ruby module/class name for the plugin or engine independent of the directory name.

My Default Options

Here are my default options for rails new:

rails new my_app \
  --database=postgresql \
  --javascript=esbuild \
  --css=bootstrap \
  --skip-action-mailbox \
  --skip-action-text \
  --skip-action-cable \
  --skip-jbuilder \
  --skip-test \
  --skip-thruster \
  --skip-kamal \
  --skip-solid \
  --skip-decrypted-diffs

--skip-test because I generally use rspec, not minitest.
--skip-jbuilder since I can easily add it if I need it later on.
--skip-solid because I only really use Solid Queue, so I install that separately.
--skip-kamal because I generally deploy to Heroku. For the same reason, I also --skip-decrypted-diffs, since I prefer to use Heroku environment variables for credentials.

I want to look into the --template option, since that seems like a great way to add all the other defaults that I can’t configure from rails new, such as standardrb and rspec.

Remove Nth Node From End of List

Fri, 09 May 2025 00:00:00 +0000

I occasionally solve algorithm questions on Leetcode as a fun exercise. I recently wrote about a dynamic programming question I solved, and I thought it would be fun to share another one. This time it’s a simple linked list question.

Remove Nth Node From End of List

Given the head of a linked list, remove the nth node from the end of the list and return its head.

Example 1:
Input: head = [1,2,3,4,5], n = 2
Output: [1,2,3,5]

Example 2:
Input: head = [1], n = 1
Output: []

Example 3:
Input: head = [1,2], n = 1
Output: [1]

I started off just creating a skeleton of the solution that allows me to run the examples and see the output.

class ListNode
  attr_accessor :val, :next
  def initialize(val = 0, _next = nil)
    @val = val
    @next = _next
  end
end

# @param {ListNode} head
# @param {Integer} n
# @return {ListNode}
def remove_nth_from_end(head, n)
  head
end

def print_list(head)
  result = []
  while head
    result << head.val
    head = head.next
  end
  pp result
end

print_list(remove_nth_from_end(ListNode.new(1, ListNode.new(2, ListNode.new(3, ListNode.new(4, ListNode.new(5))))), 2))
print_list(remove_nth_from_end(ListNode.new(1), 1))
print_list(remove_nth_from_end(ListNode.new(1, ListNode.new(2)), 1))

Now onto the actual solution. Since this is a linked list we can’t simply walk all the way to the end of the list and then backtrack. Instead I found it useful to think of the desired end state of the algorithm - we want to end up with a pointer pointing to the node before the nth node from the end of the list. So in the example of [1, 2, 3, 4, 5] we want a pointer at the 3 node, which is the third node from the end, in order to be able to do p.next = p.next.next.

The trickiest part of this algorithm is the edge cases - I got a bit stuck on how to solve for [1, 2] with n = 1 (the result being [1]) and n = 2 (the result being [2]). Then I realized that when the size of the list is the same as n, that is a special case where you return head.next. In all the other cases I can modify the list and return head.

# @param {ListNode} head
# @param {Integer} n
# @return {ListNode}
def remove_nth_from_end(head, n)
  p = head
  list_size = 0
  until p.nil?
    list_size += 1
    p = p.next
  end

  if n == list_size
    head.next
  else
    p = head
    (list_size - n - 1).times { p = p.next }
    p.next = p.next.next
    head
  end
end

I didn’t really enjoy doing this algorithm as much as the dynamic programming one. I’m doing the algorithms because it’s something I enjoyed doing when I was a student, and it helps to bring back some of the joy of programming for it’s own sake. Maybe I’ll stick to the ‘hard’ questions, or at least questions where a brute force answer is easy but an optimal one takes some creativity.

Interacting with Shell Commands in Ruby

Fri, 09 May 2025 00:00:00 +0000

We often need to run shell commands from within Ruby code, and Ruby provides a number of ways to do that. It’s sligtly confusing at first - specifically because there are different ways of accomplishing the same thing. I found this useful flowchart on StackOverflow that helps you choose the appropriate option. If you’re looking for a detailed writeup of the different options I recommend reading 6 Ways to Run Shell Commands in Ruby by Nate Murray.

One common mistake I’ve seen - and made myself - is to use string interpolation when passing arguments to a shell command. For example, in my recent post on Tweeting new Jekyll Posts I was using Ruby to interact with the git command. I could have written:

# Git add and commit (unsafe example)
commit_message = "Blogged: #{document.title} #{URI.join(site_config.fetch('url'), document.url)}"
`git add #{post_path}`
`git commit -m "#{commit_message}"`

This is a bad idea because

Can break if it includes double quotes, ampersands, semicolons, etc.
Can lead to command injection if the title includes malicious input.

We could get around (1) by using Shellwords.escape and you could argue I don’t have to worry about (2) because I control the input. However, best practice dictates that you should never interpolate any arguments into shell commands. Instead you should use system and pass the arguments as parameters.

# Git add and commit
commit_message = "Blogged: #{document.title} #{URI.join(site_config.fetch('url'), document.url)}"
system('git', 'add', post_path)
system('git', 'commit', '-m', commit_message)

This principle doesn’t just apply to Ruby - I had the exact same issue with Java code I wrote recently. Instead of building the entire command:

String command = String.format(
    "pg_dump --dbname=%s --host=%s --port=%s --username=%s",
    connectionProperties.dbName(),
    connectionProperties.host(),
    connectionProperties.port(),
    connectionProperties.username()
);

ProcessBuilder processBuilder = new ProcessBuilder("bash", "-c", command);
processBuilder.redirectErrorStream(true);
Process process = processBuilder.start();

Pass the arguments as parameters:

List<String> pgDumpCommand =
    List.of(
        "pg_dump",
        "--dbname",
        connectionProperties.dbName(),
        "--host",
        connectionProperties.host(),
        "--port",
        connectionProperties.port().toString(),
        "--username",
        connectionProperties.username()
    );

ProcessBuilder processBuilder = new ProcessBuilder(
    Stream.of(pgDumpCommand, options).flatMap(Collection::stream).toList()
);
processBuilder.redirectErrorStream(true);
Process process = processBuilder.start();

In this example I also needed to allow the consumers of this code (which I control) to pass additional options. In that case I would also suggest that you use an explicit whitelist/allowlist for accepted options.

Fingerprinting Jekyll SASS Assets

Wed, 07 May 2025 00:00:00 +0000

As I’ve been updating the stylesheets on my blog, I ran into an issue with browser caching — changes to my CSS weren’t showing up right away. Since I’m serving assets through AWS CloudFront with a 7-day cache for non-HTML files, this behavior makes sense. While I could disable caching altogether, that feels like a blunt and amateur solution. Instead, I’m implementing asset fingerprinting to keep the performance benefits of caching while ensuring everyone always get the latest version of my styles.

I’m using the built-in jekyll-sass-converter plugin for Jekyll to compile my SASS files into CSS. Unfortunately it doesn’t offer any support for fingerprinting assets. I searched around and found the jekyll-minibundle which supports both minification and fingerprinting, but it doesn’t work with jekyll-sass-converter. I did stumble upon this gist by Yaroslav Markin which looked appealing - just a few lines of code and you have a hand-rolled digesting solution.

# frozen_string_literal: true

require 'digest'

module Jekyll
  # Jekyll assets cachebuster filter
  #
  # Place this file into `_plugins`.
  module CachebusterFilter
    # Usage example:
    #
    # {{ "/style.css" | cachebuster }}
    # {{ "/style.css" | cachebuster | absolute_url }}
    def cachebuster(filename)
      sha256 = Digest::SHA256.file(
        File.join(@context.registers[:site].dest, filename)
      )

      "#{filename}?#{sha256.hexdigest[0, 6]}"
    rescue StandardError
      # Return filename unmodified if file was not found
      filename
    end
  end
end

Liquid::Template.register_filter(Jekyll::CachebusterFilter)

I ran my site locally and it worked - my css file had a fingerprint appended.

<link rel="stylesheet" href="/css/main.css?b0763bce">

I pushed it to my staging site and it didn’t work - no fingerprint. Which must mean the filename wasn’t found. I then went through a few rounds of debugging with ChatGPT - adding Jekyll logging, etc - and finally concluded that ChatGPT’s initial direction (which I ignored) was correct - when the filter runs the compiled CSS file doesn’t exist yet. It works locally because the previous build had already generated the file.

A quick hacky fix was to run jekyll build twice in staging, which did work - but is obviously not a great solution. Instead I needed to either get access to the compiled CSS file or generate the digest from the source SASS files. I don’t think it’s possible to access the compiled CSS file - because it doesn’t necessarily exist yet - so instead I started to look at the jekyll-sass-converter code in more detail.

At a high level the filter is invoked on the output of the SASS converter - in my case, /css/main.css. It then needs to

Find the source manifest file - in my case, /css/main.scss
Find all referenced SASS files

Instead of trying to parse out the SASS files I opted to simply look at all the files in my /_sass directory. The plugin allows you to alter the source directory for the SASS files (and add others) so ideally I need to interact with the plugin directly and get the config that way - I didn’t want to duplicate the code to parse the plugin’s config. Luckily this is something supported by Jekyll.

# frozen_string_literal: true

require 'digest'

module Jekyll
  # Jekyll assets sass_digest filter
  module SassDigestFilter
    # Usage example:
    #
    # {{ "/style.css" | sass_digest }}
    # {{ "/style.css" | sass_digest | absolute_url }}
    def sass_digest(filename)
      site = @context.registers[:site]
      return site.data['sass_digest'][filename] if site.data.dig('sass_digest', filename)

      scss_file = site.in_source_dir("#{File.dirname(filename)}/#{File.basename(filename, '.css')}.scss")
      unless File.exist?(scss_file)
        Jekyll.logger.warn 'SassDigest:', "#{scss_file} does not exist"
        return filename
      end

      scss_converter = site.find_converter_instance(Jekyll::Converters::Scss)
      if scss_converter.nil?
        Jekyll.logger.warn 'SassDigest:', "#{Jekyll::Converters::Scss} converter not found"
        return filename
      end

      files = [site.in_source_dir(filename.sub(/\.css$/, '.scss'))]
      scss_converter.sass_load_paths.each do |path|
        Dir.glob("#{site.in_source_dir(path)}/**/*.scss") do |sass_file|
          files << sass_file
        end
      end

      site.data['sass_digest'] ||= {}
      site.data['sass_digest'][filename] = "#{filename}?#{digest(files)}"
    end

    private

    def digest(files)
      combined = files.sort.map { |f| File.read(f) }.join
      Digest::SHA256.hexdigest(combined)[0, 8]
    end
  end
end

Liquid::Template.register_filter(Jekyll::SassDigestFilter)

The last piece that I had to figure out was how to cache the computed digest within a single build - it turns out the site object has a data hash that makes this straightforward.

I’m using this for now, but I’m not confident that this is the correct approach. I want to see if it’s possible to add this to the jekyll-sass-converter plugin directly.

Tweeting New Jekyll Posts From Github Actions - Part 2

Mon, 05 May 2025 00:00:00 +0000

I previously wrote about my experience attempting to use Github Actions to post a tweet every time I publish a new post on my self-hosted Jekyll/S3/Cloudfront blog. I managed to get to a working solution that was too complicated, so I’m trying another approach. I was following this post by Dave Brock where he described using the commit message as the entire tweet - so every commit message and git push is a tweet. I dismissed it as too simple, but now that I’ve seen how complicated the alternative is I’m going to try something similar.

Here is the new, simplified, workflow I envisioned:

When I am ready to add a new post to git I run a Ruby script that generates the commit message
When I deploy to production the Github Action workflow tweets out the commit message

The first step is to write a Ruby script that generates the commit message. Identifying the post title and full url seems straightforward, but Jekyll allows you lots of flexibility around generating URLs, so I didn’t want to optimize for one specific use case. It turns out that I can instantiate the jekyll objects and tell it to parse the post itself.

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'uri'
require 'jekyll'

# Get uncommitted post files
status_lines = `git status --porcelain _posts`.lines
new_posts = status_lines.select { |line| line.start_with?('??') }.map { |line| line.split.last }

if new_posts.empty?
  puts 'No new, uncommitted post found.'
  exit 0
end

# Initialize a site object from _config.yml
site_config = Jekyll.configuration({ quiet: true })
site = Jekyll::Site.new(site_config)

# Path to the new post
post_path = File.expand_path(new_posts.first)
document = Jekyll::Document.new(post_path, { site: site, collection: site.posts })
document.read

# Git add and commit
commit_message = "Blogged: #{document.title} #{URI.join(site_config.fetch('url'), document.url)}"
system('git', 'add', post_path)
system('git', 'commit', '-m', commit_message)

puts "Committed with message:\n#{commit_message}"

A nice part of this approach is that I can easily test the script locally.

A key piece of nuance is that not every commit is a new post - sometimes I’m editing an old post (fixing a typo, updating a link, etc), or I’m making a change to the site itself (Jekyll config, css, etc). I wanted to update my Github Action workflow to look for a commit message with the Blogged: prefix. Github Actions exposes the latest commit message via a variable - github.event.head_commit.message - but it’s not populated on manual workflows (which is what I’m using for a production deploy). So instead I need to manually fetch the latest git commit.

- name: Get latest commit message
  id: get_commit
  run: |
    msg=$(git log -1 --pretty=%B)
    echo "message=$msg" >> $GITHUB_OUTPUT

- name: Tweet the new post
  if: startsWith(steps.get_commit.outputs.message, 'Blogged:')
  uses: nearform-actions/github-action-notify-twitter@master
  with:
    message: ${{ steps.get_commit.outputs.message }}
    twitter-app-key: ${{ secrets.TWITTER_CONSUMER_API_KEY}}
    twitter-app-secret: ${{ secrets.TWITTER_CONSUMER_API_SECRET}}
    twitter-access-token: ${{ secrets.TWITTER_ACCESS_TOKEN }}
    twitter-access-token-secret: ${{ secrets.TWITTER_ACCESS_TOKEN_SECRET }}

This is a much simpler approach to what I had before, and it’s much easier to debug and predict what is going to happen. The only downside is that I need to be careful with my git commits before pushing to production - for example, if I fix a typo before pushing to production I need to be careful to amend the original commit.

Tweeting New Jekyll Posts From Github Actions

Sat, 03 May 2025 00:00:00 +0000

This site is built with Jekyll and hosted on Amazon S3, with Cloudfront as the CDN. I recently did some work to make the deployments automated with Github Actions.

I wrote about that experience already, but the current workflow is:

Every push to main is deployed to a staging environment (I don’t use pull requests)
Deployments to production is handled through a github action workflow that is manually triggered

I previously used Blogger and WordPress to manage my blog and both of those had the option to automatically tweet about new posts, but there is no built-in mechanism to do that with Jekyll (if you’re self-hosting). Of course I could manually tweet about every new post, but what’s the fun in that?

Here is the workflow I envisioned:

When I trigger the deployment to production some Github Action is triggered that checks for any new posts
For every new post (usually just one), extract the title and URL of the post
Tweet about it

How hard could it be? (Spoiler: very)

I read through this post by Dave Brock who had a similar goal, but he simply put his desired tweet as the commit message. I didn’t really like that approach - Jekyll already knows the post title and URL, and it’s all available in git - so I should be able to get that information and tweet it out.

The first idea I had was to start using Github releases to track every new ‘publication’ - meaning when I deploy to production a new release is created, and I can check for any new posts introduced between this latest release and the previous one. I started down that path but then I realized that it’s actually too heavy-handed for what I wanted - I’m really only using the existance of the tag.

I started off creating a new tag on every production deploy, so just extending my existing workflow:

- name: Create Release Tag
  env:
    GH_TOKEN: ${{ secrets.GITHUB_TOKEN }}
  run: |
    TAG_NAME=production-release-$(date +'%Y.%m.%d.%H%M')
    git tag "$TAG_NAME"
    git push origin "$TAG_NAME"

The GITHUB_TOKEN is automatically exposed by Github Actions, so that felt like I was on the right track. Instead of extending the production workflow further I decided to have a dedicated ‘tweet’ workflow that is triggered by the creation of a new tag.

on:
  push:
    tags:
      - 'production-release-*'

In order to test this new workflow I simply had it print out the newly created tag name. However, when I tested it the workflow never triggered. Apparently this is by design:

GitHub does not trigger a new workflow run when a tag is pushed using the default GITHUB_TOKEN (which you’re using via GH_TOKEN). This is a deliberate design choice to prevent infinite loops between workflows.

Not to worry, I did some more reading and discovered I can set the trigger for my tweet workflow to explicitly kick off when the production deployment workflow completes. That’s arguably even cleaner, since I’m not relying on side effect of the deployment - I’m relying on the deployment workflow itself. The only annoying bit is that I need to explicitly check if the workflow run was successful.

on:
  workflow_run:
    workflows: ["Deploy to production on manual trigger"]
    types:
      - completed
jobs:
  tweet:
    if: ${{ github.event.workflow_run.conclusion == 'success' }}
    runs-on: ubuntu-latest
    ...

I tested this and ran into an obscure error:

Error
No event triggers defined in `on`

Again, apparently this is a known issue (according to ChatGPT - I didn’t find this note in the documentation):

workflow_run only triggers on workflows that run in response to events like push, pull_request, schedule, etc. It does not trigger for workflows started manually via workflow_dispatch.

This is a known limitation in GitHub Actions: workflow_run does not support triggering on workflows that were manually triggered (workflow_dispatch).

I had the option of trying to use a repository_dispatch - which is manually calling a Github API to trigger the workflow via an HTTP API - or simply adding the tweet steps to the existing workflow. Since having a separate workflow wasn’t particularly important I opted to try that first.

At this point I needed to parse the current and previous tags, determine if there was a new post in the changeset, extract the title and URL of the post(s), and invoke the proper twitter API. The git part of this was reasonably straightforward after I read through some of the documentation:

- name: Get previous production release tag
  id: calculate_previous_release_tag
  run: |
    TAG=$(git tag --list 'production-release-*' --sort=-creatordate --no-contains | head -n 1)
    echo "previous_release_tag=$TAG" >> $GITHUB_OUTPUT

- name: Identify new published post
  id: identify_new_post
  run: |
    CURRENT_TAG=${{ github.ref_name }}
    PREVIOUS_TAG=${{ steps.calculate_previous_release_tag.outputs.previous_release_tag }}

    echo "The new release tag is: $CURRENT_TAG"
    echo "The previous release tag is: $PREVIOUS_TAG"

    POST_PATH=$(git diff --name-only --diff-filter=A "$PREVIOUS_TAG" "$CURRENT_TAG" -- _posts/ | head -n 1)
    if [ -n "$POST_PATH" ]; then
      echo "Found new post: $POST_PATH"
      echo "post_path=$POST_PATH" >> $GITHUB_OUTPUT
    fi

Extracting the title and URL of the post was not that straightforward. I was hoping there was a jekyll command to get that metadata, but nothing like that exists (although I’m sure you could write a plugin to do that nicely). I started to do this with a bunch of bash scripts but it felt super shaky, so I wrote a simple ruby script instead.

content = File.read(options[:post_path])
post_metadata = YAML.safe_load(content.split(/^---\s*$/, 3)[1])

if options[:extract] == 'title'
  raise "#{options[:post_path]} does not contain a title" unless post_metadata.key?('title')

  puts post_metadata['title']
else
  raise "#{options[:post_path]} does not contain a permalink" unless post_metadata.key?('permalink')

  site_metadata = YAML.safe_load(File.read('_config.yml'))
  puts URI.join(site_metadata['url'], post_metadata['permalink'])
end

This script also feels a bit shaky, but less so than having to write bash to remove comments out of a YAML file. At this point I finally had the title and URL of the newly added post, all extracted from the git history. All that was left was to use the Github Action to send Twitter notifications.

- name: Tweet the new post
  if: steps.identify_new_post.outputs.post_path != ''
  uses: nearform-actions/github-action-notify-twitter@master
  with:
    message: "🚀 Blogged: ${{ steps.extract_post_info.outputs.post_title }}\n\n${{ steps.extract_post_info.outputs.post_url }}"
    twitter-app-key: ${{ secrets.TWITTER_CONSUMER_API_KEY}}
    twitter-app-secret: ${{ secrets.TWITTER_CONSUMER_API_SECRET}}
    twitter-access-token: ${{ secrets.TWITTER_ACCESS_TOKEN }}
    twitter-access-token-secret: ${{ secrets.TWITTER_ACCESS_TOKEN_SECRET }}

And at this point I decided I really hate this entire solution: it’s way too complicated. I think the approach used by Dave Brock is actually much better - I can simply write commit messages with a certain format and invoke the Github Action as part of production releases if that format is present. I can easily write a ruby script to write the commit message which will be much easier to test and debug.

Read on to part 2 where I implement this new approach.

Using Separate Database Users for Migrations in Rails

Wed, 30 Apr 2025 00:00:00 +0000

In the past year I have done a lot of work with PostgreSQL (PG) and managing DDL (Data Definition Language) changes. This work has been focused on Java applications which use Flyway to manage migrations, which has less abstractions than Rails migrations. I have been thinking about how to apply some of the lessons I’ve learnt to Rails applications.

One of the first changes - or best practices - I applied with PG migrations was to use separate roles (the PG concept for user or group) for running migrations and for the application itself. This is important for a few reasons:

It’s a basic best practice to allow only a privileged role to perform DDL changes to limit the impact of any unintended SQL queries from the application layer - bugs, hackers, etc
In PG the role that creates database objects (e.g. tables and schemas) is also set as the owner of that object, and by default the owner has certain (usually full) privileges (the PG concept for permissions) on those objects.

There are a few gems that provide guardrails around Rails migrations

These are all wrappers around the native Active Record migrations in various forms and focus on executing the migrations themselves, but don’t consider permissions or roles. The Active Record Migrations Guide also doesn’t mention anything about permissions, roles, or related topics like object ownership.

Active Record doesn’t provide any documented path for running migrations as a separate role from the application role. Here are a few options I have found on StackOverflow and related sites:

Create a separate environment for running migrations

This would involve create a new environment and related database role in config/database.yml. In order to run migrations, you would need to specify the new environment, eg. RAILS_ENV=migrations rails db:migrate. In practice you would need to have a migration environment for each of your existing environments, so development_migrations, test_migrations, production_migrations, etc - since you run migrations in all of these environments, and running with a different role only in production seems like a bad idea. The golden rule is that you want to have the same privileges in local development (and CI) as in production.

This idea could work, but I really don’t like the idea of adding a new configuration file for config/environments/production_migrations.rb (as well as all the other environments). Intuitively running migrations feels more like a concept that happens within a given environment, rather than a separate environment. If Rails has a configuration property to set the migration connection string - which defaulted to the environment’s existing connection string - that would feel like a pretty natural feature to me.

Modify the migration rake task

There are a few posts online suggesting this solution in various forms - creating a custom rake task, monkey patching or enhancing the db:migrate task, etc. ChatGPT was very keen on the idea of using an initializer to manually establish the database connection with a different role.

# config/initializers/override_connection_for_migrations.rb
if defined?(Rails::Command) && ARGV.include?("db:migrate")
  puts "[INFO] Using migration user"
  ActiveRecord::Base.establish_connection(
    Rails.configuration.database_configuration[Rails.env].merge(
      'username' => ENV['MIGRATION_DB_USERNAME'] || 'migration_user',
      'password' => ENV['MIGRATION_DB_PASSWORD'] || 'migration_password'
    )
  )
end

This could work, but I’m not sure if all the different ways of running migrations (eg. db:structure:load, db:reset, etc) would work correctly.

Environment Variables

Active Record allows you to specify the canonical database connection string via a DATABASE_URL variable. We can use this behavior to specify a different connection string and user when running migrations.

I found a nice way to structure this behavior in this post on PgAnalyze. The idea is to use the foreman gem with a Procfile entry to run your migrations.

web: bundle exec puma -C ./config/puma.rb
migrate: DATABASE_URL=${DATABASE_URL_MIGRATIONS:?DATABASE_URL_MIGRATIONS must be set} bundle exec rails db:migrate

This seems like the most sensible approach to me - since it ties into an existing Active Record feature. It’s also possible to write a gem to capture some of this behavior and make it more specific to the db:migrate task. This approach has the same problem as with the custom or modified rake task - it may not work with all the different ways of running migrations.

I’m going to try the environment variable approach first, since that also works nicely with Heroku (which is my default deployment platform). As part of this I also want to explore how to manage different database roles (locally and in production) and dump the entire database schema - which includes privileges and ownership.