Streamline code conversion and testing from Microsoft SQL Server and Oracle to PostgreSQL with Amazon Bedrock

Organizations are increasingly modernizing their database infrastructures by transitioning from legacy systems like Microsoft SQL Server and Oracle to more cost-effective and scalable open-source alternatives such as PostgreSQL. This shift not only alleviates licensing expenses but also harnesses the flexibility and innovation inherent in PostgreSQL’s extensive feature set.

In this discussion, we explore the process of converting and testing database code from Microsoft SQL Server and Oracle to PostgreSQL, leveraging the generative AI capabilities of Amazon Bedrock. This platform simplifies and accelerates migrations, allowing organizations to automate and streamline migration tasks, thereby minimizing the effort, time, and risks associated with database transitions. Additionally, Amazon Q Developer, built on Amazon Bedrock, can be utilized to achieve similar outcomes.

Database migration challenges

The migration process from Microsoft SQL Server or Oracle to PostgreSQL encompasses several critical steps:

• Schema conversion – Adapting database objects such as tables, views, indexes, and constraints to align with PostgreSQL’s syntax and architecture.
• Business logic transformation – Converting stored procedures, functions, and triggers into PostgreSQL-compatible formats.
• Data migration – Executing extract, transform, and load (ETL) processes while ensuring data accuracy and consistency.
• Application changes – Modifying application code to accommodate PostgreSQL’s syntax and behavior.
• Performance tuning – Optimizing the migrated database for enhanced performance and scalability.

The role of Amazon Bedrock in database migration

Amazon Bedrock plays a pivotal role in addressing the challenges of database migration through generative AI, which enhances and automates the migration process. Key contributions include:

• Automated schema and code conversion – The platform simplifies schema conversion by analyzing the source database and generating PostgreSQL-compatible schemas. It adeptly transforms complex business logic, including stored procedures and functions, with a high degree of accuracy, significantly reducing manual effort.
• AI-driven data transformation – Amazon Bedrock automates the extraction and transformation of data to align with PostgreSQL’s data types and structures, ensuring seamless migration with minimal errors.
• Code compatibility insights – The platform identifies potential compatibility issues in application code, offering actionable recommendations for adapting SQL queries to facilitate smooth integration with the new PostgreSQL backend.
• Intelligent testing and validation – By generating test cases and validation scripts, Amazon Bedrock ensures that the migrated database meets functional and performance requirements prior to production deployment.

Through prompt engineering, users can tailor AI models, such as Anthropic’s Claude, using Amazon Bedrock to better understand the specific requirements of code conversion tasks. Well-designed prompts can guide the AI to focus on critical aspects, such as aligning with PostgreSQL syntax, preserving business logic, and adhering to performance optimization best practices. This approach minimizes errors and ambiguities in the converted code while enhancing efficiency. Additionally, prompt engineering allows for iterative refinement, enabling fine-tuning of the conversion process based on feedback, thus improving accuracy and reducing manual effort.

Code conversion example using Amazon Bedrock

To illustrate the code conversion process, we present an example of using Amazon Bedrock to convert code from Microsoft SQL Server to PostgreSQL. After gaining access to the model:

1. In the AWS Management Console for Amazon Bedrock, select Chat/Text under Playgrounds in the navigation pane.
2. In the new window, click on Select model.
3. Choose the Anthropic category and select Claude 3.5 Sonnet.
4. Click Apply to access the Amazon Bedrock chat window, where you can enter prompts.

To follow along, copy and paste the following prompt in your Amazon Bedrock chat window:

Can you convert the Microsoft SQL server procedure to PostgreSQL function and generate the test cases covering complete code coverage

