Making Hyperswitch Server support for mysql with diesel library

[manojradhakrishnan]

Context

To keep things simpler, we initially chose PSQL for building Hyperswitch. However the vision was to keep Hyperswitch pluggable with any database technology.

Since we adopted Rust as the programming language, we chose diesel as our database communication library, due to its ORM nature.

Problem

With MySQL having greater than 40%+ market share in the Relational Database market, we would prefer to have Hyperswitch compatible with MySQL.

Diesel supports communication with PSQL, MySQL and SQLite - Hyperswitch is currently built to support only PSQL. The challenge is to extend the support to MySQL.

How to get started?

• Setup Hyperswitch by following these instructions on your local machine and make a test payment. This is also a prerequisite.
• Configure diesel with mysql and try running the application
• Discover the gaps that needs to be addressed in order to successfully run Hyperswitch with MySQL support

Expected Outcome

Share a proposal on how you can help us implement this in a clean manner. Create the proposal as Github issue and share under this discussion.

[subhash972001]

good-first-issue!

[manojradhakrishnan]

This problem statement is expected to be more complex than a good-first-issue. If you are looking for good-first-issue, please jump to the issues page of Hyperswitch.

P.S: The good-first-issues are not included in the hiring process.

[CodeHars12]

In this line we have making a Hyperswitch Server support by using the mysql diesel library. To make a Hyperswitch server support MySQL with the Diesel library, you can follow these steps:. All the step are mandatory to getting the solve the problem.

Steps to Implementation:

Setup Hyperswitch: Ensure you have Hyperswitch set up and running in your project. You can follow the Hyperswitch documentation for this.

Add Diesel to Your Project: Add Diesel to your project by adding it to your Cargo.toml:
[dependencies]
diesel = { version = "1.4", features = ["mysql"] }

Setup Diesel: Configure Diesel to work with your MySQL database. You'll need to set the DATABASE_URL environment variable to point to your MySQL database:
export DATABASE_URL=mysql://username:password@localhost/database_name
Replace username, password, and database_name with your MySQL credentials and database name.

Generate Diesel Models: Use Diesel's CLI to generate models from your database schema. Run the following command in your project directory:
diesel setup

Define Your Models: Define your database models in the src/schema.rs file generated by Diesel. This file will contain Rust structs representing your database tables.

Implement Diesel Queries: Use Diesel's query builder to write queries for interacting with your database. You can use these queries in your Hyperswitch server routes to perform CRUD operations on your MySQL database.

Update Hyperswitch Routes: Update your Hyperswitch server routes to use Diesel queries to interact with your MySQL database. You can use the Request and Response types provided by Hyperswitch to handle incoming requests and send responses.

Test Your Implementation: Test your Hyperswitch server to ensure that it correctly interacts with your MySQL database using the Diesel library.

Deploy Your Hyperswitch Server: Once you've tested your implementation, deploy your Hyperswitch server to your desired environment.

[Shailesh003]

In line with our vision to make Hyperswitch pluggable with any database technology, we're considering adding MySQL support alongside the existing PostgreSQL (PSQL) support. This proposal outlines the steps to implement MySQL compatibility in Hyperswitch using the Diesel ORM library.

Steps to Implement:

Setup Hyperswitch locally:
Begin by setting up Hyperswitch on your local machine, following the existing instructions. Ensure the application builds and runs smoothly using PostgreSQL as the database backend.

Configure Diesel with MySQL:
Investigate how to configure Diesel ORM to communicate with MySQL. This may involve modifying the diesel.toml configuration file to include MySQL as a supported backend and adjusting connection parameters accordingly.

Database Schema Compatibility:
Review the existing database schema used by Hyperswitch and ensure it's compatible with both PostgreSQL and MySQL. Address any differences in data types, constraints, or indexing strategies between the two databases.

Test Compatibility:
Develop a comprehensive test suite to verify Hyperswitch's functionality with MySQL. Test CRUD operations, transactions, and complex queries to ensure compatibility and correctness.

