How to Insert, Select, Update, and Delete Data in Magento 2

With CRUD operations, data manipulation in the database becomes straightforward and efficient, eliminating the need for repetitive code. Magento 2 offers built-in functions that simplify database interactions. Below are the key components for developing a CRUD module.

• Create Module
• Create Database Schema
• Read record 
• Insert record
• Update record
• Delete record

In this blog, we’re going to explore the steps to interest, select, update, and delete data in Magento 2. 

Steps to Insert, Select, Update, and Delete Data in Magento 2

Magento 2 provides a robust way to interact with the database through its Model-Resource Model system. This system abstracts the database operations, allowing developers to handle data more efficiently in an object-oriented manner. 

Here are the steps required to create a custom table and perform basic CRUD (Create, Read, Update, Delete) operations.

Table Creation Example: klizer_services

We will start by creating a new table called klizer_services, which will store the services provided by the company “Klizer.” After setting up the module and the table, we’ll demonstrate how to insert, update, select, and delete data.

Step 1: Create the Module Registration File

You need to register your custom module using registration.php.

Path: app/code/Klizer/CrudOperations/registration.php

<?php
/**
 * Klizer
 *
 * @category  Klizer
 * @package   Klizer_CrudOperations
 * @copyright Copyright © 2024 Klizer. All rights reserved.
 * @author    Klizer - info@klizer.com
 */

\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'Klizer_CrudOperations',
    __DIR__
);

Step 2: Define the Module in module.xml

Now define the module using the below path: 

Path: app/code/Klizer/CrudOperations/etc/module.xml

<?xml version="1.0"?>
<!--
/**
 * Klizer
 *
 * @category  Klizer
 * @package   Klizer_CrudOperations
 * @copyright Copyright © 2024 Klizer. All rights reserved.
 * @author    Klizer - info@klizer.com
*/
-->

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Klizer_CrudOperations" setup_version="1.0.0">
    </module>
</config>

Step 3: Create the Database Table

We use the db_schema.xml file to define the table structure for klizer_services.Path: app/code/Klizer/CrudOperations/etc/db_schema.xml

<?xml version="1.0"?>
<!--
/**
 * Klizer
 *
 * @category  Klizer
 * @package   Klizer_CrudOperations
 * @copyright Copyright © 2024 Klizer. All rights reserved.
 * @author    Klizer - info@klizer.com
*/
--> 

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="klizer_services" resource="default" comment="Klizer Services Table">
        <column xsi:type="int" name="entity_id" identity="true" nullable="false" comment="ID"/>
        <column xsi:type="varchar" name="services_name" nullable="false" comment="Services Name"/>
        <column xsi:type="varchar" name="description" nullable="false" comment="Description"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
    </table>
</schema>

Step 4: Run the Setup Upgrade Command

To create the table, run the following command:

php bin/magento setup:upgradeNow Insert some dummy data in the klizer_services table.

INSERT INTO `klizer_services` (`entity_id`, `services_name`, `description`) VALUES

(1, 'Artificial Intelligence', 'Transform your Business with AI-driven solutions by Klizer.'),

(2, 'Headless Commerce', 'Embrace Flexibility with Headless Commerce. The future of ecommerce is Headless.'),

(3, 'Adobe Commerce', 'Adobe Commerce gives you total control over your online store, covering design, development, deployment, and management.'),

(4, 'Shopify Plus', 'Klizer is a leading Shopify & Shopify Plus development agency, that helps you scale and create customer centric website experiences for your online store.'),

(5, 'BigCommerce', 'Experience a new level of online excellence with Klizer, where our team of BigCommerce developers redefines your digital presence through meticulous BigCommerce development.'),

(6, 'Mobile app', 'iOS & Android mobile app development services that offer the best experiences for your customers and teams.'),

(7, 'Ecommerce QA Services', 'Make no compromise on quality.'),

