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!
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;
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:
- Supported Statements
- Change Log
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.
- 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.