AI-powered SQL Query Converter Tool is Now Available in Relational Migrator
When I traveled to Japan for the first time it was shortly after translation apps on smartphones had really taken off. Even though I knew enough phrases to get by as a tourist I was amazed at how empowered I was by being able to have smoother conversations and read signs more easily. The power of AI helped me understand a language I had only a passing familiarity with and drastically improved my experience in another country. I was able to spend more time enjoying myself and spend less time looking up common words and sentences in a phrase book. So what does this have to do with application modernization?
MongoDB.local NYC
Join us in person on May 2, 2024 for our keynote address, announcements, and technical sessions to help you build and deploy mission-critical applications at scale.
Use Code Web50 for 50% off your ticket!
Learn More
Transitioning from relational databases as part of a modernization effort is more than migrating data from a legacy database to a modern one. There is all the planning, designing, testing, refactoring, validating, and ongoing operation that makes modernization efforts a complex project to navigate successfully. MongoDB’s free
Relational Migrator
tool has helped with many of these tasks including schema design, data migration, and code generation, but we know this is just the beginning.
One of the most common challenges of migrating legacy applications to MongoDB is working with SQL queries, triggers, and stored procedures that are often undocumented and must be manually converted to MongoDB Query API syntax. This requires deep knowledge of both SQL and the MongoDB Query API, which is rare if teams are used to only using one system or the other. In addition, teams often have hundreds, if not thousands of queries, triggers, and stored procedures that must be converted, which is extremely time-consuming and tedious. Doing these conversions manually would be like traveling abroad and looking up each object one by one in a phrase book instead of using a translation app. Thankfully with generative AI, we are finally able to get the modern version of the translation app on your phone.
The
latest release of Relational Migrator
is able to use generative AI to help your developers quickly convert existing SQL queries, triggers, and stored procedures to work with MongoDB using your choice of programming language (JavaScript, C#, or Java). By automating the generation of development-ready MongoDB queries, your team can be more efficient by redirecting their time to more important testing and optimization efforts — accelerating your migration project. Teams that are familiar with SQL can also use the Query Converter to help close their MongoDB knowledge gap. The SQL objects they're familiar with are translated, making it easier to learn the new syntax by seeing them next to each other.
Let’s take a closer look at how Query Converter can convert a SQL Server stored procedure to work with MongoDB.
Figure 1:
The MongoDB Query Converter Dashboard
We’ll start by importing the stored procedure from the relational database into our Relational Migrator project. This particular stored procedure joins the results from two tables, performs some arithmetic on some of the columns, and filters the results based on an input parameter.
CREATE PROCEDURE CustOrdersDetail @OrderID int
AS
SELECT ProductName,
UnitPrice=ROUND(Od.UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
Developers who are experienced with the MongoDB aggregation framework would know that the equivalent method to join data from two collections is to use the
$lookup
stage. However, when migrating a relational database to MongoDB, it often makes sense to consolidate data from multiple tables into a single collection. In this example, we are doing exactly that, by combining data from the
Orders
,
Order Details
, and
Products
table into a single
orders
collection.
This means that, when considering the changes to the schema, we do not actually need a
$lookup
stage at all, as the data from each of the required tables has already been merged into a single collection. Relational Migrator’s Query Converter works alongside the schema mapping functionality and automatically adjusts the generated query to work against your chosen schema. With JavaScript chosen as our target language, the converted query avoids the need for a costly join and includes MongoDB equivalents of our original SQL arithmetic functions. The query is now ready to test and include in our modernized app.
const CustOrdersDetail = async (db, OrderID) => {
return await db.collection('orders').aggregate([
{
$match: {
orderId: OrderID
}
},
{
$unwind: '$lineItems'
},
{
$project: {
ProductName: '$product.productName',
UnitPrice: { $round: ['$lineItems.unitPrice', 2] },
Quantity: '$lineItems.quantity',
Discount: { $multiply: ['$lineItems.discount', 100] },
ExtendedPrice: {
$round: [
{
$multiply: [
'$lineItems.quantity',
{ $subtract: [1, '$lineItems.discount'] },
'$lineItems.unitPrice'
]
},
2
]
}
}
}
]).toArray();
};
Relational Migrator
does more than just query conversion, it also assists with app code generation, data modeling, and data migration, which drastically cuts down on the time and effort required to modernize your team's applications. Just like a language translation app while traveling abroad it can drastically improve your experience converting and understanding a new language or technology.
The new Query Converter tool is now available for free for anyone to try as part of a public preview in the Relational Migrator tool.
Download Relational Migrator
and try converting your SQL queries and stored procedures today.
March 25, 2024