"CREATE PROCEDURE dbo.UpdateEmployeeSalaries @DepartmentID INT, @IncreasePercentage DECIMAL(5,2), @MaxIncreaseAmount MONEY, @EffectiveDate DATE AS BEGIN SET NOCOUNT ON;
DECLARE @ErrorMsg NVARCHAR(4000);
DECLARE @RowCount INT = 0;
BEGIN TRY
-- Validate input parameters
IF @IncreasePercentage  25
THROW 50001, 'Invalid increase percentage. Must be between 0 and 25.', 1;
IF @MaxIncreaseAmount  @MaxIncreaseAmount
THEN e.Salary + @MaxIncreaseAmount
ELSE e.Salary * (1 + @IncreasePercentage/100)
END AS NewSalary
FROM Employees e
WHERE e.DepartmentID = @DepartmentID
AND e.EmploymentStatus = 'Active'
AND DATEDIFF(YEAR, e.HireDate, @EffectiveDate) >= 1;
-- Update employee salaries
UPDATE e
SET e.Salary = t.NewSalary,
e.LastSalaryUpdateDate = @EffectiveDate
FROM Employees e
INNER JOIN #TempSalaryUpdates t ON e.EmployeeID = t.EmployeeID;
SET @RowCount = @@ROWCOUNT;
-- Log salary updates
INSERT INTO SalaryUpdateLog (EmployeeID, OldSalary, NewSalary, UpdateDate)
SELECT e.EmployeeID, e.Salary AS OldSalary, t.NewSalary, @EffectiveDate
FROM Employees e
INNER JOIN #TempSalaryUpdates t ON e.EmployeeID = t.EmployeeID;
-- Commit transaction
COMMIT TRANSACTION;
-- Return success message
SELECT CONCAT(@RowCount, ' employee salaries updated successfully.') AS ResultMessage;
END TRY
BEGIN CATCH
-- Rollback transaction on error
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @ErrorMsg = ERROR_MESSAGE();
THROW 50000, @ErrorMsg, 1;
END CATCH;
END;"

Upon submission, the generative AI will provide the PostgreSQL equivalent of the Microsoft SQL Server procedure, along with test cases for comprehensive code coverage. However, due to the probabilistic nature of generative AI, the output may vary with each request.

For instance, the AI might generate the following response:

Here's the PostgreSQL equivalent of the Microsoft SQL Server procedure, along with test cases for complete code coverage:

CREATE OR REPLACE FUNCTION update_employee_salaries(
    p_department_id INT,
    p_increase_percentage DECIMAL(5,2),
    p_max_increase_amount DECIMAL(10,2),
    p_effective_date DATE
)
RETURNS TEXT AS $$
DECLARE
    v_error_msg TEXT;
    v_row_count INT := 0;
BEGIN
    -- Validate input parameters
    IF p_increase_percentage  25 THEN
        RAISE EXCEPTION 'Invalid increase percentage. Must be between 0 and 25.' USING ERRCODE = '50001';
    END IF;

    IF p_max_increase_amount  p_max_increase_amount
                   THEN e.salary + p_max_increase_amount
                   ELSE e.salary * (1 + p_increase_percentage/100)
               END AS new_salary
        FROM employees e
        WHERE e.department_id = p_department_id
          AND e.employment_status = 'Active'
          AND p_effective_date - e.hire_date >= INTERVAL '1 year';

        -- Update employee salaries
        UPDATE employees e
        SET salary = t.new_salary,
            last_salary_update_date = p_effective_date
        FROM temp_salary_updates t
        WHERE e.employee_id = t.employee_id;

        GET DIAGNOSTICS v_row_count = ROW_COUNT;

        -- Log salary updates
        INSERT INTO salary_update_log (employee_id, old_salary, new_salary, update_date)
        SELECT e.employee_id, e.salary AS old_salary, t.new_salary, p_effective_date
        FROM employees e
        INNER JOIN temp_salary_updates t ON e.employee_id = t.employee_id;

        -- Return success message
        RETURN v_row_count || ' employee salaries updated successfully.';
    EXCEPTION
        WHEN OTHERS THEN
            -- Rollback is automatic in PostgreSQL for unhandled exceptions
            v_error_msg := SQLERRM;
            RAISE EXCEPTION '%', v_error_msg USING ERRCODE = '50000';
    END;
END;
$$ LANGUAGE plpgsql;

