Migrating from PostgreSQL to Geode

<h2 id="migrating-from-postgresql-to-geode" class="position-relative d-flex align-items-center group"> Migrating from PostgreSQL to Geode <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="migrating-from-postgresql-to-geode" aria-haspopup="dialog" aria-label="Share link: Migrating from PostgreSQL to Geode"> Share link </button> </h2><div id="headingShareModal" class="heading-share-modal" role="dialog" aria-modal="true" aria-labelledby="headingShareTitle" hidden> <div class="hsm-dialog" role="document"> <div class="hsm-header"> <h2 id="headingShareTitle" class="h6 mb-0 fw-bold">Share this section</h2> <button type="button" class="hsm-close" aria-label="Close"> </button> </div> <div class="hsm-body"> <label for="headingShareInput" class="form-label small text-muted mb-1 text-uppercase fw-bold" style="font-size: 0.7rem; letter-spacing: 0.5px;">Permalink</label> <div class="input-group mb-4 hsm-url-group"> <input id="headingShareInput" type="text" class="form-control font-monospace" readonly aria-readonly="true" style="font-size: 0.85rem;" /> <button class="btn btn-primary hsm-copy" type="button" aria-label="Copy" title="Copy"> </button> </div> <div class="small fw-bold mb-2 text-muted text-uppercase" style="font-size: 0.7rem; letter-spacing: 0.5px;">Share via</div> <div class="hsm-share-grid"> <a id="share-twitter" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Twitter </a> <a id="share-linkedin" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> LinkedIn </a> <a id="share-facebook" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Facebook </a> </div> </div> </div> </div> <style> .heading-share-modal { position: fixed; inset: 0; display: flex; justify-content: center; align-items: center; background: rgba(0, 0, 0, 0.6); z-index: 1050; padding: 1rem; backdrop-filter: blur(4px); -webkit-backdrop-filter: blur(4px); } .heading-share-modal[hidden] { display: none !important; } .hsm-dialog { max-width: 420px; width: 100%; background: var(--bs-body-bg, #fff); color: var(--bs-body-color, #212529); border: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); border-radius: 1rem; box-shadow: 0 25px 50px -12px rgba(0, 0, 0, 0.25); overflow: hidden; animation: hsm-fade-in 0.2s ease-out; } @keyframes hsm-fade-in { from { opacity: 0; transform: scale(0.95); } to { opacity: 1; transform: scale(1); } } [data-bs-theme="dark"] .hsm-dialog { background: #1e293b; border-color: rgba(255,255,255,0.1); color: #f8f9fa; } .hsm-header { display: flex; justify-content: space-between; align-items: center; padding: 1rem 1.5rem; border-bottom: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); background: rgba(0,0,0,0.02); } [data-bs-theme="dark"] .hsm-header { background: rgba(255,255,255,0.02); border-color: rgba(255,255,255,0.1); } .hsm-close { background: transparent; border: none; color: inherit; opacity: 0.5; padding: 0.25rem 0.5rem; border-radius: 0.25rem; font-size: 1.2rem; line-height: 1; transition: opacity 0.2s; } .hsm-close:hover { opacity: 1; } .hsm-body { padding: 1.5rem; } .hsm-url-group { display: flex !important; align-items: stretch; } .hsm-url-group .form-control { flex: 1; min-width: 0; margin: 0; background: var(--bs-secondary-bg, #f8f9fa); border-color: var(--bs-border-color, #dee2e6); border-top-right-radius: 0; border-bottom-right-radius: 0; height: 42px; } .hsm-url-group .btn { flex: 0 0 auto; margin: 0; margin-left: -1px; border-top-left-radius: 0; border-bottom-left-radius: 0; height: 42px; display: flex; align-items: center; justify-content: center; padding: 0 1.25rem; z-index: 2; } [data-bs-theme="dark"] .hsm-url-group .form-control { background: #0f172a; border-color: #334155; color: #e2e8f0; } .hsm-share-grid { display: flex; flex-direction: column; gap: 0.5rem; } .hsm-share-grid .btn { display: flex; align-items: center; justify-content: center; font-size: 0.9rem; padding: 0.6rem; border-color: var(--bs-border-color); width: 100%; } [data-bs-theme="dark"] .hsm-share-grid .btn { color: #e2e8f0; border-color: #475569; } [data-bs-theme="dark"] .hsm-share-grid .btn:hover { background: #334155; border-color: #cbd5e1; } </style> <script> (function(){ const modal = document.getElementById('headingShareModal'); if(!modal) return; const input = modal.querySelector('#headingShareInput'); const copyBtn = modal.querySelector('.hsm-copy'); const twitter = modal.querySelector('#share-twitter'); const linkedin = modal.querySelector('#share-linkedin'); const facebook = modal.querySelector('#share-facebook'); const closeBtn = modal.querySelector('.hsm-close'); let lastFocus=null; let trapBound=false; function buildUrl(id){ return window.location.origin + window.location.pathname + '#' + id; } function isOpen(){ return !modal.hasAttribute('hidden'); } function hydrate(id){ const url=buildUrl(id); input.value=url; const enc=encodeURIComponent(url); const text=encodeURIComponent(document.title); if(twitter) twitter.href=`https://twitter.com/intent/tweet?url=${enc}&text=${text}`; if(linkedin) linkedin.href=`https://www.linkedin.com/sharing/share-offsite/?url=${enc}`; if(facebook) facebook.href=`https://www.facebook.com/sharer/sharer.php?u=${enc}`; } function openModal(id){ lastFocus=document.activeElement; hydrate(id); if(!isOpen()){ modal.removeAttribute('hidden'); } requestAnimationFrame(()=>{ input.focus(); }); trapFocus(); } function closeModal(){ if(!isOpen()) return; modal.setAttribute('hidden',''); if(lastFocus && typeof lastFocus.focus==='function') lastFocus.focus(); } function copyCurrent(){ try{ navigator.clipboard.writeText(input.value).then(()=>feedback(true),()=>fallback()); } catch(e){ fallback(); } } function fallback(){ input.select(); try{ document.execCommand('copy'); feedback(true);}catch(e){ feedback(false);} } function feedback(ok){ if(!copyBtn) return; const icon=copyBtn.querySelector('i'); if(!icon) return; const prev=copyBtn.getAttribute('data-prev')||icon.className; if(!copyBtn.getAttribute('data-prev')) copyBtn.setAttribute('data-prev',prev); icon.className= ok ? 'fa-duotone fa-clipboard-check':'fa-duotone fa-circle-exclamation'; setTimeout(()=>{ icon.className=prev; },1800); } function handleShareClick(e){ e.preventDefault(); const btn=e.currentTarget; const id=btn.getAttribute('data-share-target'); if(id) openModal(id); } function bindShareButtons(){ document.querySelectorAll('.h-share').forEach(btn=>{ if(!btn.dataset.hShareBound){ btn.addEventListener('click', handleShareClick); btn.dataset.hShareBound='1'; } }); } bindShareButtons(); if(document.readyState==='loading'){ document.addEventListener('DOMContentLoaded', bindShareButtons); } else { requestAnimationFrame(bindShareButtons); } document.addEventListener('click', function(e){ const shareBtn=e.target.closest && e.target.closest('.h-share'); if(shareBtn && !shareBtn.dataset.hShareBound){ handleShareClick.call(shareBtn, e); } }, true); document.addEventListener('click', e=>{ if(e.target===modal) closeModal(); if(e.target.closest && e.target.closest('.hsm-close')){ e.preventDefault(); closeModal(); } if(copyBtn && (e.target===copyBtn || (e.target.closest && e.target.closest('.hsm-copy')))) { e.preventDefault(); copyCurrent(); } }); document.addEventListener('keydown', e=>{ if(e.key==='Escape' && isOpen()) closeModal(); }); function trapFocus(){ if(trapBound) return; trapBound=true; modal.addEventListener('keydown', f=>{ if(f.key==='Tab' && isOpen()){ const focusable=[...modal.querySelectorAll('a[href],button,input,textarea,select,[tabindex]:not([tabindex="-1"])')].filter(el=>!el.hasAttribute('disabled')); if(!focusable.length) return; const first=focusable[0]; const last=focusable[focusable.length-1]; if(f.shiftKey && document.activeElement===first){ f.preventDefault(); last.focus(); } else if(!f.shiftKey && document.activeElement===last){ f.preventDefault(); first.focus(); } } }); } if(closeBtn) closeBtn.addEventListener('click', e=>{ e.preventDefault(); closeModal(); }); })(); </script>This guide provides a comprehensive approach to migrating from PostgreSQL to Geode. Unlike migrating between similar databases, moving from a relational model to a graph model requires rethinking how you structure and query your data. <h3 id="migration-overview" class="position-relative d-flex align-items-center group"> Migration Overview <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="migration-overview" aria-haspopup="dialog" aria-label="Share link: Migration Overview"> Share link </button> </h3> <h4 id="when-to-use-a-graph-database" class="position-relative d-flex align-items-center group"> When to Use a Graph Database <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="when-to-use-a-graph-database" aria-haspopup="dialog" aria-label="Share link: When to Use a Graph Database"> Share link </button> </h4>Graph databases excel when: <ul> <li>Relationships are first-class citizens: Your queries traverse multiple relationships</li> <li>Schema is flexible: New relationship types emerge over time</li> <li>Many-to-many relationships dominate: Junction tables become unwieldy</li> <li>Path queries are common: Finding connections between entities</li> </ul> <h4 id="when-to-keep-postgresql" class="position-relative d-flex align-items-center group"> When to Keep PostgreSQL <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="when-to-keep-postgresql" aria-haspopup="dialog" aria-label="Share link: When to Keep PostgreSQL"> Share link </button> </h4>Consider keeping PostgreSQL for: <ul> <li>Simple CRUD operations with fixed schemas</li> <li>Heavy aggregation/reporting workloads</li> <li>Strong ACID requirements with complex constraints</li> <li>Well-defined, stable relational structures</li> </ul> <h4 id="hybrid-approach" class="position-relative d-flex align-items-center group"> Hybrid Approach <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="hybrid-approach" aria-haspopup="dialog" aria-label="Share link: Hybrid Approach"> Share link </button> </h4>Many organizations run both: <ul> <li>PostgreSQL for transactional data and reporting</li> <li>Geode for relationship-heavy queries and traversals</li> </ul> <h3 id="relational-to-graph-model-conversion" class="position-relative d-flex align-items-center group"> Relational to Graph Model Conversion <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="relational-to-graph-model-conversion" aria-haspopup="dialog" aria-label="Share link: Relational to Graph Model Conversion"> Share link </button> </h3> <h4 id="core-concepts-mapping" class="position-relative d-flex align-items-center group"> Core Concepts Mapping <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="core-concepts-mapping" aria-haspopup="dialog" aria-label="Share link: Core Concepts Mapping"> Share link </button> </h4><table> <thead> <tr> <th>Relational Concept</th> <th>Graph Equivalent</th> </tr> </thead> <tbody> <tr> <td>Table</td> <td>Node Label</td> </tr> <tr> <td>Row</td> <td>Node</td> </tr> <tr> <td>Column</td> <td>Property</td> </tr> <tr> <td>Primary Key</td> <td>Node ID property</td> </tr> <tr> <td>Foreign Key</td> <td>Relationship</td> </tr> <tr> <td>Junction Table</td> <td>Relationship (with properties)</td> </tr> <tr> <td>View</td> <td>Stored Query</td> </tr> </tbody> </table> <h4 id="example-e-commerce-schema" class="position-relative d-flex align-items-center group"> Example: E-Commerce Schema <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="example-e-commerce-schema" aria-haspopup="dialog" aria-label="Share link: Example: E-Commerce Schema"> Share link </button> </h4>PostgreSQL Schema: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">CREATE TABLE customers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT NOW() ); CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2) NOT NULL, category_id INTEGER REFERENCES categories(id) ); CREATE TABLE categories ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER REFERENCES categories(id) ); CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), order_date TIMESTAMP DEFAULT NOW(), status VARCHAR(50) ); CREATE TABLE order_items ( id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES orders(id), product_id INTEGER REFERENCES products(id), quantity INTEGER NOT NULL, unit_price DECIMAL(10, 2) NOT NULL ); CREATE TABLE reviews ( id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), product_id INTEGER REFERENCES products(id), rating INTEGER CHECK (rating BETWEEN 1 AND 5), comment TEXT, created_at TIMESTAMP DEFAULT NOW() ); </code></pre></div>Geode Graph Model: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Nodes (:Customer {id, name, email, created_at}) (:Product {id, name, price}) (:Category {id, name}) (:Order {id, order_date, status}) // Relationships (:Customer)-[:PLACED]->(:Order) (:Order)-[:CONTAINS {quantity, unit_price}]->(:Product) (:Product)-[:IN_CATEGORY]->(:Category) (:Category)-[:PARENT]->(:Category) (:Customer)-[:REVIEWED {rating, comment, created_at}]->(:Product) </code></pre></div>Visual Representation: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback"> +-----------+ | Category | +-----------+ ^ | :PARENT (self-referential) | +-----------+ | Category | +-----------+ ^ | :IN_CATEGORY | +-----------+ +-----------+ +-----------+ | Customer |------>| Order |------>| Product | +-----------+ :PLACED +-----------+ :CONTAINS +-----------+ | ^ | | +---------------------------------------+ :REVIEWED </code></pre></div> <h3 id="table-to-node-mapping" class="position-relative d-flex align-items-center group"> Table to Node Mapping <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="table-to-node-mapping" aria-haspopup="dialog" aria-label="Share link: Table to Node Mapping"> Share link </button> </h3> <h4 id="simple-tables" class="position-relative d-flex align-items-center group"> Simple Tables <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="simple-tables" aria-haspopup="dialog" aria-label="Share link: Simple Tables"> Share link </button> </h4>PostgreSQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(100) UNIQUE, email VARCHAR(255), age INTEGER, active BOOLEAN DEFAULT true ); </code></pre></div>GQL Node Creation: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE (:User { id: 1, username: 'alice', email: 'alice@example.com', age: 30, active: true }) </code></pre></div> <h4 id="tables-with-enums" class="position-relative d-flex align-items-center group"> Tables with Enums <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="tables-with-enums" aria-haspopup="dialog" aria-label="Share link: Tables with Enums"> Share link </button> </h4>PostgreSQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">CREATE TYPE user_role AS ENUM ('admin', 'user', 'guest'); CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(255), role user_role ); </code></pre></div>GQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Option 1: Store as string property CREATE (:User {id: 1, name: 'Alice', role: 'admin'}) // Option 2: Use labels for roles CREATE (:User:Admin {id: 1, name: 'Alice'}) </code></pre></div> <h4 id="tables-with-arrays" class="position-relative d-flex align-items-center group"> Tables with Arrays <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="tables-with-arrays" aria-haspopup="dialog" aria-label="Share link: Tables with Arrays"> Share link </button> </h4>PostgreSQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">CREATE TABLE articles ( id SERIAL PRIMARY KEY, title VARCHAR(255), tags TEXT[] ); </code></pre></div>GQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Option 1: Store as list property CREATE (:Article {id: 1, title: 'Graph Databases', tags: ['database', 'graph', 'nosql']}) // Option 2: Create Tag nodes with relationships CREATE (:Article {id: 1, title: 'Graph Databases'}) CREATE (:Tag {name: 'database'}) CREATE (:Tag {name: 'graph'}) MATCH (a:Article {id: 1}), (t:Tag {name: 'database'}) CREATE (a)-[:TAGGED]->(t) </code></pre></div> <h4 id="tables-with-json" class="position-relative d-flex align-items-center group"> Tables with JSON <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="tables-with-json" aria-haspopup="dialog" aria-label="Share link: Tables with JSON"> Share link </button> </h4>PostgreSQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">CREATE TABLE events ( id SERIAL PRIMARY KEY, name VARCHAR(255), metadata JSONB ); </code></pre></div>GQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Option 1: Store as map property CREATE (:Event { id: 1, name: 'PageView', metadata: {browser: 'Chrome', os: 'Windows', version: '120.0'} }) // Option 2: Flatten commonly queried fields CREATE (:Event { id: 1, name: 'PageView', browser: 'Chrome', os: 'Windows', metadata: {version: '120.0'} }) </code></pre></div> <h3 id="foreign-keys-to-relationships" class="position-relative d-flex align-items-center group"> Foreign Keys to Relationships <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="foreign-keys-to-relationships" aria-haspopup="dialog" aria-label="Share link: Foreign Keys to Relationships"> Share link </button> </h3> <h4 id="one-to-many-relationships" class="position-relative d-flex align-items-center group"> One-to-Many Relationships <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="one-to-many-relationships" aria-haspopup="dialog" aria-label="Share link: One-to-Many Relationships"> Share link </button> </h4>PostgreSQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">CREATE TABLE departments ( id SERIAL PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(255), department_id INTEGER REFERENCES departments(id) ); </code></pre></div>GQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Create nodes CREATE (:Department {id: 1, name: 'Engineering'}) CREATE (:Employee {id: 1, name: 'Alice'}) // Create relationship MATCH (e:Employee {id: 1}) MATCH (d:Department {id: 1}) CREATE (e)-[:WORKS_IN]->(d) </code></pre></div> <h4 id="many-to-many-relationships-junction-tables" class="position-relative d-flex align-items-center group"> Many-to-Many Relationships (Junction Tables) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="many-to-many-relationships-junction-tables" aria-haspopup="dialog" aria-label="Share link: Many-to-Many Relationships (Junction Tables)"> Share link </button> </h4>PostgreSQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">CREATE TABLE students ( id SERIAL PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE courses ( id SERIAL PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE enrollments ( student_id INTEGER REFERENCES students(id), course_id INTEGER REFERENCES courses(id), enrolled_at TIMESTAMP DEFAULT NOW(), grade VARCHAR(2), PRIMARY KEY (student_id, course_id) ); </code></pre></div>GQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Junction table becomes relationship with properties CREATE (:Student {id: 1, name: 'Alice'}) CREATE (:Course {id: 1, name: 'Database Systems'}) MATCH (s:Student {id: 1}) MATCH (c:Course {id: 1}) CREATE (s)-[:ENROLLED_IN {enrolled_at: timestamp(), grade: 'A'}]->(c) </code></pre></div> <h4 id="self-referential-relationships" class="position-relative d-flex align-items-center group"> Self-Referential Relationships <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="self-referential-relationships" aria-haspopup="dialog" aria-label="Share link: Self-Referential Relationships"> Share link </button> </h4>PostgreSQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(255), manager_id INTEGER REFERENCES employees(id) ); </code></pre></div>GQL: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE (:Employee {id: 1, name: 'CEO'}) CREATE (:Employee {id: 2, name: 'VP Engineering'}) CREATE (:Employee {id: 3, name: 'Developer'}) MATCH (m:Employee {id: 1}), (e:Employee {id: 2}) CREATE (e)-[:REPORTS_TO]->(m) MATCH (m:Employee {id: 2}), (e:Employee {id: 3}) CREATE (e)-[:REPORTS_TO]->(m) </code></pre></div> <h3 id="sql-to-gql-translation" class="position-relative d-flex align-items-center group"> SQL to GQL Translation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="sql-to-gql-translation" aria-haspopup="dialog" aria-label="Share link: SQL to GQL Translation"> Share link </button> </h3> <h4 id="basic-queries" class="position-relative d-flex align-items-center group"> Basic Queries <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="basic-queries" aria-haspopup="dialog" aria-label="Share link: Basic Queries"> Share link </button> </h4>SELECT: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL SELECT name, email FROM customers WHERE age > 25; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (c:Customer) WHERE c.age > 25 RETURN c.name, c.email </code></pre></div>INSERT: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL INSERT INTO customers (name, email, age) VALUES ('Alice', '[email protected]', 30); </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL CREATE (:Customer {name: 'Alice', email: 'alice@example.com', age: 30}) </code></pre></div>UPDATE: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL UPDATE customers SET age = 31 WHERE name = 'Alice'; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (c:Customer {name: 'Alice'}) SET c.age = 31 </code></pre></div>DELETE: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL DELETE FROM customers WHERE id = 1; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (c:Customer {id: 1}) DELETE c </code></pre></div> <h4 id="joins-to-pattern-matching" class="position-relative d-flex align-items-center group"> Joins to Pattern Matching <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="joins-to-pattern-matching" aria-haspopup="dialog" aria-label="Share link: Joins to Pattern Matching"> Share link </button> </h4>Inner Join: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL SELECT o.id, c.name FROM orders o INNER JOIN customers c ON o.customer_id = c.id; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (c:Customer)-[:PLACED]->(o:Order) RETURN o.id, c.name </code></pre></div>Left Join: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON c.id = o.customer_id; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (c:Customer) OPTIONAL MATCH (c)-[:PLACED]->(o:Order) RETURN c.name, o.id </code></pre></div>Multiple Joins: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL SELECT c.name, p.name, oi.quantity FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (c:Customer)-[:PLACED]->(o:Order)-[item:CONTAINS]->(p:Product) RETURN c.name, p.name, item.quantity </code></pre></div> <h4 id="subqueries" class="position-relative d-flex align-items-center group"> Subqueries <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="subqueries" aria-haspopup="dialog" aria-label="Share link: Subqueries"> Share link </button> </h4>Correlated Subquery: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL SELECT c.name, (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count FROM customers c; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (c:Customer) OPTIONAL MATCH (c)-[:PLACED]->(o:Order) RETURN c.name, count(o) AS order_count </code></pre></div>EXISTS Subquery: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders WHERE customer_id = c.id ); </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (c:Customer)-[:PLACED]->(:Order) RETURN DISTINCT c </code></pre></div> <h4 id="aggregations" class="position-relative d-flex align-items-center group"> Aggregations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="aggregations" aria-haspopup="dialog" aria-label="Share link: Aggregations"> Share link </button> </h4>GROUP BY: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL SELECT category_id, COUNT(*), AVG(price) FROM products GROUP BY category_id; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (p:Product)-[:IN_CATEGORY]->(c:Category) RETURN c.id, count(p), avg(p.price) </code></pre></div>HAVING: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL SELECT customer_id, COUNT(*) as order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 5; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (c:Customer)-[:PLACED]->(o:Order) WITH c, count(o) AS order_count WHERE order_count > 5 RETURN c.id, order_count </code></pre></div> <h4 id="window-functions" class="position-relative d-flex align-items-center group"> Window Functions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="window-functions" aria-haspopup="dialog" aria-label="Share link: Window Functions"> Share link </button> </h4>ROW_NUMBER: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL SELECT name, price, ROW_NUMBER() OVER (ORDER BY price DESC) as rank FROM products; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL (using collect and unwind) MATCH (p:Product) WITH p ORDER BY p.price DESC WITH collect(p) AS products UNWIND range(0, size(products)-1) AS idx RETURN products[idx].name, products[idx].price, idx + 1 AS rank </code></pre></div> <h4 id="common-table-expressions-ctes" class="position-relative d-flex align-items-center group"> Common Table Expressions (CTEs) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="common-table-expressions-ctes" aria-haspopup="dialog" aria-label="Share link: Common Table Expressions (CTEs)"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL WITH high_value_customers AS ( SELECT customer_id, SUM(total) as total_spent FROM orders GROUP BY customer_id HAVING SUM(total) > 1000 ) SELECT c.name, hvc.total_spent FROM customers c JOIN high_value_customers hvc ON c.id = hvc.customer_id; </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL MATCH (c:Customer)-[:PLACED]->(o:Order) WITH c, sum(o.total) AS total_spent WHERE total_spent > 1000 RETURN c.name, total_spent </code></pre></div> <h4 id="recursive-queries" class="position-relative d-flex align-items-center group"> Recursive Queries <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="recursive-queries" aria-haspopup="dialog" aria-label="Share link: Recursive Queries"> Share link </button> </h4>PostgreSQL Recursive CTE: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- SQL: Find all subordinates WITH RECURSIVE subordinates AS ( SELECT id, name, manager_id FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN subordinates s ON e.manager_id = s.id ) SELECT * FROM subordinates; </code></pre></div>GQL Variable-Length Path: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GQL: Much simpler! MATCH (manager:Employee {id: 1})<-[:REPORTS_TO*]-(subordinate) RETURN subordinate.id, subordinate.name </code></pre></div> <h3 id="etl-pipeline-setup" class="position-relative d-flex align-items-center group"> ETL Pipeline Setup <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="etl-pipeline-setup" aria-haspopup="dialog" aria-label="Share link: ETL Pipeline Setup"> Share link </button> </h3> <h4 id="architecture-overview" class="position-relative d-flex align-items-center group"> Architecture Overview <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="architecture-overview" aria-haspopup="dialog" aria-label="Share link: Architecture Overview"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ PostgreSQL │────▶│ ETL Job │────▶│ Geode │ └─────────────┘ └─────────────┘ └─────────────┘ │ │ │ ┌──────┴──────┐ │ │ │ ▼ ▼ ▼ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ Extract │ │Transform│ │ Load │ └─────────┘ └─────────┘ └─────────┘ </code></pre></div> <h4 id="python-etl-script" class="position-relative d-flex align-items-center group"> Python ETL Script <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="python-etl-script" aria-haspopup="dialog" aria-label="Share link: Python ETL Script"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">import asyncio import psycopg from geode_client import Client # Configuration PG_CONFIG = { "host": "localhost", "port": 5432, "user": "postgres", "password": "password", "dbname": "ecommerce" } GEODE_CONFIG = { "host": "localhost", "port": 3141, "skip_verify": True } class PostgresToGeodeETL: def __init__(self): self.pg_conn = None self.geode_client = None async def connect(self): # Connect to PostgreSQL self.pg_conn = await psycopg.AsyncConnection.connect( **PG_CONFIG, autocommit=True ) # Connect to Geode self.geode_client = Client(**GEODE_CONFIG) async def extract_table(self, table_name, batch_size=1000): """Extract data from PostgreSQL table in batches.""" async with self.pg_conn.cursor() as cur: await cur.execute(f"SELECT COUNT(*) FROM {table_name}") total = (await cur.fetchone())[0] for offset in range(0, total, batch_size): await cur.execute( f"SELECT * FROM {table_name} ORDER BY id LIMIT %s OFFSET %s", (batch_size, offset) ) columns = [desc[0] for desc in cur.description] rows = await cur.fetchall() for row in rows: yield dict(zip(columns, row)) async def load_nodes(self, label, data_generator, id_field='id'): """Load nodes into Geode.""" async with self.geode_client.connection() as conn: count = 0 await conn.begin() async for row in data_generator: # Convert types as needed props = self.transform_properties(row) await conn.execute( f"CREATE (n:{label} $props)", {"props": props} ) count += 1 # Commit in batches if count % 1000 == 0: await conn.commit() await conn.begin() print(f"Loaded {count} {label} nodes...") await conn.commit() print(f"Total: {count} {label} nodes loaded") async def load_relationships(self, rel_type, from_label, to_label, from_field, to_field, data_generator): """Load relationships into Geode.""" async with self.geode_client.connection() as conn: count = 0 await conn.begin() async for row in data_generator: props = {k: v for k, v in row.items() if k not in [from_field, to_field]} props = self.transform_properties(props) await conn.execute(f""" MATCH (a:{from_label} {{id: $from_id}}) MATCH (b:{to_label} {{id: $to_id}}) CREATE (a)-[:{rel_type} $props]->(b) """, { "from_id": row[from_field], "to_id": row[to_field], "props": props }) count += 1 if count % 1000 == 0: await conn.commit() await conn.begin() print(f"Loaded {count} {rel_type} relationships...") await conn.commit() print(f"Total: {count} {rel_type} relationships loaded") def transform_properties(self, row): """Transform PostgreSQL types to Geode-compatible types.""" result = {} for key, value in row.items(): if value is None: continue # Skip null values elif isinstance(value, datetime): result[key] = value.isoformat() elif isinstance(value, Decimal): result[key] = float(value) elif isinstance(value, (list, dict)): result[key] = value # Lists and maps are supported else: result[key] = value return result async def run_migration(self): """Run the complete migration.""" await self.connect() # Step 1: Create indexes for efficient relationship creation async with self.geode_client.connection() as conn: await conn.execute("CREATE INDEX customer_id ON :Customer(id)") await conn.execute("CREATE INDEX product_id ON :Product(id)") await conn.execute("CREATE INDEX category_id ON :Category(id)") await conn.execute("CREATE INDEX order_id ON :Order(id)") # Step 2: Load nodes print("\n=== Loading Nodes ===") await self.load_nodes('Customer', self.extract_table('customers')) await self.load_nodes('Category', self.extract_table('categories')) await self.load_nodes('Product', self.extract_table('products')) await self.load_nodes('Order', self.extract_table('orders')) # Step 3: Load relationships print("\n=== Loading Relationships ===") # Product -> Category async with self.pg_conn.cursor() as cur: await cur.execute(""" SELECT id as product_id, category_id FROM products WHERE category_id IS NOT NULL """) columns = [desc[0] for desc in cur.description] rows = await cur.fetchall() async def product_category_gen(): for row in rows: yield dict(zip(columns, row)) await self.load_relationships( 'IN_CATEGORY', 'Product', 'Category', 'product_id', 'category_id', product_category_gen() ) # Category -> Parent Category async with self.pg_conn.cursor() as cur: await cur.execute(""" SELECT id as child_id, parent_id FROM categories WHERE parent_id IS NOT NULL """) columns = [desc[0] for desc in cur.description] rows = await cur.fetchall() async def category_parent_gen(): for row in rows: yield dict(zip(columns, row)) await self.load_relationships( 'PARENT', 'Category', 'Category', 'child_id', 'parent_id', category_parent_gen() ) # Customer -> Order async with self.pg_conn.cursor() as cur: await cur.execute(""" SELECT id as order_id, customer_id FROM orders """) columns = [desc[0] for desc in cur.description] rows = await cur.fetchall() async def customer_order_gen(): for row in rows: yield dict(zip(columns, row)) await self.load_relationships( 'PLACED', 'Customer', 'Order', 'customer_id', 'order_id', customer_order_gen() ) # Order -> Product (from order_items) await self.load_relationships( 'CONTAINS', 'Order', 'Product', 'order_id', 'product_id', self.extract_table('order_items') ) # Customer -> Product (reviews) await self.load_relationships( 'REVIEWED', 'Customer', 'Product', 'customer_id', 'product_id', self.extract_table('reviews') ) print("\n=== Migration Complete ===") # Run migration async def main(): etl = PostgresToGeodeETL() await etl.run_migration() asyncio.run(main()) </code></pre></div> <h4 id="go-etl-script" class="position-relative d-flex align-items-center group"> Go ETL Script <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="go-etl-script" aria-haspopup="dialog" aria-label="Share link: Go ETL Script"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-go" data-lang="go">package main import ( "context" "database/sql" "fmt" "log" _ "github.com/lib/pq" _ "geodedb.com/geode" ) type ETL struct { pg *sql.DB geode *sql.DB } func NewETL(pgConn, geodeConn string) (*ETL, error) { pg, err := sql.Open("postgres", pgConn) if err != nil { return nil, err } geode, err := sql.Open("geode", geodeConn) if err != nil { return nil, err } return &ETL{pg: pg, geode: geode}, nil } func (e *ETL) MigrateTable(ctx context.Context, table, label string) error { // Count rows var count int err := e.pg.QueryRowContext(ctx, fmt.Sprintf("SELECT COUNT(*) FROM %s", table)).Scan(&count) if err != nil { return err } log.Printf("Migrating %d rows from %s to %s", count, table, label) // Query source data rows, err := e.pg.QueryContext(ctx, fmt.Sprintf("SELECT * FROM %s", table)) if err != nil { return err } defer rows.Close() columns, err := rows.Columns() if err != nil { return err } // Begin transaction tx, err := e.geode.BeginTx(ctx, nil) if err != nil { return err } migrated := 0 for rows.Next() { // Scan row values := make([]interface{}, len(columns)) valuePtrs := make([]interface{}, len(columns)) for i := range values { valuePtrs[i] = &values[i] } if err := rows.Scan(valuePtrs...); err != nil { return err } // Build property map props := make(map[string]interface{}) for i, col := range columns { if values[i] != nil { props[col] = values[i] } } // Insert into Geode _, err = tx.ExecContext(ctx, fmt.Sprintf("CREATE (n:%s $props)", label), props, ) if err != nil { tx.Rollback() return err } migrated++ if migrated%1000 == 0 { log.Printf("Migrated %d/%d rows", migrated, count) } } if err := tx.Commit(); err != nil { return err } log.Printf("Completed: %d rows migrated", migrated) return nil } func main() { etl, err := NewETL( "postgres://user:pass@localhost/db?sslmode=disable", "localhost:3141", ) if err != nil { log.Fatal(err) } ctx := context.Background() // Migrate tables tables := map[string]string{ "customers": "Customer", "products": "Product", "categories": "Category", "orders": "Order", } for table, label := range tables { if err := etl.MigrateTable(ctx, table, label); err != nil { log.Fatalf("Failed to migrate %s: %v", table, err) } } log.Println("Migration complete!") } </code></pre></div> <h3 id="incremental-migration" class="position-relative d-flex align-items-center group"> Incremental Migration <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="incremental-migration" aria-haspopup="dialog" aria-label="Share link: Incremental Migration"> Share link </button> </h3> <h4 id="change-data-capture-cdc-setup" class="position-relative d-flex align-items-center group"> Change Data Capture (CDC) Setup <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="change-data-capture-cdc-setup" aria-haspopup="dialog" aria-label="Share link: Change Data Capture (CDC) Setup"> Share link </button> </h4>Use PostgreSQL logical replication to capture changes: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- Enable logical replication in postgresql.conf -- wal_level = logical -- Create replication slot SELECT pg_create_logical_replication_slot('geode_slot', 'pgoutput'); -- Create publication CREATE PUBLICATION geode_pub FOR TABLE customers, products, orders; </code></pre></div> <h4 id="cdc-consumer" class="position-relative d-flex align-items-center group"> CDC Consumer <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="cdc-consumer" aria-haspopup="dialog" aria-label="Share link: CDC Consumer"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">import asyncio import psycopg from psycopg import sql from geode_client import Client class CDCConsumer: def __init__(self, pg_config, geode_config): self.pg_config = pg_config self.geode_config = geode_config self.geode_client = None async def connect(self): self.geode_client = Client(**self.geode_config) async def process_change(self, change): """Process a single CDC change event.""" async with self.geode_client.connection() as conn: table = change['table'] operation = change['operation'] data = change['data'] # Map table to label label = table.title().rstrip('s') # customers -> Customer if operation == 'INSERT': await conn.execute( f"CREATE (n:{label} $props)", {"props": data} ) elif operation == 'UPDATE': set_clause = ', '.join(f"n.{k} = ${k}" for k in data.keys()) await conn.execute( f"MATCH (n:{label} {{id: $id}}) SET {set_clause}", data ) elif operation == 'DELETE': await conn.execute( f"MATCH (n:{label} {{id: $id}}) DELETE n", {"id": data['id']} ) async def start_replication(self): """Start consuming CDC events.""" await self.connect() conn = await psycopg.AsyncConnection.connect( **self.pg_config, autocommit=True ) async with conn.cursor() as cur: # Start replication await cur.execute( "SELECT * FROM pg_logical_slot_get_changes('geode_slot', NULL, NULL)" ) async for row in cur: change = self.parse_change(row) await self.process_change(change) def parse_change(self, row): """Parse PostgreSQL logical replication message.""" # Implementation depends on output plugin format pass # Run CDC consumer async def main(): consumer = CDCConsumer( pg_config={"host": "localhost", "user": "postgres", "dbname": "ecommerce"}, geode_config={"host": "localhost", "port": 3141, "skip_verify": True} ) await consumer.start_replication() asyncio.run(main()) </code></pre></div> <h4 id="dual-write-pattern" class="position-relative d-flex align-items-center group"> Dual-Write Pattern <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="dual-write-pattern" aria-haspopup="dialog" aria-label="Share link: Dual-Write Pattern"> Share link </button> </h4>During migration, write to both databases: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">class DualWriteRepository: def __init__(self, pg_conn, geode_client): self.pg = pg_conn self.geode = geode_client async def create_customer(self, name, email, age): """Write to both PostgreSQL and Geode.""" # Write to PostgreSQL (primary) async with self.pg.cursor() as cur: await cur.execute( "INSERT INTO customers (name, email, age) VALUES (%s, %s, %s) RETURNING id", (name, email, age) ) customer_id = (await cur.fetchone())[0] # Write to Geode (secondary) try: async with self.geode.connection() as conn: await conn.execute( "CREATE (:Customer {id: $id, name: $name, email: $email, age: $age})", {"id": customer_id, "name": name, "email": email, "age": age} ) except Exception as e: # Log error but don't fail - PostgreSQL is source of truth logging.error(f"Failed to write to Geode: {e}") return customer_id async def query_customer_network(self, customer_id, depth=3): """Query from Geode for graph traversals.""" async with self.geode.connection() as conn: page, _ = await conn.query(f""" MATCH (c:Customer {{id: $id}})-[:KNOWS*1..{depth}]->(friend) RETURN DISTINCT friend """, {"id": customer_id}) return page.rows </code></pre></div> <h3 id="hybrid-operation-period" class="position-relative d-flex align-items-center group"> Hybrid Operation Period <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="hybrid-operation-period" aria-haspopup="dialog" aria-label="Share link: Hybrid Operation Period"> Share link </button> </h3> <h4 id="query-routing" class="position-relative d-flex align-items-center group"> Query Routing <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-routing" aria-haspopup="dialog" aria-label="Share link: Query Routing"> Share link </button> </h4>Route queries to the appropriate database: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">class QueryRouter: def __init__(self, pg_conn, geode_client): self.pg = pg_conn self.geode = geode_client # Define which queries go where self.graph_patterns = [ 'KNOWS', 'FRIENDS', 'FOLLOWS', # Social relationships 'PURCHASED', 'VIEWED', # Customer behavior 'REPORTS_TO', 'MANAGES', # Hierarchies ] def should_use_graph(self, query): """Determine if query should go to graph database.""" query_upper = query.upper() # Check for relationship patterns for pattern in self.graph_patterns: if f':{pattern}' in query_upper or f'-[:{pattern}' in query_upper: return True # Check for path queries if '*' in query and ('-[' in query or '->' in query): return True return False async def execute(self, query, params=None): """Route and execute query.""" if self.should_use_graph(query): async with self.geode.connection() as conn: return await conn.query(query, params or {}) else: # Translate to SQL if needed sql_query = self.translate_to_sql(query) async with self.pg.cursor() as cur: await cur.execute(sql_query, params) return await cur.fetchall() </code></pre></div> <h4 id="data-consistency-checks" class="position-relative d-flex align-items-center group"> Data Consistency Checks <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="data-consistency-checks" aria-haspopup="dialog" aria-label="Share link: Data Consistency Checks"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">async def verify_data_consistency(pg_conn, geode_client): """Verify data is consistent between PostgreSQL and Geode.""" discrepancies = [] # Check node counts tables_labels = [ ('customers', 'Customer'), ('products', 'Product'), ('orders', 'Order'), ] for table, label in tables_labels: # PostgreSQL count async with pg_conn.cursor() as cur: await cur.execute(f"SELECT COUNT(*) FROM {table}") pg_count = (await cur.fetchone())[0] # Geode count async with geode_client.connection() as conn: page, _ = await conn.query(f"MATCH (n:{label}) RETURN count(n)") geode_count = page.rows[0]['count(n)'].as_int if pg_count != geode_count: discrepancies.append({ 'type': 'count_mismatch', 'entity': label, 'postgresql': pg_count, 'geode': geode_count }) # Sample data verification async with pg_conn.cursor() as cur: await cur.execute("SELECT id, name, email FROM customers ORDER BY RANDOM() LIMIT 100") samples = await cur.fetchall() async with geode_client.connection() as conn: for pg_id, pg_name, pg_email in samples: page, _ = await conn.query( "MATCH (c:Customer {id: $id}) RETURN c.name, c.email", {"id": pg_id} ) if not page.rows: discrepancies.append({ 'type': 'missing_node', 'entity': 'Customer', 'id': pg_id }) else: row = page.rows[0] if row['c.name'].as_string != pg_name or row['c.email'].as_string != pg_email: discrepancies.append({ 'type': 'data_mismatch', 'entity': 'Customer', 'id': pg_id, 'postgresql': {'name': pg_name, 'email': pg_email}, 'geode': {'name': row['c.name'].as_string, 'email': row['c.email'].as_string} }) return discrepancies </code></pre></div> <h3 id="migration-validation" class="position-relative d-flex align-items-center group"> Migration Validation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="migration-validation" aria-haspopup="dialog" aria-label="Share link: Migration Validation"> Share link </button> </h3> <h4 id="comprehensive-validation-script" class="position-relative d-flex align-items-center group"> Comprehensive Validation Script <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="comprehensive-validation-script" aria-haspopup="dialog" aria-label="Share link: Comprehensive Validation Script"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">async def run_migration_validation(pg_conn, geode_client): """Run comprehensive migration validation.""" print("=" * 60) print("MIGRATION VALIDATION REPORT") print("=" * 60) # 1. Count validation print("\n1. NODE COUNT VALIDATION") print("-" * 40) entities = [ ('customers', 'Customer'), ('products', 'Product'), ('categories', 'Category'), ('orders', 'Order'), ] all_counts_match = True for table, label in entities: async with pg_conn.cursor() as cur: await cur.execute(f"SELECT COUNT(*) FROM {table}") pg_count = (await cur.fetchone())[0] async with geode_client.connection() as conn: page, _ = await conn.query(f"MATCH (n:{label}) RETURN count(n) AS c") geode_count = page.rows[0]['c'].as_int status = "PASS" if pg_count == geode_count else "FAIL" if status == "FAIL": all_counts_match = False print(f" {label}: PG={pg_count}, Geode={geode_count} [{status}]") # 2. Relationship count validation print("\n2. RELATIONSHIP COUNT VALIDATION") print("-" * 40) relationships = [ ("SELECT COUNT(*) FROM order_items", "MATCH ()-[r:CONTAINS]->() RETURN count(r)"), ("SELECT COUNT(*) FROM reviews", "MATCH ()-[r:REVIEWED]->() RETURN count(r)"), ("SELECT COUNT(*) FROM products WHERE category_id IS NOT NULL", "MATCH ()-[r:IN_CATEGORY]->() RETURN count(r)"), ] for pg_query, gql_query in relationships: async with pg_conn.cursor() as cur: await cur.execute(pg_query) pg_count = (await cur.fetchone())[0] async with geode_client.connection() as conn: page, _ = await conn.query(gql_query) geode_count = page.rows[0]['count(r)'].as_int rel_type = gql_query.split(':')[1].split(']')[0] status = "PASS" if pg_count == geode_count else "FAIL" print(f" {rel_type}: PG={pg_count}, Geode={geode_count} [{status}]") # 3. Data integrity validation print("\n3. DATA INTEGRITY VALIDATION") print("-" * 40) # Check referential integrity async with geode_client.connection() as conn: # Orders should have customers page, _ = await conn.query(""" MATCH (o:Order) WHERE NOT (o)<-[:PLACED]-(:Customer) RETURN count(o) AS orphaned """) orphaned_orders = page.rows[0]['orphaned'].as_int print(f" Orphaned orders: {orphaned_orders} [{'FAIL' if orphaned_orders > 0 else 'PASS'}]") # Order items should have products page, _ = await conn.query(""" MATCH (o:Order)-[c:CONTAINS]->() WHERE NOT ()-[c]->(:Product) RETURN count(c) AS orphaned """) orphaned_items = page.rows[0]['orphaned'].as_int print(f" Orphaned order items: {orphaned_items} [{'FAIL' if orphaned_items > 0 else 'PASS'}]") # 4. Query equivalence validation print("\n4. QUERY EQUIVALENCE VALIDATION") print("-" * 40) test_queries = [ { 'name': 'Top customers by order count', 'sql': """ SELECT c.name, COUNT(o.id) as order_count FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name ORDER BY order_count DESC LIMIT 5 """, 'gql': """ MATCH (c:Customer) OPTIONAL MATCH (c)-[:PLACED]->(o:Order) RETURN c.name, count(o) AS order_count ORDER BY order_count DESC LIMIT 5 """ }, { 'name': 'Products by category', 'sql': """ SELECT cat.name, COUNT(p.id) FROM categories cat LEFT JOIN products p ON p.category_id = cat.id GROUP BY cat.id, cat.name """, 'gql': """ MATCH (cat:Category) OPTIONAL MATCH (p:Product)-[:IN_CATEGORY]->(cat) RETURN cat.name, count(p) """ } ] for test in test_queries: async with pg_conn.cursor() as cur: await cur.execute(test['sql']) pg_results = await cur.fetchall() async with geode_client.connection() as conn: page, _ = await conn.query(test['gql']) geode_results = [(r[0].as_string, r[1].as_int) for r in [(row.values()[0], row.values()[1]) for row in page.rows]] # Compare first few results match = len(pg_results) == len(geode_results) print(f" {test['name']}: [{'PASS' if match else 'FAIL'}]") print("\n" + "=" * 60) print("VALIDATION COMPLETE") print("=" * 60) </code></pre></div> <h3 id="common-pitfalls" class="position-relative d-flex align-items-center group"> Common Pitfalls <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="common-pitfalls" aria-haspopup="dialog" aria-label="Share link: Common Pitfalls"> Share link </button> </h3> <h4 id="1-losing-referential-integrity" class="position-relative d-flex align-items-center group"> 1. Losing Referential Integrity <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="1-losing-referential-integrity" aria-haspopup="dialog" aria-label="Share link: 1. Losing Referential Integrity"> Share link </button> </h4>Problem: Foreign key constraints aren’t automatically enforced. Solution: Create application-level validation or use Geode constraints. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Create constraints CREATE CONSTRAINT customer_id_unique ON :Customer(id) ASSERT UNIQUE CREATE CONSTRAINT order_customer_exists ON :Order(customer_id) ASSERT EXISTS </code></pre></div> <h4 id="2-n1-query-pattern" class="position-relative d-flex align-items-center group"> 2. N+1 Query Pattern <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="2-n1-query-pattern" aria-haspopup="dialog" aria-label="Share link: 2. N&#43;1 Query Pattern"> Share link </button> </h4>Problem: Fetching related data with multiple queries. Solution: Use graph patterns to fetch all related data at once. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Instead of multiple queries // Query 1: Get customer // Query 2: Get orders // Query 3: Get products for each order // Use a single query MATCH (c:Customer {id: $id})-[:PLACED]->(o:Order)-[:CONTAINS]->(p:Product) RETURN c, o, p </code></pre></div> <h4 id="3-missing-indexes" class="position-relative d-flex align-items-center group"> 3. Missing Indexes <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="3-missing-indexes" aria-haspopup="dialog" aria-label="Share link: 3. Missing Indexes"> Share link </button> </h4>Problem: Slow queries due to missing indexes. Solution: Create indexes for frequently queried properties. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX customer_email ON :Customer(email) CREATE INDEX product_name ON :Product(name) CREATE INDEX order_date ON :Order(order_date) </code></pre></div> <h4 id="4-over-normalization" class="position-relative d-flex align-items-center group"> 4. Over-normalization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="4-over-normalization" aria-haspopup="dialog" aria-label="Share link: 4. Over-normalization"> Share link </button> </h4>Problem: Creating too many node types for simple values. Solution: Use properties for simple values, nodes for entities. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Bad: Over-normalized (:Customer)-[:HAS_EMAIL]->(:Email {address: 'alice@example.com'}) // Good: Property (:Customer {email: 'alice@example.com'}) </code></pre></div> <h4 id="5-ignoring-null-semantics" class="position-relative d-flex align-items-center group"> 5. Ignoring NULL Semantics <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="5-ignoring-null-semantics" aria-haspopup="dialog" aria-label="Share link: 5. Ignoring NULL Semantics"> Share link </button> </h4>Problem: Different NULL handling between SQL and GQL. Solution: Use COALESCE and explicit NULL checks. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (c:Customer) WHERE c.middle_name IS NOT NULL RETURN c.name, COALESCE(c.nickname, c.name) AS display_name </code></pre></div> <h3 id="migration-checklist" class="position-relative d-flex align-items-center group"> Migration Checklist <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="migration-checklist" aria-haspopup="dialog" aria-label="Share link: Migration Checklist"> Share link </button> </h3> <h4 id="pre-migration" class="position-relative d-flex align-items-center group"> Pre-Migration <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="pre-migration" aria-haspopup="dialog" aria-label="Share link: Pre-Migration"> Share link </button> </h4><ul> <li><input disabled="" type="checkbox"> Analyze PostgreSQL schema complexity</li> <li><input disabled="" type="checkbox"> Identify tables suitable for graph modeling</li> <li><input disabled="" type="checkbox"> Document all foreign key relationships</li> <li><input disabled="" type="checkbox"> Assess query patterns and performance requirements</li> <li><input disabled="" type="checkbox"> Plan hybrid operation period</li> </ul> <h4 id="schema-migration" class="position-relative d-flex align-items-center group"> Schema Migration <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="schema-migration" aria-haspopup="dialog" aria-label="Share link: Schema Migration"> Share link </button> </h4><ul> <li><input disabled="" type="checkbox"> Design node labels and properties</li> <li><input disabled="" type="checkbox"> Design relationship types and properties</li> <li><input disabled="" type="checkbox"> Create Geode schema (indexes, constraints)</li> <li><input disabled="" type="checkbox"> Document schema mapping</li> </ul> <h4 id="data-migration" class="position-relative d-flex align-items-center group"> Data Migration <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="data-migration" aria-haspopup="dialog" aria-label="Share link: Data Migration"> Share link </button> </h4><ul> <li><input disabled="" type="checkbox"> Create ETL pipeline</li> <li><input disabled="" type="checkbox"> Test with sample data</li> <li><input disabled="" type="checkbox"> Run full data migration</li> <li><input disabled="" type="checkbox"> Validate row/node counts</li> <li><input disabled="" type="checkbox"> Validate relationship counts</li> </ul> <h4 id="application-migration" class="position-relative d-flex align-items-center group"> Application Migration <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="application-migration" aria-haspopup="dialog" aria-label="Share link: Application Migration"> Share link </button> </h4><ul> <li><input disabled="" type="checkbox"> Update data access layer</li> <li><input disabled="" type="checkbox"> Translate SQL queries to GQL</li> <li><input disabled="" type="checkbox"> Implement dual-write if needed</li> <li><input disabled="" type="checkbox"> Update connection configuration</li> <li><input disabled="" type="checkbox"> Test all CRUD operations</li> </ul> <h4 id="validation" class="position-relative d-flex align-items-center group"> Validation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="validation" aria-haspopup="dialog" aria-label="Share link: Validation"> Share link </button> </h4><ul> <li><input disabled="" type="checkbox"> Run data consistency checks</li> <li><input disabled="" type="checkbox"> Execute query equivalence tests</li> <li><input disabled="" type="checkbox"> Perform load testing</li> <li><input disabled="" type="checkbox"> Verify backup/restore procedures</li> </ul> <h4 id="cutover" class="position-relative d-flex align-items-center group"> Cutover <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="cutover" aria-haspopup="dialog" aria-label="Share link: Cutover"> Share link </button> </h4><ul> <li><input disabled="" type="checkbox"> Plan maintenance window</li> <li><input disabled="" type="checkbox"> Prepare rollback procedure</li> <li><input disabled="" type="checkbox"> Execute final data sync</li> <li><input disabled="" type="checkbox"> Switch application to Geode</li> <li><input disabled="" type="checkbox"> Monitor for errors</li> <li><input disabled="" type="checkbox"> Decommission PostgreSQL integration</li> </ul> <h3 id="resources" class="position-relative d-flex align-items-center group"> Resources <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="resources" aria-haspopup="dialog" aria-label="Share link: Resources"> Share link </button> </h3><ul> <li><a href="/docs/gql-reference/" >GQL Reference</a> </li> <li><a href="/guides/graph-modeling/" >Graph Modeling Guide</a> </li> <li><a href="/guides/query-performance/" >Query Performance</a> </li> <li><a href="/clients/" >Client Libraries</a> </li> </ul> <h3 id="getting-help" class="position-relative d-flex align-items-center group"> Getting Help <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="getting-help" aria-haspopup="dialog" aria-label="Share link: Getting Help"> Share link </button> </h3>If you encounter issues during migration: <ul> <li><a href="https://docs.geodedb.com" aria-label="Documentation – opens in new window" target="_blank" rel="noopener noreferrer" >Documentation ↗ </a> </li> <li><a href="https://gitlab.com/devnw/codepros/geode/geode/-/issues" aria-label="GitLab Issues – opens in new window" target="_blank" rel="noopener noreferrer" >GitLab Issues ↗ </a> </li> <li><a href="https://forum.geodedb.com" aria-label="Community Forum – opens in new window" target="_blank" rel="noopener noreferrer" >Community Forum ↗ </a> </li> <li><a href="mailto:[email protected]" >Email Support</a> </li> </ul>

Related Guides

Migrating from Neo4j to Geode

Migration Guide