😁Sqlite Sequelize Example (ok)

Chú ý bạn phải cài cả sqlite3 vì Sequelize cần nó 😒

package.json

{
  "name": "sqlite-sequelize-example-starter",
  "version": "1.0.0",
  "description": "SQLite database demo, storing database file on local filesystem. Uses Sequelize ORM.",
  "main": "src/index.js",
  "scripts": {
    "start": "nodemon src/index.js localhost 8080"
  },
  "dependencies": {
    "body-parser": "^1.20.3",
    "express": "^4.21.2",
    "nodemon": "^3.1.9",
    "path": "^0.12.7",
    "sequelize": "^6.37.5",
    "sqlite3": "^5.1.7"
  }
}

views\index.html

<!DOCTYPE html>
<html>
  <head>
    <title>SQLite3 with Sequelize Example Starter</title>
    <meta name="description" content="SQLite3 with Sequelize example starter" />
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
  </head>
  <body>
    <header>
      <h1>
        Users
      </h1>
    </header>
    <main>
      <form
        action="/new"
        method="POST"
        enctype="application/x-www-form-urlencoded"
      >
        <input
          name="user"
          type="text"
          maxlength="100"
          value=""
          placeholder="Add a user"
        />
        <button type="submit">Add</button>
      </form>
      <section class="users">
        <ul id="users"></ul>
      </section>
      <p><a href="/reset">Reset</a></p>
    </main>
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script src="/client.js"></script>
  </body>
</html>

src\index.js

// init project
var express = require("express");
var Sequelize = require("sequelize");
const path = require('path');
var app = express();
var bodyParser = require("body-parser");
// Using `public` for static files: http://expressjs.com/en/starter/static-files.html
app.use(express.static("public"));
// Initial set of users to populate the database with
var defaultUsers = ["Brad Pitt", "Ed Norton", "Denzel Washington"];
var users = defaultUsers.slice();
// Use bodyParser to parse application/x-www-form-urlencoded form data
var urlencodedParser = bodyParser.urlencoded({ extended: false });
// setup a new database using database credentials set in .env
var sequelize = new Sequelize(
  "database",
  process.env.USER,
  process.env.PASSWORD,
  {
    host: "0.0.0.0",
    dialect: "sqlite",
    pool: {
      max: 5,
      min: 0,
      idle: 10000
    },
    // Data is stored in the file `database.sqlite` in the folder `db`.
    // Note that if you leave your app public, this database file will be copied if
    // someone forks your app. So don't use it to store sensitive information.
    storage: path.resolve('db', 'database.sqlite')
  }
);
// authenticate with the database
sequelize
  .authenticate()
  .then(function(err) {
    console.log("Connection established.");
    // define new table: 'users'
    User = sequelize.define("users", {
      name: {
        type: Sequelize.STRING
      }
    });
    setup();
  })
  .catch(function(err) {
    console.log("Unable to connect to database: ", err);
  });
// populate database with default users
function setup() {
  User.sync({ force: true }) // Using 'force: true' for demo purposes. It drops the table users if it already exists and then creates a new one.
    .then(function() {
      // Add default users to the database
      for (var i = 0; i < users.length; i++) {
        // loop through all users
        User.create({ name: users[i] }); // create a new entry in the users table
      }
    });
}
// Send user data - used by client.js
app.get("/users", function(request, response) {
  User.findAll().then(function(users) {
    // finds all entries in the users table
    response.send(users); // sends users back to the page
  });
});
// create a new entry in the users table
app.post("/new", urlencodedParser, function(request, response) {
  User.create({ name: request.body.user });
  response.redirect("/");
});
// drops the table users if it already exists and creates a new table with just the default users
app.get("/reset", function(request, response) {
  users = defaultUsers.slice();
  setup();
  response.redirect("/");
});
// Serve the root url: http://expressjs.com/en/starter/basic-routing.html
app.get("/", function(request, response) {
  response.sendFile(path.resolve('views', 'index.html'));
});
// Listen on port 8080
var listener = app.listen(8080, function() {
  console.log("Listening on port " + listener.address().port);
});

public\client.js

$(function() {
  $.get("/users", function(users) {
    users.forEach(function(user) {
      $("<li></li>")
        .text(user.name)
        .appendTo("ul#users");
    });
  });
});

C:\Users\Administrator\Desktop\sandbox\db\database.sqlite

12KB

database.sqlite

Open

PreviousQuerying Sequelize Full link sequelize.org (ok)NextCRUD with React and GraphQL Mysql (ok)

Last updated 10 months ago

// init project var express = require("express"); var Sequelize = require("sequelize"); const path = require('path'); var app = express(); var bodyParser = require("body-parser"); // Using `public` for static files: http://expressjs.com/en/starter/static-files.html app.use(express.static("public")); // Initial set of users to populate the database with var defaultUsers = ["Brad Pitt", "Ed Norton", "Denzel Washington"]; var users = defaultUsers.slice(); // Use bodyParser to parse application/x-www-form-urlencoded form data var urlencodedParser = bodyParser.urlencoded({ extended: false }); // setup a new database using database credentials set in .env var sequelize = new Sequelize( "database", process.env.USER, process.env.PASSWORD, { host: "0.0.0.0", dialect: "sqlite", pool: { max: 5, min: 0, idle: 10000 }, // Data is stored in the file `database.sqlite` in the folder `db`. // Note that if you leave your app public, this database file will be copied if // someone forks your app. So don't use it to store sensitive information. storage: path.resolve('db', 'database.sqlite') } ); // authenticate with the database sequelize .authenticate() .then(function(err) { console.log("Connection established."); // define new table: 'users' User = sequelize.define("users", { name: { type: Sequelize.STRING } }); setup(); }) .catch(function(err) { console.log("Unable to connect to database: ", err); }); // populate database with default users function setup() { User.sync({ force: true }) // Using 'force: true' for demo purposes. It drops the table users if it already exists and then creates a new one. .then(function() { // Add default users to the database for (var i = 0; i < users.length; i++) { // loop through all users User.create({ name: users[i] }); // create a new entry in the users table } }); } // Send user data - used by client.js app.get("/users", function(request, response) { User.findAll().then(function(users) { // finds all entries in the users table response.send(users); // sends users back to the page }); }); // create a new entry in the users table app.post("/new", urlencodedParser, function(request, response) { User.create({ name: request.body.user }); response.redirect("/"); }); // drops the table users if it already exists and creates a new table with just the default users app.get("/reset", function(request, response) { users = defaultUsers.slice(); setup(); response.redirect("/"); }); // Serve the root url: http://expressjs.com/en/starter/basic-routing.html app.get("/", function(request, response) { response.sendFile(path.resolve('views', 'index.html')); }); // Listen on port 8080 var listener = app.listen(8080, function() { console.log("Listening on port " + listener.address().port); });