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).
create-next-app installed globally. (npm global install create-next-app if you don't)And that's it. Let's do this.
Note that I'm using JavaScript instead of TypeScript in this post. I'll have a TypeScript version up soon. I take no sides in that holy war. I love them both. Or maybe I hate them both. It really depends on the day.
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];user=[USERNAME];password=[PASSWORD];encrypt=true"
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. Stay tuned for a future post explaining how to do that.
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