Performance Optimization :
Conduct performance testing to identify and address any potential bottlenecks when using MySQL. Optimize queries, indexing, and caching strategies as needed to ensure optimal performance.

Documentation and Deployment:
Update the documentation to include instructions for configuring and deploying Hyperswitch with MySQL support. Provide guidance on best practices for managing and maintaining MySQL instances running Hyperswitch.

Expected Outcome:
This proposal aims to extend Hyperswitch's database support to include MySQL cleanly and efficiently, offering users greater flexibility in choosing their preferred database technology.

Additional Notes:
Your feedback on this proposed approach is highly appreciated. Our goal is to seamlessly integrate MySQL support into Hyperswitch while ensuring code quality and reliability. Let's collaborate to make this enhancement a reality.

[manojradhakrishnan]

Thanks @Shailesh003

Next steps:

1. Change the configuration to MySQL and make the code to compile
2. Try implementing the CRUD APIs of Merchant Account with MySQL connection
3. Raise a PR

[sireesha5055]

GitHub Issue Proposal: Adding MySQL Support to Hyperswitch

Background:
In line with our vision to make Hyperswitch pluggable with any database technology, we're considering adding MySQL support alongside the existing PostgreSQL (PSQL) support. This proposal outlines the steps to implement MySQL compatibility in Hyperswitch using the Diesel ORM library.

Steps to Implement:

1. Setup Hyperswitch locally:
   Begin by setting up Hyperswitch on your local machine, following the existing instructions. Ensure the application builds and runs smoothly using PostgreSQL as the database backend.

2. Configure Diesel with MySQL:
   Investigate how to configure Diesel ORM to communicate with MySQL. This may involve modifying the diesel.toml configuration file to include MySQL as a supported backend and adjusting connection parameters accordingly.

3. Database Schema Compatibility:
   Review the existing database schema used by Hyperswitch and ensure it's compatible with both PostgreSQL and MySQL. Address any differences in data types, constraints, or indexing strategies between the two databases.

4. Test Compatibility:
   Develop a comprehensive test suite to verify Hyperswitch's functionality with MySQL. Test CRUD operations, transactions, and complex queries to ensure compatibility and correctness.

5. Performance Optimization:
   Conduct performance testing to identify and address any potential bottlenecks when using MySQL. Optimize queries, indexing, and caching strategies as needed to ensure optimal performance.

6. Documentation and Deployment:
   Update the documentation to include instructions for configuring and deploying Hyperswitch with MySQL support. Provide guidance on best practices for managing and maintaining MySQL instances running Hyperswitch.

Expected Outcome:
This proposal aims to extend Hyperswitch's database support to include MySQL cleanly and efficiently, offering users greater flexibility in choosing their preferred database technology.

Additional Notes:
Your feedback on this proposed approach is highly appreciated. Our goal is to seamlessly integrate MySQL support into Hyperswitch while ensuring code quality and reliability. Let's collaborate to make this enhancement a reality.

[Somesh-008]

Proposal for Adding MySQL Support to Hyperswitch

Objective:

Extend Hyperswitch compatibility to include MySQL databases alongside existing support for PostgreSQL.

Steps:

1) Configuring Diesel for MySQL:

• Update the 'diesel.toml' configuration file to specify MySQL as one of the supported databases.
• Adjust the connection URL to point to your MySQL database, including specifying the host, port, database name, username, and
  password.
• Ensure that the necessary Diesel dependencies for MySQL are included in the project's 'Cargo.toml' file.

2) Database Schema and Migrations:

• Review the existing PostgreSQL database schema used by Hyperswitch.
• Modify or create database migrations to ensure that the schema is compatible with MySQL.
• Pay attention to differences in data types, constraints, and indexing between PostgreSQL and MySQL.

3) SQL Query Adjustments:

• Review all SQL queries used in the Hyperswitch codebase.
• Replace any PostgreSQL-specific syntax or functions with their MySQL equivalents.
• Be aware of differences in syntax for common operations such as string concatenation, date manipulation, and window functions.

