September 07, 2021

Microsoft SQL Server Support in Prisma is Production-Ready

Today we are excited to announce that Prisma support for SQL Server and Azure SQL is Generally Available and ready for production! Prisma helps developers be productive and confident when building data-intensive applications. Try it out

Microsoft SQL Server Support in Prisma is Production-Ready

Contents

Support for SQL Server and Azure SQL in Prisma is Generally Available

Today we are excited to announce that Prisma support for SQL Server and Azure SQL is Generally Available and ready for production workloads as part of the 3.0.1 release! 🎉

Since we released Prisma Client for General Availability over a year ago with support for PostgreSQL, MySQL, SQLite, and MariaDB, we've heard from thousands of engineers about how the Prisma ORM is helping them be more productive and confident when building data-intensive applications.

After passing rigorous testing internally and by the community over the last year, we are thrilled to bring Prisma's streamlined developer experience and type safety to developers using Microsoft SQL Server and Azure SQL in General Availability 🚀.

By extending the range of supported databases in Prisma, we're welcoming the Microsoft SQL Server and Azure SQL developer communities to join thousands of developers already using Prisma in production in various mission-critical applications.

Databases are hard

Developers working with relational databases face an ongoing challenge: How do you ensure that your application code aligns with your database schema throughout the development cycle? This is particularly challenging when you are iteratively evolving both the application and database schema to deliver new features and bug fixes.

Data modeling, schema migrations and writing database queries are common tasks application developers deal with every day.

At Prisma, we found that the Node.js ecosystem –while becoming increasingly popular to build database-backed applications– does not provide modern tools for application developers to deal with these tasks.

Even though there are many existing ORMs, developers still face the challenges of confidently deploying code changes that interact with the database – especially without tooling to statically verify code changes and database schema changes in tandem.

Prisma – making databases easy

Prisma is a next-generation ORM for Node.js and TypeScript that eliminates many of these problems and makes you more confident when building data-intensive backends. It can be used as an alternative to traditional ORMs and SQL query builders to read and write data to your database.

It consists of the following tools:

There are three core concepts central to the development workflow with Prisma:

  • The Prisma schema: A single source of truth for your database schema and application models which can be written by hand or populated by introspecting an existing database.
  • Type safety: A way to ensure that all application code interacting with the database can only do so safely. Attempting to query a non-existent column will immediately raise a type error. Prisma gives you type safety without the burden of manually defining types based on your database schema using TypeScript and code generation.
  • Code generation: You should only need to write things once. Prisma saves you time by auto-generating two artifacts that you would otherwise have to write by hand:
    • Fully typed TypeScript database client
    • SQL migrations based on changes in the Prisma schema.

Today's release brings Prisma support for Microsoft SQL Server and Azure SQL to General Availability. We couldn't be more excited to welcome new developers to the Prisma community so that you can reap the benefits of productivity and confidence.

Getting started

Prisma can be adopted in new projects using SQL Server or Azure SQL with Prisma Migrate and existing projects using introspection.

Adding Prisma to an existing SQL Server/Azure SQL project

To add Prisma to an existing project, you will use Prisma's introspection workflow, where you begin by introspecting (prisma db pull) an existing SQL Server database that populates the Prisma schema with models mirroring the tables of your database schema. Then you can generate Prisma Client (prisma generate) and interact with your database in a type-safe manner with Node.js or TypeScript.


Add Prisma to an existing SQL Server project

Using Prisma in a new SQL Server/Azure SQL project

To start a new project with Prisma and SQL Server, you define your data model with the Prisma schema and then use Prisma Migrate to create the database tables with Prisma Migrate. Then you can generate Prisma Client (prisma generate) and interact with your database in a type-safe manner with Node.js or TypeScript.


Start a new Prisma project with SQL Server

You can also dig into our ready-to-run example in the prisma-examples repo which includes the Prisma schema, an initial migration to create a database, and instructions on how to query the database with Prisma Client.

Finally, check out our Azure Functions deployment guide which covers how to deploy a Prisma based Node.js REST API to Azure Functions together with Azure SQL as the database.

What does this release means for you?

If you are using Microsoft SQL Server or Azure SQL today, you can try out Prisma with your existing database in 15 minutes by following this guide.

If you've been using the SQL Server and Azure SQL connector while it was in Preview in versions prior to 3.0.1, check out the Breaking Changes section in the release notes for more details about upgrading.

You can also remove the microsoftSqlServer flag from previewFeatures in your Prisma Schema.

It should be noted that Prisma can be deployed to any cloud runtimes that supports Node.js, e.g. Azure Functions, Vercel, and many more. This means that Prisma can be used to modernize older applications where the maintenance burden is high and confidence in the codebase is low.

To get a better feel for what it's like to modernize an existing application using SQL Server, check out this interview where Luís Rudge, a Software Developer who shares his experience modernizing a 10-old project written in .NET MVC and Microsoft SQL Server using Prisma:

Open-source and beyond

With SQL Server and Azure SQL support in Prisma reaching General Availability, it is ready for adoption in production. But it doesn't stop here – beyond the open-source Prisma ORM, our long-term vision for Prisma is much broader.

During our recent Prisma Day conference, we started sharing this vision, which we call the Prisma Data Platform.

Prisma's vision is to democratize the custom data access layer used by companies like Facebook, Twitter, and Airbnb and make it available to development teams and organizations of all sizes.

This idea is largely inspired by companies like Facebook, Twitter, and Airbnb that built custom data access layers on top of their databases and other data sources to make it easier for the application developers to access the data they need in a safe and efficient manner.

You can already try out the Prisma Data Platform today; however, note that connecting to Azure SQL databases typically requires updating firewall rules to allow traffic from public IP addresses. Soon, you will be able to get the static IP of the Prisma Data Platform to be added to the firewall's allow list.

Thank you to our community

We've been overwhelmed by the positive response since the Microsoft SQL Server connector Preview release last year, and we'd like to thank everyone who tested and provided insightful feedback – today's release is the product of those efforts 🙌

👷‍♀️ We can't wait to see what you all build with Prisma's SQL Server and Azure SQL connector

🌍 We also invite you to join our Slack, where you can discuss all things Prisma and get help from the community.


SQL Server connector Docs

Learn more about how to configure the Prisma SQL Server connector

Don’t miss the next post!

Sign up for the Prisma Newsletter