How to Integrate Replit with MySQL

1// db.js — mysql2 connection pool with SSL
2const mysql = require('mysql2/promise');
3
4// Parse connection config from environment
5function getConnectionConfig() {
6  // Option 1: Full connection URL
7  if (process.env.MYSQL_URL) {
8    return {
9      uri: process.env.MYSQL_URL,
10      ssl: { rejectUnauthorized: true },
11      waitForConnections: true,
12      connectionLimit: 10,
13      queueLimit: 0
14    };
15  }
16
17  // Option 2: Individual credentials
18  return {
19    host: process.env.MYSQL_HOST,
20    port: parseInt(process.env.MYSQL_PORT || '3306'),
21    database: process.env.MYSQL_DATABASE,
22    user: process.env.MYSQL_USER,
23    password: process.env.MYSQL_PASSWORD,
24    // SSL is required for external providers
25    ssl: process.env.MYSQL_SSL_CA
26      ? { ca: process.env.MYSQL_SSL_CA, rejectUnauthorized: true }
27      : { rejectUnauthorized: false }, // Self-signed certs: set to false
28    waitForConnections: true,
29    connectionLimit: 10,
30    queueLimit: 0
31  };
32}
33
34// Create pool lazily on first use
35let pool = null;
36
37function getPool() {
38  if (!pool) {
39    pool = mysql.createPool(getConnectionConfig());
40  }
41  return pool;
42}
43
44// Execute a query with automatic connection management
45async function query(sql, params = []) {
46  const connection = await getPool().getConnection();
47  try {
48    const [rows, fields] = await connection.execute(sql, params);
49    return rows;
50  } finally {
51    connection.release(); // Always release back to pool
52  }
53}
54
55// Test the connection
56async function testConnection() {
57  try {
58    const result = await query('SELECT 1 as test');
59    console.log('MySQL connected successfully:', result[0]);
60    return true;
61  } catch (err) {
62    console.error('MySQL connection failed:', err.message);
63    return false;
64  }
65}
66
67module.exports = { query, testConnection, getPool };

1# app.py — Python Flask with MySQL (mysql-connector-python)
2import os
3import mysql.connector
4from mysql.connector import pooling
5from flask import Flask, request, jsonify
6
7app = Flask(__name__)
8
9# Build connection config from environment
10def get_db_config():
11    db_url = os.environ.get('MYSQL_URL')
12    if db_url:
13        # Parse mysql://user:pass@host:port/database
14        from urllib.parse import urlparse
15        parsed = urlparse(db_url)
16        return {
17            'host': parsed.hostname,
18            'port': parsed.port or 3306,
19            'database': parsed.path.lstrip('/'),
20            'user': parsed.username,
21            'password': parsed.password,
22            'ssl_disabled': False,
23            'ssl_verify_cert': True
24        }
25    # Individual environment variables
26    return {
27        'host': os.environ['MYSQL_HOST'],
28        'port': int(os.environ.get('MYSQL_PORT', 3306)),
29        'database': os.environ['MYSQL_DATABASE'],
30        'user': os.environ['MYSQL_USER'],
31        'password': os.environ['MYSQL_PASSWORD'],
32        'ssl_disabled': False,
33        'ssl_verify_cert': True
34    }
35
36# Create connection pool
37try:
38    db_config = get_db_config()
39    connection_pool = mysql.connector.pooling.MySQLConnectionPool(
40        pool_name='replit_pool',
41        pool_size=5,
42        **db_config
43    )
44    print('MySQL connection pool created')
45except Exception as e:
46    print(f'Failed to create MySQL pool: {e}')
47    connection_pool = None
48
49def execute_query(sql, params=None):
50    """Execute a query using a pooled connection."""
51    conn = connection_pool.get_connection()
52    try:
53        cursor = conn.cursor(dictionary=True)
54        cursor.execute(sql, params or ())
55        if sql.strip().upper().startswith('SELECT'):
56            return cursor.fetchall()
57        else:
58            conn.commit()
59            return {'affected_rows': cursor.rowcount,
60                    'last_insert_id': cursor.lastrowid}
61    finally:
62        cursor.close()
63        conn.close()  # Returns to pool
64
65@app.route('/health', methods=['GET'])
66def health():
67    try:
68        result = execute_query('SELECT VERSION() as version')
69        return jsonify({'status': 'ok', 'mysql_version': result[0]['version']})
70    except Exception as e:
71        return jsonify({'status': 'error', 'message': str(e)}), 500
72
73@app.route('/users', methods=['GET'])
74def get_users():
75    try:
76        users = execute_query('SELECT id, name, email, created_at FROM users LIMIT 100')
77        return jsonify({'users': users})
78    except Exception as e:
79        return jsonify({'error': str(e)}), 500
80
81@app.route('/users', methods=['POST'])
82def create_user():
83    data = request.json
84    try:
85        result = execute_query(
86            'INSERT INTO users (name, email) VALUES (%s, %s)',
87            (data['name'], data['email'])
88        )
89        return jsonify({'id': result['last_insert_id'], 'success': True})
90    except Exception as e:
91        return jsonify({'error': str(e)}), 500
92
93if __name__ == '__main__':
94    app.run(host='0.0.0.0', port=3000)

1// Production-ready Express server with MySQL
2const express = require('express');
3const { query, testConnection } = require('./db');
4
5const app = express();
6app.use(express.json());
7
8// Test DB on startup
9testConnection().then(ok => {
10  if (!ok) {
11    console.error('Database connection failed on startup');
12    // In production, you may want to exit:
13    // process.exit(1);
14  }
15});
16
17// Example: paginated users endpoint
18app.get('/users', async (req, res) => {
19  try {
20    const page = parseInt(req.query.page) || 1;
21    const limit = Math.min(parseInt(req.query.limit) || 20, 100);
22    const offset = (page - 1) * limit;
23
24    const users = await query(
25      'SELECT id, name, email, created_at FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?',
26      [limit, offset]
27    );
28    const [countResult] = await query('SELECT COUNT(*) as total FROM users');
29
30    res.json({
31      users,
32      pagination: {
33        page,
34        limit,
35        total: countResult.total,
36        pages: Math.ceil(countResult.total / limit)
37      }
38    });
39  } catch (err) {
40    console.error('Query error:', err.message);
41    res.status(500).json({ error: 'Database query failed' });
42  }
43});
44
45// Graceful shutdown — close pool on process exit
46process.on('SIGTERM', async () => {
47  console.log('Shutting down, closing database pool...');
48  await require('./db').getPool().end();
49  process.exit(0);
50});
51
52app.listen(3000, '0.0.0.0', () => {
53  console.log('Server running on port 3000');
54});