Using database drivers in Node.js and PHP to execute queries safely
From SQL to Application Code
A database sitting on a server is useless until your application can talk to it. Both Node.js and PHP use database client libraries that manage connections and send SQL queries.
The general pattern is the same in every language:
Establish a connection to the database server (host, port, credentials)
Send SQL queries through the connection
Receive results as rows/objects in your language
Close the connection when done (or return it to a pool)
Node.js: The pg Library
The pg (node-postgres) library is the standard PostgreSQL client for Node.js. It uses a connection pool to efficiently reuse database connections across requests.
const { Pool } = require('pg');
// Create a connection pool
const pool = new Pool({
host: process.env.DB_HOST || 'localhost',
port: process.env.DB_PORT || 5432,
database: process.env.DB_NAME || 'cse135',
user: process.env.DB_USER || 'postgres',
password: process.env.DB_PASSWORD
});
// GET /api/stories - list all stories
app.get('/api/stories', async (req, res) => {
try {
const result = await pool.query('SELECT * FROM stories ORDER BY created_at DESC');
res.json(result.rows);
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Database error' });
}
});
// GET /api/stories/:id - get one story
app.get('/api/stories/:id', async (req, res) => {
try {
const result = await pool.query('SELECT * FROM stories WHERE id = $1', [req.params.id]);
if (result.rows.length === 0) {
return res.status(404).json({ error: 'Story not found' });
}
res.json(result.rows[0]);
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Database error' });
}
});
// POST /api/stories - create a story
app.post('/api/stories', async (req, res) => {
const { title, description, priority } = req.body;
try {
const result = await pool.query(
'INSERT INTO stories (title, description, priority) VALUES ($1, $2, $3) RETURNING *',
[title, description, priority]
);
res.status(201).json(result.rows[0]);
} catch (err) {
console.error(err);
res.status(500).json({ error: 'Database error' });
}
});
Key points about the Node.js pg library:
The Pool manages a set of reusable connections — you don't open/close one per request
Parameterized queries use $1, $2, $3 as positional placeholders
Values are passed as an array in the second argument to pool.query()
Results come back in result.rows as an array of JavaScript objects
All database calls are async — use async/await with try/catch
PHP: PDO (PHP Data Objects)
PDO is PHP's built-in database abstraction layer. It works with PostgreSQL, MySQL, SQLite, and many other databases through a consistent interface.
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
// GET /api/stories - list all stories
function getAllStories($pdo) {
$stmt = $pdo->query('SELECT * FROM stories ORDER BY created_at DESC');
return $stmt->fetchAll();
}
// GET /api/stories/:id - get one story
function getStory($pdo, $id) {
$stmt = $pdo->prepare('SELECT * FROM stories WHERE id = :id');
$stmt->execute(['id' => $id]);
return $stmt->fetch(); // returns false if not found
}
// POST /api/stories - create a story
function createStory($pdo, $data) {
$stmt = $pdo->prepare(
'INSERT INTO stories (title, description, priority)
VALUES (:title, :description, :priority) RETURNING *'
);
$stmt->execute([
'title' => $data['title'],
'description' => $data['description'] ?? null,
'priority' => $data['priority'] ?? 'medium'
]);
return $stmt->fetch();
}
?>
Key points about PHP PDO:
PDO is built into PHP — no external package to install (just enable the pdo_pgsql extension)
Prepared statements use named placeholders like :id, :title or positional ? placeholders
Values are passed as an associative array to $stmt->execute()
Setting PDO::ERRMODE_EXCEPTION makes PDO throw exceptions on errors instead of failing silently
Setting PDO::FETCH_ASSOC returns rows as associative arrays by default
Side-by-Side Comparison
Concept
Node.js (pg)
PHP (PDO)
Install
npm install pg
Built-in (enable pdo_pgsql extension)
Connection
new Pool({host, database, ...})
new PDO($dsn, $user, $password)
Simple query
pool.query('SELECT ...')
$pdo->query('SELECT ...')
Parameterized
pool.query('... $1 ...', [val])
$stmt->execute(['id' => $val])
Placeholder style
Positional: $1, $2, $3
Named: :id, :title or positional: ?
Get rows
result.rows
$stmt->fetchAll()
Connection pooling
Built into Pool
Via persistent connections or external pooler
Error handling
try/catch with async/await
try/catch with PDO::ERRMODE_EXCEPTION
Securing Database Credentials
Database credentials (host, username, password) should never be hardcoded in your source code. Instead, use environment variables:
Node.js: Access via process.env.DB_PASSWORD. Use a .env file with the dotenv package during development.
PHP: Access via getenv('DB_PASSWORD'). Set environment variables in your web server configuration or a .env file.