4) ORM Mapping Adjustments:

• Review the ORM mappings used by Diesel to interact with the database.
• Ensure that the mappings are compatible with both PostgreSQL and MySQL.
• Adjust mappings as needed to accommodate differences in data types and constraints between the two database systems.

5) Testing:

• Develop a suite of integration tests to validate the functionality of Hyperswitch with MySQL.
• Test various scenarios including payment processing, data retrieval, and CRUD operations.
• Use mock databases or fixtures to simulate real-world scenarios and edge cases.

6) Performance Tuning:

• Profile the performance of Hyperswitch with MySQL to identify any bottlenecks or areas for optimization.
• Optimize database queries and indexing strategies to improve performance.
• Monitor resource utilization and performance metrics under load to ensure scalability and reliability.

7) Documentation Update:

• Update the project documentation to include instructions for configuring Hyperswitch with MySQL.
• Provide guidance on database setup, migration, and troubleshooting for MySQL users.
• Document any known differences or limitations in functionality between PostgreSQL and MySQL.

8) Release and Feedback:

• Prepare a release of Hyperswitch with MySQL support.
• Solicit feedback from users and the community to identify any additional issues or areas for improvement.
• Address reported issues promptly and iteratively improve MySQL compatibility in future releases.

Conclusion:

By implementing MySQL support in Hyperswitch, we aim to enhance the flexibility and usability of the system, allowing users to leverage the benefits of MySQL's widespread adoption in addition to PostgreSQL. This proposal outlines a structured approach to achieve this objective while ensuring the integrity and reliability of the Hyperswitch platform.

[krcpr007]

Hyperswitch Server support for MySQL with diesel library

Here is my approach and working approach

1. Add Diesel and MySQL dependencies to our cargo.toml file

[dependencies]
diesel = { version = "2.1.0", features = ["mysql"] }
dotenv = "0.15.0"

2. Install the CLI tool

cargo install diesel_cli

3. Setup database connection in the new file, perhaps src/db.rs:

use diesel::r2d2::{self, ConnectionManager};

pub type Pool = r2d2::Pool<ConnectionManager<MysqlConnection>>;

pub fn create_db_pool(database_url: &str) -> Pool {
    let manager = ConnectionManager::<MysqlConnection>::new(database_url);
    r2d2::Pool::builder().build(manager).expect("Failed to create pool.")
}

4. Use the pool in the server code to execute the queries
5. Update existing code to use Diesel's Query builder syntax instead of raw SQL strings. This helps ensure the code is safe from SQL injection attacks and compatible with MySQL.
6. Test the application thoroughly to ensure all features work as expected with MySQL.

About Me

krcpr007.vercel.app

[varshith257]

@manojradhakrishnan @SanchithHegde How can we get ahead to start this work? Can clarify what the hiring hiring-challenge label is? What could be my next steps?

[shubham-200315]

Hey @varshith257 here is the documentation for your latter query => https://github.com/juspay/hyperswitch/wiki/Hyperswitch-Hiring-Challenge
,And for the prior one let's wait for the maintainers take on this.

[VaibhavRai01]

Proposal for Adding MySQL Support to Hyperswitch

Steps:

1. Database Configuration:

• Modify Hyperswitch's database configuration to support MySQL alongside PostgreSQL.
• This involves updating connection parameters, such as host, port, username, password, and database name.

2. Migration Scripts:

• Create migration scripts to ensure a seamless transition for existing PostgreSQL users to MySQL.
• Adjust data types, constraints, and queries to MySQL-compatible syntax.

3. Codebase Modification:

• Identify PostgreSQL-specific syntax and functions used in the codebase and replace them with MySQL equivalents or adjust them to be database-agnostic.
• Ensure compatibility with MySQL-specific features and limitations.

4. Testing:

• Develop comprehensive test cases to validate the functionality and performance of Hyperswitch with MySQL.
• Test various scenarios, including CRUD operations, transactions, concurrent access, and error handling.
• Perform integration tests to verify compatibility with existing features and plugins.

Below is a high-level overview of the code changes required:

1. Database Configuration:
   #[cfg(feature = "mysql")]
pub fn establish_connection() -> Result<Connection, diesel::ConnectionError> {
let database_url = dotenv::var("DATABASE_URL").expect("DATABASE_URL must be set");
Connection::establish(&database_url)
}

2. Migration Scripts:
   #[cfg(feature = "mysql")]
pub fn run_mysql_migrations() {
embedded_migrations::run_with_output(&Connection::establish(DATABASE_URL).unwrap(), &mut std::io::stdout()).expect("Failed to run migrations");
}

3. Database Type Definition:
   #[cfg(any(feature = "postgres", feature = "mysql"))]
pub type DatabaseConnection = diesel::MysqlConnection;

4. Query Execution:
   #[cfg(feature = "mysql")]
pub fn execute_query(query: &str) -> Result<(), diesel::result::Error> {
diesel::sql_query(query).execute(&*establish_connection())
}

5. Data Type Mapping:
   #[cfg(feature = "mysql")]
pub fn map_data_types(data_type: &str) -> &str {
match data_type {
"TEXT" => "TEXT",
"INTEGER" => "INT",
_ => data_type,
}
}

6. Error Handling:
   #[cfg(feature = "mysql")]
pub fn handle_error(error: diesel::result::Error) {
match error {
diesel::result::Error::DatabaseError(diesel::result::DatabaseErrorKind::UniqueViolation, _) => {
eprintln!("Unique constraint violation occurred");
}
_ => eprintln!("An error occurred: {:?}", error),
}
}

These proposed changes aim to make the database.rs file compatible with both PostgreSQL and MySQL databases. By using conditional compilation based on feature flags, the codebase can seamlessly switch between the two database systems, allowing Hyperswitch to support MySQL alongside its existing PostgreSQL support.

[manojradhakrishnan]

Thanks @VaibhavRai01

Next steps:

1. Change the configuration to MySQL and make the code to compile
2. Try implementing the CRUD APIs of Customers with MySQL connection
3. Raise a PR

[Mohamed-Ayaan358]

Do we provide our plan to complete the task before getting started on it?

[NarsGNA]

While we recommend starting with a proposal / your approach but do feel free to raise a PR if you're ready!

[CuriousCorrelation]

Proposal up at #4191
The idea is simple - dependency injection and separation of concerns at service layer.

[manojradhakrishnan]

Thank @CuriousCorrelation !! Really liking the way you look at this problem.

Next steps:

1. Change the configuration to MySQL and make the code to compile
2. Try implementing the CRUD APIs of Payments with MySQL connection
3. Raise a PR

[CuriousCorrelation]

@manojradhakrishnan Really glad to know, thank you!

I'll start with the first part of the proposal, i.e. dependency injection via compiled features. I anticipate "storage as a service" will be quite involved - might require significant input from the infra team.

[ProgrammingPirates]

Proposal for Adding MySQL Support to Hyperswitch

Objective:
The proposal aims to extend Hyperswitch's database compatibility to include MySQL alongside its existing support for PostgreSQL. This will broaden the application's user base and offer users the flexibility to choose their preferred database system.

Steps:

1. Database Configuration:

   • Update Hyperswitch's database configuration to accommodate MySQL alongside PostgreSQL.
   • Adjust connection parameters such as host, port, username, password, and database name accordingly.

2. Migration Scripts:

   • Develop migration scripts to facilitate a seamless transition from PostgreSQL to MySQL.
   • Adapt data types, constraints, and queries to MySQL-compatible syntax.

3. Codebase Modification:

   • Identify PostgreSQL-specific syntax and functions within the codebase.
   • Replace them with MySQL equivalents or refactor to ensure database-agnostic compatibility.
   • Address any MySQL-specific features or limitations.

4. Testing:

   • Create comprehensive test cases to validate Hyperswitch's functionality with MySQL.
   • Cover CRUD operations, transactions, concurrency, and error handling scenarios.
   • Conduct integration tests to verify compatibility with existing features and plugins.

