RDS and MySQL

[anawandh]

Goal

Connecting the Spring Backend database to AWS relational database service. We need to do so while keeping the SQlite database for the development and connecting to RDS only for production. Additionally, We need to make it easier to switch dynamically.

---

Schedule

Last week --> Setting up RDS with spring and connecting the backend

Week of 5/20

T - Check CRUD W - Check CRUD Th - Integrate into main Project Fri - Test end points

Week of 5/27

M - Off T - Testing W - Testing Th - N@TM

---

Plans and Research

Understanding AWS RDS (Relational Database Service)

Amazon RDS (Relational Database Service) is a managed database service that allows you to set up, operate, and scale a relational database in the cloud easily. It provides scalable capacity while managing time-consuming database administration tasks such as hardware provisioning, database setup, patching, and backups. It offers high availability and security options, including automated backups, database snapshots, and automatic host replacement.

MySQL vs SQLite on RDS

When you create a database instance in RDS, you choose a database engine. RDS supports several engines such as MySQL, PostgreSQL, Oracle, SQL Server, and MariaDB. The choice of engine determines the features and the SQL dialect that you'll use. For instance:

• MySQL on RDS: If you choose MySQL when setting up your RDS instance, your database hosted on AWS will be using the MySQL engine. It means that your application needs to be compatible with MySQL SQL syntax and configurations.
• SQLite: This is a lightweight, file-based database, different from MySQL. SQLite is typically used for development, testing, or small applications given its simplicity and the fact that it doesn't require a separate server process.

Switching Databases from SQLite to MySQL

Your question about whether your backend needs to switch from SQLite to MySQL depends on how you set up your application's database configurations. In practice, it's quite common to use SQLite for local development due to its simplicity and then switch to a more scalable option like MySQL in production.

Configuring Spring Boot for Different Environments

In Spring Boot, you can manage different configurations for different environments using profile-specific application properties files. Here’s how you can set this up:

1. application.properties: This will be your main configuration file.
2. application-dev.properties: This will hold configurations for your development environment (using SQLite).
3. application-prod.properties: This will hold configurations for your production environment (using RDS MySQL).

Example Configuration

For development (application-dev.properties):

spring.datasource.url=jdbc:sqlite:path_to_your_local_db_file.db
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.jpa.database-platform=org.hibernate.dialect.SQLiteDialect

For production (application-prod.properties):

spring.datasource.url=jdbc:mysql://your-rds-instance-endpoint:3306/yourdatabase
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.ddl-auto=update

Running Spring Boot with Profiles

To activate a specific profile, you can run your Spring Boot application with an environment variable:

java -jar -Dspring.profiles.active=dev your-app.jar   # For development
java -jar -Dspring.profiles.active=prod your-app.jar  # For production

Or, if you're using Maven or Gradle to run locally:

mvn spring-boot:run -Dspring-boot.run.profiles=dev

This setup allows your application to seamlessly transition from using SQLite in development to using MySQL in AWS RDS in production, keeping local development fast and simple while leveraging more robust and scalable database management features in production.

SQL Syntax Compatibility

1. SQLite to MySQL: Both SQLite and MySQL are relatively similar in their syntax for basic SQL operations like SELECT, INSERT, UPDATE, and DELETE. However, they differ in more advanced features and data types. For instance, SQLite is more permissive with data types and allows column types to be freely cast, whereas MySQL is stricter.

2. SQLite to PostgreSQL: PostgreSQL is known for its standards compliance and extensive feature set, supporting advanced SQL functionalities that SQLite does not (e.g., CTEs, window functions). Its syntax for basic operations is similar to that of SQLite, but it diverges significantly when it comes to advanced features and data types.

Factors to Consider

Migration Complexity: If your current use of SQLite involves basic SQL, migrating to either MySQL or PostgreSQL will be straightforward for the basic CRUD (Create, Read, Update, Delete) operations. However, if you use more complex queries or SQLite-specific features (e.g., using REPLACE), these might need more adjustment when migrating to PostgreSQL due to its strict SQL compliance.

Feature Set:

• MySQL: Offers a good balance of features, performance, and ease of use. It is widely used in the industry and supports a broad array of applications. MySQL tends to be easier for those unfamiliar with the advanced SQL standards.
• PostgreSQL: Known as the most advanced open-source relational database, PostgreSQL supports a wide range of SQL standards and additional features not available in MySQL or SQLite, such as advanced indexing techniques, full support for different join types, and extensive capabilities for managing complex data types and large datasets.

