PromptBase
$3.99
After purchasing, you will gain access to the prompt file which you can use with GPT or the app builder. You'll receive 20 free generation credits with this purchase. By purchasing this prompt, you agree to our terms of service.
2 weeks ago
Prompt Details
Model
Chat - GPT-4o (gpt-4o)
Token size
120 ($0.00600 / call)
Example input
[ programming language ]: JavaScript
[ database type ]: MySQL
[ table name ]: Users
[ example fields ]: user_id, username, email, created_at
Example output
Here's a code snippet demonstrating how to perform CRUD operations using JavaScript to interact with a MySQL database, utilizing the popular `mysql2` library for asynchronous MySQL queries. This code structure uses modular functions, includes error handling, and follows best practices for managing database queries and transactions.
To run this code, you’ll need to install the `mysql2` library:
```bash
npm install mysql2
```
Here’s the full code:
```javascript
// Import the MySQL library
const mysql = require('mysql2/promise');
// Create a connection pool to the MySQL database
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'your_password',
database: 'your_database',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
// Function to create a new user in the Users table
async function createUser(username, email) {
let connection;
try {
connection = await pool.getConnection();
const query = `INSERT INTO Users (username, email, created_at) VALUES (?, ?, NOW())`;
const [result] = await connection.execute(query, [username, email]);
console.log('User created successfully with user_id:', result.insertId);
} catch (error) {
console.error('Error creating user:', error.message);
} finally {
if (connection) connection.release();
}
}
// Function to read user data from the Users table by user_id
async function getUser(user_id) {
let connection;
try {
connection = await pool.getConnection();
const query = `SELECT * FROM Users WHERE user_id = ?`;
const [rows] = await connection.execute(query, [user_id]);
return rows[0]; // Return the user record
} catch (error) {
console.error('Error retrieving user:', error.message);
} finally {
if (connection) connection.release();
}
}
// Function to update a user's information in the Users table
async function updateUser(user_id, username, email) {
let connection;
try {
connection = await pool.getConnection();
const query = `UPDATE Users SET username = ?, email = ? WHERE user_id = ?`;
const [result] = await connection.execute(query, [username, email, user_id]);
if (result.affectedRows === 0) {
console.log('No user found with the specified user_id.');
} else {
console.log('User updated successfully.');
}
} catch (error) {
console.error('Error updating user:', error.message);
} finally {
if (connection) connection.release();
}
}
// Function to delete a user from the Users table
async function deleteUser(user_id) {
let connection;
try {
connection = await pool.getConnection();
const query = `DELETE FROM Users WHERE user_id = ?`;
const [result] = await connection.execute(query, [user_id]);
if (result.affectedRows === 0) {
console.log('No user found with the specified user_id.');
} else {
console.log('User deleted successfully.');
}
} catch (error) {
console.error('Error deleting user:', error.message);
} finally {
if (connection) connection.release();
}
}
// Usage example
(async () => {
await createUser('johndoe', 'johndoe@example.com'); // Create a user
const user = await getUser(1); // Read user with user_id 1
console.log(user);
await updateUser(1, 'john_doe', 'john@example.com'); // Update user with user_id 1
await deleteUser(1); // Delete user with user_id 1
})().catch(error => console.error('Error in main execution:', error));
```
### Explanation of Each Function
1. **`createUser(username, email)`**:
- **Purpose**: Inserts a new user into the `Users` table.
- **Query**: Uses `INSERT INTO` to add a new row, automatically setting `created_at` to the current timestamp.
- **Error Handling**: Logs any errors that occur during execution.
- **Connection Release**: Ensures the connection is returned to the pool after execution.
2. **`getUser(user_id)`**:
- **Purpose**: Retrieves a user by their `user_id`.
- **Query**: Uses `SELECT * FROM Users WHERE user_id = ?` to fetch a single user.
- **Return**: Returns the user data if found.
- **Connection Release**: Releases the connection after the query execution.
3. **`updateUser(user_id, username, email)`**:
- **Purpose**: Updates the specified fields of an existing user.
- **Query**: Uses `UPDATE` to modify the `username` and `email` fields.
- **Connection Release**: Returns the connection to the pool, regardless of success or failure.
4. **`deleteUser(user_id)`**:
- **Purpose**: Deletes a user from the `Users` table based on `user_id`.
- **Query**: Uses `DELETE FROM` to remove the user record.
- **Connection Release**: Ensures the connection is released after operation completion.
### Additional Notes
- **Connection Pooling**: We use a connection pool to efficiently manage connections, reusing them across different requests.
- **Error Handling**: Each function logs errors, helping diagnose issues.
- **Self-Executing Function**: The `async` IIFE allows example operations to run immediately for demonstration purposes.