In this response, the AI generates the PostgreSQL function and corresponding test cases. However, it may alter naming conventions, such as converting to lowercase or adding underscores. To maintain consistency with the original naming conventions, you can prompt the AI to retain the desired format:

I see the naming convention is modified by adding "_". Can you generate the code again by retaining the naming conventions similar to the source?

Subsequently, the AI may provide a revised version that aligns more closely with the original naming conventions, ensuring consistency throughout the code.

Further enhancements to automate test cases and generate the summary

Interacting with Amazon Bedrock can be accomplished through chat or API calls. By utilizing the Amazon Bedrock API, you can develop an application that automates the testing of generated test cases against both source and target environments, verifying the results efficiently. This automation streamlines business logic validation, identifies failed test cases, and reduces manual testing efforts. Furthermore, the custom application can measure latency between environments, assess the performance of converted code, and optimize specific business logic functionalities, thus eliminating the need for manual verification of each procedure and function.

To accelerate code conversion during database migration, AWS provides a suite of tools and services. For further details, refer to Accelerate database modernization with generative AI using AWS Database Migration Service Schema Conversion.

For comprehensive quality assurance, high code coverage should be complemented with integration and system tests. Specialized tools like pgTAP can provide precise line-by-line coverage information, helping identify any remaining uncovered code paths.

To validate the responses generated by Amazon Bedrock, you can execute the results in your PostgreSQL environment. If you lack the necessary tables, you can prompt Amazon Bedrock for the required scripts to create them.

Certainly! I'll provide the table creation scripts and insert statements for test data for the tables used in the function: Employees and SalaryUpdateLog.

Table Creation Scripts:

-- Create Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT NOT NULL,
    Salary DECIMAL(10, 2) NOT NULL,
    EmploymentStatus VARCHAR(20) NOT NULL,
    HireDate DATE NOT NULL,
    LastSalaryUpdateDate DATE
);

-- Create SalaryUpdateLog table
CREATE TABLE SalaryUpdateLog (
    LogID SERIAL PRIMARY KEY,
    EmployeeID INT NOT NULL,
    OldSalary DECIMAL(10, 2) NOT NULL,
    NewSalary DECIMAL(10, 2) NOT NULL,
    UpdateDate DATE NOT NULL
);

Test Data:

-- Insert test data into Employees table
INSERT INTO Employees (EmployeeID, DepartmentID, Salary, EmploymentStatus, HireDate, LastSalaryUpdateDate)
VALUES
    (1001, 1, 50000.00, 'Active', '2022-01-01', '2022-01-01'),
    (1002, 1, 40000.00, 'Active', '2022-01-01', '2022-01-01'),
    (1003, 1, 45000.00, 'Inactive', '2022-01-01', '2022-01-01'),
    (1004, 1, 55000.00, 'Active', '2023-05-01', '2023-05-01'),
    (1005, 1, 60000.00, 'Active', '2022-01-01', '2022-01-01'),
    (1006, 1, 70000.00, 'Active', '2021-01-01', '2022-01-01'),
    (2001, 2, 55000.00, 'Active', '2021-06-01', '2022-06-01'),
    (2002, 2, 48000.00, 'Active', '2022-03-15', '2022-03-15'),
    (3001, 3, 65000.00, 'Active', '2020-11-01', '2022-11-01');

-- The SalaryUpdateLog table will be populated by the function, so we don't need to insert test data here

This test data includes:
1. Employees in different departments
2. Employees with various salaries
3. Both active and inactive employees
4. Employees with different hire dates (some less than a year, some more)
5. Employees with different last salary update dates

You can use this data to test various scenarios in your function. For example:

- Employees 1001, 1002, 1005, and 1006 are eligible for salary updates when testing Department 1.
- Employee 1003 is inactive and should be excluded.
- Employee 1004 was hired less than a year ago and should be excluded.
- Employees 2001 and 2002 can be used to test updates in a different department.
- Employee 3001 can be used to test a case where no employees in a department are updated.

This data set allows you to verify that your function correctly applies salary increases, respects the eligibility criteria (employment status and hire date), and handles different departments appropriately.