DOCSEARCHbyAlgolia
Submit Docs

ActiveRecord Trilogy Adapter

repository·main·Indexed 41 days ago

https://github.com/trilogy-libraries/activerecord-trilogy-adapter

A database adapter for Active Record that enables the use of Trilogy, a MySQL-compatible client library. It provides support for Rails versions 6.0 through 7.0, featuring SSL mode configuration, multi-statement execution, and high-precision timestamps. For Rails v7.1+, Trilogy support is included by default and this gem is unnecessary.

Tokens
2.3K
Snippets
11
Records
15
Agent score
46%

What's inside ActiveRecord Trilogy Adapter

  1. Check compatibility and requirements

    main

    Rails Compatibility

    • Rails v7.1+: Trilogy support is included by default. This gem is unnecessary, though you should still add gem "trilogy" to your Gemfile.
    • Rails v7.0.x: Supported ✅
    • Rails v6.1.x: Supported ✅
    • Rails v6.0.x: Supported ✅

    System Requirements

    • Ruby: v2.7 or higher
    • Active Record: v6.0.x or higher
    • Trilogy: v2.4.0 or higher (included as a dependency)

  6. Configure SSL modes for the Trilogy adapter

    main

    When configuring your database connection in database.yml, you can specify the ssl_mode. The adapter supports the following modes, which map to Trilogy::SSL_* constants:

    • SSL_MODE_DISABLED: No SSL used.
    • SSL_MODE_PREFERRED: SSL is preferred but not required.
    • SSL_MODE_REQUIRED: SSL is required, but verification is not performed.
    • SSL_MODE_VERIFY_CA: SSL is required and the CA certificate is verified.
    • SSL_MODE_VERIFY_IDENTITY: SSL is required, the CA is verified, and the server identity is verified.

    You can provide these as strings (e.g., ssl_mode: VERIFY_CA) or as integers.

    da-adapter:
  adapter: trilogy
  host: your-host
  database: your-db
  username: your-user
  password: your-password
  ssl_mode: VERIFY_CA

  7. Configure the development environment with docker-compose.yml

    main

    The project provides a docker-compose.yml file to orchestrate a local development or testing environment consisting of a MySQL database and the application service.

    Services

    • db: Runs mysql:5.7. It is configured with the SQL mode --sql_mode="NO_ENGINE_SUBSTITUTION" and allows empty passwords via the MYSQL_ALLOW_EMPTY_PASSWORD: 1 environment variable. Data is persisted in the db-data volume.
    • app: Builds the application using Dockerfile.test from the current context. It depends on the db service and connects to the database using the DB_HOST: db environment variable.

    Environment Variables

    When running the application container, use the following environment variable to point to the database service:

    • DB_HOST: Set this to db to allow the application to communicate with the MySQL service defined in the compose file.
    version: "3.5"
services:
  db:
    image: mysql:5.7
    command: --sql_mode="NO_ENGINE_SUBSTITUTION"
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
    volumes:
      - "db-data:/var/lib/mysql"
  app:
    image: app
    build:
      context: .
      dockerfile: Dockerfile.test
    environment:
      DB_HOST: db
    depends_on:
      - db

volumes:
  db-data:

  8. Troubleshoot connection errors

    main

    The Trilogy adapter translates specific Trilogy errors into standard ActiveRecord error classes to help with debugging:

    • ActiveRecord::NoDatabaseError: Raised when the database specified in your configuration cannot be found (Error code 1049).
      • Resolution: Ensure the database exists or check your database.yml for the correct name. You can run bin/rails db:create to create it.
    • ActiveRecord::DatabaseConnectionError: Raised when there are authentication issues (Error code 1045) or hostname/DNS issues (TRILOGY_DNS_ERROR).
      • Resolution: Check your username, password, and host configuration.
    • ActiveRecord::ConnectionNotEstablished: Raised for other general connection failures.

  10. Enable multi-statement support in the connection

    main

    If your configuration does not have multi_statement: true enabled by default, you can wrap code blocks in with_multi_statements to temporarily enable multi-statement support on the server. The adapter will automatically turn multi-statements off after the block completes.

    connection.with_multi_statements do
  # Statements containing multiple ';' separated queries can be executed here
  connection.execute("INSERT INTO table1 (id) VALUES (1); INSERT INTO table2 (id) VALUES (1)")
end

  11. Use the database console via Trilogy adapter

    main

    The adapter provides a dbconsole method that attempts to launch a command-line database client (like mysql or mysql5) using the credentials and configuration provided in your ActiveRecord setup. It automatically maps configuration keys to CLI flags:

    • host $\rightarrow$ --host
    • port $\rightarrow$ --port
    • socket $\rightarrow$ --socket
    • username $\rightarrow$ --user
    • encoding $\rightarrow$ --default-character-set
    • sslca $\rightarrow$ --ssl-ca
    • sslcert $\rightarrow$ --ssl-cert
    • sslcapath $\rightarrow$ --ssl-capath
    • sslcipher $\rightarrow$ --ssl-cipher
    • sslkey $\rightarrow$ --ssl-key
    • ssl_mode $\rightarrow$ --ssl-mode

    Note: If include_password: true is passed to the method, it will include the --password flag. Otherwise, it uses -p to prompt for a password.

  12. Explain query execution and performance

    main

    The explain method allows you to run an EXPLAIN statement on an Arel object or SQL query. It automatically calculates the execution time and uses MySQL::ExplainPrettyPrinter to output a formatted, human-readable explanation of the query plan.

    # Usage with an Arel object
connection.explain(User.where(active: true).arel)

# Usage with options (e.g., EXPLAIN ANALYZE)
connection.explain(User.arel, [], ['ANALYZE'])