Code Changes Overview:

• Database Configuration:

  • Adjust the establish_connection function to support MySQL alongside PostgreSQL.

• Migration Scripts:

  • Implement functions to execute MySQL-specific migration scripts.

• Database Type Definition:

  • Define the DatabaseConnection type to utilize MySQL connections when necessary.

• Query Execution:

  • Update query execution functions to handle MySQL-specific syntax and behavior.

• Data Type Mapping:

  • Modify data type mapping functions to ensure compatibility with MySQL.

• Error Handling:

  • Enhance error handling mechanisms to address MySQL-specific errors.

Hyperswitch will seamlessly integrate MySQL support alongside its existing PostgreSQL compatibility. This expansion will enhance the application's versatility and appeal to a broader audience, aligning with the project's goals of providing a flexible and robust database solution.

[omthakare16]

Challenge Proposal: Hyperswitch Server support for MySQL with Diesel Library

Problem:

While Diesel supports PostgreSQL, MySQL, and SQLite, Hyperswitch currently only supports PostgreSQL. The challenge lies in extending this support to MySQL.

Expected Outcome:

The strategy for implementing MySQL support in Hyperswitch cleanly.

Proposal:

To address the challenge of extending Hyperswitch's database support to MySQL, the following steps can be undertaken:

1. Database Abstraction Layer Refactoring:

   • Streamline database operations in Hyperswitch by introducing a simplified layer that handles them efficiently.
   • Define generic traits for common database operations such as querying, insertion, deletion, and updates.

2. Backend Configuration Management:

   • Enhance Hyperswitch's configuration system to support dynamic database selection.
   • Implement mechanisms for specifying the database type (e.g., PostgreSQL, MySQL) and connection details.

3. Diesel Integration with MySQL:

   • Utilize Diesel's capabilities to establish connections and execute queries for MySQL databases.
   • Adjust existing Diesel-based code to be compatible with MySQL syntax and features.

4. Migration Support for PostgreSQL to MySQL:

   • Include provisions for smooth migration from PostgreSQL to MySQL when necessary, ensuring seamless transition between database systems.

This proposal aims to provide a structured approach towards achieving MySQL compatibility in Hyperswitch.

[ShivamAgarwal-code]

Proposal: Hyperswitch Server support for MySQL with Diesel Library

Steps to Implement:

Setup Hyperswitch locally: Follow the instructions in the provided documentation to set up Hyperswitch locally and ensure it is running with PostgreSQL as the database.

Configure Diesel with MySQL:
Update the Cargo.toml file to include the mysql feature for Diesel.
Update the database URL in the configuration files (config/development.toml, config/sandbox.toml, config/production.toml) to use a MySQL connection string.

Database Schema Compatibility:
Analyze the existing database migrations in Hyperswitch to identify any SQL syntax differences between PostgreSQL and MySQL.
Update the existing migrations or create new migrations that are compatible with MySQL syntax.

Test Compatibility:
Run Hyperswitch with the updated configuration and migrations, using MySQL as the database.
Identify and address any compatibility issues or errors that arise due to differences between PostgreSQL and MySQL.
Thoroughly test all features and functionalities of Hyperswitch with the MySQL database.

Performance Optimization:
Analyze the performance of Hyperswitch with MySQL and identify any bottlenecks or areas for optimization.
Implement necessary optimizations, such as indexing, query tuning, or database configuration changes, to ensure optimal performance with MySQL.

Documentation and Deployment:
Update the project documentation to include instructions for setting up and using Hyperswitch with MySQL.
Provide clear guidance on configuring the MySQL connection, running migrations, and deploying Hyperswitch with MySQL.

Expected Outcome:
Hyperswitch should be able to run seamlessly with MySQL as the database management system.
All existing features and functionalities should work as expected with MySQL.
Performance of Hyperswitch with MySQL should be optimized and comparable to or better than its performance with PostgreSQL.

