MySql - Executing SQL queries from code

Executing SQL Queries from Code

MySQL is one of the most widely-used relational database management systems. One of its strengths is the ability to integrate seamlessly with various programming languages. Executing SQL queries from application code is essential for dynamic database-driven systems. This document provides a comprehensive overview of how to execute SQL queries from within Node.js, Python, Java, and PHP applications. We will explore connection setup, executing SELECT, INSERT, UPDATE, and DELETE statements, using prepared statements, and best practices for secure and efficient interactions with MySQL.

1. Executing SQL Queries in Node.js

1.1 Installing MySQL Driver

Use the mysql2 package, which provides a robust MySQL client for Node.js.

npm install mysql2

1.2 Connecting and Running Queries

const mysql = require('mysql2');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'testdb'
});

connection.connect(err => {
    if (err) throw err;
    console.log('Connected to MySQL');
});

1.3 Executing a SELECT Query

connection.query('SELECT * FROM users', (err, results) => {
    if (err) throw err;
    console.log(results);
});

1.4 Executing INSERT Query

const user = { name: 'John Doe', email: 'john@example.com' };
connection.query('INSERT INTO users SET ?', user, (err, result) => {
    if (err) throw err;
    console.log('User inserted with ID:', result.insertId);
});

1.5 Using Prepared Statements

connection.execute(
    'SELECT * FROM users WHERE email = ?',
    ['john@example.com'],
    (err, results) => {
        if (err) throw err;
        console.log(results);
    }
);

2. Executing SQL Queries in Python

2.1 Installing Connector

Use mysql-connector-python:

pip install mysql-connector-python

2.2 Connecting to MySQL

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='testdb'
)

2.3 Executing a SELECT Query

cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

2.4 Inserting Data

sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = ("Alice", "alice@example.com")
cursor.execute(sql, values)
conn.commit()
print("Inserted ID:", cursor.lastrowid)

2.5 Using Prepared Statements

cursor.execute("SELECT * FROM users WHERE email = %s", ("alice@example.com",))
result = cursor.fetchone()
print(result)

3. Executing SQL Queries in Java

3.1 Including MySQL JDBC Driver

Add the MySQL Connector/J JAR file to your project. If using Maven:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

3.2 Connecting to MySQL

import java.sql.*;

public class DBConnect {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String user = "root";
        String password = "password";

        try {
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to MySQL");
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3.3 Executing SELECT Statement

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");

while (rs.next()) {
    System.out.println(rs.getInt("id") + ": " + rs.getString("name"));
}

3.4 Executing INSERT Statement

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "Bob");
pstmt.setString(2, "bob@example.com");
pstmt.executeUpdate();

3.5 Retrieving Inserted ID

PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pstmt.executeUpdate();
ResultSet keys = pstmt.getGeneratedKeys();
if (keys.next()) {
    System.out.println("Inserted ID: " + keys.getInt(1));
}

4. Executing SQL Queries in PHP

4.1 Using mysqli Extension

<?php
$host = "localhost";
$user = "root";
$password = "password";
$database = "testdb";

$conn = new mysqli($host, $user, $password, $database);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected to MySQL";
?>

4.2 Running SELECT Query

<?php
$sql = "SELECT * FROM users";
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . "<br>";
}
$conn->close();
?>

4.3 Using Prepared Statements

<?php
$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$email = "bob@example.com";
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['name'];
}
$stmt->close();
$conn->close();
?>

4.4 Inserting Data

<?php
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$name = "Eve";
$email = "eve@example.com";
$stmt->execute();
echo "Inserted ID: " . $stmt->insert_id;
$stmt->close();
$conn->close();
?>

5. Best Practices for Executing Queries

5.1 Use Prepared Statements

Prepared statements prevent SQL injection and improve performance for repeated queries.

5.2 Sanitize Input

Even when using prepared statements, always validate and sanitize user input.

5.3 Close Connections

Always close your database connection when done to avoid memory leaks and connection exhaustion.

5.4 Use Connection Pooling

Connection pooling improves performance by reusing connections instead of creating a new one each time.

5.5 Handle Exceptions Gracefully

Use proper exception handling to prevent application crashes and log errors for debugging.

5.6 Separate SQL Logic

Organize SQL queries into separate files or functions to improve maintainability and readability.

5.7 Limit SELECT Queries

Always use LIMIT in SELECT queries when fetching large data to avoid memory overhead.

SELECT * FROM users LIMIT 100;

6. Monitoring and Logging

Log all database errors and slow queries. In MySQL, enable the slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

In your application, log all failed queries and connection attempts for diagnostics.

Executing SQL queries from code is a foundational skill in full-stack development. Whether you’re using Node.js, Python, Java, or PHP, establishing secure and efficient connections and writing optimized queries is critical for performance and reliability. Always use prepared statements to protect against SQL injection, and manage resources like connections and cursors diligently. With this guide, you now have a clear understanding of how to interact with MySQL across multiple programming environments and follow best practices in doing so.