Decision: We will add support for MSSQL

At Gridraven, we build Claw, a forecasting platform for the energy sector. Gridraven Claw was initially built with Postgres as its database, but when we learned that MSSQL is the industry standard for our on-premise customers, we faced a dilemma: add MSSQL support or limit our market reach.

Adding support for another database is not a decision to be taken lightly.

1. Database-related issues are time-consuming to investigate and it requires deep expertise to investigate such issues. Supporting the same business logic with different databases would just increase the time spent investigating database-related issues and thus slow down feature development velocity.
2. A database engine is never truly a drop-in replacement for another database engine. Hence it is inevitable that there will be code written which is specific to a database engine.

However, there was an obvious business case for adding support for MSSQL. On-premise deployment is a very important use case for Gridraven Claw, and MSSQL appears to be the database engine preferred by most energy grid operators.

We did not want to fully give up Postgres in favor of MSSQL. Postgres is the market-leading relational database, and it is the database engine that our engineers know well. Postgres has strong support for multi-tenant workloads, which is what we need for our cloud offering. So it came down to deciding if there was a way to add support for another database without it significantly slowing down product development in the future.

We decided there is a way to add support for another database without significant maintenance overhead, based on the following properties of Gridraven Claw.

1. The main complexity in Gridraven Claw is not in the database layer. Gridraven Claw is used to configure forecasting and to display results. These seemed like use cases we can handle without pushing a database to its limits.
2. We were already using SQLAlchemy and Alembic, which allow writing code in a dialect-agnostic way. We would still need to write some dialect-specific code, but having these libraries in use, made it possible to keep the amount of dialect specific code at a minimum.

Project goal: one unified code base that works with both database engines

Once we decided to go ahead with adding MSSQL support for Gridraven Claw, we focused on finding the optimal way to implement this functionality. We wanted a solution that met two key criteria:

1. There is a minimal maintenance overhead
2. There is one code base with minimal database-specific logic

General approach: we use SQLAlchemy and Alembic to write database-agnostic code

SQLAlchemy is a popular Python tool for writing database queries without raw SQL. SQLAlchemy is dialect-agnostic: SQLAlchemy code that works with Postgres is likely to also work with other database engines. For example, the following query

s.query(Organisation).filter(Organisation.id == 'org12345').limit(1)

compiles to the following Postgres-compatible raw SQL:

SELECT organisation.id as id, ... as column_2 FROM organisation WHERE id='org12345' LIMIT 1

or to the following MSSQL-compatible raw SQL:

SELECT TOP 1 organisation.id as id, ... as column_2 FROM organisation WHERE id='org12345'

SQLAlchemy's dialect-agnostic nature is what allows us to mostly write code once and run it on all database engines.

Alembic is a data migration tool that is part of the SQLAlchemy project. When we change the database schema (add a column, create a table, etc), we do so by updating SQLAlchemy models and then auto-generating migrations with Alembic. In principle, the data migration scripts created by Alembic are dialect-agnostic. In practice, we needed to make adjustments to migrations to account for differences between dialects. The adjustments we made will be explained in the next section of this article.

Implementation: Updating database migrations scripts and application code

The first step in adding support for MSSQL was to run the existing automated tests with an MSSQL database. This uncovered a number of compatibility issues to fix. To identify issues that did not appear during automated tests, we set up an additional staging environment with MSSQL.

In the end, the changes to add support to MSSQL amounted to approximately 400 lines of dialect-specific code across 10 files. There were eventually a small number of adjustments that were needed for our database migration scripts and application layer.

Adjustments to migration scripts

• The TIMESTAMP datatype in MSSQL is a deprecated synonym for rowversion and it has absolutely nothing to do with date and time. Whereas previously we had defined TIMESTAMP as the data type for temporal database fields, we now use the following helper function in Alembic migrations:

1def timezone_aware_timestamp() -> Union[mssql.DATETIMEOFFSET, sa.TIMESTAMP]:
2    if is_mssql():
3        return mssql.DATETIMEOFFSET(timezone=True)
4    return sa.TIMESTAMP(timezone=True)