Conclusion:
The proposal outlines a comprehensive approach to implementing MySQL support in Hyperswitch, enabling the application to work seamlessly with both PostgreSQL and MySQL databases. By following the steps outlined in the proposal, the Hyperswitch codebase will be updated to configure Diesel for MySQL, migrate the database schema to be compatible with MySQL syntax, thoroughly test for any compatibility issues, optimize performance for MySQL, and update the documentation and deployment instructions accordingly.

Successful implementation of this proposal will result in Hyperswitch being a more versatile and flexible application, capable of operating with either PostgreSQL or MySQL as the underlying database management system. This increased compatibility will allow Hyperswitch to be adopted in a wider range of environments and infrastructures, catering to the preferences and existing setups of different organizations and teams.

Moreover, the performance optimizations carried out during the implementation process will ensure that Hyperswitch maintains its efficiency and reliability when running with MySQL, providing a seamless experience for users and developers alike.

[AishwaryaMark]

Context To keep things simpler, we initially chose PSQL for building Hyperswitch. However the vision was to keep Hyperswitch pluggable with any database technology. Since we adopted Rust as the programming language, we chose diesel as our database communication library, due to its ORM nature. Problem With MySQL having greater than 40%+ market share in the Relational Database market, we would prefer to have Hyperswitch compatible with MySQL. Diesel supports communication with PSQL, MySQL and SQLite - Hyperswitch is currently built to support only PSQL. The challenge is to extend the support to MySQL. Expected Outcome Share a proposal on how you can help us implement this in a clean manner. Create the proposal as Github issue and share under this discussion.

Proposal for Adding MySQL Support to Hyperswitch

Objective: The objective of this proposal is to extend the database support of Hyperswitch to include MySQL, in addition to the existing PostgreSQL support.

Approach: To achieve this objective, we propose the following steps:

Research and Analysis:

Investigate the differences between PostgreSQL and MySQL in terms of SQL syntax, data types, and features supported by both databases.Understand the compatibility challenges and potential solutions for adapting Hyperswitch's database operations to work seamlessly with MySQL.

Database Abstraction Layer:

Implement a database abstraction layer within Hyperswitch to encapsulate the specific database operations.Design the abstraction layer to provide a unified interface for interacting with different database systems, allowing seamless switching between PostgreSQL and MySQL.

Adaptation of Diesel ORM:

Leverage the capabilities of the Diesel ORM library to facilitate database communication.Modify the existing database interaction code to be compatible with both PostgreSQL and MySQL, utilizing Diesel's features for generating SQL queries and managing database connections.

Configuration and Integration:

Introduce configuration options for specifying the target database type (PostgreSQL or MySQL) in Hyperswitch's configuration files.Implement mechanisms for dynamically configuring database connections based on the chosen database type.

Testing and Validation:

Develop comprehensive test suites to validate the functionality of Hyperswitch with both PostgreSQL and MySQL databases.Conduct thorough testing to ensure that all database operations, including CRUD operations and transactions, behave as expected across different database platforms.

[Ayushgadre]

Proposal: Adding MySQL Support to Hyperswitch #4179

@manojradhakrishnan

Overview: This proposal seeks to enhance Hyperswitch by integrating MySQL support alongside its existing PostgreSQL compatibility. The plan involves modifying database configurations, creating migration scripts for seamless transitions, adjusting the codebase for MySQL compatibility, conducting comprehensive testing, changing the configuration to target MySQL, implementing CRUD APIs for Merchant Account functionality using MySQL connections, and ensuring thorough documentation and test coverage. By following these steps, Hyperswitch will significantly improve its compatibility and flexibility in database selection, offering users a broader range of options while maintaining robust functionality and performance.

Steps:

Database Configuration:
Modify Hyperswitch's database configuration to support MySQL alongside PostgreSQL. This involves updating connection parameters, such as host, port, username, password, and database name.

// database.rs

#[cfg(feature = "mysql")]
pub fn establish_connection() -> Result<Connection, diesel::ConnectionError> {
    let database_url = dotenv::var("MYSQL_DATABASE_URL").expect("MYSQL_DATABASE_URL must be set");
    Connection::establish(&database_url)
}

