My proposal for the problem statement : https://github.com/juspay/hyperswitch/discussions/4179

SqlSupport.md

Proposal for Implementing MySQL Support in Hyperswitch with Docker Compose and Diesel

Introduction:

This proposal outlines a detailed approach to incorporate MySQL support into Hyperswitch, leveraging Docker Compose for containerized development and Diesel for database interaction.

Benefits:

• Expanded User Base: Cater to users who prefer MySQL, significantly increasing potential adoption.
• Enhanced Flexibility: Provide users with the freedom to choose the most suitable database depending on their needs.

Implementation Plan:

Step 1: Edit Docker Compose by adding MySql:

  mysql:
    image: mysql:5.7
    networks:
      - router_net
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: hyperswitch_db
      MYSQL_USER: db_user
      MYSQL_PASSWORD: db_pass

# Migrations
  sql_migration_runner:
      image: rust:latest
      command: "bash -c 'cargo install diesel_cli --no-default-features --features mysql && diesel migration --database-url mysql://$${DATABASE_USER}:$${DATABASE_PASSWORD}@$${DATABASE_HOST}:$${DATABASE_PORT}/$${DATABASE_NAME} run'"
      working_dir: /app
      networks:
        - router_net
      volumes:
        - ./:/app
      environment:
        - DATABASE_USER=db_user
        - DATABASE_PASSWORD=db_pass
        - DATABASE_HOST=mysql
        - DATABASE_PORT=3306
        - DATABASE_NAME=hyperswitch_db

**Step 2: Diesel Features in Model Crate **

1. Modify Cargo.toml in the model crate:

   Ini, TOML

   [dependencies]
   diesel = { version = "X.Y", features = ["postgres", "mysql", "serde_json", "time", "64-column-tables"] }
   

   • Replace X.Y with the desired Diesel version supporting MySQL.
   • Include the mysql feature for Diesel to enable MySQL functionality.

**Step 3: Database Connection Function **

1. Modify connection.rs file:

   use diesel::MysqlConnection;
   pub  type  MysqlPool  = bb8::Pool<async_bb8_diesel::ConnectionManager<MysqlConnection>>;
   
   pub async fn diesel_make_mysql_pool(database: &Database, _test_transaction: bool) -> MysqlPool {
   let database_url = format!(
       "mysql://{}:{}@{}:{}/{}",
       database.username,
       database.password.peek(),
       database.host,
       database.port,
       database.dbname
   );
   let manager = async_bb8_diesel::ConnectionManager::<MysqlConnection>::new(database_url);
   let pool = bb8::Pool::builder()
       .max_size(database.pool_size)
       .connection_timeout(std::time::Duration::from_secs(database.connection_timeout));
   
   pool.build(manager)
       .await
       .expect("Failed to create MySQL connection pool")#[allow(clippy::expect_used)]
   pub async fn diesel_make_mysql_pool(database: &Database, _test_transaction: bool) -> MysqlPool {
   let database_url = format!(
       "mysql://{}:{}@{}:{}/{}",
       database.username,
       database.password.peek(),
       database.host,
       database.port,
       database.dbname
   );
   let manager = async_bb8_diesel::ConnectionManager::<MysqlConnection>::new(database_url);
   let pool = bb8::Pool::builder()
       .max_size(database.pool_size)
       .connection_timeout(std::time::Duration::from_secs(database.connection_timeout));
   
   pool.build(manager)
       .await
       .expect("Failed to create MySQL connection pool")}
   
   #[allow(clippy::expect_used)]
   	pub async fn mysql_connection(
   pool: &MysqlPool,
   ) -> PooledConnection<'_, async_bb8_diesel::ConnectionManager<MysqlConnection>> {
   pool.get()
       .await
       .expect("Couldn't retrieve MySQL connection")
   }
   }
    
    

   • This function utilizes Diesel's MysqlConnection type to establish a connection to the MySQL database.
   • It takes the database connection URL as input (likely configured as an environment variable).

Step 4: Error Handling Function:

1. Add an error handler file:

   use diesel::result::Error;
   
   pub fn handle_db_error(err: Error) {
       match err {
           Error::DatabaseError(err_info) => {
               println!("Database error: {}", err_info);
               // Optionally implement specific error handling for different error codes
           },
           _ => println!("Unexpected error: {}", err),
       }
   }

   • This function provides centralized error handling for database interactions.
   • It differentiates between DatabaseError and other potential errors.
   • For DatabaseError, it logs the error information and allows for implementing specific actions based on error codes (optional).

Step 5: Model and Schema Updates (Code Focus):

1. Data Model Review: Locate data model files (.rs files using Diesel macros).

2. Data Type Comparison:

   • Compare model data types with MySQL-compatible types (reference: https://dev.mysql.com/doc/refman/8.3/en/data-types.html).
   • Example (Hypothetical):

   // Assuming a User model with a `created_at` timestamp
   #[derive(Debug, Clone, Queryable)]
   pub struct User {
       pub id: i32,
       pub name: String,
       // ... other fields
       pub created_
   
   

3. Data Type Comparison (Continued):

   Rust

   // Assuming a User model with a `created_at` timestamp
   #[derive(Debug, Clone, Queryable)]
   pub struct User {
       pub id: i32,
       pub name: String,
       // ... other fields
       pub created_at: Timestamp<Utc>, // Might need adjustment for MySQL
   }

   • Potential Adjustment: Use DateTime<Utc> (or a suitable MySQL-compatible type) for created_at.

4. Constraint Review: Ensure constraints (primary keys, foreign keys, check constraints) translate appropriately to MySQL syntax (minor adjustments might be needed).

5. SQL Query Modifications:

   • Review embedded SQL queries for potential syntax differences.
   • Adapt queries interacting with database-specific features (e.g., window functions):

   Rust

   // Assuming a function `fetch_users`
   let users = diesel::sql::query("SELECT * FROM users").load::<User>(&conn)?;
   
   // Potential adjustment (if using a PostgreSQL-specific window function)
   let users = diesel::sql::query("SELECT *, RANK() OVER (ORDER BY created_at DESC) AS row_num FROM users").load::<User>(&conn)?;

6. Error Handling:

   • Update error handling to utilize the handle_db_error function from db_errors.rs:

   let result = diesel::update(users::table)
       .set(users::name.eq("John Doe"))
       .where_eq(users::id, 1)
       .execute(&conn);
   
   match result {
       Ok(_) => println!("User updated successfully"),
       Err(err) => {
           db_errors::handle_db_error(err);
       }
   }

Other modifications including unhandled error while testing will be added along the way