Introduction
After successfully connecting to a database, the next step is executing queries. Database queries allow applications to retrieve, insert, update, and delete data stored in a database.
In relational databases like MySQL, queries are written using SQL (Structured Query Language). Common SQL statements include SELECT, INSERT, UPDATE, and DELETE.
In NoSQL databases like MongoDB, similar operations are performed using JavaScript methods such as find(), insertOne(), updateOne(), and deleteOne().
For automation engineers, executing database queries is an important skill. It enables them to verify backend data after API calls, validate application behavior, prepare test data before execution, and clean up records after automated tests.
In this tutorial, you’ll learn how to execute common database queries using JavaScript.
What are Database Queries?
A database query is a command used to communicate with a database.
Queries allow applications to:
Retrieve data.
Insert records.
Update existing records.
Delete records.
Verify stored information.
Common SQL Queries
| Query | Purpose |
|---|---|
| SELECT | Retrieve data |
| INSERT | Add new records |
| UPDATE | Modify existing records |
| DELETE | Remove records |
Example 1: Execute a SELECT Query
const [rows] =
await connection.execute(
"SELECT * FROM users"
);
console.log(
rows
);
Sample Output
[
{
id: 1,
name: "John"
}
]
Example 2: Execute an INSERT Query
await connection.execute(
"INSERT INTO users(name, age) VALUES (?, ?)",
[
"Alice",
25
]
);
console.log(
"Record inserted."
);
Sample Output
Record inserted.
Example 3: Execute an UPDATE Query
await connection.execute(
"UPDATE users SET age = ? WHERE id = ?",
[
30,
1
]
);
console.log(
"Record updated."
);
Sample Output
Record updated.
Example 4: Execute a DELETE Query
await connection.execute(
"DELETE FROM users WHERE id = ?",
[
1
]
);
console.log(
"Record deleted."
);
Sample Output
Record deleted.
Example 5: Retrieve a Single Record
const [rows] =
await connection.execute(
"SELECT * FROM users WHERE id = ?",
[
2
]
);
console.log(
rows[0]
);
Sample Output
{
id: 2,
name: "Alice"
}
MongoDB Query Examples
Find Documents
const users =
await collection.find({})
.toArray();
console.log(
users
);
Insert Document
await collection.insertOne(
{
name: "John",
age: 25
}
);
Update Document
await collection.updateOne(
{
name: "John"
},
{
$set: {
age: 30
}
}
);
Delete Document
await collection.deleteOne(
{
name: "John"
}
);
Automation Testing Examples
Executing queries is common in automation frameworks.
Example 1: Verify User Exists
const [rows] =
await connection.execute(
"SELECT * FROM users WHERE id = ?",
[
1
]
);
console.log(
rows.length
);
Sample Output
1
Example 2: Verify Record Count
const [rows] =
await connection.execute(
"SELECT COUNT(*) AS total FROM users"
);
console.log(
rows[0].total
);
Sample Output
25
Example 3: Validate Updated Record
const [rows] =
await connection.execute(
"SELECT age FROM users WHERE id = ?",
[
1
]
);
console.log(
rows[0].age
);
Sample Output
30
Example 4: Verify Record Deletion
const [rows] =
await connection.execute(
"SELECT * FROM users WHERE id = ?",
[
5
]
);
console.log(
rows.length
);
Sample Output
0
Example 5: Validate MongoDB Document
const user =
await collection.findOne(
{
name: "John"
}
);
console.log(
user
);
Sample Output
{
name: "John",
age: 30
}
Query Types
| Query Type | Purpose |
|---|---|
| SELECT / find() | Retrieve data |
| INSERT / insertOne() | Add data |
| UPDATE / updateOne() | Modify data |
| DELETE / deleteOne() | Remove data |
Real-World Automation Uses
Database queries are commonly used for:
Backend verification.
Test data preparation.
Test data cleanup.
API validation.
Login verification.
Order validation.
Inventory verification.
Payment verification.
Regression testing.
Data-driven testing.
Common Mistakes
Using String Concatenation in SQL Queries
Avoid building SQL queries using string concatenation because it can lead to SQL injection vulnerabilities.
Incorrect Example
"SELECT * FROM users WHERE id = " + userId
Correct Example
"SELECT * FROM users WHERE id = ?"
Forgetting to Await Query Execution
Database operations are asynchronous. Always use await or handle the returned Promise.
Ignoring Query Errors
Wrap database queries in try...catch blocks to handle exceptions properly.
Forgetting to Close Database Connections
Always close database connections after executing queries to free resources.
Best Practices
Use parameterized SQL queries.
Handle errors using
try...catch.Close database connections after use.
Keep database configuration separate.
Validate returned query results.
Reuse query helper functions.
Use transactions for operations that require multiple related database changes.
Conclusion
Executing database queries is a fundamental skill in JavaScript and Node.js development. Whether working with MySQL using SQL statements or MongoDB using document methods, queries enable applications to retrieve and modify stored data efficiently.
For automation engineers, executing queries is essential for verifying backend data, preparing test environments, validating application behavior, and cleaning up test records. Mastering database queries is an important step toward building reliable and comprehensive automation frameworks.
Frequently Asked Questions (FAQs)
What is a database query?
A database query is a command used to retrieve, insert, update, or delete data in a database.
Which SQL statement retrieves data?
The SELECT statement is used to retrieve data.
Which MongoDB method retrieves documents?
The find() method retrieves multiple documents, while findOne() retrieves a single document.
Why should parameterized queries be used?
Parameterized queries help prevent SQL injection attacks and improve query safety.
Why are database queries important in automation testing?
They allow automation engineers to verify backend data, prepare test data, validate application behavior, and clean up test records.
Key Takeaways
Database queries interact with stored data.
SQL databases use
SELECT,INSERT,UPDATE, andDELETE.MongoDB uses methods such as
find(),insertOne(),updateOne(), anddeleteOne().Use parameterized SQL queries for security.
Always use
awaitfor asynchronous database operations.Handle database errors using
try...catch.Validate query results during testing.
Close database connections after executing queries.
Database verification is an important part of API and automation testing.
Mastering database queries is essential for JavaScript, Node.js, and automation engineers.