(8, 'Digital Accessibility for Distributors', 'Deliver an effortless experience to all customers, including those who are differently abled, with Klizer's ADA compliance and accessibility testing and services.');

Step 5: Create the Model

Define the model for the klizer_services table.

Path: app/code/Klizer/CrudOperations/Model/KlizerServices.php

<?php
/**
 * Klizer
 *
 * @category  Klizer
 * @package   Klizer_CrudOperations
 * @copyright Copyright © 2024 Klizer. All rights reserved.
 * @author    Klizer - info@klizer.com
 */

namespace Klizer\CrudOperations\Model;

use Magento\Framework\Model\AbstractModel;

class KlizerServices extends AbstractModel
{
    protected function _construct()
    {
        $this->_init(\Klizer\CrudOperations\Model\ResourceModel\KlizerServices::class);
    }
}

Step 6: Create the Resource Model

Define the resource model for interacting with the klizer_services table.

Path: app/code/Klizer/CrudOperations/Model/ResourceModel/KlizerServices.php

<?php
/**
 * Klizer
 *
 * @category  Klizer
 * @package   Klizer_CrudOperations
 * @copyright Copyright © 2024 Klizer. All rights reserved.
 * @author    Klizer - info@klizer.com
 */

namespace Klizer\CrudOperations\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class KlizerServices extends AbstractDb
{
    protected function _construct()
    {
        $this->_init('klizer_services', 'entity_id');
    }
}

Step 7: Create the Collection Model

The collection model helps retrieve multiple records from the database.

Path: app/code/Klizer/CrudOperations/Model/ResourceModel/KlizerServices/Collection.php

<?php
/**
 * Klizer
 *
 * @category  Klizer
 * @package   Klizer_CrudOperations
 * @copyright Copyright © 2024 Klizer. All rights reserved.
 * @author    Klizer - info@klizer.com
 */

namespace Klizer\CrudOperations\Model\ResourceModel\KlizerServices;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;

class Collection extends AbstractCollection
{
    protected function _construct()
    {
        $this->_init(
            \Klizer\CrudOperations\Model\KlizerServices::class,
            \Klizer\CrudOperations\Model\ResourceModel\KlizerServices::class
        );
    }
}

CRUD Operations

1. Insert Data

You can insert data into the klizer_services table using the save() method.

Example:

/**
 * @var \Klizer\CrudOperations\Model\KlizerServicesFactory
 */
protected $klizerServicesFactory;

public function __construct(
    \Klizer\CrudOperations\Model\KlizerServicesFactory $klizerServicesFactory
) {
    $this->klizerServicesFactory = $klizerServicesFactory;
}

$data = [
    ["services_name" => "Ecommerce QA Services", "description" => "Make no compromise on quality."],
    ["services_name" => "Digital Accessibility", "description" => "ADA compliance and accessibility services."]
];

foreach ($data as $serviceData) {
    $serviceModel = $this->klizerServicesFactory->create();
    $serviceModel->addData($serviceData);
    $serviceModel->save();
}

2. Update Data

To update existing data, you must load the record by its entity_id, modify it, and save it.

Notes: In Order to update the record you need to make sure that the Primary field of the table is provided to load the model. If the primary field is set to null Magento will create a new record.

Let us assume the primary field of your table is named id. Now the updated code will be

Example:

$id = 1;
$data = [
    "services_name" => "Ecommerce QA Services - Updated",
    "description" => "Quality services with an updated approach."
];

$klizerServices = $this->klizerServicesFactory->create();
$klizerServicesModel = $klizerServices->load($id);
$klizerServicesModel->addData($data)->save();

3. Select Data

To retrieve data, you can load a single record by ID or fetch multiple records using a collection.

Example:

Select Multiple Records:

$klizerServices = $this->klizerServicesFactory->create();
$servicesCollection = $klizerServices->getCollection();

foreach ($servicesCollection as $service) {
    print_r($service->getData());
}

Select a Single Record:

$id = 1; 
$klizerServices = $this->klizerServicesFactory->create();
$klizerServicesModel = $klizerServices->load($id); 
print_r($klizerServicesModel->getData());

4. Delete Data

To delete a record, load the entity by its primary key and then use the delete() method.

Example:

$id = 1;
$klizerServices = $this->klizerServicesFactory->create();
$klizerServices->load($id)->delete();

With these examples, you can perform CRUD (Create, Read, Update, Delete) operations in Magento 2.

Conclusion

In Magento 2, CRUD (Create, Read, Update, Delete) operations are fundamental to interacting with the database. These operations allow developers to manage data efficiently in custom modules or extensions. So this covers the basic principles of performing CRUD operations in Magento 2. These are the key concepts to master for efficient CRUD handling.

To optimize your Magento 2 store with expert assistance, get in touch with Klizer for customized Magento 2 services. If you need help with data management, extension development, or advanced integrations, Klizer’s experts are here to help you with tailored solutions to enhance your business.