How to combine Node.js and SQL with page layering to achieve dynamic website development

Node.js is JavaScript that runs on the server side.
SQL is a structured query language used to manage relational databases. These two technologies are combined to enable the development of dynamic websites.

When developing a dynamic website, we need to establish a connection with the database and execute SQL statements to read and write data. In Node.js, many modules can be used to help us connect to the database, the most popular is the mysql module.

Page layering refers to dividing the front-end HTML, CSS and JavaScript code into multiple levels, with each level having different code responsibilities. The advantage of this is that the code can be better organized and the code maintainability can be improved.

Let’s introduce how to combine Node.js and SQL with page layering to develop dynamic websites.

1. Project structure

In order to better organize the code, we need to divide the code into multiple levels. The following is the directory structure of the project:

myapp/
├── app.js
├── package.json
├── node_modules/
├── public/
│   ├── css/
│   │   └── style.css
│   ├── js/
│   │   ├── main.js
│   │   └── jquery.min.js
│   └── img/
│       └── logo.png
└── views/
    ├── index.html
    ├── header.html
    ├── footer.html
    └── error.html

• app.js: The entry file of the project.
• package.json: Project configuration file.
• node_modules/: Stores the modules required for the project.
• public/: Store static files, such as CSS, JavaScript, and images.
• views/: Stores HTML template files.

2. Database connection

In app.js, we need to connect to the database. The code to use the mysql module to connect to the MySQL database is as follows:

const mysql = require('mysql');

// 创建连接
const connection = mysql.createConnection({
  host: 'localhost',  // 数据库主机地址
  user: 'root',       // 数据库用户名
  password: '123456', // 数据库密码
  database: 'myapp'   // 数据库名称
});

// 连接数据库
connection.connect((err) => {
  if (err) {
    console.error('连接数据库失败：', err);
    return;
  }
  console.log('连接数据库成功');
});

// 关闭连接
connection.end((err) => {
  if (err) {
    console.error('关闭连接失败：', err);
    return;
  }
  console.log('关闭连接成功');
});

3. Database operation

After connecting to the database, we can execute SQL statements to read and write data. The following is a simple example to insert a piece of data into the database:

const sql = 'INSERT INTO users SET ?';
const data = { username: 'test', password: '123456' };
connection.query(sql, data, (err, results, fields) => {
  if (err) {
    console.error('插入数据失败：', err);
    return;
  }
  console.log('插入数据成功');
});

The query method is used here to execute SQL statements. The first parameter is the SQL statement, and the second parameter is the data to be inserted, expressed in the form of an object.

4. Page layering

In the views directory, we store the HTML template files of the website. These template files are rendered into final HTML pages by the template engine.

We use the ejs template engine to render HTML templates. Here is a simple example:

<!-- index.html -->
nbsp;html>


  <title>Node.js 实现 SQL 和页面分层</title>
  <link>


  

  <div>
    <h1 id="欢迎来到我的网站">欢迎来到我的网站！</h1>
    <p></p>
  </div>

  

  <script></script>
  <script></script>

As you can see, use and in HTML to insert JavaScript code. The include directive is used to introduce other HTML template files.

In Node.js, use the ejs module to render HTML templates. The following is an example:

const ejs = require('ejs');
const fs = require('fs');

const template = fs.readFileSync('views/index.html', 'utf8');
const data = { message: '欢迎访问我的网站！' };
const html = ejs.render(template, data);

console.log(html);

This code reads the views/index.html template file and uses the object { message: 'Welcome to my website! ' } to render the template and finally generate the final HTML page.

5. Summary

Through the above introduction, we have learned how to use Node.js and SQL combined with page layering to develop dynamic websites. In actual projects, there are more technologies and tools that can be used, such as ORM framework, Webpack, Gulp, etc., which can greatly improve development efficiency and code quality.

The above is the detailed content of How to combine Node.js and SQL with page layering to achieve dynamic website development. For more information, please follow other related articles on the PHP Chinese website!