原文： https://www.mynotepaper.com/nodejs-simple-crud-with-expressjs-and-mysql
In this guide, I’m going to build a CRUD application in Node.js with Express.js and MySQL. I’ll create a books table and will implement CRUD for the books table.
Table of Contents
Create Project and Install Dependencies.Create Database, Table and Connect to Database.Make CRUD Routes.Create View Files.Import Created Files to app.js.Run and See Output.Step 1 : Create Project and Install Dependencies If you didn’t install the Express application generator, then install this globally using this command:
# with NPM command npm install -g express-generatorNow we are going to create a project using the express application generator. Go to the project directory and create a project named “nodejs-crud“.
# create project express --view=ejs nodejs-crud # go to the project folder: cd nodejs-crudNext, we need to install some dependencies. Let’s install these:
# to send flash message: npm install express-flash --save # to make session like PHP: npm install express-session --save # to send PUT and DELETE requests: npm install method-override --save # driver to connect Node.js with MySQL: npm install mysql --saveStep 2 : Create Database, Table and Connect to Database Create a database and then create a books table. Here’s the table structure for books table:
CREATE TABLE `books` ( `id` int(11) NOT NULL, `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `author` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ALTER TABLE `books` ADD PRIMARY KEY (`id`); ALTER TABLE `books` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;We’ve created a database and table. Let’s connect our project to this database. In the root project directory, make a folder called ‘lib‘. Under the lib folder, make a file named ‘db.js‘.
Then open the db.js file and paste this code:
lib/db.js
var mysql = require('mysql'); var connection = mysql.createConnection({ host:'localhost', user:'USERNAME_HERE', password:'PASSWORD_HERE', database:'DATABASE_NAME_HERE' }); connection.connect(function(error){ if(!!error) { console.log(error); } else { console.log('Connected..!'); } }); module.exports = connection;Don’t forget to enter your database credentials.
Step 3 : Make CRUD Routes In this step, we are going to register CRUD routes: create, read, update and delete book data from books table. Go to the routes folder and make a file called ‘books.js‘. We will define routes in this file.
routes/books.js
var express = require('express'); var router = express.Router(); var dbConn = require('../lib/db'); // display books page router.get('/', function(req, res, next) { dbConn.query('SELECT * FROM books ORDER BY id desc',function(err,rows) { if(err) { req.flash('error', err); // render to views/books/index.ejs res.render('books',{data:''}); } else { // render to views/books/index.ejs res.render('books',{data:rows}); } }); }); // display add book page router.get('/add', function(req, res, next) { // render to add.ejs res.render('books/add', { name: '', author: '' }) }) // add a new book router.post('/add', function(req, res, next) { let name = req.body.name; let author = req.body.author; let errors = false; if(name.length === 0 || author.length === 0) { errors = true; // set flash message req.flash('error', "Please enter name and author"); // render to add.ejs with flash message res.render('books/add', { name: name, author: author }) } // if no error if(!errors) { var form_data = { name: name, author: author } // insert query dbConn.query('INSERT INTO books SET ?', form_data, function(err, result) { //if(err) throw err if (err) { req.flash('error', err) // render to add.ejs res.render('books/add', { name: form_data.name, author: form_data.author }) } else { req.flash('success', 'Book successfully added'); res.redirect('/books'); } }) } }) // display edit book page router.get('/edit/(:id)', function(req, res, next) { let id = req.params.id; dbConn.query('SELECT * FROM books WHERE id = ' + id, function(err, rows, fields) { if(err) throw err // if user not found if (rows.length <= 0) { req.flash('error', 'Book not found with id = ' + id) res.redirect('/books') } // if book found else { // render to edit.ejs res.render('books/edit', { title: 'Edit Book', id: rows[0].id, name: rows[0].name, author: rows[0].author }) } }) }) // update book data router.post('/update/:id', function(req, res, next) { let id = req.params.id; let name = req.body.name; let author = req.body.author; let errors = false; if(name.length === 0 || author.length === 0) { errors = true; // set flash message req.flash('error', "Please enter name and author"); // render to add.ejs with flash message res.render('books/edit', { id: req.params.id, name: name, author: author }) } // if no error if( !errors ) { var form_data = { name: name, author: author } // update query dbConn.query('UPDATE books SET ? WHERE id = ' + id, form_data, function(err, result) { //if(err) throw err if (err) { // set flash message req.flash('error', err) // render to edit.ejs res.render('books/edit', { id: req.params.id, name: form_data.name, author: form_data.author }) } else { req.flash('success', 'Book successfully updated'); res.redirect('/books'); } }) } }) // delete book router.get('/delete/(:id)', function(req, res, next) { let id = req.params.id; dbConn.query('DELETE FROM books WHERE id = ' + id, function(err, result) { //if(err) throw err if (err) { // set flash message req.flash('error', err) // redirect to books page res.redirect('/books') } else { // set flash message req.flash('success', 'Book successfully deleted! ID = ' + id) // redirect to books page res.redirect('/books') } }) }) module.exports = router;So, our CRUD routes are ready.
Step 4 : Create View Files To display all books, add & edit book form, we need to create three view files. We’ve set EJS for templating. Go to the views folder & create a folder called books. Under the books folder, make three files named index.ejs, add.ejs and edit.ejs. Then copy the below code and paste:
books/index.ejs
<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <title>Books</title> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"> </head> <body class="container" style="margin-top: 50px;"> <% if (messages.success) { %> <div class="alert alert-success" role="alert"><%- messages.success %></div> <% } %> <% if (messages.error) { %> <div class="alert alert-danger" role="alert"><%- messages.error %></div> <% } %> <div class="card"> <div class="card-header"> <ul class="nav nav-pills w-100"> <li class="nav-pill active"> <a class="nav-link">Books</a> </li> <li class="nav-pill ml-auto"> <a class="nav-link active" href="/books/add">Add Book</a> </li> </ul> </div> <div class="card-body"> <% if(data.length) { %> <table class="table"> <thead> <tr> <th scope="col">#</th> <th scope="col">Name</th> <th scope="col">Author</th> <th width="200px">Action</th> </tr> </thead> <tbody> <% for(var i = 0; i< data.length; i++) { %> <tr> <th scope="row"><%= (i+1) %></th> <td><%= data[i].name%></td> <td><%= data[i].author%></td> <td> <a class="btn btn-success edit" href="../books/edit/<%=data[i].id%>">Edit</a> <a class="btn btn-danger delete" onclick="return alert('Are You sure?')" href="../books/delete/<%=data[i].id%>">Delete</a> </td> </tr> <% } %> </tbody> </table> <% } %> <!-- if result is empty --> <% if(!data.length) { %> <p class="text-center">No book found!</p> <% } %> </div> </div> </body> </html>books/add.ejs
<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <title>Add Book</title> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"> </head> <body class="container" style="margin-top: 50px;"> <% if (messages.error) { %> <div class="alert alert-danger" role="alert"><%- messages.error %></div> <% } %> <div class="card"> <div class="card-header"> Add Book </div> <div class="card-body"> <form action="/books/add" method="post"> <div class="form-group"> <label>Name:</label> <input type="text" class="form-control" name="name" value="<%= name %>" autocomplete="off"> </div> <div class="form-group"> <label>Author:</label> <input type="text" class="form-control" name="author" value="<%= author %>" autocomplete="off"> </div> <div class="form-group"> <input type="submit" class="btn btn-info" value="Add"/> </div> </form> </div> </div> </body> </html>books/edit.ejs
<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <title>Edit Book</title> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"> </head> <body class="container" style="margin-top: 50px;"> <% if (messages.error) { %> <div class="alert alert-danger" role="alert"><%- messages.error %></div> <% } %> <div class="card"> <div class="card-header"> Edit Book </div> <div class="card-body"> <form action="/books/update/<%= id %>" method="post"> <div class="form-group"> <label>Name:</label> <input type="text" class="form-control" name="name" value="<%= name %>" autocomplete="off"> </div> <div class="form-group"> <label>Author:</label> <input type="text" class="form-control" name="author" value="<%= author %>" autocomplete="off"> </div> <div class="form-group"> <input type="submit" class="btn btn-info" value="Update"/> </div> </form> </div> </div> </body> </html>Step 5 : Import Created Files to app.js Our project is about to finish. We have to import all created files to app.js. We need to import these lines:
var flash = require('express-flash'); var session = require('express-session'); var mysql = require('mysql'); var connection = require('./lib/db'); var booksRouter = require('./routes/books'); app.use(session({ cookie: { maxAge: 60000 }, store: new session.MemoryStore, saveUninitialized: true, resave: 'true', secret: 'secret' })) app.use(flash()); app.use('/books', booksRouter); After importing all files, the app.js looks like: app.js var createError = require('http-errors'); var express = require('express'); var path = require('path'); var cookieParser = require('cookie-parser'); var logger = require('morgan'); var flash = require('express-flash'); var session = require('express-session'); var mysql = require('mysql'); var connection = require('./lib/db'); var indexRouter = require('./routes/index'); var usersRouter = require('./routes/users'); var booksRouter = require('./routes/books'); var app = express(); // view engine setup app.set('views', path.join(__dirname, 'views')); app.set('view engine', 'ejs'); app.use(logger('dev')); app.use(express.json()); app.use(express.urlencoded({ extended: false })); app.use(cookieParser()); app.use(express.static(path.join(__dirname, 'public'))); app.use(session({ cookie: { maxAge: 60000 }, store: new session.MemoryStore, saveUninitialized: true, resave: 'true', secret: 'secret' })) app.use(flash()); app.use('/', indexRouter); app.use('/users', usersRouter); app.use('/books', booksRouter); // catch 404 and forward to error handler app.use(function(req, res, next) { next(createError(404)); }); // error handler app.use(function(err, req, res, next) { // set locals, only providing error in development res.locals.message = err.message; res.locals.error = req.app.get('env') === 'development' ? err : {}; // render the error page res.status(err.status || 500); res.render('error'); }); module.exports = app;Step 6 : Run and See Output We have finished all the steps. It’s time to run the project. Let’s run the project and see the output:
# run project: npm start # project URL: http://localhost:3000 # books crud URL: http://localhost:3000/books个人收藏网址而已.
