SQL Server to React in 60 Seconds With Prisma

Previously published at fullstackfish.com

Connecting SQL Server to React with Prisma is a straightforward and satisfying experience. This post will guide you through the process of getting your Azure SQL Server data onto a screen in a React app in less than a minute. Really. (As long as you pause your timer while npm is downloading files.)

Of course, there are a few prerequisites before you can get it connected that quickly. Prisma supports working with existing databases (which we are doing in this post) as well as defining the database in your app and then creating the database objects through migrations (which will be covered in other posts).

1. You must have an existing Azure SQL Server with a database that you have credentials for. You must also have your ip whitelisted in Azure.
2. The database must have at least one table with data that we can query.
3. You must have up-to-date versions of Node.js (12.6 or higher) and Visual Studio Code installed. See nvm is for barbarians. Manage Node versions with Volta instead for a great way to manage Node versions.
4. You must have create-next-app installed globally. (npm global install create-next-app if you don't)
5. You must have an empty folder on your hard-drive for this project.

And that's it. Let's do this.

1. Open your empty project folder in VS Code
2. Open a terminal and run the following command to initialize a new next app:

npx create-next-app .

The dot (.) will create the next app in the current path.

Once the app is initialized, you should see a confirmation. Run the app to verify it compiles and runs correctly.

npm run dev

Open http://localhost:3000 in a browser to verify.

Now stop the app and let's get some data.

You'll need to add the following dependencies, prisma as a dev dependency and @prisma/client as a regular dependency.

npm i -D prisma

npm i @prisma/client

Check your package.json file to verify that you have version 3.01 or greater of prisma in the dev dependencies.

Initialize prisma in your app. This will add a prisma folder in root with a schema.prisma file. It will also add a .env file if you don't already have one, which you shouldn't at this point. For future reference, if you do have a .env file, this step will add a new entry into the file without harming anything else you may have in there.

npx prisma init

The default connection that is configured in the schema.prisma file is for Postgres. Let's fix that.

Edit the file to look like this:

// schema.prisma
generator client {
  provider        = "prisma-client-js"
}

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

Note the constant DATABASE_URL that is referenced. We'll update the value of that constant in the .env file now.

Set the connection string in the .env file to point to your Azure SQL Server database. I'm using a username and password for this example. Explaining all the variations on connection strings is beyond the scope of this post, but I'm assuming you can figure that out.

My connection string, with the sensitive bits masked to protect the innocent:

DATABASE_URL="sqlserver://[SERVERNAME].database.windows.net:1433;database=[DATABASENAME];encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;user=[USERNAME];password=[PASSWORD]"

Connection strings can be long, so you'll have to scroll to the right to see the whole thing.

Now we're ready to introspect the database. This step will populate the Prisma schema based on your database schema. Make sure you have your schema.prisma file open before you run the following. It's fun to watch the magic happen.

npx prisma db pull

Boom! Our app and database are now friends.

Assuming your connection string is correct and you have access to your database, you should now see a list of all the tables in the dbo schema generated as models in your schema.prisma file. This functionality is currently limited to tables in the dbo schema. You won't have views or functions, or stored procs in the schema file, which is true for all databases Prisma works with, so don't get your SQL Server feelings hurt. You can still interact with those objects in your app.

Now that we have a valid schema, let's run the command to generate the Prisma client.

npx prisma generate

Next is a fabulous tool that keeps your backend and frontend code safely separate but extremely easy to work with in the same project, among many other benefits.

Add a lib folder in root with a prisma.js file, and copy/paste or type the following into that file:

// lib/prisma.js
import { PrismaClient } from "@prisma/client";

let prisma;

if (process.env.NODE_ENV === "production") {
  prisma = new PrismaClient();
} else {
  if (!global.prisma) {
    global.prisma = new PrismaClient();
  }
  prisma = global.prisma;
}

export default prisma;

This is the best practice defined by Prisma and Next for the integration. You'll be able to import a single instance of the prisma client throughout your app (something we'll do shortly), which will handle connection pools for you and protect you from memory leaks. See Best practice for instantiating PrismaClient with Next.js for more details.

Let's define an API route that our front-end will consume.

Add a file in pages/api called products.js. Replace "products" with whatever table you want to work with from your database. I'm using Adventure Works for this example.

Add this to the products.js:

// pages/api/products.js
import prisma from "../../lib/prisma";

export default async function handle(req, res) {
  const products = await prisma.product.findMany();
  res.json(products);
}

Now delete the contents of the pages/index.js file and replace it with the following as a guide. You'll need to use your React skills to create a component that incorporates your data.

You'll need to replace products with whatever table you want from your database, and use fields from that table instead of the ones I'm using below.

// pages/index.js
export const getServerSideProps = async () => {
  const res = await fetch("http://localhost:3000/api/products");
  const products = await res.json();
  return {
    props: { products },
  };
};

export default function Product({ products }) {
  return (
    <div>
      {products.map((p) => {
        return (
          <div key={p.ProductID}>
            <p>
              {p.Name} — ${p.ListPrice}
            </p>
          </div>
        );
      })}
    </div>
  );
}

That's it. Let's see if it worked.

npm run dev

Open http://localhost:3000 in a browser and behold your data!

Hit me up on twitter @jonrcrowell to let me know what you think or if you ran into any issues.

Prisma released the SQL Server connector on September 7, 2021. Read the official announcement here:
Microsoft SQL Server Support in Prisma is Production-Ready

Comments

[ImaginativeWork]

I thoroughly enjoyed this post! Will your follow-up on the topic of Stored Procedures be soon?