Salin dan Bagikan

Cara Koneksi MySQL dari PHP, Python, Java, dan Node.js dengan Best Practices

Setiap bahasa pemrograman memiliki cara berbeda untuk connect ke MySQL. Setelah mengintegrasikan MySQL dengan berbagai tech stacks, saya akan berbagi patterns yang terbukti reliable dan secure.

PHP dengan PDO

1. Basic Connection

<?php
$host = 'localhost';
$dbname = 'myapp';
$username = 'app_user';
$password = 'secure_password';

try {
    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );
    echo "Connected successfully";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

2. Prepared Statements

<?php
// Insert dengan prepared statement
$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt->execute(['username' => 'john_doe', 'email' => 'john@example.com']);

// Select dengan parameters
$stmt = $pdo->prepare("SELECT * FROM users WHERE status = :status");
$stmt->execute(['status' => 'active']);
$users = $stmt->fetchAll();
?>

3. Connection Pooling (Swoole/Workerman)

<?php
// Dengan Swoole extension
use Swoole\Database\PDOProxy;

$pool = new PDOProxy(
    'mysql:host=localhost;dbname=myapp;charset=utf8mb4',
    'app_user',
    'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ],
    10  // Pool size
);
?>

Python dengan mysql-connector-python

1. Basic Connection

import mysql.connector
from mysql.connector import Error

def create_connection():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='myapp',
            user='app_user',
            password='secure_password',
            charset='utf8mb4'
        )
        if connection.is_connected():
            print("Connected to MySQL")
            return connection
    except Error as e:
        print(f"Error: {e}")
        return None

conn = create_connection()

2. Using Context Manager

from contextlib import contextmanager

@contextmanager
def get_db_connection():
    conn = None
    try:
        conn = mysql.connector.connect(
            host='localhost',
            database='myapp',
            user='app_user',
            password='secure_password'
        )
        yield conn
    finally:
        if conn and conn.is_connected():
            conn.close()

# Usage
with get_db_connection() as conn:
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM users WHERE status = %s", ('active',))
    users = cursor.fetchall()

3. SQLAlchemy dengan Connection Pool

from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

# Create engine dengan pooling
engine = create_engine(
    'mysql+mysqlconnector://app_user:password@localhost/myapp?charset=utf8mb4',
    pool_size=10,
    max_overflow=20,
    pool_timeout=30,
    pool_recycle=3600,
    echo=False
)

# Session factory
Session = sessionmaker(bind=engine)

# Usage
def get_active_users():
    with Session() as session:
        result = session.execute(text("SELECT * FROM users WHERE status = 'active'"))
        return result.fetchall()

Java dengan JDBC

1. Basic Connection

import java.sql.*;

public class MySQLConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/myapp?useSSL=false&serverTimezone=UTC";
    private static final String USER = "app_user";
    private static final String PASSWORD = "secure_password";
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    
    public static void main(String[] args) {
        try (Connection conn = getConnection()) {
            System.out.println("Connected successfully");
            
            // Prepared statement
            String sql = "SELECT * FROM users WHERE status = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, "active");
                ResultSet rs = stmt.executeQuery();
                
                while (rs.next()) {
                    System.out.println(rs.getString("username"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. HikariCP Connection Pool

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class ConnectionPool {
    private static HikariDataSource dataSource;
    
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/myapp");
        config.setUsername("app_user");
        config.setPassword("secure_password");
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setIdleTimeout(300000);
        config.setConnectionTimeout(20000);
        
        dataSource = new HikariDataSource(config);
    }
    
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

3. Spring Boot dengan JPA

// application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/myapp?useSSL=false&serverTimezone=UTC
spring.datasource.username=app_user
spring.datasource.password=secure_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=300000

// Entity
@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String username;
    private String email;
    // getters and setters
}

// Repository
public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByStatus(String status);
}

Node.js dengan mysql2

1. Basic Connection

const mysql = require('mysql2/promise');

// Create connection pool
const pool = mysql.createPool({
    host: 'localhost',
    user: 'app_user',
    password: 'secure_password',
    database: 'myapp',
    charset: 'utf8mb4',
    waitForConnections: true,
    connectionLimit: 20,
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0
});

// Usage dengan async/await
async function getUsers() {
    try {
        const [rows] = await pool.execute(
            'SELECT * FROM users WHERE status = ?',
            ['active']
        );
        return rows;
    } catch (error) {
        console.error('Error:', error);
        throw error;
    }
}

2. Using Connection Pool dengan Transactions

async function transferMoney(fromId, toId, amount) {
    const connection = await pool.getConnection();
    
    try {
        await connection.beginTransaction();
        
        await connection.execute(
            'UPDATE accounts SET balance = balance - ? WHERE id = ?',
            [amount, fromId]
        );
        
        await connection.execute(
            'UPDATE accounts SET balance = balance + ? WHERE id = ?',
            [amount, toId]
        );
        
        await connection.commit();
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
}

Security Best Practices

1. Gunakan Prepared Statements

// PHP - SELALU gunakan prepared statements
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$userId]);

# Python - Gunakan parameterized queries
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

2. Connection Encryption

# SSL Connection
connection = mysql.connector.connect(
    host='localhost',
    database='myapp',
    user='app_user',
    password='password',
    ssl_ca='/path/to/ca-cert.pem',
    ssl_cert='/path/to/client-cert.pem',
    ssl_key='/path/to/client-key.pem'
)

3. Environment Variables untuk Credentials

import os

DB_HOST = os.environ.get('DB_HOST', 'localhost')
DB_USER = os.environ.get('DB_USER')
DB_PASSWORD = os.environ.get('DB_PASSWORD')
DB_NAME = os.environ.get('DB_NAME')

Kesimpulan

Setiap bahasa memiliki best practices:

PHP: Gunakan PDO dengan prepared statements
Python: mysql-connector-python atau SQLAlchemy dengan pooling
Java: JDBC dengan HikariCP untuk production
Node.js: mysql2 dengan promise API dan connection pooling

Selalu gunakan connection pooling dan prepared statements untuk security dan performance!

Cara Koneksi MySQL dari PHP, Python, Java, dan Node.js dengan Best Practices

Cara Koneksi MySQL dari PHP, Python, Java, dan Node.js dengan Best Practices

PHP dengan PDO

1. Basic Connection

2. Prepared Statements

3. Connection Pooling (Swoole/Workerman)

Python dengan mysql-connector-python

1. Basic Connection

2. Using Context Manager

3. SQLAlchemy dengan Connection Pool

Java dengan JDBC

1. Basic Connection

2. HikariCP Connection Pool

3. Spring Boot dengan JPA

Node.js dengan mysql2

1. Basic Connection

2. Using Connection Pool dengan Transactions

Security Best Practices

1. Gunakan Prepared Statements

2. Connection Encryption

3. Environment Variables untuk Credentials

Kesimpulan

Artikel Terkait