<?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 Ademar Victorino on Medium]]></title>
        <description><![CDATA[Stories by Ademar Victorino on Medium]]></description>
        <link>https://medium.com/@ademar.victorino_41856?source=rss-9a0703ae0e1d------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*PPvs0dl396IQzJCu</url>
            <title>Stories by Ademar Victorino on Medium</title>
            <link>https://medium.com/@ademar.victorino_41856?source=rss-9a0703ae0e1d------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 21:36:15 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@ademar.victorino_41856/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[How to connect Lambdas in one AWS account to a MongoDB Atlas Cluster in another AWS account…]]></title>
            <link>https://medium.com/@ademar.victorino_41856/how-to-connect-lambdas-in-one-aws-account-to-a-mongodb-atlas-cluster-in-another-aws-account-5535c7d3dd70?source=rss-9a0703ae0e1d------2</link>
            <guid isPermaLink="false">https://medium.com/p/5535c7d3dd70</guid>
            <category><![CDATA[mongodb-peering]]></category>
            <category><![CDATA[security-vpc]]></category>
            <category><![CDATA[vpc-peering]]></category>
            <category><![CDATA[aws-peering]]></category>
            <category><![CDATA[aws-lambda]]></category>
            <dc:creator><![CDATA[Ademar Victorino]]></dc:creator>
            <pubDate>Mon, 23 Aug 2021 16:21:15 GMT</pubDate>
            <atom:updated>2021-08-23T16:21:15.444Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>How to connect Lambdas in one AWS account to a MongoDB Atlas Cluster in another AWS account without compromising security.</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/941/1*b1uV_PuYsOi4kA85M8cxgw.png" /><figcaption>Photo by <a href="https://unsplash.com/@modestasu?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Modestas Urbonas</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>AWS lambdas resolve dynamic IPs during the runtime, connecting them to MongoDB without opening ports to the extranet is not trivial. The best way to secure your lambdas connections against MongoDB is creating Peering connections. MongoDB Atlas could be an AWS hosted service and offers an interface to relate each cluster with a specific security group. Following this manual we will setup the VPC, table route and connect it in a safe way to the Atlas MongoDB.</p><h3>Problem to Solve</h3><p>Security is the name of the game, the best way to make your infrastructure safe is by isolating it from the internet. Taking the advantage that both accounts are hosted in AWS and MongoDB offers this functionality to avoid your data going through the internet. The peering connection is already supported by Google Cloud and Azure as well.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/579/1*hve1QvRYlySzjc6inMUHXw.png" /></figure><h3>What you will setup today:</h3><h4>MongoDB:</h4><ul><li>Cluster / Connection</li><li>Peer Connection</li><li>Whitelist</li></ul><h4>Aws Side:</h4><ul><li>VPC</li><li>Subnets / Routing Tables</li><li>Peering Connection</li><li>Security Group</li></ul><h3>Journey</h3><p>Following the journey below you will be ready to setup the VPC against MongoDB like a security professional.</p><p><strong>1 — AWS — VPC:</strong></p><p>Create a VPC to give the IPv4 CIDR 10.0.100.0/28 or any that follows the block rule. See the reference links in the “Further Reading / Links”. For test purposes, my VPC will be called: <em>vpc-test</em></p><p><strong>2 — AWS — Enable DNS</strong></p><p>Enabling the DNS Hostnames and DNS Resolution from the action menu.</p><p><strong>3 — MongoDB — Create Peering Connection</strong></p><p>Open the “Network access” menu and click on “add peering connection”</p><p>Fill the form with your AWS account as I describe in the next steps:</p><ul><li>Account ID — Located in the “My Account” menu</li><li>VPC ID : vpc-0131ce8649yyyy and VPC CIDR — 10.0.0.0/24</li><li>Application VPC Region — Atlas supports VPC peering with other AWS VPCs in both the same and different regions.</li><li>In the section “Your Atlas VPC” copy the VPC CIDR parameter to use later.</li><li>Click “Initiate Peering”</li></ul><p>The peering line must be added in the console with the status “Waiting for Approval”</p><p><strong>4 — AWS — Accept Peering Request</strong></p><ul><li>Navigate to the left VIRTUAL PRIVATE CLOUD menu and click on Peering Connection.</li><li>Select the connection that is related to the IP number provided by the MongoDB peering ( the IP number that you copied previously).</li><li>Click in Action and then click “Accept Request”.</li><li>Confirm the data provided then click “Yes, Accept“.</li><li>Check the MongoDB peering page to see if the status changed to “Available”, this step can take a couple of minutes.</li></ul><p><strong>5 — AWS — Update Route Table in the default Subnet</strong></p><ul><li>Create the Route Table for the subnet that has been created together with the VPC-TEST.</li><li>Click “Edit Route” then “Add Route”. For the Destination field, add the VPC CIDR: <em>10.0.100.0/28, and </em>fill the target with Peering Connection ID: <em>pcx-0193b07d9aXXX</em></li></ul><p><strong>6 — AWS — Security Group</strong></p><ul><li>Create the security Group related to the VPC: vpc-test</li></ul><p><strong>7 — MongoDB — IP Access List (whitelist)</strong></p><ul><li>Click “Add IP address”</li><li>Add the Security Group ID ( sg-0daXXXX ) in the field “Access List Entry”</li></ul><h3><strong>Conclusion</strong></h3><p>This process will bring two benefits to your infrastructure:</p><ul><li>Speed — All requests to MongoDB will be done in the same network using the AWS internal communication process.</li><li>Security — No one outside of your AWS account can access the MongoDB Instance anymore.</li></ul><h3>Beyond code</h3><p>Do you have an application that requires enhancing the security level to become an upper stage of inaccessibility for any attacking threat?</p><p><a href="https://whiteprompt.com/contact/">Talk to us</a>. We can help!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5535c7d3dd70" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Implementing Graph queries in a Relational Database]]></title>
            <link>https://blog.whiteprompt.com/implementing-graph-queries-in-a-relational-database-7842b8075ca8?source=rss-9a0703ae0e1d------2</link>
            <guid isPermaLink="false">https://medium.com/p/7842b8075ca8</guid>
            <category><![CDATA[common-table-expressions]]></category>
            <category><![CDATA[recursive-sql-queries]]></category>
            <category><![CDATA[graph-database]]></category>
            <category><![CDATA[advanced-sql]]></category>
            <category><![CDATA[traversing-queries]]></category>
            <dc:creator><![CDATA[Ademar Victorino]]></dc:creator>
            <pubDate>Thu, 15 Jul 2021 19:31:19 GMT</pubDate>
            <atom:updated>2021-07-16T13:01:21.484Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*e5Vf07_3j_IfELV2mhiDRg.jpeg" /></figure><h3>Introduction</h3><p>Use the RECURSIVE functionality to create graph queries in relational databases using the CTE syntax ( Recursive Common Table Expression ).</p><p>To better illustrate the functionality I will provide some practical examples to perform recursive queries using traditional DBMS modelings such as ForeignKeys and Many-to-Many relationships.</p><p>The big advantage of this approach is that it allows us to model hierarchical relationships — graph models — in a relational database, with good query performance, saving us from the complexity of having a second graph-oriented database.</p><h3>What are we going to build?</h3><p>We recently had to implement a large database with a complex relational design. My initial estimation is around 75 million rows in the largest table. The client specification requires some graph queries to build paths traversing between relationships using many-to-many tables.</p><p>Usually, relational databases have serious performance problems for executing these types of queries through recursive code functions. Normally we move the data out of the database using a side process to export and create the nodes and edges in a graph database, requiring a lot of effort to keep the data synchronized with the master source.</p><p>Many times we only need to know “friends of friends” ( analogy to the social networks), for just a simple query we need to maintain this entire structure.</p><p>Postgres offers the RECURSIVE query which will bring the same data as a Graph with a plus that we can use the standard SQL features as “like”, “join”, and “aggregations” and just introducing the recursive query as part of your code.</p><h3>Roadmap</h3><p>In this project, we are going to create a model representing a simple database system based on the sqlfiddle site to perform some queries.</p><p>We will use <a href="http://sqlfiddle.com/">http://sqlfiddle.com/</a> ( select Postgres &gt;=9.6 ) to run the examples and view the results. Feel free to use your own Postgres server.</p><h4>Recursive Query</h4><p>The recursive queries are useful in many situations such as querying hierarchical data like organizational structure, parental relationships, etc.</p><p>The following example illustrates the syntax of a recursive CTE:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/478e6e0bb1b6a3e2cd04c64120edce5a/href">https://medium.com/media/478e6e0bb1b6a3e2cd04c64120edce5a/href</a></iframe><p>A recursive CTE has three elements:</p><ul><li>Non-recursive term: a query that forms the base result set of the CTE structure.</li><li>Recursive term: the query that joins with the non-recursive term using UNION.</li><li>Termination check: the recursion stops when no rows are returned from the previous iteration. For this example, we are limiting<em> n &lt; 100</em>.</li></ul><p>PostgreSQL executes a recursive CTE in the following sequence:</p><ul><li>Execute the non-recursive term to create the base result set (R0).</li><li>Execute recursive term with Ri as an input to return the result set Ri+1 as the output.</li><li>Repeat step 2 until an empty set is returned. (termination check).</li><li>Return the final result set that is a UNION of the results sets R0, R1, … Rn.</li></ul><h4>Hierarch Basic Data</h4><p>In this section, I will present some real-world examples with functional code for table creations and query structures that you can copy and paste into sqlfiddle.com.</p><h4>Trees</h4><p>We created a table called <em>PERSON </em>who will receive the names of each participant, their salary, and who they are related to, which is an FK for the table itself.</p><p>The self-relationship is used to create algorithms in the form of a tree that follows a pre-defined hierarch.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/389/0*USg3b4keR6pPjD1i" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3ab01d1e03545ef610d0a9d0d3311823/href">https://medium.com/media/3ab01d1e03545ef610d0a9d0d3311823/href</a></iframe><p>It’s a basic concept, but we can navigate this structure in several ways. For example:</p><ul><li>Returns the path between Manuel and Oscar</li><li>All <em>PERSON</em>below Anderson, who earn more than 50k</li><li>What is the distance between points A and D</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/582/0*twZMfVp9NMcPqWcu" /></figure><p>Get manager hierarchy from an employee:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d87569cf9b560f222432497dce5a7a76/href">https://medium.com/media/d87569cf9b560f222432497dce5a7a76/href</a></iframe><p>After creating the temporary table “hierarch” using WITH becomes easy to play with the result:</p><p>Replace the “<em>SELECT * FROM hierarch;</em>” for the code below to display the data in a user-friendly result:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2c774db1508078979732f04a42d4a459/href">https://medium.com/media/2c774db1508078979732f04a42d4a459/href</a></iframe><h4>Graph</h4><p>In this example, I created a <em>many-to-many lookup</em> table to relate <em>FRIENDS</em>. This schema allows us to create the path of all relationships that exist in this table, referring to the “Friends of Friends” functionality.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/639/0*ReGiHqkq1OveYMCP" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/bf02a12d0edbac1804ad7ef846ad55d6/href">https://medium.com/media/bf02a12d0edbac1804ad7ef846ad55d6/href</a></iframe><p>Usually, the graph algorithms help us to calculate the distance or path between the point 9 to 12 following the example above:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/503/0*xr2fc2IJNHDOVLgu" /></figure><h4>Briefing about Postgres Array:</h4><ul><li>Definition: <em>ARRAY</em>[1, 2]</li><li>Concatenate: <em>ARRAY</em>[1, 2] || <em>ARRAY</em>[3] = [1, 2, 3]</li><li>ALL: find a value inside the <em>ARRAY </em>returning TRUE or FALSE</li><li><em>UNNEST</em>: expand an array to a set of rows</li></ul><h4>Build the Friends of Friends graph</h4><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/aae33b601340a85317f5012d1d2f62c0/href">https://medium.com/media/aae33b601340a85317f5012d1d2f62c0/href</a></iframe><p>You can filter for a specific FRIEND in the non-recursive query using WHERE (advisable) OR allow it to be filtered later in the temporary table (heavy solution).</p><p>To apply the limits, add a simple WHERE clause to the<em> non-recursive </em>term:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5c71f758f7846fe80d50954643399cd0/href">https://medium.com/media/5c71f758f7846fe80d50954643399cd0/href</a></iframe><p>Following the example that explains how to limit the path using the temporary table by <em>START</em> and <em>TARGET:</em></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b3ffe9c47ce531d1e9f5c2d61bc3dec9/href">https://medium.com/media/b3ffe9c47ce531d1e9f5c2d61bc3dec9/href</a></iframe><blockquote>A query without limits can generate a database overload, ideally, always limit the depth that you are looking for the relationships.</blockquote><h4>Benchmark</h4><p>To prove the concept using the same model, I developed a routine to insert 2Mi rows in the PERSON table, followed by a random function to relate 5 FRIENDS per PERSON. Subsequently, the database must process the JOIN against 12Mi rows.</p><p>I randomly choose two PERSON (78258, 1807125) not connected by the FRIEND table running the simple query below:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/640970553e1fa856c7136f3023a699a7/href">https://medium.com/media/640970553e1fa856c7136f3023a699a7/href</a></iframe><p>Then I executed the recursive query to generate all possible paths between <em>from_person_id</em>=78258 <em>to_person_id</em>=1807125.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/38d8a02a92b9c9c1864f66210b27744b/href">https://medium.com/media/38d8a02a92b9c9c1864f66210b27744b/href</a></iframe><p>Simply change the final query to retrieve the shortest path from A to B adding the <em>limit </em>statement:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8174975ea352e03949822b44ace0944e/href">https://medium.com/media/8174975ea352e03949822b44ace0944e/href</a></iframe><p>I ran the queries many times and the results are super fast varying from 9ms to 120ms and for the worst-case scenario, it takes 1 second to calculate the full path in a temporary table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/893/0*M744IXMa4xHZxO0X" /><figcaption>Postgres Explain Analyze</figcaption></figure><h3>Wrap up and take-aways</h3><p>If you are using a NoSQL like MongoDB, the recommendation is to perform a function to export the relations of your data to a Neo4J, Arangodb, or even Redis to support the graph queries.</p><p>With a simple query, we avoid rewriting an entire system. Of course, Graph databases can offer many other features. The main advantage is being able to do the basics within the same database, and this is adequate for a simple approach.</p><p>The performance of the recursive queries even running with 10 mi rows is pretty good, returning in a few milliseconds, comparable to the time taken for the database to execute a: <em>IN( subquery )</em>.</p><p>I don’t recommend using a relational database <strong>only </strong>for recursive queries. Many open-source solutions exist to handle the job using fewer resources. The presented solution avoids the data transfer between the database and other query engines.</p><h3>We think, and we do</h3><p>Do you have an application that requires graph queries in a relational database? <a href="https://whiteprompt.com/contact/">Talk to us</a>. We can help!</p><h3>Further Reading / Links</h3><p><a href="https://www.postgresql.org/docs/9.1/queries-with.html">https://www.postgresql.org/docs/9.1/queries-with.html</a> <a href="https://www.geeksforgeeks.org/binary-tree-data-structure/">https://www.geeksforgeeks.org/binary-tree-data-structure/ https://www.geeksforgeeks.org/graph-data-structure-and-algorithms/</a> <a href="https://www.alibabacloud.com/blog/postgresql-graph-search-practices---10-billion-scale-graph-with-millisecond-response_595039">https://www.alibabacloud.com/blog/postgresql-graph-search-practices---10-billion-scale-graph-with-millisecond-response_595039</a> <a href="https://www.postgresqltutorial.com/postgresql-union/">https://www.postgresqltutorial.com/postgresql-union/</a></p><p>Photo by<a href="https://unsplash.com/@pietrozj?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText"> Pietro Jeng</a> on<a href="https://unsplash.com/s/photos/graph?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText"> Unsplash</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7842b8075ca8" width="1" height="1" alt=""><hr><p><a href="https://blog.whiteprompt.com/implementing-graph-queries-in-a-relational-database-7842b8075ca8">Implementing Graph queries in a Relational Database</a> was originally published in <a href="https://blog.whiteprompt.com/">White Prompt Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>