# Change Python and Perl applications to support database migration from Microsoft SQL Server to Amazon Aurora PostgreSQL-Compatible Edition *Dwarika Patra and Deepesh Jayaprakash, Amazon Web Services* ## Summary This pattern describes changes to application repositories that might be required when you migrate databases from Microsoft SQL Server to Amazon Aurora PostgreSQL-Compatible Edition. The pattern assumes that these applications are Python-based or Perl-based, and provides separate instructions for these scripting languages. Migrating SQL Server databases to Aurora PostgreSQL-Compatible involves schema conversion, database object conversion, data migration, and data loading. Because of the differences between PostgreSQL and SQL Server (relating to data types, connection objects, syntax, and logic), the most difficult migration task involves making the necessary changes to the code base so that it works correctly with PostgreSQL. For a Python-based application, connection objects and classes are scattered throughout the system. Also, the Python code base might use multiple libraries to connect to the database. If the database connection interface changes, the objects that run the application’s inline queries also require changes. For a Perl-based application, changes involve connection objects, database connection drivers, static and dynamic inline SQL statements, and how the application handles complex dynamic DML queries and results sets. When you migrate your application, you can also consider possible enhancements on AWS, such as replacing the FTP server with Amazon Simple Storage Service (Amazon S3) access. The application migration process involves the following challenges: + Connection objects. If connection objects are scattered in the code with multiple libraries and function calls, you might have to find a generalized way to change them to support PostgreSQL. + Error or exception handling during record retrieval or updates. If you have conditional create, read, update, and delete (CRUD) operations on the database that return variables, results sets, or data frames, any errors or exceptions might result in application errors with cascading effects. These should be handled carefully with proper validations and save points. One such save point is to call large inline SQL queries or database objects inside `BEGIN...EXCEPTION...END` blocks. + Controlling transactions and their validation. These includes manual and automatic commits and rollbacks. The PostgreSQL driver for Perl requires you to always explicitly set the auto-commit attribute. + Handling dynamic SQL queries. This requires a strong understanding of the query logic and iterative testing to ensure that queries work as expected. + Performance. You should ensure that code changes don’t result in degraded application performance. This pattern explains the conversion process in detail. ## Prerequisites and limitations **Prerequisites** + Working knowledge of Python and Perl syntax. + Basic skills in SQL Server and PostgreSQL. + Understanding of your existing application architecture. + Access to your application code, SQL Server database, and PostgreSQL database. + Access to Windows or Linux (or other Unix) development environment with credentials for developing, testing, and validating application changes. + For a Python-based application, standard Python libraries that your application might require, such as **Pandas **to handle data frames, and **psycopg2** or **SQLAlchemy **for database connections. + For a Perl-based application, required Perl packages with dependent libraries or modules. The Comprehensive Perl Archive Network (CPAN) module can support most application requirements. + All required dependent customized libraries or modules.  + Database credentials for read access to SQL Server and read/write access to Aurora. + PostgreSQL to validate and debug application changes with services and users. + Access to development tools during application migration such as Visual Studio Code, Sublime Text, or **pgAdmin**. **Limitations** + Some Python or Perl versions, modules, libraries, and packages aren’t compatible with the cloud environment. + Some third-party libraries and frameworks used for SQL Server cannot be replaced to support PostgreSQL migration.  + Performance variations might require changes to your application, to inline Transact-SQL (T-SQL) queries, database functions, and stored procedures. + PostgreSQL supports lowercase names for table names, column names, and other database objects.  + Some data types, such as UUID columns, are stored in lowercase only. Python and Perl applications must handle such case differences.  + Character encoding differences must be handled with the correct data type for the corresponding text columns in the PostgreSQL database.                                 **Product versions** + Python 3.6 or later (use the version that supports your operating system) + Perl 5.8.3 or later (use the version that supports your operating system) + Aurora PostgreSQL-Compatible Edition 4.2 or later (see [details](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Updates.20180305.html#AuroraPostgreSQL.Updates.20180305.42)) ## Architecture **Source technology stack** + Scripting (application programming) language: Python 2.7 or later, or Perl 5.8  + Database: Microsoft SQL Server version 13 + Operating system: Red Hat Enterprise Linux (RHEL) 7  **Target technology stack  ** + Scripting (application programming) language: Python 3.6 or later, or Perl 5.8 or later  + Database: Aurora PostgreSQL-Compatible 4.2 + Operating system: RHEL 7  **Migration architecture ** ![Migrating a Perl or Python application with SQL Server to Aurora PostgreSQL-Compatible](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/b64de64a-bd55-4db7-ba7b-0a2557862af1/images/b8fab3e2-ded5-4f58-86bf-3f645252e9fc.png) ## Tools **AWS services and tools** + [Aurora PostgreSQL–Compatible Edition](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html) is a fully managed, PostgreSQL-compatible, and ACID-compliant relational database engine that combines the speed and reliability of high-end commercial databases with the cost-effectiveness of open-source databases. Aurora PostgreSQL is a drop-in replacement for PostgreSQL and makes it easier and more cost-effective to set up, operate, and scale your new and existing PostgreSQL deployments. + [AWS Command Line Interface (AWS CLI) ](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html)is an open-source tool that enables you to interact with AWS services by using commands in your command-line shell. **Other tools** + [Python](https://www.python.org/) and PostgresSQL database connection libraries such as [psycopg2](https://pypi.org/project/psycopg2/) and [SQLAlchemy](https://www.sqlalchemy.org/) + [Perl](https://www.perl.org/) and its [DBI modules](https://metacpan.org/pod/DBD::Pg) + [PostgreSQL interactive terminal](https://www.postgresql.org/docs/13/app-psql.html) (psql) ## Epics ### Migrate your application repository to PostgreSQL – high-level steps | Task | Description | Skills required | | --- | --- | --- | | Follow these code conversion steps to migrate your application to PostgreSQL. | [See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/change-python-and-perl-applications-to-support-database-migration-from-microsoft-sql-server-to-amazon-aurora-postgresql-compatible-edition.html)The following epics provide detailed instructions for some of these conversion tasks for Python and Perl applications. | App developer | | Use a checklist for each step of the migration. | Add the following to your checklist for each step of application migration, including the final step:[See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/change-python-and-perl-applications-to-support-database-migration-from-microsoft-sql-server-to-amazon-aurora-postgresql-compatible-edition.html) | App developer | ### Analyze and update your application – Python code base | Task | Description | Skills required | | --- | --- | --- | | Analyze your existing Python code base. | Your analysis should include the following to facilitate the application migration process:[See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/change-python-and-perl-applications-to-support-database-migration-from-microsoft-sql-server-to-amazon-aurora-postgresql-compatible-edition.html) | App developer | | Convert your database connections to support PostgreSQL. | Most Python applications use the **pyodbc **library to connect with SQL Server databases as follows.
import pyodbc
....
try:
    conn_string = "Driver=ODBC Driver 17 for SQL
    Server;UID={};PWD={};Server={};Database={}".format (conn_user, conn_password,
    conn_server, conn_database)
    conn = pyodbc.connect(conn_string)
    cur = conn.cursor()
    result = cur.execute(query_string)
    for row in result:
    print (row)
