Working with Databases

In this chapter we can use a local MySQL server.

Or use an online free MySQL service:

https://aiven.io/

To work with data, we can use different relational DBMS like SQLite or MySQL, PostgreSQL, Oracle Database, IBM DB2, and Microsoft SQL Server.

In addition to relational database systems, there are non-relational models for database systems. These systems are usually categorized with the term NoSQL and includes systems such as Cassandra, Firebase and MongoDB.

NoSQL systems handle huge datasets better than relational systems. But they

scenarios. SQL databases use schemas for a very

aren‟t the best answer for all good reason: they ensure data integrity and data consistency.

Definitions:

Data integrity: The guarantee of all data constraints (primary and foreign keys, data types, etc...).

Data consistency: The guarantee that database constraints are not violated when executing transactions.

Document Stores (also called document-oriented databases) associate keys with values, but unlike key-value stores, they call that value a document.

• A document can be a binary file like a .doc or .pdf or a semi-structured XML or JSON document.
• Most NoSQL systems are of this type. MongoDB (ranking 5, 1 in no-SQL), AWS DynamoDB (16), Google FireBase (39), and Cloud Datastore (79) are popular examples.

MongoDB is an open-source, NoSQL, document-oriented database. It can be used with any backend technology (JEE, PHP, etc.), it is much more commonly used with Node You simply package your data as a JSON object, give it to MongoDB, and it stores this object or document as a binary JavaScript object (BSON).

MongoDB does not support: transactions (from 4.0), joins (it uses nested documents instead).

The ability to run on multiple servers means MongoDB can handle large datasets: replication => redundancy + high availability

• Object Relational Mapping libraries (ORM) allow the use of an API to query databases without using database-specific queries.
• They offer a layer of abstraction that makes your code independent of the subsequent database system used.
• Advantages:
  • Reduce security risks (SQL or non-SQL injections).
  • Simplify database communication.
  • Allow changing the database system without changing your code.

const connectToDB = async => { try { await sequelize.authenticate; console.log(`Successfully connected to database server...`); } catch(error) { console.log(error); } } Note: here the sequelize object will connect to the database using the given configuration.

// Create app.post(api/employees/v1/', async (request, response) => {

‘/

const { id, name, position } = request.body; const newEMployee = Employee.build({ "id": id, "name": name, "position": position }); try { await newEmployee.save; response.status(201).json(newEmployee); } catch(error) { response.json(error); } })

// Search by position router.get(employees/v1/position/:position', async (request, response) =>

‘/

{ try { const employee = await Employee.findAll({ where: { position: request.params.position } }); response.status(200).json(employee); } catch (error) { response.json(error); } })

// Search by position and city const { Op } = require('sequelize ); // Import Op obj, it defines operations

’

router.get('/employees/v1/position/:position/city/:city', async (request, response) => { try { const employee = await Employee.findAll({ where: { [ Op.and ]: [ { city: request.params.city } , { position: request.params.position } ] } }); response.status(200).json(employee); } catch (error) { response.json(error); } })

// Delete all employees from a given city router.delete('/employees/v1/city/:city', async (request, response) => { try { // delete record in a given city const employee = await Employee.destroy({ where: { city: request.params.city } }); // send response response.status(200).json(employee); } catch (error) { response.json(error); } })

// Find all employees from outside a given city router.get('/employees/v1/ not /city/:city', async (request, response) => { try { const employee = await Employee.findAll({ where: { city: { [ Op.ne ]: request.params.city // ne stands for Not Equal } } }); // send response response.status(200).json(employee); } catch (error) { response.json(error); } })

// Find all employees with age less than a number router.get('/employees/v1/ lt /age/:age', async (request, response) => { try { const employee = await Employee.findAll({ where: { age: { [ Op.lt ]: request.params.age } } }); // send response response.status(200).json(employee); } catch (error) { response.json(error); } })

We can insert some data into a table using a bulk create. We need to re-execute these operation whenever our tables are deleted and recreated (altered):

db.sequelize.sync({ alter: true }) .then(async => { Country.count // counts the number of records .then(async (count) => { if(!count) { // if table empty await Country.bulkCreate([ // add these records { name: "KSA" }, { name: "Oman" }, { name: "Egypt" } ]); } }); });

const Employee = db.sequelize.define('Employee', { ...

}); // Let’s add a Country table, every employee will be associated with 1 country const Country = db.sequelize.define(‘Country'

, {

name: { type: DataTypes.STRING, unique: true } }, { timestamps: false // no need to store timestamps, not critical data (static) }); Country.hasMany(Employee); // Employee will contain the foreign key to a country, it will be called ‚

CountryId ‛

Employee.belongsTo(Country); // Employee objects now have a getter .getCountry

// Search by id and get the country name using the one-to-many association router.get(‘/employees/v1/:id'

, async (request, response) => {

try { const employee = await Employee.findOne({ where: { id: request.params.id } }); const country = await employee .getCountry; // enriched API response.status(200).json({employee, country}); } catch (error) { response.json(error); } })

Validations are checks performed in the Sequelize level, in pure JavaScript. If a validation fails, no SQL query will be sent to the database at all. On the other hand, constraints are rules defined at SQL level. If a constraint check fails, an error will be thrown by the database.

const User = sequelize.define('user', { username: { type: DataTypes.STRING, allowNull: false, // not null constraint unique: true, // Unique constraint }, hashedPassword: { type: DataTypes.STRING(64), validate: { is: /^[0-9a-f]{64}$/i, // Validation enforced by Sequelize }, }, });

is: /^[a-z]+$/i, // matches this RegExp not: /^[a-z]+$/i, // does not match this RegExp isEmail: true, // checks for email format ([email protected]) isUrl: true, // checks for url format (https://foo.com) isIP: true, // checks for IPv4 (129.89.23.1) or IPv6 format isAlpha: true, // will only allow letters isAlphanumeric: true, // will only allow alphanumeric characters, so "_abc" will fail isNumeric: true, // will only allow numbers isInt: true, // checks for valid integers isFloat: true, // checks for valid floating point numbers isDecimal: true, // checks for any numbers isLowercase: true, // checks for lowercase isUppercase: true, // checks for uppercase notNull: true, // won't allow null isNull: true, // only allows null notEmpty: true, // don't allow empty strings

equals: 'specific value', // only allow a specific value contains: 'foo', // force specific substrings notIn: [['foo', 'bar']], // check the value is not one of these isIn: [['foo', 'bar']], // check the value is one of these notContains: 'bar', // don't allow specific substrings len: [2,10], // only allow values with length between 2 and 10 isDate: true, // only allow date strings isAfter: "2011-11-05", // only allow date strings after a specific date isBefore: "2011-11-05", // only allow date strings before a specific date max: 23, // only allow values <= 23 min: 23, // only allow values >= 23

const User = sequelize.define('user', {

...

email: { type: DataTypes.STRING, validate: {

isEmail: true

} }, }); // Then in the controller:

const user = User.build({ username: 'Salah', email: ' salah ' }); if(user.validate) { try { await user.save;

• Create account in aiven.io
• Create Database se371db
• Install extensions: MySQL, Thunder Client
• Connect to database
• Try get, post, del, put

END