public
Last active

Short tutorial on how to use Express and node-mysql

  • Download Gist
app.js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
// Module dependencies
 
var express = require('express'),
mysql = require('mysql');
 
// Application initialization
 
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : 'password'
});
var app = module.exports = express.createServer();
 
// Database setup
 
connection.query('CREATE DATABASE IF NOT EXISTS test', function (err) {
if (err) throw err;
connection.query('USE test', function (err) {
if (err) throw err;
connection.query('CREATE TABLE IF NOT EXISTS users('
+ 'id INT NOT NULL AUTO_INCREMENT,'
+ 'PRIMARY KEY(id),'
+ 'name VARCHAR(30)'
+ ')', function (err) {
if (err) throw err;
});
});
});
 
// Configuration
 
app.use(express.bodyParser());
 
// Main route sends our HTML file
 
app.get('/', function(req, res) {
res.sendfile(__dirname + '/index.html');
});
 
// Update MySQL database
 
app.post('/users', function (req, res) {
connection.query('INSERT INTO users SET ?', req.body,
function (err, result) {
if (err) throw err;
res.send('User added to database with ID: ' + result.insertId);
}
);
});
 
// Begin listening
 
app.listen(3000);
console.log("Express server listening on port %d in %s mode", app.address().port, app.settings.env);
index.html
HTML
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
<!doctype html>
<html>
<head>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$('#user-submit').click(function () {
var payload = {
name: $('#user-name').val()
};
 
$.ajax({
url: "/users",
type: "POST",
contentType: "application/json",
processData: false,
data: JSON.stringify(payload),
complete: function (data) {
$('#output').html(data.responseText);
}
});
});
});
</script>
</head>
<body>
<h3>Enter a username to enter into the database:</h3>
<input id="user-name" type="text" />
<input id="user-submit" type="submit" />
<p id="output"></p>
</body>
</html>
package.json
JSON
1 2 3 4 5 6 7 8 9 10 11
{
"name": "reddit-node-mysql"
, "description": "A demo of how to use Express and MySQL together"
, "author": "Clarence Leung <github@clarle>"
, "version": "0.0.1"
, "private": true
, "dependencies": {
"express": "~2.5",
"mysql": "~2.0"
}
}

hi, question - if you would like to access 'connection' from another file, what would you do?

I got the below error when I tried to run this demo. Can you please help me to find out where is the problem?

D:\Nodejs>node app.js
Warning: express.createServer() is deprecated, express
applications no longer inherit from http.Server,
please use:

var express = require("express");
var app = express();

connect.multipart() will be removed in connect 3.0
visit https://github.com/senchalabs/connect/wiki/Connect-3.0 for alternatives
connect.limit() will be removed in connect 3.0

D:\Nodejs\app.js:55
console.log("Express server listening on port %d in %s mode", app.address().po
^
TypeError: Object function app(req, res, next){ app.handle(req, res, next); } ha
s no method 'address'
at Object. (D:\Nodejs\app.js:55:67)
at Module._compile (module.js:456:26)
at Object.Module._extensions..js (module.js:474:10)
at Module.load (module.js:356:32)
at Function.Module._load (module.js:312:12)
at Function.Module.runMain (module.js:497:10)
at startup (node.js:119:16)
at node.js:902:3

Hi all, I'll be updating this gist for Express 4.0 and the latest version of node-mysql shortly. Stay tuned!

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.