except Exception as e:
    print(str(e))
Convert the database connection to support PostgreSQL as follows.
import pyodbc
import psycopg2
....
try:
    conn_string = ‘postgresql+psycopg2://’+
    conn_user+’:’+conn_password+’@’+conn_server+’/’+conn_database
    conn = pyodbc.connect(conn_string, connect_args={‘options’:’-csearch_path=dbo’})
    cur = conn.cursor()
    result = cur.execute(query_string)
    for row in result:
    print (row)
except Exception as e:
    print(str(e))
| App developer | | Change inline SQL queries to PostgreSQL. | Convert your inline SQL queries to a PostgreSQL-compatible format. For example, the following SQL Server query retrieves a string from a table.
dtype = "type1"
stm = ‘"SELECT TOP 1 searchcode FROM TypesTable (NOLOCK)
WHERE code="’ + "’" + str(dtype) + "’"
# For Microsoft SQL Server Database Connection
engine = create_engine(‘mssql+pyodbc:///?odbc_connect=%s’ % urllib.parse.quote_plus(conn_string), connect_args={‘connect_timeout’:login_timeout})
conn = engine_connect()
rs = conn.execute(stm)
for row in rs:
    print(row)
After conversion, the PostgreSQL-compatible inline SQL query looks like the following.
dtype = "type1"
stm = ‘"SELECT searchcode FROM TypesTable
WHERE code="’ + "’" + str(dtype) + "’ LIMIT 1"
# For PostgreSQL Database Connection
engine = create_engine(‘postgres+psycopg2://%s’ %conn_string, connect_args={‘connect_timeout’:login_timeout})
conn = engine.connect()
rs = conn.execute(stm)
for row in rs:
    print(row)
| App developer | | Handle dynamic SQL queries. | Dynamic SQL can be present in one script or in multiple Python scripts. Earlier examples showed how to use Python’s string replace function to insert variables for constructing dynamic SQL queries. An alternate approach is to append the query string with variables wherever applicable. In the following example, the query string is constructed on the fly based on the values returned by a function.
query = ‘"SELECT id from equity e join issues i on e.permId=i.permId where e.id’"
query += get_id_filter(ids) + " e.id is NOT NULL
These types of dynamic queries are very common during application migration. Follow these steps to handle dynamic queries:[See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/change-python-and-perl-applications-to-support-database-migration-from-microsoft-sql-server-to-amazon-aurora-postgresql-compatible-edition.html) | App developer | | Handle results sets, variables, and data frames. | For Microsoft SQL Server, you use Python methods such as `fetchone()` or `fetchall()` to retrieve the results set from the database. You can also use `fetchmany(size)` and specify the number of records to return from the results set. To do this, you can use the **pyodbc **connection object as shown in the following example.**pyodbc (Microsoft SQL Server)**
import pyodbc 
server = 'tcp:myserver.database.windows.net' 
database = 'exampledb' 
username = 'exampleusername' 
password = 'examplepassword' 
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()
cursor.execute("SELECT * FROM ITEMS") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()
In Aurora, to perform similar tasks such as connecting to PostgreSQL and fetching results sets, you can use either **psycopg2 **or **SQLAlchemy**. These Python libraries provide the connection module and cursor object to traverse through the PostgreSQL database records, as shown in the following example.**psycopg2 (Aurora PostgreSQL-Compatible)**
import psycopg2
query = "SELECT * FROM ITEMS;"
//Initialize variables
host=dbname=user=password=port=sslmode=connect_timeout=""
connstring = "host='{host}' dbname='{dbname}' user='{user}' \
password='{password}'port='{port}'".format(host=host,dbname=dbname,\
user=user,password=password,port=port) 
conn = psycopg2.connect(connstring)
cursor = conn.cursor()
cursor.execute(query)
column_names = [column[0] for column in cursor.description]
print("Column Names: ", column_names)
print("Column values: "
for row in cursor:
    print("itemid :", row[0])
    print("itemdescrption :", row[1])
    print("itemprice :", row[3]))
**SQLAlchemy (Aurora PostgreSQL-Compatible)**
from sqlalchemy import create_engine
from pandas import DataFrame
conn_string = 'postgresql://core:database@localhost:5432/exampledatabase'
engine = create_engine(conn_string)
conn = engine.connect()
dataid = 1001
result = conn.execute("SELECT * FROM ITEMS")
df = DataFrame(result.fetchall())
df.columns = result.keys()
df = pd.DataFrame()
engine.connect()
df = pd.read_sql_query(sql_query, engine, coerce_float=False)
print("df=", df)
| App developer | | Test your application during and after migration. | Testing the migrated Python application is an ongoing process. Because the migration includes connection object changes (**psycopg2 **or **SQLAlchemy**), error handling, new features (data frames), inline SQL changes, bulk copy functionalities (`bcp` instead of `COPY`) and similar changes, it must be tested carefully during and after application migration. Check for:[See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/change-python-and-perl-applications-to-support-database-migration-from-microsoft-sql-server-to-amazon-aurora-postgresql-compatible-edition.html) | App developer | ### Analyze and update your application – Perl code base | Task | Description | Skills required | | --- | --- | --- | | Analyze your existing Perl code base. | Your analysis should include the following to facilitate the application migration process. You should identify:[See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/change-python-and-perl-applications-to-support-database-migration-from-microsoft-sql-server-to-amazon-aurora-postgresql-compatible-edition.html) | App developer | | Convert the connections from the Perl application and DBI module to support PostgreSQL. | Perl-based applications generally use the Perl DBI module, which is a standard database access module for the Perl programming language. You can use the same DBI module with different drivers for SQL Server and PostgreSQL.For more information about required Perl modules, installations, and other instructions, see the [DBD::Pg documentation](https://metacpan.org/pod/DBD::Pg). The following example connects to Aurora PostgreSQL-Compatible at `exampletest-aurorapg-database.cluster-sampleclusture.us-east.-rds.amazonaws.com`.
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $hostname = "exampletest-aurorapg-database-sampleclusture.us-east.rds.amazonaws.com"
my $dsn = "DBI:$driver: dbname = $hostname;host = 127.0.0.1;port = 5432";
my $username = "postgres";
my $password = "pass123";
$dbh = DBI->connect("dbi:Pg:dbname=$hostname;host=$host;port=$port;options=$options",
      $username,
      $password,
      {AutoCommit => 0, RaiseError => 1, PrintError => 0}
      );
| App developer | | Change Inline SQL queries to PostgreSQL. | Your application might have inline SQL queries with `SELECT`, `DELETE`, `UPDATE`, and similar statements that include query clauses that PostgreSQL doesn’t support. For example, query keywords such as `TOP` and `NOLOCK` aren’t supported in PostgreSQL. The following examples show how you can handle `TOP`, `NOLOCK`, and Boolean variables.In SQL Server:
$sqlStr = $sqlStr
. "WHERE a.student_id in (SELECT TOP $numofRecords c_student_id   \
FROM active_student_record b WITH (NOLOCK) \
INNER JOIN student_contributor c WITH (NOLOCK) on c.contributor_id = b.c_st)
For PostgreSQL, convert to:
$sqlStr = $sqlStr
. "WHERE a.student_id in (SELECT TOP $numofRecords c_student_id  \
FROM active_student_record b INNER JOIN student_contributor c  \
on c.contributor_id = b.c_student_contr_id WHERE b_current_1 is true \
LIMIT $numofRecords)"
| App developer | | Handle dynamic SQL queries and Perl variables. | Dynamic SQL queries are SQL statements that are built at application runtime. These queries are constructed dynamically when the application is running, depending on certain conditions, so the full text of the query isn’t known until runtime. An example is a financial analytics application that analyzes the top 10 shares on a daily basis, and these shares change every day. The SQL tables are created based on top performers, and the values aren’t known until runtime.Let’s say that the inline SQL queries for this example are passed to a wrapper function to get the results set in a variable, and then a variable uses a condition to determine whether the table exists:[See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/change-python-and-perl-applications-to-support-database-migration-from-microsoft-sql-server-to-amazon-aurora-postgresql-compatible-edition.html)Here’s an example of variable handling, followed by the SQL Server and PostgreSQL queries for this use case.
my $tableexists = db_read( arg 1, $sql_qry, undef, 'writer');
my $table_already_exists = $tableexists->[0]{table_exists};
if ($table_already_exists){
# do some thing
}
else {
# do something else
}
SQL Server:
my $sql_qry = "SELECT OBJECT_ID('$backendTable', 'U') table_exists", undef, 'writer')";
PostgreSQL:
my $sql_qry = "SELECT TO_REGCLASS('$backendTable', 'U') table_exists", undef, 'writer')";
The following example uses a** **Perl variable in inline SQL, which runs a `SELECT` statement with a `JOIN` to fetch the primary key of the table and position of the key column.SQL Server:
my $sql_qry = "SELECT column_name', character_maxi mum_length \
FROM INFORMATION_SCHEMA.COLUMNS \
WHERE TABLE_SCHEMA='$example_schemaInfo' \
AND TABLE_NAME='$example_table' \
AND DATA_TYPE IN ('varchar','nvarchar');";
PostgreSQL:
my $sql_qry = "SELECT c1.column_name, c1.ordinal_position \
FROM information_schema.key_column_usage AS c LEFT \
JOIN information_schema.table_constraints AS t1 \
ON t1.constraint_name = c1.constraint_name \
WHERE t1.table_name = $example_schemaInfo'.'$example_table’ \
AND t1.constraint_type = 'PRIMARY KEY' ;";
| App developer | ### Make additional changes to your Perl-based or Python-based application to support PostgreSQL | Task | Description | Skills required | | --- | --- | --- | | Convert additional SQL Server constructs to PostgreSQL. | The following changes apply to all applications, regardless of programming language.[See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/change-python-and-perl-applications-to-support-database-migration-from-microsoft-sql-server-to-amazon-aurora-postgresql-compatible-edition.html) | App developer | ### Improve performance | Task | Description | Skills required | | --- | --- | --- | | Take advantage of AWS services to make performance enhancements. | When you migrate to the AWS Cloud, you can refine your application and database design to take advantage of AWS services. For example, if the queries from your Python application, which is connected to an Aurora PostgreSQL-Compatible database server, is taking more time than your original Microsoft SQL Server queries, you could consider creating a feed of historical data directly to an Amazon Simple Storage Service (Amazon S3) bucket from the Aurora server, and use Amazon Athena-based SQL queries to generate reports and analytic data queries for your user dashboards. | App developer, Cloud architect | ## Related resources + [Perl](https://www.perl.org/) + [Perl DBI Module](https://metacpan.org/pod/DBI) + [Python](https://www.python.org/) + [psycopg2](https://pypi.org/project/psycopg2/) + [SQLAlchemy](https://www.sqlalchemy.org/) + [Bulk Copy - PostgreSQL](https://www.postgresql.org/docs/9.2/sql-copy.html) + [Bulk Copy - Microsoft SQL Server](https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15) + [PostgreSQL](https://www.postgresql.org/) + [Working with Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html) ## Additional information Both Microsoft SQL Server and Aurora PostgreSQL-Compatible are ANSI SQL-complaint. However, you should still be aware of any incompatibilities in syntax, column data types, native database-specific functions, bulk inserts, and case sensitivity when you migrate your Python or Perl application from SQL Server to PostgreSQL. The following sections provide more information about possible inconsistencies. **Data type comparison** Data type changes from SQL Server to PostgreSQL can lead to significant differences in the resulting data that applications operate on. For a comparison of data types, see the table on the [Sqlines website](https://www.sqlines.com/sql-server-to-postgresql). **Native or built-in SQL functions** The behavior of some functions differs between SQL Server and PostgreSQL databases. The following table provides a comparison. | | | Microsoft SQL Server | Description | PostgreSQL | | --- |--- |--- | | `CAST`  | Converts a value from one data type to another. | PostgreSQL `type :: operator` | | `GETDATE()` | Returns the current database system date and time, in a `YYYY-MM-DD hh:mm:ss.mmm` format. | `CLOCK_TIMESTAMP` | | `DATEADD` | Adds a time/date interval to a date. | `INTERVAL` expression | | `CONVERT` | Converts a value to a specific data format. | `TO_CHAR` | | `DATEDIFF` | Returns the difference between two dates. | `DATE_PART` | | `TOP` | Limits the number of rows in a `SELECT` results set. | `LIMIT/FETCH` | ** Anonymous blocks** A structured SQL query is organized into sections such as declaration, executables, and exception handling. The following table compares the Microsoft SQL Server and PostgreSQL versions of a simple anonymous block. For complex anonymous blocks, we recommend that you call a custom database function within your application. | | | Microsoft SQL Server | PostgreSQL | | --- |--- | | 
my $sql_qry1=
my $sql_qry2 =
my $sqlqry = "BEGIN TRAN
$sql_qry1 $sql_qry2
if @\@error !=0 ROLLBACK
TRAN
else COMIT TRAN";
| 
my $sql_qry1=
my $sql_qry2 =
my $sql_qry = " DO \$\$
BEGIN
$header_sql $content_sql
END
\$\$";
|   **Other differences** + **Bulk inserts of rows:** The PostgreSQL equivalent of the [Microsoft SQL Server bcp utility](https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15) is [COPY](https://www.postgresql.org/docs/9.2/sql-copy.html). + **Case sensitivity:**  Column names are case-sensitive in PostgreSQL, so you have to convert your SQL Server column names to lowercase or uppercase. This becomes a factor when you extract or compare data, or place column names in results sets or variables. The following example identifies columns where values might be stored in uppercase or lowercase. ``` my $sql_qry = "SELECT $record_id FROM $exampleTable WHERE LOWER($record_name) = \'failed transaction\'"; ``` + **Concatenation: **SQL Server uses `+` as an operator for string concatenation, whereas PostgreSQL uses `||`. + **Validation: **You should test and validate inline SQL queries and functions before you use them in application code for PostgreSQL. + **ORM Library inclusion : **You can also look for including or replace existing database connection library with Python ORM libraries such as [SQLAlchemy](https://www.sqlalchemy.org/) and [PynomoDB](https://pynamodb.readthedocs.io/en/latest/quickstart.html). This will help to easily query and manipulate data from a database using an object-oriented paradigm.