• We were using Postgres functions to set default values for certain columns. For example the MSSQL equivalent of CURRENT_TIMESTAMP is SYSDATETIMEOFFSET. Again we use a helper function in Alembic migrations to set a server default for both database engines:

1def now_default() -> TextClause:
2    if is_mssql():
3        return text("SYSDATETIMEOFFSET()")
4    return text("CURRENT_TIMESTAMP")

• In MSSQL a column with a unique index can contain only a single null value. To handle that we changed some of our unique indexes to be partial and exclude null values.

Adjustments to application code

• MSSQL syntax for boolean fields differs from Postgres. For example: We changed in SQLAlchemy from .filter(Organisation.is_active.is_(True)) to .filter(Organisation.is_active == True), which translates correctly for both databases.
  • Postgres: WHERE is_active IS true
  • MSSQL: WHERE is_active = 1 (no boolean literals, only 0/1)
• MSSQL supports advisory lock, select for update, but with a different syntax.
• UUID v7 generates time-ordered IDs where newer IDs sort after older ones in Postgres. However, MSSQL's UNIQUEIDENTIFIER type uses a funny sort order that doesn't respect this chronological ordering - it sorts by the last 6 bytes first. We decided to stop relying on UUID ordering in our application code and use order by created_at instead.
• MSSQL has a limitation of up to 2100 parameters per query. There were some bulk insert queries which would have exceeded that limit without adjustments. We adjusted bulk insert queries with MSSQL to be executed in chunks to avoid hitting the limit.
• Something that we knew to avoid beforehand was the Postgres JSONB datatype. The JSONB data type is unique feature of Postgres. In our Postgres databases we use JSONB datatype, but avoid using Postgres JSON functions and operators. In MSSQL we store JSON data as text using NVARCHAR(MAX) data type. SQLAlchemy converts JSON data stored as text to a Python dictionary. Just as it converts JSON data stored as JSONB to a dictionary.

Maintenance: Minimal effort to maintain compatibility

The key components for maintaining MSSQL compatibility are:

• The test suite which runs all the tests with both Postgres and MSSQL database. The tests are executed both in the engineer’s dev machine and in the CI/CD pipeline. Developers discover compatibility issues no later than when CI/CD run.
• A staging environment with MSSQL database. A realistic workload runs in that staging environment, which also has full observability and monitoring. Running a realistic workload on the MSSQL staging environment allowed us to discover the MSSQL-specific parameters-per-query issue before it could affect our customers. We can also use the MSSQL staging environment to reproduce and study any issues which occur in the on-premise installations running on MSSQL.

Because we need to maintain support for different databases, we are quick to move workloads outside of a relational database when the workload is not best suited for a relational database:

• Weather forecast data on Gridraven Claw is highly granular time series data, which can be compressed very effectively. We started by storing weather forecast data in our application database, then moved it over to parquet stored in object storage and experienced a big performance win.
• We initially used relational database as Celery backend, but were quick to move to a Valkey backend once we encountered performance issues caused by using MSSQL as Celery backend.

Summary: Multi-database support can work with the right foundation

One month after deployment, the investment has proven worthwhile - MSSQL support has made Gridraven Claw easier to integrate for many of our customers without slowing down our development velocity.

Three factors were critical to keeping maintenance overhead minimal:

1. SQLAlchemy and Alembic from day one - We didn't retrofit abstraction layers; they were already our standard tools
2. Comprehensive testing against both databases - Running the full test suite against Postgres and MSSQL in CI/CD catches compatibility issues immediately
3. A realistic MSSQL staging environment - This discovered problems (like the 2100 parameter limit) that tests alone would have missed

The key to sustainable multi-database support is discipline. We've committed to:

• Moving workloads out of the relational database when they don't belong there (weather forecast timeseries data → Parquet, Celery backend → Valkey)
• Keeping database operations simple and avoiding database-specific features

It's still early days. We've encountered and fixed several MSSQL-specific issues in production, but haven't yet experienced the cumulative maintenance burden of supporting two databases over a longer period. If multi-database support remains sustainable, we'll write a follow-up post with longer-term insights. If it becomes a maintenance burden, that will be worth sharing too.