Database: SQL

Question 1

What is SQL?

Answer 1

SQL stands for Structured Query Language, is the standard query language for relational databases

Question 2

How is data structured in SQL databases?

Answer 2

In tables- each table has fields, or columns, and records, or rows, which fill the fields.

Question 3

How does SQL compare to NoSQL databases?

Answer 3

SQL databases are relational: They can relate different tables to create a join version of them, without duplicating the data, the merge can be made one to one, one to many, or many to many tables;

SQL databases have strong schema requirements, the records need to have all fields filled

Question 4

Weakness of SQL compared to NoSQL

Answer 4

Relational databases are hard to scale horizontally; (Some cloud services do the scaling in the background).

Lots of requests to highly joined tables can result in long code, and slow processing

NoSQL runs more types of data more easily

NoSQL scales and adapts better

Question 5

what is mysql 2

Answer 5

mysql2 is a relational database system that uses sql as its query language

Question 6

downloading mysql 2 client to node

Answer 6

npm/yarn i/add mysql2 –save

Question 7

creating a connection/pool object

Answer 7

sql.createPool({
user: ‘root’,
password: ‘’,
host: ‘localhost’,
databate: ‘database name’
});
or
sql.createConnection({
user: ‘root’,
password: ‘’,
host: ‘localhost’,
databate: ‘database name’
});

a connection closes as soon as a query is issued, a pool keeps the connection open

Question 8

executing a mysql2 package command

Answer 8

sql.execute(‘SELECT * FROM (table));

Question 9

what are the two most convenient things to download in order to host a mysql database?

Answer 9

a mysql server and a workbench

Question 10

sql2 command to insert records into a table

Answer 10

execute(‘INSERT INTO product (field1, field2) VALUES (?, ?)’, [value1, value2]);

the (?, ?) helps avoiding SQL Injection attacks.

Question 11

what is sequelize?

Answer 11

its an object-relational mapping library for Node.js, it has inbed commands that maps js commands to SQL so that it is possible to use SQL without writing SQL

Question 12

creating a pool with sequelize

Answer 12

new require(‘sequelize’)(‘database’, ‘user’, ‘password’, {(options)})

(options) : host: ‘127.0.0.1’ - specifies the host, defaults to localhost;
dialect: ‘mysql’ | ‘postgres’ - specifies the sql dialect, because there are differences in how each engine uses SQL

Question 13

defining a model with sequelize

Answer 13

new sequelize().define('table', {
     fieldName: { type: sequelize.STRING, allowNull: false }
})

field properties:
sequelize.STRING | NUMBER | BOOLEAN | DATE etc
allowNull:bool - controls whether the field is required
primaryKey:bool - makes the key primary or not, ideal to do searches and relations between tables

Question 14

synchronizing js models with the database (creating tables if none exists, creating 1-1, 1-many or many-many relationships)

Answer 14

new sequelize().sync((options)).then().catch();

sequelize automatically creates createdAt and updatedAt fields, overwriting tables is also possible.

options:
force: bool - forces DELETE IF EXISTS of tables, good in development version

Question 15

checking if the connection with the database is OK

Answer 15

sequelize.authenticate().then().catch()

Question 16

Solving the intellisense bug with sequelize that would make instantiated classes from it not be suggested?

Answer 16

const Sequelize = require(‘sequelize’).Sequelize;

importing it to other files still keeps it working there

Question 17

Creating a instance of a model with sequelize

Answer 17

model.build({record});

Question 18

Saving a model to the database

Answer 18

model.save();

Question 19

Shortcut function to create and save a model at the same time

Answer 19

model.create({record});

Question 20

Finding one record by the primary key

Answer 20

model.findByPk((pk)): Promise

Question 21

Updating a record

Answer 21

model.findByPk().then(result => {
result.(Var) = (value);
result.save();
})

Question 22

deleting a record

Answer 22

record.deleteByPk((id));
or
record.findByPk … then(record => record.destroy());

Question 23

How are table relations declared with sequelize and how to declare one?

Answer 23

Table relations are declared throught its model representatives such as a Product, Cart or User model.

User.hasMany((model))
or Product.belongsTo((model), (options))

options:
constraints: bool - ??
onDelete: ‘CASCADE’ | ‘SET NULL’ - ??

Question 24

When creating a User.hasMany(Product), give 2 examples of relational methods that can be used in the User model

Answer 24

User.createProduct((product));
User.getProducts({where: {field: val}})
User.addProduct(product.id)
User.addProducts()

Question 25

how to create one model that stores Pks belonging to two other models (intermediate table)?

Answer 25

model1. belongsTo(model2, {through: intermediateModel});

model2. belongsTo(model1, {through: intermediateModel});

Question 26

When using a relational method from a model, how to pass additional options to an intermediate table

Answer 26

Model.createModel2({(options)}, {through: {property: value}})

Question 27

deleting an intermediate table through another model

Answer 27

model.intermediateTable.destroy();

Question 28

assigning new table values to an intermediate table through another model

Answer 28

model. intermediateTable = {key: val}
model. intermediateTable.prop = val ?

apparently only works after calling a related table with another related table.

Question 29

relational function to set the value of multiple records at the same time

Answer 29

Model.setModel2(model.id)

Question 30

what does the concept of ‘eager loading’ means in SQL and how to apply it using Sequelize?

Answer 30

eager loading means fetching one/multiple records along with one or more relational record(s) associated with one record on one query.

Applying it in Sequelize:

since it works for relational stuff only, it can only be called by relational functions such as Model.getModel2({includes: [‘product’]})

Question 31

relational method in sequelize to remove a relation from model A to model B

Answer 31

modelA.removeModelB(id);

// This will remove only the relation, not the actual product.

Question 32

can you write “model.intermediateTable = {key: val}”

and then “model.save()” ?

Answer 32

Yes, it will automatically create an intermediate table with the assigned field

Question 33

updating more than one field with sequelize

Answer 33

model.update({values}, {options})

options:
validate: bool - if false, validation wont be run, default: true.

Question 34

creating a getter and setter function in sequelize.define(), in a property level.

Answer 34

sequelize.define("name", {prop: {
        .....,
        get(){
            //do something and return
        },
        set(value){
            //do something and set a value
        }
   }
})

Question 35

complete the code:

sequelize.define(“name”, {prop: {type: Sequelize.STRING,
…..,
get(){
return (a prop from this model);
}
}
})

Answer 35

completion:

return this.getDataValue(“string”);

this context offers getDataValue(“string”), to get values in get() functions and make them pseudo properties when returning.

And also setDataValue(“prop name”, value) to set a value in a setter of a sequelize model.

Question 36

creating a getter and setter function in sequelize.define(), in a model level.

Answer 36

sequelize.define(“name”, {attributes}, { getterMethods:{}, setterMethods{}});

getterMethods: { func(){}, func2(){} } - allows to set getters on a model level. accessing this.getDataValue(“prop name”) is also possible.

setterMethods: { func(){}, func2(){} } - same thing, but with setters and you wont return anything.

• both setter and getter should probably have the same name, since the difference between setter and getter is
  model. prop and model.prop = something.

Question 37

difference between this.getDataValues(“prop”) and this.prop?

Answer 37

the first returns the raw value, the second returns the getter value if available.

Question 38

calling a sequelize model pseudo property vs a modified property

Answer 38

model.prop | model.prop

defining getters and setters in different levels(property and model level) doesnt change how you call them.