Performance and Scalability:

• MySQL is often praised for its speed in read-heavy scenarios, which is ideal for websites and applications with a heavy load of fetch operations.
• PostgreSQL excels in handling complex queries and provides better support for concurrent write operations, making it suitable for applications with heavy data manipulation needs.

Ecosystem and Community:

• Both MySQL and PostgreSQL have large communities and extensive ecosystems. PostgreSQL, however, often has the edge in terms of support for new SQL standards and features.

Recommendation

Given that you are already familiar with SQLite and concerned about the ease of migration:

• Choose MySQL if your application's current SQL usage is straightforward and basic. MySQL's syntax and behavior will feel more familiar coming from SQLite, and it requires less adjustment in terms of learning and application code modification.
• Choose PostgreSQL if you anticipate needing advanced SQL features, better concurrency support, or if you are planning to scale significantly and may benefit from PostgreSQL's robustness in handling complex operations and larger datasets.

In both cases, you'll need to adjust some SQL queries when migrating from SQLite, especially for features that SQLite handles uniquely. Testing is crucial during migration to catch and address these differences.

Lastly, both MySQL and PostgreSQL have excellent support in Spring Boot, and it's relatively straightforward to configure either as your backend database. Make sure to use a connection pool (like HikariCP, which comes by default with Spring Boot) and the appropriate JPA dialect for whichever database you choose.

https://chat.openai.com/share/d7b76af2-3a71-403a-9824-64e904ec7f4b

Step 1: Define Spring Profiles

You need to create different application.properties files for each profile. By default, the application.properties file is used for common configurations, but you can specify profile-specific configurations using the application-{profile}.properties format.

1. application-dev.properties - for SQLite development settings
2. application-prod.properties - for MySQL production settings

Step 2: Configure Profile-specific Properties

For SQLite (Development):

application-dev.properties

spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:sqlite:volumes/sqlite.db
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.datasource.username=admin
spring.datasource.password=admin

For MySQL (Production):

application-prod.properties

spring.jpa.database-platform=org.hibernate.jpa.HibernatePersistenceProvider
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://<MySQL-Server-URL>:3306/<database-name>?useSSL=false&serverTimezone=UTC
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=<MySQL-username>
spring.datasource.password=<MySQL-password>

Replace <MySQL-Server-URL>, <database-name>, <MySQL-username>, and <MySQL-password> with your actual MySQL server details.

Step 3: Modify Entity Annotations if Necessary

Ensure that your JPA entity configurations are compatible with both databases. For instance, the JSON column in MySQL might need adjustments since SQLite's JSON support is handled differently:

@Column(columnDefinition = "json") // For SQLite
@Column(columnDefinition = "jsonb") // For MySQL, ensure that your MySQL version supports jsonb type

You may need to adjust this in your entity definitions or handle it dynamically via application profiles.

Step 4: Running with Profiles

When you run your application, specify the active profile:

• Development:

  $ java -jar -Dspring.profiles.active=dev your-app.jar

• Production:

  $ java -jar -Dspring.profiles.active=prod your-app.jar

Alternatively, you can set the active profile in your IDE configurations or within the application's deployment settings when deploying.

Step 5: Verify Configuration

Ensure that the application correctly connects to the intended database based on the active profile. Test both configurations thoroughly to handle any differences in database behavior, especially concerning SQL dialects and data types.

Step 6: Handling Auto DDL

Be cautious with spring.jpa.hibernate.ddl-auto=update in production. It's generally safer to manage schema changes through migration tools like Flyway or Liquibase to avoid accidental data loss.

By following these steps, you can effectively manage different database configurations for development and production environments using Spring profiles.

---

It Works

• [ ] Create new application.properties
  • [ ]
• [ ] Set up new application properties
  • [ ] export SPRING_PROFILES_ACTIVE=prod
  • [ ] mvn clean install // this sets up the new application properties
• [ ] Use jar file to run the spring with RDS
  • [ ] cd target
  • [ ] java -jar spring-0.0.1-SNAPSHOT.jar --spring.profiles.active=prod
• [ ] Changes in code
  • [ ]
• [ ] Works
  • [ ]
  • [ ]
  • [ ]

---

Testing

• [ ] Read
  • [ ]
• [ ] Create
  • [ ]
  • [ ]

Integration

-[ ] integrated -