Migration Scripts:

Create migration scripts to ensure a seamless transition for existing PostgreSQL users to MySQL. Adjust data types, constraints, and queries to MySQL-compatible syntax.

// migration.rs

#[cfg(feature = "mysql")]
pub fn run_mysql_migrations() {
    embedded_migrations::run_with_output(&Connection::establish(DATABASE_URL).unwrap(), &mut std::io::stdout()).expect("Failed to run migrations");
}

Codebase Modification:

Identify PostgreSQL-specific syntax and functions used in the codebase and replace them with MySQL equivalents or adjust them to be database-agnostic. Ensure compatibility with MySQL-specific features and limitations.

// Example of code modification in merchant_account.rs
#[cfg(feature = "mysql")]
pub fn create_merchant_account(account: &MerchantAccount, conn: &MysqlConnection) -> Result<(), diesel::result::Error> {
    diesel::insert_into(merchant_accounts::table)
        .values(account)
        .execute(conn)
        .map(|_| ())
}

Testing:

Develop comprehensive test cases to validate the functionality and performance of Hyperswitch with MySQL. Test various scenarios, including CRUD operations, transactions, concurrent access, and error handling.

Change Configuration to MySQL:

Update the configuration settings in Hyperswitch to target MySQL instead of PostgreSQL. This involves modifying the database configuration file (e.g., database.rs or config.rs) to use MySQL connection parameters.

We can modify the database configuration to target MySQL:

// database.rs

#[cfg(feature = "mysql")]
pub fn establish_connection() -> Result<Connection, diesel::ConnectionError> {
    let database_url = dotenv::var("MYSQL_DATABASE_URL").expect("MYSQL_DATABASE_URL must be set");
    Connection::establish(&database_url)
}

Ensure that the MYSQL_DATABASE_URL environment variable is set to the MySQL database URL in your development environment or configuration files.

Implement CRUD APIs for Merchant Account using MySQL:

Now that the configuration is updated to use MySQL, we can proceed to implement CRUD APIs for the Merchant Account functionality using MySQL connections.

We can implement CRUD operations for the Merchant Account in a file like merchant_account.rs:

// merchant_account.rs

use diesel::mysql::MysqlConnection;
use diesel::prelude::*;

#[derive(Queryable, Insertable)]
#[table_name = "merchant_accounts"]
pub struct MerchantAccount {
    pub id: i32,
    pub name: String,
    pub email: String,
    // Add other fields as needed
}

impl MerchantAccount {
    #[cfg(feature = "mysql")]
    pub fn create(account: &MerchantAccount, conn: &MysqlConnection) -> Result<(), diesel::result::Error> {
        diesel::insert_into(merchant_accounts::table)
            .values(account)
            .execute(conn)
            .map(|_| ())
    }

    #[cfg(feature = "mysql")]
    pub fn read(id: i32, conn: &MysqlConnection) -> Result<MerchantAccount, diesel::result::Error> {
        merchant_accounts::table.find(id).first(conn)
    }

    #[cfg(feature = "mysql")]
    pub fn update(id: i32, account: &MerchantAccount, conn: &MysqlConnection) -> Result<(), diesel::result::Error> {
        diesel::update(merchant_accounts::table.find(id))
            .set(account)
            .execute(conn)
            .map(|_| ())
    }

    #[cfg(feature = "mysql")]
    pub fn delete(id: i32, conn: &MysqlConnection) -> Result<(), diesel::result::Error> {
        diesel::delete(merchant_accounts::table.find(id)).execute(conn).map(|_| ())
    }
}

Replace merchant_accounts with the actual table name for Merchant Accounts in our database schema.

Testing:

After implementing the CRUD APIs for the Merchant Account with MySQL connections, it's crucial to thoroughly test the functionality. Write unit tests and integration tests to cover CRUD operations, error handling, and edge cases.

We can write a unit test for creating a Merchant Account using MySQL:

