<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Ali Naqvi on Medium]]></title>
        <description><![CDATA[Stories by Ali Naqvi on Medium]]></description>
        <link>https://medium.com/@alinaqvi097?source=rss-2c58c37671c2------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*A7ED2ksoENGuE40PoaLrew.jpeg</url>
            <title>Stories by Ali Naqvi on Medium</title>
            <link>https://medium.com/@alinaqvi097?source=rss-2c58c37671c2------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 08:20:26 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@alinaqvi097/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Difference between Procedures and Functions in SQL. PostgreSQL and MySQL databases, and performance]]></title>
            <link>https://medium.com/@alinaqvi097/difference-between-procedures-and-functions-in-sql-postgresql-and-mysql-databases-and-performance-f0d6408a375a?source=rss-2c58c37671c2------2</link>
            <guid isPermaLink="false">https://medium.com/p/f0d6408a375a</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[sql-functions]]></category>
            <category><![CDATA[sql-procedure]]></category>
            <dc:creator><![CDATA[Ali Naqvi]]></dc:creator>
            <pubDate>Wed, 24 May 2023 17:01:48 GMT</pubDate>
            <atom:updated>2023-05-24T17:01:48.508Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*O24cmQp3lQ4DRvPH" /><figcaption>Photo by <a href="https://unsplash.com/es/@rubaitulazad?utm_source=medium&amp;utm_medium=referral">Rubaitul Azad</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3><strong>Table of content:</strong></h3><p><em>Introduction</em></p><p><em>Enhancing Performance with Procedures</em></p><p><em>Optimizing Productivity with Functions</em></p><p><em>Efficient Query Execution</em></p><p><em>Conclusion</em></p><h3><strong>Introduction</strong></h3><p>In the realm of SQL databases, leveraging procedures and functions can significantly enhance both performance and productivity. These powerful database constructs offer various benefits that streamline development processes and optimize query execution. In this article, we will explore how procedures and functions can improve performance and boost productivity, backed by concrete examples and explanations.</p><h3><strong>Enhancing Performance with Procedures</strong></h3><p>Procedures play a vital role in performance optimization by allowing the execution of complex tasks as a single unit. This ensures that multiple database operations are processed atomically, reducing the overhead of multiple round trips between the application and the database. With procedures, we can encapsulate business logic, data manipulation, and transaction control, resulting in improved efficiency and reduced latency.</p><p><em>Example: Bulk Data Updates:</em></p><p>Consider a scenario where we need to update a large number of records in a table. By encapsulating the update logic within a procedure, we can minimize network latency and database overhead. The procedure enables us to process the updates in batches or transactions, thereby reducing the impact on system resources and optimizing performance.</p><pre>CREATE PROCEDURE UpdateRecords()<br>BEGIN<br>   START TRANSACTION;<br>   -- Perform bulk updates here<br>   UPDATE my_table SET column1 = value1 WHERE condition;<br>   UPDATE my_table SET column2 = value2 WHERE condition;<br>   -- Commit the transaction<br>   COMMIT;<br>END;</pre><h3><strong>Optimizing Productivity with Functions</strong></h3><p>Functions provide a way to encapsulate reusable logic, promoting code reusability and simplifying development efforts. By abstracting complex calculations or frequently used SQL snippets into functions, we can write concise and maintainable code. Functions also enable us to encapsulate data transformations and aggregations, promoting code modularity and enhancing code readability.</p><p><em>Example: Data Transformation and Aggregation:</em></p><p>Suppose we need to compute the total revenue generated by a product category. By encapsulating the revenue calculation within a function, we can reuse the logic across various queries and reports. This not only reduces code duplication but also improves productivity as changes to the revenue calculation need only be made in one place, ensuring consistency and reducing the likelihood of errors.</p><pre>CREATE FUNCTION CalculateRevenue(category_id INT)<br>RETURNS DECIMAL(10, 2)<br>BEGIN<br>   DECLARE total_revenue DECIMAL(10, 2);<br>   -- Perform revenue calculation here<br>   SELECT SUM(price * quantity) INTO total_revenue<br>   FROM sales<br>   WHERE product_category_id = category_id;<br>   RETURN total_revenue;<br>END;</pre><h3>Efficient Query Execution</h3><p>Both procedures and functions can contribute to optimized query execution. By leveraging stored procedures, we can pre-compile and optimize frequently used complex queries, reducing the query compilation overhead at runtime. Functions, on the other hand, allow us to encapsulate complex calculations and transformations within the database engine, leveraging the database’s query optimization capabilities for improved performance.</p><p><em>Example: Complex Reports</em></p><p>Consider a scenario where we need to generate a complex report involving multiple calculations and aggregations. By encapsulating the report generation logic within a stored procedure or a set of functions, we can leverage the database engine’s query optimization capabilities. This ensures that the report is generated efficiently, minimizing the processing time and improving overall performance.</p><pre>CREATE PROCEDURE GenerateReport()<br>BEGIN<br>   -- Perform complex report generation using functions and queries<br>   SELECT category_id, CalculateRevenue(category_id) AS revenue<br>   FROM product_categories;<br>   -- Additional report logic goes here<br>END;</pre><h3>Conclusion:</h3><p>Procedures and functions are indispensable tools in optimizing performance and enhancing productivity in SQL databases. Procedures enable us to execute complex tasks as atomic units, reducing overhead and improving efficiency. Functions promote code reusability, simplify complex calculations, and enhance code modularity. By leveraging these constructs, we can maximize performance, boost productivity, and build robust SQL solutions that meet the demands of modern applications.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f0d6408a375a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Migrate a MySQL Database to PostgreSQL with Sequelize and Nodejs]]></title>
            <link>https://medium.com/@alinaqvi097/how-to-migrate-a-mysql-database-to-postgresql-with-sequelize-and-nodejs-c97f86f60745?source=rss-2c58c37671c2------2</link>
            <guid isPermaLink="false">https://medium.com/p/c97f86f60745</guid>
            <category><![CDATA[postgresql-database]]></category>
            <category><![CDATA[database-migration]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[nodejs]]></category>
            <category><![CDATA[sequelize]]></category>
            <dc:creator><![CDATA[Ali Naqvi]]></dc:creator>
            <pubDate>Sat, 06 May 2023 08:09:55 GMT</pubDate>
            <atom:updated>2023-05-06T08:09:55.265Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*6_vuz-9npW-CyXh9" /><figcaption>Photo by <a href="https://unsplash.com/@clemono?utm_source=medium&amp;utm_medium=referral">Clem Onojeghuo</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>Introduction:</h3><p>Database migrations are a crucial part of software development. They allow you to change the structure of your database without losing any data. In this article, we will show you how to migrate a MySQL database to PostgreSQL using Sequelize and Nodejs.</p><h3>Steps for Migrating a MySQL Database to PostgreSQL Using Sequelize and Node.js:</h3><ol><li>Generate a schema from the MySQL database using Sequelize-auto.</li><li>Create a connection with the PostgreSQL database using Sequelize.</li><li>Automatically create tables in PostgreSQL using the schema files generated from MySQL database.</li><li>Migrate the data from MySQL to PostgreSQL by looping through the tables and using Sequelize to insert the data into the corresponding PostgreSQL table.</li></ol><h3>1. Generating a schema from the MySQL database:</h3><p>The first step in the migration process is to generate a schema from the MySQL database. We can use the Sequelize-auto package to do this. Here’s an example of how to use Sequelize-auto to generate a schema from the MySQL database:</p><pre>const SequelizeAuto = require(&#39;sequelize-auto&#39;);<br><br>const auto = new SequelizeAuto(&#39;database&#39;, &#39;username&#39;, &#39;password&#39;, {<br>  host: &#39;localhost&#39;,<br>  dialect: &#39;mysql&#39;,<br>  directory: &#39;./models&#39;, // where to write files<br>  port: 3306,<br>  additional: {<br>    timestamps: false<br>  }<br>});<br><br>auto.run((err) =&gt; {<br>  if (err) throw err;<br>  console.log(auto.tables); // table list<br>  console.log(auto.foreignKeys); // foreign key list<br>});</pre><p>In the above code, we create a new instance of Sequelize-auto and pass in the database credentials. We also specify the host, dialect, directory, port, and additional options. The directory option tells Sequelize-auto where to write the generated schema files.</p><p>When we run the auto.run() method, Sequelize-auto generates a schema from the MySQL database and writes the files to the specified directory. The schema files will have the same structure as the MySQL tables.</p><h3>2. Creating a connection with the PostgreSQL database:</h3><p>Once we have the schema files, we can create a connection with the PostgreSQL database using Sequelize. Here’s an example of how to create a connection with the PostgreSQL database:</p><pre>const { Sequelize } = require(&#39;sequelize&#39;);<br><br>const sequelize = new Sequelize(&#39;database&#39;, &#39;username&#39;, &#39;password&#39;, {<br>  host: &#39;localhost&#39;,<br>  dialect: &#39;postgres&#39;<br>});</pre><p>In the above code, we create a new instance of Sequelize and pass in the database credentials. We also specify the host and dialect options. The dialect option tells Sequelize which database driver to use.</p><h3>3. Creating tables in PostgreSQL:</h3><p>With the connection established, we can use Sequelize to automatically create the tables in the PostgreSQL database using the schema files generated from the MySQL database. Here’s an example of how to do this:</p><pre>const fs = require(&#39;fs&#39;);<br>const path = require(&#39;path&#39;);<br><br>const modelsPath = path.join(__dirname, &#39;models&#39;);<br><br>fs.readdirSync(modelsPath).forEach((file) =&gt; {<br>  const model = require(path.join(modelsPath, file))(sequelize, Sequelize.DataTypes);<br>  sequelize.define(model.name, model.attributes, model.options);<br>});<br><br>sequelize.sync();</pre><p>In the above code, we loop through the schema files in the models directory and define each model using sequelize.define(). We then call sequelize.sync() to create the tables in the PostgreSQL database based on the defined models.</p><h3>4. Migrating data from MySQL to PostgreSQL:</h3><p>With the tables created, we can now migrate the data from the MySQL database to the PostgreSQL database. Here’s an example of how to do this:</p><pre>const mysql = require(&#39;mysql2/promise&#39;);<br>const { QueryTypes } = require(&#39;sequelize&#39;);<br><br>const mysqlConnection = await mysql.createConnection({<br>  host: &#39;localhost&#39;,<br>  user: &#39;root&#39;,<br>  password: &#39;password&#39;,<br>  database: &#39;mydatabase&#39;<br>});<br><br>const tables = await sequelize.getQueryInterface().showAllTables();<br><br>for (const table of tables) {<br>  const [rows] = await mysqlConnection.query(`SELECT * FROM ${table}`);</pre><p>In the above code, we first create a connection with the MySQL database using the mysql2/promise package. We then use sequelize.getQueryInterface().showAllTables() to get a list of all the tables in the PostgreSQL database.</p><p>We then loop through each table and use the findAll() method of the Sequelize model to retrieve all the data from the MySQL table. We then use the bulkCreate() method to insert the data into the corresponding PostgreSQL table.</p><p>Here’s the code to complete the data migration step:</p><pre>  const model = sequelize.models[table];<br>  const data = rows.map((row) =&gt; {<br>    const obj = {};<br>    for (const key in row) {<br>      obj[key] = row[key];<br>    }<br>    return obj;<br>  });<br>  await model.bulkCreate(data);<br>}</pre><p>In the above code, we first get the Sequelize model for the table using sequelize.models[table]. We then map the MySQL rows to an array of objects and use the bulkCreate() method of the Sequelize model to insert the data into the PostgreSQL table.</p><h3>5. Conclusion:</h3><p>That’s it! In this article, we’ve shown you how to migrate a MySQL database to PostgreSQL using Sequelize and Nodejs. We started by generating a schema from the MySQL database using Sequelize-auto, then we created a connection with the PostgreSQL database using Sequelize, and automatically created tables in PostgreSQL using the schema files generated from MySQL database.</p><p>Finally, we migrated the data from MySQL to PostgreSQL by looping through the tables and using Sequelize to insert the data into the corresponding PostgreSQL table.</p><p>By following the steps outlined in this article, you should now be able to successfully migrate your MySQL database to PostgreSQL with Sequelize and Nodejs. Good luck!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c97f86f60745" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>