Stored Procedures - Server-Side GQL Programs | Tags

<h2 id="stored-procedures" class="position-relative d-flex align-items-center group"> Stored Procedures <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="stored-procedures" aria-haspopup="dialog" aria-label="Share link: Stored Procedures"> 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>Stored procedures are reusable, server-side programs written in GQL that encapsulate complex database logic within the database engine. They enable code reuse across applications, improve performance by reducing network roundtrips, enhance security through controlled data access, simplify maintenance by centralizing business logic, and provide transactional consistency for multi-step operations. <h3 id="stored-procedure-fundamentals" class="position-relative d-flex align-items-center group"> Stored Procedure Fundamentals <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="stored-procedure-fundamentals" aria-haspopup="dialog" aria-label="Share link: Stored Procedure Fundamentals"> Share link </button> </h3> <h4 id="what-are-stored-procedures" class="position-relative d-flex align-items-center group"> What are Stored Procedures? <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="what-are-stored-procedures" aria-haspopup="dialog" aria-label="Share link: What are Stored Procedures?"> Share link </button> </h4>Stored procedures are named GQL programs that: Execute Server-Side - Run within the database Accept Parameters - Parameterized for reuse Return Results - Tables, scalars, or status codes Encapsulate Logic - Hide complexity from clients <h4 id="benefits" class="position-relative d-flex align-items-center group"> Benefits <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="benefits" aria-haspopup="dialog" aria-label="Share link: Benefits"> Share link </button> </h4><ul> <li>Performance - Reduced network roundtrips</li> <li>Reusability - Write once, call many times</li> <li>Security - Control data access</li> <li>Maintainability - Centralize business logic</li> <li>Consistency - Ensure uniform data operations</li> </ul> <h3 id="creating-stored-procedures" class="position-relative d-flex align-items-center group"> Creating Stored Procedures <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="creating-stored-procedures" aria-haspopup="dialog" aria-label="Share link: Creating Stored Procedures"> Share link </button> </h3> <h4 id="basic-syntax" class="position-relative d-flex align-items-center group"> Basic Syntax <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-syntax" aria-haspopup="dialog" aria-label="Share link: Basic Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Simple procedure CREATE PROCEDURE get_user_by_id(user_id STRING) RETURNS TABLE ( id STRING, name STRING, email STRING ) LANGUAGE GQL AS $$ BEGIN RETURN QUERY MATCH (u:User {id: user_id}) RETURN u.id, u.name, u.email; END; $$; -- Call procedure CALL get_user_by_id('user123'); </code></pre></div> <h4 id="parameters" class="position-relative d-flex align-items-center group"> Parameters <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="parameters" aria-haspopup="dialog" aria-label="Share link: Parameters"> Share link </button> </h4>Support multiple parameter types: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE PROCEDURE search_users( min_age INTEGER, max_age INTEGER DEFAULT 100, name_pattern STRING DEFAULT '%' ) RETURNS TABLE (id STRING, name STRING, age INTEGER) LANGUAGE GQL AS $$ BEGIN RETURN QUERY MATCH (u:User) WHERE u.age >= min_age AND u.age <= max_age AND u.name LIKE name_pattern RETURN u.id, u.name, u.age ORDER BY u.name; END; $$; -- Call with all parameters CALL search_users(18, 30, 'A%'); -- Call with defaults CALL search_users(21); </code></pre></div> <h3 id="complex-procedures" class="position-relative d-flex align-items-center group"> Complex Procedures <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="complex-procedures" aria-haspopup="dialog" aria-label="Share link: Complex Procedures"> Share link </button> </h3> <h4 id="transaction-management" class="position-relative d-flex align-items-center group"> Transaction Management <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="transaction-management" aria-haspopup="dialog" aria-label="Share link: Transaction Management"> Share link </button> </h4>Control transactions: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE PROCEDURE transfer_funds( from_account STRING, to_account STRING, amount DECIMAL ) RETURNS BOOLEAN LANGUAGE GQL AS $$ DECLARE from_balance DECIMAL; BEGIN -- Start transaction BEGIN TRANSACTION; -- Check balance SELECT balance INTO from_balance FROM Account WHERE id = from_account FOR UPDATE; IF from_balance < amount THEN ROLLBACK; RETURN FALSE; END IF; -- Debit source UPDATE Account SET balance = balance - amount WHERE id = from_account; -- Credit destination UPDATE Account SET balance = balance + amount WHERE id = to_account; COMMIT; RETURN TRUE; END; $$; </code></pre></div> <h4 id="control-flow" class="position-relative d-flex align-items-center group"> Control Flow <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="control-flow" aria-haspopup="dialog" aria-label="Share link: Control Flow"> Share link </button> </h4>Use conditionals and loops: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE PROCEDURE calculate_bonus(employee_id STRING) RETURNS DECIMAL LANGUAGE GQL AS $$ DECLARE salary DECIMAL; years_employed INTEGER; bonus DECIMAL; BEGIN -- Get employee data SELECT e.salary, e.years_employed INTO salary, years_employed FROM Employee e WHERE e.id = employee_id; -- Calculate base bonus bonus := salary * 0.1; -- Apply multipliers IF years_employed >= 10 THEN bonus := bonus * 1.5; ELSIF years_employed >= 5 THEN bonus := bonus * 1.25; END IF; RETURN bonus; END; $$; </code></pre></div> <h3 id="calling-procedures" class="position-relative d-flex align-items-center group"> Calling Procedures <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="calling-procedures" aria-haspopup="dialog" aria-label="Share link: Calling Procedures"> Share link </button> </h3> <h4 id="from-gql" class="position-relative d-flex align-items-center group"> From GQL <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="from-gql" aria-haspopup="dialog" aria-label="Share link: From GQL"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Simple call CALL get_user_by_id('user123'); -- With results SELECT * FROM get_user_by_id('user123'); </code></pre></div> <h4 id="from-client-libraries" class="position-relative d-flex align-items-center group"> From Client Libraries <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="from-client-libraries" aria-haspopup="dialog" aria-label="Share link: From Client Libraries"> Share link </button> </h4>Python: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Call procedure result = await client.call_procedure( 'search_users', min_age=18, max_age=30 ) for row in result.rows: print(row['name'], row['age']) </code></pre></div>Go: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-go" data-lang="go">// Call procedure rows, err := db.Query("CALL search_users($1, $2)", 18, 30) if err != nil { log.Fatal(err) } defer rows.Close() for rows.Next() { var id, name string var age int rows.Scan(&id, &name, &age) fmt.Printf("%s: %d\n", name, age) } </code></pre></div> <h3 id="advanced-patterns" class="position-relative d-flex align-items-center group"> Advanced Patterns <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="advanced-patterns" aria-haspopup="dialog" aria-label="Share link: Advanced Patterns"> Share link </button> </h3> <h4 id="dynamic-query-construction" class="position-relative d-flex align-items-center group"> Dynamic Query Construction <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="dynamic-query-construction" aria-haspopup="dialog" aria-label="Share link: Dynamic Query Construction"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE PROCEDURE search_entities( entity_type STRING, filter_conditions MAP<STRING, ANY>, sort_field STRING DEFAULT 'name', limit_count INTEGER DEFAULT 100 ) RETURNS TABLE (entity MAP<STRING, ANY>) LANGUAGE GQL AS $$ DECLARE query STRING; where_clause STRING := ''; BEGIN -- Build WHERE clause dynamically FOR key, value IN filter_conditions LOOP IF where_clause <> '' THEN where_clause := where_clause || ' AND '; END IF; where_clause := where_clause || key || ' = $' || key; END LOOP; -- Construct dynamic query query := 'MATCH (n:' || entity_type || ') '; IF where_clause <> '' THEN query := query || 'WHERE ' || where_clause || ' '; END IF; query := query || 'RETURN n AS entity ORDER BY n.' || sort_field || ' LIMIT ' || limit_count; -- Execute dynamic query RETURN QUERY EXECUTE query USING filter_conditions; END; $$; </code></pre></div> <h4 id="batch-processing" class="position-relative d-flex align-items-center group"> Batch Processing <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="batch-processing" aria-haspopup="dialog" aria-label="Share link: Batch Processing"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE PROCEDURE batch_update_prices( price_adjustments MAP<STRING, DECIMAL> ) RETURNS TABLE ( product_id STRING, old_price DECIMAL, new_price DECIMAL ) LANGUAGE GQL AS $$ DECLARE pid STRING; adjustment DECIMAL; batch_size INTEGER := 100; processed INTEGER := 0; BEGIN FOR pid, adjustment IN price_adjustments LOOP UPDATE Product SET price = price * adjustment, updated_at = NOW() WHERE id = pid RETURNING id AS product_id, price / adjustment AS old_price, price AS new_price; processed := processed + 1; -- Commit in batches IF processed % batch_size = 0 THEN COMMIT; BEGIN; END IF; END LOOP; COMMIT; END; $$; </code></pre></div> <h4 id="recursive-procedures" class="position-relative d-flex align-items-center group"> Recursive Procedures <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-procedures" aria-haspopup="dialog" aria-label="Share link: Recursive Procedures"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE PROCEDURE calculate_org_hierarchy(root_id STRING, max_depth INTEGER DEFAULT 10) RETURNS TABLE ( node_id STRING, node_name STRING, level INTEGER, path STRING ) LANGUAGE GQL AS $$ BEGIN RETURN QUERY WITH RECURSIVE hierarchy AS ( -- Base case SELECT e.id AS node_id, e.name AS node_name, 0 AS level, CAST(e.id AS STRING) AS path FROM Employee e WHERE e.id = root_id UNION ALL -- Recursive case SELECT e.id, e.name, h.level + 1, h.path || '/' || e.id FROM Employee e JOIN hierarchy h ON e.manager_id = h.node_id WHERE h.level < max_depth ) SELECT * FROM hierarchy ORDER BY level, node_name; END; $$; </code></pre></div> <h3 id="versioning-and-migration" class="position-relative d-flex align-items-center group"> Versioning and 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="versioning-and-migration" aria-haspopup="dialog" aria-label="Share link: Versioning and Migration"> Share link </button> </h3> <h4 id="version-management" class="position-relative d-flex align-items-center group"> Version Management <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="version-management" aria-haspopup="dialog" aria-label="Share link: Version Management"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Version 1 CREATE OR REPLACE PROCEDURE calculate_discount_v1( customer_id STRING, order_total DECIMAL ) RETURNS DECIMAL AS $$ BEGIN RETURN order_total * 0.1; -- Simple 10% discount END; $$; -- Version 2: Enhanced logic CREATE OR REPLACE PROCEDURE calculate_discount_v2( customer_id STRING, order_total DECIMAL ) RETURNS DECIMAL AS $$ DECLARE customer_tier STRING; discount_rate DECIMAL; BEGIN -- Get customer tier SELECT tier INTO customer_tier FROM Customer WHERE id = customer_id; -- Tiered discounts discount_rate := CASE customer_tier WHEN 'platinum' THEN 0.20 WHEN 'gold' THEN 0.15 WHEN 'silver' THEN 0.10 ELSE 0.05 END; RETURN order_total * discount_rate; END; $$; -- Create alias for current version CREATE OR REPLACE PROCEDURE calculate_discount( customer_id STRING, order_total DECIMAL ) RETURNS DECIMAL AS $$ BEGIN RETURN calculate_discount_v2(customer_id, order_total); END; $$; </code></pre></div> <h4 id="deprecation-strategy" class="position-relative d-flex align-items-center group"> Deprecation Strategy <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="deprecation-strategy" aria-haspopup="dialog" aria-label="Share link: Deprecation Strategy"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE PROCEDURE old_procedure(param STRING) AS $$ BEGIN -- Log deprecation warning RAISE WARNING 'Procedure old_procedure is deprecated. Use new_procedure instead.'; -- Forward to new implementation RETURN new_procedure(param); END; $$; </code></pre></div> <h3 id="monitoring-and-debugging" class="position-relative d-flex align-items-center group"> Monitoring and Debugging <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="monitoring-and-debugging" aria-haspopup="dialog" aria-label="Share link: Monitoring and Debugging"> Share link </button> </h3> <h4 id="execution-logging" class="position-relative d-flex align-items-center group"> Execution Logging <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="execution-logging" aria-haspopup="dialog" aria-label="Share link: Execution Logging"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE PROCEDURE process_order_with_logging(order_id STRING) RETURNS BOOLEAN AS $$ DECLARE start_time TIMESTAMP; end_time TIMESTAMP; rows_affected INTEGER; BEGIN start_time := NOW(); -- Log procedure start INSERT INTO procedure_log ( procedure_name, parameters, start_time, status ) VALUES ( 'process_order_with_logging', json_build_object('order_id', order_id), start_time, 'running' ); -- Main logic UPDATE Order SET status = 'processing' WHERE id = order_id; GET DIAGNOSTICS rows_affected = ROW_COUNT; -- Process order steps... end_time := NOW(); -- Log completion UPDATE procedure_log SET end_time = end_time, duration_ms = EXTRACT(MILLISECONDS FROM (end_time - start_time)), rows_affected = rows_affected, status = 'completed' WHERE procedure_name = 'process_order_with_logging' AND start_time = start_time; RETURN TRUE; EXCEPTION WHEN OTHERS THEN -- Log error UPDATE procedure_log SET end_time = NOW(), status = 'failed', error_message = SQLERRM WHERE procedure_name = 'process_order_with_logging' AND start_time = start_time; RAISE; END; $$; </code></pre></div> <h4 id="performance-profiling" class="position-relative d-flex align-items-center group"> Performance Profiling <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="performance-profiling" aria-haspopup="dialog" aria-label="Share link: Performance Profiling"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Query procedure statistics SELECT procedure_name, call_count, total_time_ms, avg_time_ms, min_time_ms, max_time_ms, last_called FROM system.procedure_stats WHERE procedure_name LIKE 'process_%' ORDER BY total_time_ms DESC; -- Identify slow procedures SELECT procedure_name, avg_time_ms, call_count FROM system.procedure_stats WHERE avg_time_ms > 1000 -- Slower than 1 second ORDER BY avg_time_ms DESC; </code></pre></div> <h3 id="testing-stored-procedures" class="position-relative d-flex align-items-center group"> Testing Stored Procedures <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="testing-stored-procedures" aria-haspopup="dialog" aria-label="Share link: Testing Stored Procedures"> Share link </button> </h3> <h4 id="unit-testing" class="position-relative d-flex align-items-center group"> Unit Testing <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="unit-testing" aria-haspopup="dialog" aria-label="Share link: Unit Testing"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Test procedure CREATE PROCEDURE test_calculate_discount() RETURNS BOOLEAN AS $$ DECLARE result DECIMAL; expected DECIMAL; BEGIN -- Test case 1: Regular customer result := calculate_discount('cust_123', 100.00); expected := 10.00; IF result <> expected THEN RAISE EXCEPTION 'Test failed: Expected %, got %', expected, result; END IF; -- Test case 2: Platinum customer result := calculate_discount('cust_platinum', 100.00); expected := 20.00; IF result <> expected THEN RAISE EXCEPTION 'Test failed: Expected %, got %', expected, result; END IF; -- All tests passed RETURN TRUE; END; $$; -- Run tests CALL test_calculate_discount(); </code></pre></div> <h4 id="integration-testing" class="position-relative d-flex align-items-center group"> Integration Testing <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="integration-testing" aria-haspopup="dialog" aria-label="Share link: Integration Testing"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Python integration tests import pytest from geode_client import Client @pytest.mark.asyncio async def test_process_order_procedure(): client = Client(host="localhost", port=3141) async with client.connection() as conn: # Setup test data await conn.execute(""" CREATE (o:Order { id: 'test_order_1', status: 'pending', total: 150.00 }) """) # Call procedure result, _ = await conn.query("CALL process_order('test_order_1')") # Verify results order = await conn.execute(""" MATCH (o:Order {id: 'test_order_1'}) RETURN o.status, o.processed_at """) assert order[0]['o.status'] == 'completed' assert order[0]['o.processed_at'] is not None # Cleanup await conn.execute("MATCH (o:Order {id: 'test_order_1'}) DELETE o") </code></pre></div> <h3 id="best-practices" class="position-relative d-flex align-items-center group"> Best Practices <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="best-practices" aria-haspopup="dialog" aria-label="Share link: Best Practices"> Share link </button> </h3> <h4 id="design-principles" class="position-relative d-flex align-items-center group"> Design Principles <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="design-principles" aria-haspopup="dialog" aria-label="Share link: Design Principles"> Share link </button> </h4><ol> <li>Single Responsibility: Each procedure does one thing well</li> <li>Clear Naming: Use descriptive verb-noun names (calculate_discount, process_order)</li> <li>Parameter Validation: Validate all inputs at procedure start</li> <li>Error Handling: Comprehensive exception handling with meaningful messages</li> <li>Documentation: Document parameters, return values, and business logic</li> <li>Version Control: Version procedures and maintain backwards compatibility</li> <li>Idempotency: Design procedures to be safely re-executed</li> </ol> <h4 id="performance-guidelines" class="position-relative d-flex align-items-center group"> Performance Guidelines <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="performance-guidelines" aria-haspopup="dialog" aria-label="Share link: Performance Guidelines"> Share link </button> </h4><ul> <li>Minimize Locks: Keep transactions short</li> <li>Use Indexes: Ensure queries within procedures use indexes</li> <li>Batch Operations: Process multiple items in single transaction</li> <li>Avoid Cursors: Use set-based operations when possible</li> <li>Cache Results: Store expensive calculations in temp tables</li> <li>Monitor Metrics: Track execution time and resource usage</li> </ul> <h4 id="security-best-practices" class="position-relative d-flex align-items-center group"> Security Best Practices <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="security-best-practices" aria-haspopup="dialog" aria-label="Share link: Security Best Practices"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE PROCEDURE secure_update_salary( employee_id STRING, new_salary DECIMAL, updater_id STRING ) RETURNS BOOLEAN AS $$ DECLARE updater_role STRING; BEGIN -- Authorization check SELECT role INTO updater_role FROM User WHERE id = updater_id; IF updater_role NOT IN ('admin', 'hr_manager') THEN RAISE EXCEPTION 'Unauthorized: Only admins and HR managers can update salaries'; END IF; -- Input validation IF new_salary < 0 OR new_salary > 1000000 THEN RAISE EXCEPTION 'Invalid salary: Must be between 0 and 1,000,000'; END IF; -- Audit logging INSERT INTO audit_log ( action, table_name, record_id, performed_by, timestamp ) VALUES ( 'UPDATE_SALARY', 'Employee', employee_id, updater_id, NOW() ); -- Perform update UPDATE Employee SET salary = new_salary, updated_at = NOW(), updated_by = updater_id WHERE id = employee_id; RETURN TRUE; END; $$; </code></pre></div> <h3 id="troubleshooting" class="position-relative d-flex align-items-center group"> Troubleshooting <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="troubleshooting" aria-haspopup="dialog" aria-label="Share link: Troubleshooting"> Share link </button> </h3> <h4 id="common-issues" class="position-relative d-flex align-items-center group"> Common Issues <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-issues" aria-haspopup="dialog" aria-label="Share link: Common Issues"> Share link </button> </h4>Problem: Procedure is slow <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Solution: Profile and optimize EXPLAIN ANALYZE CALL my_procedure('param'); -- Check for missing indexes SELECT * FROM system.procedure_stats WHERE procedure_name = 'my_procedure'; </code></pre></div>Problem: Deadlocks in concurrent execution <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Solution: Use explicit lock ordering CREATE PROCEDURE safe_transfer(from_id STRING, to_id STRING, amount DECIMAL) AS $$ DECLARE first_id STRING; second_id STRING; BEGIN -- Always lock in same order to prevent deadlock first_id := LEAST(from_id, to_id); second_id := GREATEST(from_id, to_id); -- Lock accounts in deterministic order SELECT * FROM Account WHERE id = first_id FOR UPDATE; SELECT * FROM Account WHERE id = second_id FOR UPDATE; -- Perform transfer -- ... END; $$; </code></pre></div> <h3 id="related-topics" class="position-relative d-flex align-items-center group"> Related Topics <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="related-topics" aria-haspopup="dialog" aria-label="Share link: Related Topics"> Share link </button> </h3><ul> <li><a href="/tags/functions/" >Functions</a> - User-defined functions</li> <li><a href="/tags/transactions/" >Transactions</a> - Transaction management</li> <li><a href="/tags/security/" >Security</a> - Access control and authorization</li> <li><a href="/tags/performance/" >Performance</a> - Query optimization</li> </ul> <h3 id="further-reading" class="position-relative d-flex align-items-center group"> Further Reading <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="further-reading" aria-haspopup="dialog" aria-label="Share link: Further Reading"> Share link </button> </h3><ul> <li><a href="https://www.postgresql.org/docs/current/sql-createprocedure.html" aria-label="PostgreSQL Stored Procedures – opens in new window" target="_blank" rel="noopener noreferrer" >PostgreSQL Stored Procedures ↗ </a> - PostgreSQL documentation</li> <li><a href="https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine" aria-label="T-SQL Procedures – opens in new window" target="_blank" rel="noopener noreferrer" >T-SQL Procedures ↗ </a> - SQL Server guide</li> <li><a href="https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/CREATE-PROCEDURE-statement.html" aria-label="PL/SQL Procedures – opens in new window" target="_blank" rel="noopener noreferrer" >PL/SQL Procedures ↗ </a> - Oracle documentation</li> </ul>

Popular

Stored Procedures - Server-Side GQL Programs

Related Articles