How to connect to Azure SQL database using the `mssql` package in Azure Static Web App with Svelte?

I have deployed an Azure Static Web App (SWA) using the Svelte framework. The SWA is connected to an Azure SQL database and uses the GraphQL endpoint to query the database. I want to replace the GraphQL queries with SQL queries because the current query is very slow. The first query takes around 10-11 seconds on a ~21000-row table. Subsequent queries still execute around 5-6 seconds (connection is probably cached).

So I decided to use the mssql package to speed up the querying:

  const sql = require('mssql');

  const config = {
    server: '<server_name>.database.windows.net',
    database: '<database_name>',
    user: process.env.USER,
    password: process.env.PASSWORD,
    options: {
      encrypt: true
    }
  };

  onMount(async () => {
    const result = await sql
      .connect(config)
      .then(() => sql.query('SELECT * FROM <my_table>'))
      .then((result) => result.recordset)
      .catch((error) => console.warn('Error querying database: ', error));
    console.log(result);
  });

Unfortunately, I got an error during the import phase.

Uncaught (in promise) ReferenceError: require is not defined

This makes sense because Svelte uses the type module instead of commonjs under the package.json file:

{
  ...
  "type": "module",
  "dependencies": {
    ...
  }
}

Therefore, I replaced the const sql = require('mssql'); with the import sql from 'mssql'; import statement. But I got a warning during the build:

(!) Some chunks are larger than 500 kB after modification.

If I ignore this warning, the following error occurs:

ReferenceError: Buffer is not defined

I have found the Skipping larger chunks while running "Npm run build" answer which fixes the warning by updating the vite.config.js file. However, I got another "500 Internal server error" during the import with this error message:

TypeError: Class extends value undefined is not a constructor or null

Is there a simple way to use the mssql package without a lot of workaround during the import statement?

I have found a simple alternative to connect the database using the mssql package. The trick was to Add an API to Azure Static Web Apps with Azure Functions that imports/uses the mssql package and then exposing it via REST API.

The easiest way to generate the APIs is to install two VSCode extensions:

• Azure Static Web Apps extension
• Azure Functions extension

These are the steps according to the current documentation:

• Create an api folder at the project root
• Press F1 to open the Command Palette
• Select Azure Static Web Apps: Create HTTP Function....
• At Select a language enter JavaScript
• At Select a programming model enter Model V4
• At Provide a function name enter databaseEndpoint

It will fill the api folder with contents. Install the mssql package with:

cd api && npm install mssql

Now, we can use the package in the src/functions/databaseEndpoint.js file:

const { app } = require('@azure/functions');
const sql = require('mssql');

const config = {
  server: '<server_name>.database.windows.net',
  database: '<database_name>',
  // The environment variables can be stored under the `api/local.settings.json` file
  user: process.env.USER,
  password: process.env.PASSWORD,
  options: {
    encrypt: true
  }
};

app.http('databaseEndpoint', {
  methods: ['GET', 'POST'],
  authLevel: 'anonymous',
  handler: async (request, context) => {
    return await sql
      .connect(config)
      .then(() => sql.query('SELECT * FROM <my_table>'))
      .then((result) => ({ jsonBody: result.recordset }))
      .catch((error) => ({ jsonBody: `Error querying database: ${error}` }));
  }
});

Finally, we can use the created API in the Svelte (+page.svelte) application:

<script>
  import { onMount } from 'svelte';

  onMount(async () => {
    const result = await fetch('api/databaseEndpoint')
      .then((response) => response.json())
      .catch((error) => console.warn('Error at fetching databaseEndpoint endpoint: ', error));
    console.log(result);
  });
</script>

Build and emulate the SWA using the following commands:

npm run build
swa start build --api-location api

I gained significant performance by replacing the GraphQL endpoint. It only takes about 1 second (5x speed-up) to fetch from the Azure Function endpoint.

Deployment

Do not forget to add the api_location during the deployment in the azure-pipelines.yaml file:

      - task: AzureStaticWebApp@0
        inputs:
          azure_static_web_apps_api_token: $(AZURE_STATIC_WEB_APPS_API_TOKEN_GRAY_WAVE_014164E03)
          app_location: '/'
          api_location: 'api'
          output_location: 'build'