How do i connect Azure SQL database to my Next TS codebase?

Question

So I have been making a form in Next TS, that when filled by the user will directly save the data into my azure sql database. Im using prisma ORM along with it to connect it better:

//this is my API route to connect my code to my database

import { PrismaClient } from "@prisma/client";
import { NextApiRequest, NextApiResponse } from "next";
import { z } from "zod";

const prisma = new PrismaClient();

const bigIntSchema = z.union([
  z.string().regex(/^\d+$/).transform(val => BigInt(val)),
  z.number().int().transform(val => BigInt(val))
]);

const ownerSchema = z.object({
  fullName: z.string().min(1, "Full name is required"),
  phoneNo: z.string().regex(/^[\d\s-+()]*$/, "Invalid phone number format"),
  gymName: z.string().min(1, "Gym name is required"),
  gymAddress: z.string().min(1, "Gym address is required"),
  images: z.string(),
  subscriptionPackages: z.string(),
  gymContactInfo: z.string(),
  workoutsOffered: z.string().optional(),
  equipmentsAvailable: z.string().optional(),
  ledger_start_transaction_id: bigIntSchema,
  ledger_start_sequence_number: bigIntSchema
});

export async function POST(req: NextApiRequest, res: NextApiResponse) {
    const parsedBody = ownerSchema.safeParse(req.body);
  
    if (!parsedBody.success) {
      return res.status(400).json({ message: "Invalid input", errors: parsedBody.error.flatten() });
    }
  
    try {
      const newGymOwner = await prisma.gymOwners.create({
        data: parsedBody.data
      });
  
      return res.status(201).json({ message: 'Gym owner created successfully', data: newGymOwner });
    } catch (error) {
      console.error('Error creating gym owner:', error);
      return res.status(500).json({ message: "Could not create gym owner", error: error instanceof Error ? error.message : 'Unknown error' });
    } finally {
      await prisma.$disconnect();
    }
}

And this is my code for the form in Next TS:

import AnotherButton from "@/components/buttons/AnotherButton"
import PrimaryButton from "@/components/buttons/PrimaryButton"
import axios from "axios"
import { log } from "console"
import { useState } from "react"


export default function GymOwners() {
    const style = {
        "border": "1px solid black"
    }

    const [formData, setFormData] = useState({
      fullName: '',
      phoneNumber: '',
      gymName: '',
      gymAddress: '',
      images: '',
      subscriptionPackages: '',
      gymContactInfo: '',
      workoutsOffered: '',
      equipmentsAvailable: '',
    })

    const handleSubmit = async (e: React.FormEvent) => {
        e.preventDefault();

        try {
          const res = await axios.post("http://localhost3000/api/v1/gymowners", formData);
          console.log(res.status)
        } catch(e: any) {
          console.log("Error:", e);
        }

    }

    const handleChange =  (e:  React.ChangeEvent

Answer 1

Make sure Prisma is correctly configured to connect to your Azure SQL database. For that, you need to set the connection string in the .env file.

DATABASE_URL="sqlserver://<USERNAME>:<PASSWORD>@<SERVER_NAME>.database.windows.net:1433;database=<DB_NAME>;encrypt=true;trustServerCertificate=false"

Replace:

• <USERNAME>: Your Azure SQL database username.
• <PASSWORD>: Your password.
• <SERVER_NAME>: The name of your Azure SQL server.
• <DB_NAME>: The database name.

Ensure Prisma is installed:

npm install @prisma/client

You'll also need the Prisma CLI for schema management and migrations:

npm install prisma --save-dev

In your prisma/schema.prisma file, make sure your model for the gymOwners table is correctly set up according to your database schema.

Example schema.prisma:

datasource db {

  provider = "sqlserver"

  url      = env("DATABASE_URL")

}

generator client {

  provider = "prisma-client-js"

}

model GymOwner {

  id                         Int      @id @default(autoincrement())

  fullName                   String

  phoneNo                    String

  gymName                    String

  gymAddress                 String

  images                     String

  subscriptionPackages       String

  gymContactInfo             String

  workoutsOffered            String?

  equipmentsAvailable        String?

  ledger_start_transaction_id BigInt

  ledger_start_sequence_number BigInt

}

Generate the Prisma client and apply migrations to your database:

npx prisma migrate dev --name init

This will generate the required SQL and apply it to your Azure SQL database.

Your API route seems mostly correct, but make sure the endpoint URL used in your frontend is correct. Ensure the API URL is valid (you had a small typo):

const res = await axios.post("/api/v1/gymowners", formData);

Make sure you're using relative URLs ("/api/v1/gymowners") unless you need to specify an absolute path.

Your handleSubmit function should successfully submit form data. Ensure that the field names match exactly what you’re expecting on the server.