A Playwright test project with built-in SQL Server database connectivity using the mssql and msnodesqlv8 npm packages with Windows Authentication. Includes a simple Express web application for end-to-end testing.
Playwright_SQLserver/
├── src/
│ ├── app.ts # Express web app (comment form on port 3000)
│ └── db.ts # Database utility (connection pool, query, execute)
├── tests/
│ ├── comment-save.spec.ts # Test: submit comment via UI, verify in DB
│ └── example-db.spec.ts # Example tests demonstrating DB + UI integration
├── .env # Environment variables (not committed to git)
├── .env.example # Template for environment variables
├── .gitignore
├── package.json
├── playwright.config.ts # Playwright configuration (auto-starts web server)
├── tsconfig.json # TypeScript configuration
└── README.md
- Node.js 18+ installed
- SQL Server instance accessible (local named instance or default)
- ODBC Driver 17 or 18 for SQL Server installed
- Windows Authentication enabled on the SQL Server instance
npm installnpx playwright installEdit the .env file with your SQL Server details:
DB_SERVER=localhost\MSSQLSERVER01
DB_DATABASE=CustomerDemo
DB_ODBC_DRIVER=ODBC Driver 18 for SQL Server
DB_ENCRYPT=true
DB_TRUST_SERVER_CERTIFICATE=true
BASE_URL=http://localhost:3000The web application stores comments in a Comments table. Run this SQL command to create it (using sqlcmd or SQL Server Management Studio):
USE CustomerDemo;
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Comments')
CREATE TABLE Comments (
id INT IDENTITY(1,1) PRIMARY KEY,
comment NVARCHAR(MAX) NOT NULL,
created_at DATETIME2 DEFAULT GETDATE()
);Or via the command line:
sqlcmd -S localhost\MSSQLSERVER01 -E -d CustomerDemo -C -Q "IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Comments') CREATE TABLE Comments (id INT IDENTITY(1,1) PRIMARY KEY, comment NVARCHAR(MAX) NOT NULL, created_at DATETIME2 DEFAULT GETDATE());"This project uses:
msnodesqlv8— Native ODBC driver for Node.js, supporting Windows Authentication and named pipes/shared memory (no TCP/IP required)mssql/msnodesqlv8— Themssqlpackage's msnodesqlv8 interface for connection pooling and parameterized queries- Windows Authentication (
Trusted_Connection=Yes) — no username/password needed
The Express web app (src/app.ts) provides a simple comment form:
- GET
/— Displays an HTML form with a textarea and a "Save" button - POST
/save— Accepts the comment and inserts it into theCommentstable in SQL Server
npm startThis starts the Express server at http://localhost:3000. Open it in a browser to use the comment form.
The playwright.config.ts is configured with a webServer block that automatically starts the Express app before running tests. You do not need to start it manually when running tests — Playwright handles it.
If the server is already running (e.g. you started it with npm start), Playwright will reuse it instead of starting a new one.
# Run all tests (auto-starts web server)
npm test
# Run only the comment save test
npx playwright test tests/comment-save.spec.ts --project=chromium
# Run only the database integration tests
npx playwright test tests/example-db.spec.ts --project=chromium
# Run tests with browser visible
npm run test:headed
# Run tests in Playwright UI mode
npm run test:ui
# Run tests in debug mode
npm run test:debug
# Run only Chromium tests
npm run test:chromium
# View the HTML test report
npm run reportEnd-to-end test that:
- Generates a unique random comment string (UUID-based)
- Navigates to
http://localhost:3000 - Enters the comment into the textarea
- Clicks the "Save" button
- Verifies the success message appears on the page
- Queries the
Commentstable directly to verify the string was stored in the database - Cleans up by deleting the test comment
Database connectivity tests that:
- Verify SQL Server connection with
SELECT 1 - Retrieve and check the server version
- Test parameterized query support
- Demonstrate combined DB + UI test pattern
The database helper is located in src/db.ts and provides these functions:
Execute a SELECT query with parameterized inputs. Returns T[].
import { query } from "../src/db";
const users = await query<{ id: number; name: string }>(
"SELECT id, name FROM Users WHERE active = @param0",
[1],
);Execute an INSERT/UPDATE/DELETE command. Returns the number of rows affected.
import { execute } from "../src/db";
const rowsAffected = await execute(
"UPDATE Users SET active = @param0 WHERE id = @param1",
[0, 42],
);Get or create the connection pool directly for advanced scenarios.
Close the connection pool. Call this in test.afterAll() to clean up.
| Script | Description |
|---|---|
npm start |
Start the Express web application on port 3000 |
npm test |
Run all Playwright tests (auto-starts web server) |
npm run test:headed |
Run tests with visible browser |
npm run test:ui |
Run tests in Playwright UI mode |
npm run test:debug |
Run tests in debug mode |
npm run test:chromium |
Run tests in Chromium only |
npm run report |
Open the HTML test report |
- Never hard-code credentials — use
.envandprocess.env - Use parameterized queries — all
query()andexecute()calls use@param0,@param1, etc. to prevent SQL injection - Close the pool — always call
closePool()intest.afterAll() - Keep DB operations separate from browser actions
- Use a test/staging database — never run tests against production
- Seed/reset data between tests for isolation when needed
- Connection pooling is built in — safe for parallel test workers
- The
.envfile is excluded from git via.gitignore - For CI/CD, inject secrets via your platform (GitHub Secrets, Azure Key Vault, etc.)
- Consider Azure AD / Managed Identity authentication for Azure SQL
- Set
DB_TRUST_SERVER_CERTIFICATE=falsein production with a valid certificate
| Technology | Purpose | Version |
|---|---|---|
| Playwright | Browser automation and end-to-end testing framework | ^1.59 |
| TypeScript | Statically typed superset of JavaScript | ^6.0 |
| Express | Minimal Node.js web framework for the comment app | ^5.2 |
| mssql | SQL Server client for Node.js — provides connection pooling, parameterized queries, and a unified API | ^12.2 |
| msnodesqlv8 | Native ODBC driver for Node.js — enables Windows Authentication and shared memory/named pipe connections | ^5.1 |
| dotenv | Loads environment variables from .env files |
^17.4 |
| ts-node | TypeScript execution engine for Node.js — runs .ts files directly without a build step |
^10.9 |
| ODBC Driver 18 for SQL Server | Microsoft's official ODBC driver used by msnodesqlv8 under the hood | 18.x |
| Alternative | Pros | Cons |
|---|---|---|
tedious (used by mssql by default) |
Pure JavaScript — no native compilation needed; works cross-platform; supports TCP/IP connections | Requires TCP/IP to be enabled on SQL Server; does not support Windows Authentication natively; slower than native ODBC |
msnodesqlv8 (used in this project) |
Native ODBC performance; supports Windows Authentication; works via shared memory and named pipes (no TCP/IP needed) | Windows-only; requires native compilation (node-gyp); requires ODBC Driver installed on the machine |
knex.js (query builder) |
Fluent query builder API; supports migrations; works with multiple databases (SQL Server, PostgreSQL, MySQL) | Additional abstraction layer; heavier dependency; overkill for simple queries in test code |
TypeORM / Prisma (ORMs) |
Full ORM with models, relations, migrations; type-safe queries; great for application development | Very heavy for test utilities; complex setup; significant learning curve; unnecessary abstraction for test DB access |
pyodbc (Python) |
Official Microsoft-recommended Python driver; excellent ODBC support | Requires Python Playwright bindings instead of Node.js; different ecosystem |
| Alternative | Pros | Cons |
|---|---|---|
| Playwright (used in this project) | Multi-browser support (Chromium, Firefox, WebKit); auto-wait; built-in web server management; excellent TypeScript support; parallel execution | Heavier than unit test frameworks; requires browser binaries |
| Cypress | Excellent developer experience; real-time browser preview; time-travel debugging | Single browser tab only (no multi-tab); Chromium-family only (limited Firefox); no native SQL Server integration |
| Selenium WebDriver | Widest browser support; language-agnostic; large community | Slower; more boilerplate; no built-in waiting; no web server management; flakier tests |
| Puppeteer | Lightweight; Google-maintained; fast for Chromium | Chromium only; no built-in test runner; no multi-browser; less features than Playwright |
| Alternative | Pros | Cons |
|---|---|---|
| Express (used in this project) | Minimal; widely adopted; simple for small apps; huge middleware ecosystem | Callback-based (older patterns); less opinionated; manual error handling |
| Fastify | Very fast; schema-based validation; built-in TypeScript support; modern plugin system | Smaller ecosystem than Express; less community resources for beginners |
| Koa | Lightweight; modern async/await design; created by Express authors | Smaller middleware ecosystem; less documentation; fewer examples |
| Next.js / Nuxt | Full-stack framework; SSR; API routes built in | Much heavier; overkill for a simple form app; requires React/Vue |
File: src/db.ts — The getDbPool() function implements a singleton connection pool pattern. A single pool is created on first use and reused across all queries. This is critical for performance, especially with Playwright's parallel test workers.
let pool: sql.ConnectionPool | null = null;
export async function getDbPool(): Promise<sql.ConnectionPool> {
if (!pool) {
pool = await new sql.ConnectionPool(config).connect();
}
return pool;
}Why it matters: Without pooling, each query would open and close a connection, adding ~100-500ms overhead per query. Pooling maintains a set of reusable connections.
File: src/db.ts — All queries use request.input() to bind parameters safely, never concatenating user input into SQL strings.
params.forEach((param, index) => {
request.input(`param${index}`, param);
});Why it matters: This prevents SQL injection attacks. The database driver handles escaping and type conversion, ensuring user-supplied values are never interpreted as SQL code.
File: src/db.ts — Uses an explicit ODBC connection string with Trusted_Connection=Yes for Windows Authentication, eliminating the need to store username/password credentials.
connectionString: `Driver={ODBC Driver 18 for SQL Server};Server=${server};Database=${database};Trusted_Connection=Yes;TrustServerCertificate=Yes;`;Why it matters: Windows Authentication uses the current user's Windows credentials via Kerberos/NTLM, which is more secure than SQL authentication (no passwords in config files). It also works via shared memory/named pipes when TCP/IP is disabled.
File: playwright.config.ts — The webServer configuration block tells Playwright to automatically start the Express app before tests run and wait until it's ready.
webServer: {
command: "npx ts-node src/app.ts",
url: "http://localhost:3000",
reuseExistingServer: !process.env.CI,
timeout: 30000,
},Why it matters: Eliminates the need to manually start the server before running tests. The reuseExistingServer option means Playwright will use an already-running server in development but always start a fresh one in CI.
Files: .env, src/db.ts, playwright.config.ts — All configuration (server, database, URLs) is loaded from environment variables using dotenv, never hard-coded.
Why it matters: Enables different configurations for development, CI/CD, and production without code changes. Secrets stay out of source control (.env is in .gitignore).
File: tests/comment-save.spec.ts — Each test generates a unique UUID-based comment, verifies it in the database, then deletes it.
const randomComment = `Test comment ${crypto.randomUUID()}`;
// ... test runs ...
await execute("DELETE FROM Comments WHERE comment = @param0", [randomComment]);Why it matters: Tests don't leave behind data that could affect other tests. UUID-based values ensure no collisions between parallel test runs. This pattern keeps the test database clean and tests repeatable.
Files: src/db.ts (server-side queries) vs tests/*.spec.ts (browser automation) — Database operations happen in the Node.js test runner process, completely separate from browser automation.
Why it matters: The database connection runs on the server side (test runner), not inside the browser. This is the correct architecture — browsers cannot and should not directly access databases. The test runner orchestrates both the browser actions and database verification independently