[Music] Welcome to Object-Relational Mapping, or ORM. In this video, we will explain how ORM can simplify application development with databases. After watching this video, you will be able to: Describe the difference between the SQL paradigm and the object-oriented programming, or OOP, paradigm. Explain the core concepts of ORM and list the pros and cons of ORM. Software developers often use a database as the main data repository for their application, so they need to integrate SQL into their application code. SQL statements must be assembled in application code and executed in the database system using database APIs. The retrieved database rows are returned to application code as Cursor, a special control data structure for iterating over rows in a database. This online course database contains only two entities, Course and Learner. The relationship between them is Many-To-Many. One course can have many learners enrolled and one learner can enroll in many courses. This relationship is persisted in an associative table. Let’s see an example of executing SQL in Python code. First, we create a connection to an embedded SQLite database. This is the empty online course database. Next, we create a Cursor object from the connection context. Then we can send and execute SQL. We start with an Insert statement to create and insert a learner record. Once the Insert statement is assembled, we call Cursor to execute methods to run the SQL. Lastly, we create another SELECT statement to query the learner record we just inserted, and we use Cursor-Fetch-One to retrieve the first row. The output shows the learner row we just inserted. In the modern application development environment, OOP is the mainstream, and it is quite different from SQL. Unlike SQL, which models entities using tables, rows, and columns, an object-oriented language models entities using classes and objects. In OOP, Course entity would be defined as a class with two primitive attributes: name and description; and one reference attribute, the list of learners. Methods for data manipulation also need to be defined along with class attributes. Here we define a simple method: get Learners. Learner entity would also be defined as a class with four attributes: first name, last name, date of birth, and occupation. A simple print profile method would also be defined. Let’s create a Learner object from a database using both the SQL and OOP paradigms. First, we execute a SQL select statement and get the first Learn row. To load the data into the Learn object, we create an object by calling its default constructor. Then we update the object with attribute values queried from cursor. We need to manually map each column to each class primitive attribute. This can become complicated if we have complex data relationships. Finally, we call the print profile method to print the object. We have seen that the OOP and SQL paradigms model data differently. OOP models entities using classes, objects, and attributes, whereas SQL models entities using tables, rows, and columns. Also, OOP models relationships using class patterns like inheritance, association, and aggregation, whereas SQL models relationships using JOIN and FOREIGN KEY. Lastly, OOP performs CRUD on data using methods, whereas SQL performs CRUD on data using data manipulation language, like the SQL statements insert, delete, and update. Since we generally build modern applications using OOP, can we also access databases using OOP instead of SQL? That way, we can stick to one programming paradigm for our development. The main reason people invented object-relational mapping was to bridge the gap between OOP and SQL and make it possible to use OOP languages to access databases. ORM libraries or tools can map and transfer data stored in a relational database as rows into objects or objects into rows. Suppose we have a learner object model created by a developer using OOP. ORM can help transfer the Learner object into a Learner row in a Learner table and read it back into a Learner object. This reduces developers’ workload because they only need to focus on object operations. Here’s how ORM can transfer a three-table-join SQL query as a single line of code. Suppose we want to get all learners who are enrolled in the `Introduction to Python` course. In SQL, we would have to join Course, Learner, and their lookup tables to get all the information. We would also need to mix both the OOP and SQL paradigms. But with the help of ORM, we just need to first call the get method in Course class to find the course by name, and then retrieve all its learners. DONE! Now that you’ve learned how convenient ORM is, you may want to use it in your application. There are ORM libraries for almost all popular languages. For Python, you can use Django Model and SQLAlchemy. For Java, you can use Hibernate and OpenJPA and for JavaScript, you can use Sequelize and TypeORM. These are just a few examples. In this course, we’ll focus on Django Model. Like all third-party software libraries, ORM has its pros and cons. On the positive side: With ORM, your class designs define the databases. For OOP application development, you just need to define classes and create objects. You can use databases without writing SQL. Also, you can use a single ORM interface to manage multiple database systems without worrying about differences in SQL syntax. All these benefits will speed up your application delivery. On the negative side: SQL and OOP are still two different languages with different modeling concepts, and ORM may fail to perform the mapping of objects into database tables. Also, since ORM combines data access logic with application code, any database change will require changes to both the application logic and the data access logic. Because ORM hides the implementation details, debugging can be challenging. Finally, ORM may reduce application performance. ORM adds an extra translation layer, but it cannot guarantee that the translated SQL statements are optimized. In this video, you learned that: SQL and OOP model data differently. ORM bridges the gap between SQL and OOP. ORM allows application developers to use databases without writing Sequel code. ORM can speed up application development and he disadvantages of ORM include imperfect mapping, increased code coupling, and difficulty in debugging.
