Our development team has been hard at work over the last few weeks working on our free-to-use transpiler, built to convert SQL statements from source to target. While SQLMorph already contains over 19,000 lines of ANTLR SQL grammar in nine different dialects, we have been continuously evaluating new opportunities to expand the tool.
Over the course of the last month, we’ve been working diligently on bringing more functionality to our Microsoft SQL Server to Snowflake transpilation, along with some quality of life updates to our UI and it’s usability. We continue to hear from our customers a need for SQL Server support, and aim to provide a full beta version as soon as possible.
What is SQLMorph and Why Would I Use It?
For those who are new to the blog series, to phData, or to SQLMorph itself, it’s worth briefly addressing what SQLMorph is and what it aims to address. When customers are migrating between database systems, it’s common to have decades of existing SQL queries, ETL processes, and stored procedures to perform a variety of tasks. These tasks could include administration, transformation, and/or application data and log storage.
In many cases, the existing SQL has complex business rules and logic. Since most database systems have their own flavor of syntax or specific functionality that they provide, migrating isn’t a simple copy and paste process.
So what do you do? You have three options:
- Manually translate each of your queries
- Start from scratch and risk losing decades of experience/knowledge
- Use a transpiler to translate the queries between syntaxes where possible
#3 is exactly what SQLMorph provides for our customers. We see a large number of customers moving to Snowflake and have built this tool to facilitate that use case.
Microsoft SQL Server Updates
Our primary focus lately has been to get Microsoft SQL Server support to a beta level, addressing the primary use cases for our customer migrations. There are a few core pieces of functionality that are complex to transpile such as stored procedures, stored functions, and administration tasks. Over the last couple of months, we’ve been working on stored procedures, but recently have shifted our focus to stored functions.
Functions are used within SQL to take parameters, perform some action (such as a complex calculation), and return the resulting value. This could be used for a variety of tasks, but let’s say you needed the ability to see how much of a particular product you had in stock. You could duplicate that logic in multiple SQL queries/functions, but that’s not best practice. Instead, let’s put it into a reusable function!
Example Source
CREATE FUNCTION dbo.GetInventory(@ProductID int)
RETURNS int
AS
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
Example Target
create or replace function dbo.GetInventory("ProductID" FLOAT)
returns FLOAT
language javascript
as
$$
var ret;
var sql_stmt = snowflake.createStatement({
sqlText: "SELECT SUM(p.Quantity) ret FROM Production.ProductInventory p WHERE p.ProductID = ProductID;"
});
var sql_result = sql_stmt.execute();
if ((ret IS NULL)) {
var ret = 0;
}
return ret;
$$
;
User Interface Updates
While we do provide a CLI option for SQLMorph, many customers continue to use our UI as well. Within the UI, we have a few different pages:
- Translate
- Supported Statements
- Change Log
- API
- Demo
- About
The main area of the application is the translate page, where we intake SQL and transpile from source to target. We’ve gone through the feedback we’re receiving from customers and made minor quality of life improvements including:
- Copy to clipboard icon is now spelled out in text
- Source name in dropdown is expanded without needing to scroll
- Command + Return will now submit the query for translation
Some of our customers frequently use multiple applications provided by phData. We’ve added an application selector in the top right corner of the navigation bar, so you can now quickly switch between applications! We’ll continue to build out our provided applications as we identify opportunities, and you’ll be able to find them there going forward.
Tidbits
- Every supported statement is flagged by the stage of maturity and support within SQLMorph by each dialect pair. You can view them here!
- When using the SQLMorph CLI to bulk translate files or directories, you will need to provide your authentication token. You can find this in the API tab in the navigation bar.
- If you want to see a summary of changes made to SQLMorph over time, you can view these in the Change Log tab in the navigation bar.
SQLMorph is Now SQL Translation
Fantastic news! phData combined all of its tools and apps into a unified interface called the phData Toolkit. With the Toolkit, you’ll have a centralized place to utilize all of your favorite phData tools including SQLMorph, now called SQL Translation.