#[cfg(test)]
mod tests {
    use super::*;
    use diesel::r2d2::{ConnectionManager, Pool};
    use dotenv::dotenv;
    use std::env;

    #[test]
    fn test_create_merchant_account() {
        dotenv().ok();
        let database_url = env::var("MYSQL_TEST_DATABASE_URL").expect("MYSQL_TEST_DATABASE_URL must be set for testing");
        let manager = ConnectionManager::<MysqlConnection>::new(database_url);
        let pool = Pool::builder().build(manager).expect("Failed to create connection pool for testing");

        let conn = pool.get().expect("Failed to get database connection for testing");

        let account = MerchantAccount {
            id: 1,
            name: "Test Merchant".to_string(),
            email: "test@example.com".to_string(),
        };

        let result = MerchantAccount::create(&account, &conn);
        assert!(result.is_ok());
    }
}

Ensure that we have a separate test database and environment variables (MYSQL_TEST_DATABASE_URL) set for testing purposes.

Once the implementation and testing of CRUD APIs for the Merchant Account using MySQL are completed, and the main Hyperswitch codebase. Include documentation, test cases.

By following these steps, we can successfully change the configuration to MySQL and implement CRUD APIs for the Merchant Account functionality in Hyperswitch.

[anzal1]

Proposal for Implementing MySQL Support in Hyperswitch #4179 @manojradhakrishnan

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

[Abhisheksur123]

Proposal: Adding MySQL Support to Hyperswitch Using Diesel #4179

Objective:
The objective of this proposal is to extend Hyperswitch's database compatibility by adding support for MySQL using the Diesel ORM library. This enhancement aligns with the vision of making Hyperswitch pluggable with any database technology, thereby increasing its usability and market adoption.

Scope of Work:

Configuration Changes:

Update the Cargo.toml file to include the MySQL driver dependency for Diesel.
Configure Diesel's connection settings to connect to a MySQL database (e.g., specifying the database URL, username, password).
SQL Query Compatibility:

Review and modify SQL queries generated by Diesel to ensure compatibility with MySQL syntax and semantics.
Handle any differences in SQL features and optimizations between PostgreSQL and MySQL.
Data Type Compatibility:

Identify and address data type differences between PostgreSQL and MySQL (e.g., handling JSONB data type in PostgreSQL vs. JSON data type in MySQL).
Implement necessary data type conversions or mappings in Diesel's ORM layer.
ORM Mappings and Relationships:

Review existing ORM mappings and relationships in Hyperswitch's codebase.
Modify ORM mappings as needed to ensure they work seamlessly with MySQL's database schema and constraints.
Transactions and Concurrency:

Test transaction handling and concurrency control mechanisms in a MySQL environment.
Ensure that transactions are atomic, consistent, isolated, and durable (ACID properties) in MySQL just as they are in PostgreSQL.
Performance Optimization:

Evaluate performance differences between PostgreSQL and MySQL.
Optimize SQL queries, indexes, and database operations for MySQL to achieve comparable or improved performance.
Technical Approach:

Dependency Management:

Add the MySQL driver dependency (e.g., diesel_mysql) to the Cargo.toml file.
Update diesel.toml configuration file to specify the MySQL connection details.
SQL Query Adaptation:

Review all SQL queries generated by Diesel's query builder and modify them for MySQL compatibility.
Handle differences in query syntax, functions, and clauses between PostgreSQL and MySQL.
Data Type Handling:

Implement data type conversions or mappings in Diesel's schema definitions for MySQL-specific data types.
Update data access code to handle data type differences appropriately.
ORM Mapping Adjustments:

Review and update ORM mappings, associations, and constraints to align with MySQL's database schema conventions.
Test CRUD operations and relationships to ensure correctness and consistency.
Testing and Validation:

Develop unit tests and integration tests specifically for MySQL compatibility.
Validate the application's behavior, data integrity, and performance in a MySQL environment.
Conduct stress testing and concurrency testing to identify and resolve any issues related to transactions and concurrent access.

[thegeek13242]

Added my proposal @ Issues #4278 @manojradhakrishnan