How to access Azure SQL Database which is connected with Azure Static Web apps from Next.js?

Jihoo Byeon 5

Hello, I deployed a Next.js app to Azure Static Web apps, and connected to Azure SQL Server via Azure Portal.

and I tried to use it as explained at https://learn.microsoft.com/ko-kr/azure/static-web-apps/database-azure-sql?tabs=bash&pivots=static-web-apps-rest, but the app fails to fetch from /data-api/ path, both on client and server side.

It seems to collide with Next.js routing… pages’ seems to searching for /pages/data-api/, not /data-api/.

VenkateshDodda-MSFT 23,781 Reputation points Microsoft Employee

2024-05-30T09:19:54.0633333+00:00
@Jihoo Byeon Thank you for posting your question in Microsoft Q&A, apologize for any inconvenience caused on this.

When you request /data-api/ where you able to retrieve/connect to sql db which is connected to static webapps?

Also what is the error message in the console when you are trying to connect

Help us with staticwebapp.database.config.json file,build pipeline files or your GitHub sample excluding PI information to check and assist you better.

Jihoo Byeon 5

@VenkateshDodda-MSFT Okay, this is my staticwebapp.database.config.json:

{
	"$schema": "https://github.com/Azure/data-api-builder/releases/download/v0.9.7/dab.draft.schema.json",
	"data-source": {
		"database-type": "mssql",
		"connection-string": "@env('AZURE_CONNECTION_STRING')",
		"options": {
			"set-session-context": false
		}
	},
	"runtime": {
		"rest": {
			"enabled": true,
			"path": "/rest",
			"request-body-strict": true
		},
		"graphql": {
			"enabled": false,
			"path": "/graphql",
			"allow-introspection": true
		},
		"host": {
			"cors": {
				"origins": [],
				"allow-credentials": false
			},
			"authentication": {
				"provider": "StaticWebApps"
			},
			"mode": "production"
		}
	},
	"entities": {
		"index": {
			"source": "dbo.Idx",
			"permissions": [
				{
					"actions": ["*"],
					"role": "anonymous"
				}
			]
		},
		"thread": {
			"source": "dbo.Thread",
			"permissions": [
				{
					"actions": ["*"],
					"role": "anonymous"
				}
			]
		},
		"member": {
			"source": "dbo.Members",
			"permissions": [
				{
					"actions": ["*"],
					"role": "anonymous"
				}
			]
		}
	}
}

And I tried 3 ways: calling /data-api/ in client side, calling it in getServerSideProps(), and calling by Next.js /api route.

First, this is client side code:

import { useEffect, useState } from 'react';

export default function Page() {
	const [data, setData] = useState('');
	useEffect(() => {
		fetch('/data-api/rest/thread')
			.then(res => res.json()) // or res.text()
			.then(data => setData(data))
	}, []);
	return (
		<div>
			<p>{ data }</p>
		</div>
	);
}

this throws GET /data-api/rest/thread 404 and the data throws HTML includes 404: This page could not be found.

Next, I tried with getServerSideProps():

export async function getServerSideProps() {
	const res = await fetch('/data-api/rest/thread');
/* i also tried:
	@/data-api/rest/thread
	http://localhost:3000/data-api/rest/thread
	http://localhost:3000/@/data-api/rest/thread
 */
	const data = await res.json(); // or await res.text()
	return { props: { data: data }}
}
export default function Page({ data } : { data: string }) {
	return (
		<div>
			<p>{ data }</p>
		</div>
	);
}

and this throws: TypeError: Failed to parse URL from /data-api/rest/thread; code: 'ERR_INVALID_URL' when I don't include http://localhost:3000/ and if I include, result is same as client-side try.

Lastly, I tried /api route:

// /pages/api/test.ts
import type { NextApiRequest, NextApiResponse } from 'next';
export default async function handler(req: NextApiRequest, res: NextApiResponse) {
	const response = await fetch('/data-api/rest/thread');
	const data = await response.text();
	res.status(200).json({ data: data });
}

// /pages/test.tsx
import { useEffect, useState } from 'react';
export default function Page() {
	const [data, setData] = useState('');
	useEffect(() => {
		fetch('/api/test')
			.then(res => res.json()) // or res.text()
			.then(data => setData(data))
	}, []);
	return (
		<div>
			<p>{ data }</p>
		</div>
	);
}

this throws code: 'ERR_INVALID_URL', and GET /api/test 500. data is HTML includes "stack":"TypeError: Failed to parse URL from /data-api/rest/thread".

VenkateshDodda-MSFT 23,781 Reputation points Microsoft Employee

2024-05-31T05:57:47.5+00:00

@Jihoo Byeon Thanks for sharing the requested information will check and update you accordingly.
VenkateshDodda-MSFT 23,781 Reputation points Microsoft Employee

2024-05-31T12:00:42.66+00:00

@Jihoo Byeon This would need one on one offline support, for deeper investigations and might need to engage product team as well and I will suggest you to open a support ticket.

In case if you are facing any issue while raising the support ticket, I have reached out to your over private message please check and help us with the requested information to assist you further on this.

1 answer

Pinaki Ghatak 5,570 Reputation points Microsoft Employee

2024-07-08T10:51:23.3+00:00
Hello @Jihoo Byeon

To access Azure SQL Database connected with Azure Static Web Apps from Next.js, you can create an API route in Next.js that fetches data from the database and returns it to the client.

Based on the error you mentioned, it seems like there might be a routing issue with your Next.js app. By default, Next.js treats any file in the pages directory as a page.

To avoid this, you can create a pages/api directory and put your API routes there. This will ensure that Next.js treats them as API routes and not pages. Here's an example of how you can create an API route in Next.js to fetch data from Azure SQL Database:

Create a new file in the pages/api directory called data-api.js.

In data-api.js, import the mssql package and create a connection to your Azure SQL Database. Here's an example:

import sql from 'mssql'; const config = { server: '.database.windows.net', database: '', user: '', password: '', options: { encrypt: true, enableArithAbort: true } }; export default async function handler(req, res) { try { // Create a new connection pool const pool = await sql.connect(config); // Query the database const result = await pool.request().query('SELECT * FROM '); // Return the result as JSON res.json(result.recordset); } catch (err) { console.error(err); res.status(500).send('Error connecting to database'); } }

In your Next.js app, you can fetch data from this API route using the fetch API.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to access Azure SQL Database which is connected with Azure Static Web apps from Next.js?

1 answer

Your answer