Invalid SQL Generated?

[DalenW]

I'm working on implementing rodauth into a fresh project. When I submit the new user form, I get this error:

Sequel::DatabaseError (PG::SyntaxError: ERROR: at or near ":": syntax error DETAIL: source SQL: SELECT 1 AS "one" FROM "user_verification_keys" WHERE (("id" = 941860947847741441) AND ((CAST("requested_at" AS timestamp) + make_interval(secs := 86400)) > CURRENT_TIMESTAMP)) LIMIT 1 ^ ):

The error is the : in secs := 86400

I am using CockroachDB, which is fully compatible with Postgresql. Any suggesstions?

[janko]

Rodauth generates the same SQL query on PostgreSQL:

SELECT * FROM "accounts" WHERE (("type" = 'main') AND ("id" = 16) AND (("status" = 2) OR (("status" = 1) AND ("id" IN (SELECT "id" FROM "account_verification_keys" WHERE ((CAST("requested_at" AS timestamp) + make_interval(secs := 86400)) > CURRENT_TIMESTAMP)))))) LIMIT 1

So, the issue is with CockcroachDB not being fully compatible with PostgreSQL. It appears that := is an older syntax, and that PostgreSQL 12+ introduced =>. Since Sequel aims to support older Postgres versions (at least Postgres 8+), that's probably the reason why it uses the legacy syntax.

I will see if Sequel can be updated to use the new syntax for newer versions. I just installed CockroachDB, and the Sequel adapter reports it matching Postgres 13, so that should be enough to make it compatible with CockroachDB.

Since this is not a bug in rodauth-rails, I will close the issue, but will follow-up with any news.

[janko]

It seems that CockroachDB doesn't support neither => nor the make_interval function. You can get around this by overriding the SQL generation for Sequel's date_arithmetic extension with Cockroach-compatible syntax:

# lib/sequel/extensions/cockroach.rb
module Sequel
  module Cockroach
    module DatasetMethods
      DURATION_UNITS = [:years, :months, :days, :hours, :minutes, :seconds].freeze
      DEF_DURATION_UNITS = DURATION_UNITS.zip(DURATION_UNITS.map { |s| s.to_s.freeze }).freeze

      def date_add_sql_append(sql, da)
        casted = Sequel.cast(da.expr, da.cast_type || Time)
        parts = []
        each_valid_interval_unit(da.interval, DEF_DURATION_UNITS) do |value, sql_unit|
          parts << "#{value} #{sql_unit}"
        end
        interval = Sequel.cast(parts.join(" "), :interval) unless parts.empty?
        if interval
          complex_expression_sql_append(sql, :+, [casted, interval])
        else
          literal_append(sql, casted)
        end
      end
    end
  end

  Dataset.register_extension(:cockroach, Cockroach::DatasetMethods)
end

# app/misc/rodauth_main.rb
class RodauthMain < Rodauth::Rails::Auth
  configure do
    db Sequel.postgres(extensions: [:activerecord_connection, :cockroach], keep_reference: false)
    # ...
  end
end

If you're using config.autoload_lib in your Rails app, make sure you pass ignore: %w[sequel] to ignore the lib/sequel directory from Zeitwerk.

[DalenW]

Sick, I'll give that a shot. Thank you!

[DalenW]

It worked, thank you so much!