Skip to main content

​
Overview

The Portal Self-Service Backend uses Microsoft SQL Server (MSSQL) as its database, with connections managed through the mssql package. The application establishes a connection pool for efficient database operations.

​
Database Requirements

MSSQL Server

Microsoft SQL Server instance (2016 or later recommended)

Network Access

Server must be accessible from your application host

Authentication

Valid database credentials (user/password)

Database Created

Target database must exist before connecting

​
Required Environment Variables

The following environment variables must be configured for database connectivity:
VariableDescriptionExample
DB_USERDatabase usernamesa or app_user
DB_PASSWORDDatabase passwordYourSecurePassword123!
DB_SERVERDatabase server hostname or IPlocalhost or 192.168.1.100
DB_DATABASEDatabase namePortalSelfService
DB_PORTSQL Server port1433 (default)

​
Connection Configuration

1

Review Database Configuration

The database configuration is defined in src/config/db.config.js. Here’s the connection setup:
src/config/db.config.js
import sql from 'mssql';

const config = {
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    server: process.env.DB_SERVER, 
    database: process.env.DB_DATABASE,
    port: parseInt(process.env.DB_PORT, 10),
    options: {
        trustServerCertificate: true, 
        enableArithAbort: true
    },
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    }
};
2

Connection Pool Settings

The application uses a connection pool with the following configuration:
  • Maximum connections: 10 concurrent connections
  • Minimum connections: 0 (connections created on-demand)
  • Idle timeout: 30 seconds before releasing idle connections
3

SSL/TLS Configuration

The connection uses trustServerCertificate: true to allow self-signed certificates.
In production environments, configure proper SSL certificates and set trustServerCertificate: false for enhanced security.

​
Connection Pool Initialization

The application establishes the database connection pool at startup:
src/config/db.config.js
const poolPromise = new sql.ConnectionPool(config)
    .connect()
    .then(pool => {
        console.log('Conexión a SQL Server establecida.');
        return pool;
    })
    .catch(err => {
        console.error('Falló la conexión a SQL Server:', err.message);
        throw err; 
    });

export { sql, poolPromise, dbConfig };
The connection is established asynchronously and verified during server startup in server.js.

​
Server Startup Verification

The application waits for the database connection before starting the HTTP server:
server.js
async function startServer() {
    try {
        await poolPromise; // Wait for database connection
        
        server.listen(PORT, () => {
            console.log(`Servidor escuchando en http://localhost:${PORT}`);
        });
    } catch (error) {
        console.error('Error fatal: La aplicación no pudo iniciar.', error.message);
        process.exit(1); 
    }
}

​
Troubleshooting

​
Connection Refused

Check Network Connectivity

Ensure the database server is reachable:
# Test connectivity to SQL Server
telnet your-db-server 1433

​
Authentication Failed

Verify Credentials

  • Double-check DB_USER and DB_PASSWORD
  • Ensure the user has access to the specified database
  • Check for special characters in password (may need escaping)

SQL Server Authentication

  • Verify SQL Server authentication mode is enabled (not Windows-only)
  • Confirm the user account is not locked or disabled

​
Connection Timeout

1

Check Firewall

Ensure port 1433 (or your custom port) is open on the database server firewall.
2

Verify SQL Server Configuration

Ensure TCP/IP protocol is enabled in SQL Server Configuration Manager.
3

Check Connection String

Verify DB_SERVER and DB_PORT are correct in your .env file.

​
Database Does Not Exist

The database specified in DB_DATABASE must be created manually before running the application. The application does not auto-create databases.
Create the database using SQL Server Management Studio (SSMS) or via T-SQL: 
CREATE DATABASE PortalSelfService;
GO

​
Common Issues

IssuePossible CauseSolution
ConnectionError: Failed to connectServer unreachableVerify DB_SERVER and network connectivity
Login failed for userInvalid credentialsCheck DB_USER and DB_PASSWORD
Cannot open databaseDatabase doesn’t existCreate database or verify DB_DATABASE name
Connection timeoutFirewall blockingEnable port 1433 in firewall rules
Self signed certificateSSL validationAlready handled with trustServerCertificate: true

​
Testing the Connection

To verify your database configuration:
1

Set Environment Variables

Configure all required variables in your .env file.
2

Start the Application

npm start
3

Check Console Output

Look for the success message:
Conexión a SQL Server establecida.
Servidor escuchando en http://localhost:3000
4

Test Health Endpoint

curl http://localhost:3000/health
Expected response:
{
  "status": "UP",
  "message": "Express Server Running"
}

​
Next Steps

Environment Configuration

Configure all environment variables including Azure AD

Deployment

Deploy your application to production

Build docs developers (and LLMs) love

Get started for freeTalk to us