how to create mysql schema in nodejs

Question

I am using nodejs, express framework and mysql for my database. I would like to know how can I replicate this mongoose code using mysql. I cannot find a way to write my sql schema in a nodejs file. Do I have to use workbench for this? Thanks!

var mongoose = require('mongoose');

var userSchema = mongoose.Schema({
    local: {
            username: String,
            password: String
         }
 });

module.exports = mongoose.model('User', userSchema);

Lavonlavona

check this: blog.ragingflame.co.za/2014/7/21/using-nodejs-with-mysql – Pneumonoultramicroscopicsilicovolcanoconiosis

One thing I don't really understand: why are you using Mongoose? If you enter Mongoose's site it clearly says: mongoose: elegant mongodb object modeling for node.js – Knotty

@Knotty i was following a tutorial series where he used this code. But I like mysql better so thats why i was looking for an alternative. – Lavonlavona

Oh, all right, I thought you were trying to use mongoose on a MySQL database. In this case, you will have to use a MySQL driver like this one (node-mysql). – Knotty

any luck with that? did you find any solution? – Steno

Answer 1

you can use any of:
bookshelf
knex
objection
orm
sequelize

see compare between that:

https://npmcompare.com/compare/bookshelf,knex,objection,orm,sequelize

This tutorial maybe help you to use of "sequelize": Creating a Role-Based User Authentication System with Angular, Express and MySQL

Acme

its not working when I click that sequelize link. – Kirima

This is Old training but if you want, can read it in: hisk.io/… – Acme

Answer 2

MySQL does not have a built-in schema definition language like Mongoose, so you'll have to create tables manually using SQL queries.

note must require mysql2 at top and install it npm i mysql2 : const mysql = require('mysql2/promise');

Step:01 Create a pool connection

const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'password',
database: 'your_database_name'
});

Step:02 Function to create user table

async function createUserTable() {
const connection = await 
pool.getConnection();
try {
    // Create user table
    await connection.query(`
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(255) NOT NULL,
            password VARCHAR(255) NOT NULL
        )
    `);
    console.log('User table created successfully');
} catch (error) {
    console.error('Error creating user table:', error);
} finally {
    connection.release();
}

}

Step:03 Call the function to create the user table

createUserTable();

Kath