{"uid":"cap_TAIoTUb1D5PSYupqba9Uo","slug":"saylor-innovations-guide-library-sqlite-cookbook-466f8c58","name":"Saylor Innovations Guide Library – SQLite Cookbook","description":"SQLite cookbook - ON CONFLICT upsert vs INSERT OR REPLACE, recursive CTE hierarchies, json_extract and json_each, date modifiers, NULL order","url":"https://saylorinnovations.com/api/kb/sqlite-upsert-recursive-cte-json-functions-date-math?utm_source=zero.xyz","method":"GET","headers":{},"bodySchema":{"type":"object","$schema":"https://json-schema.org/draft/2020-12/schema","required":["input"],"properties":{"input":{"type":"object","required":["type","method"],"properties":{"type":{"type":"string","const":"http"},"method":{"enum":["GET"],"type":"string"}},"additionalProperties":false},"output":{"type":"object","required":["type"],"properties":{"type":{"type":"string","const":"json"},"example":{"type":"object","required":["id","t","c","body"],"properties":{"c":{"type":"string","description":"Category of the guide"},"t":{"type":"string","description":"Title of the guide"},"id":{"type":"string","description":"Identifier of the record"},"body":{"type":"string","description":"Main body text"},"price":{"type":["number","null"],"description":"Price"},"license":{"type":"string","description":"License identifier"}}}}}}},"responseSchema":null,"example":null,"exampleRequest":null,"tags":["x402"],"displayCostAmount":"0.01","displayCostAsset":"USDC","priceDynamic":false,"priceHint":null,"priceStatus":"priced","priceSource":"probe","requiresHandshake":false,"reviewCount":0,"rating":{"score":"0.00","successRate":"0.00","reviews":0,"stars":null,"state":"unrated"},"availabilityStatus":"unknown","priceObserved":null,"sessionDeposit":null,"pricing":{"kind":"static","summary":"$0.01/call","primary":{"kind":"static","protocol":"x402","network":"base","amountUsd":"0.01","per":"call","confidence":"exact"},"accepted":[{"kind":"static","protocol":"x402","network":"base","amountUsd":"0.01","per":"call","confidence":"exact"}]},"paymentMethods":[{"uid":"pm_18bvh9lx8opYwR3WZsqyw","protocol":"x402","methodType":"crypto","chain":"base","mode":"charge","costAmount":"0.01","costPer":"request","priority":0,"asset":"0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913","unit":"request","depositMicros":null,"planRef":null}],"brandName":null,"brandSlug":null,"brandBaseUrl":null,"brandDocsUrl":null,"whatItDoes":"Returns a structured guide covering SQLite advanced topics: ON CONFLICT upsert vs INSERT OR REPLACE, recursive CTEs, json_extract/json_each, date modifiers, and NULL ordering","exampleAgentPrompt":"Can you pull up the SQLite cookbook guide that covers ON CONFLICT upsert versus INSERT OR REPLACE, recursive CTE hierarchies, json_extract and json_each functions, date modifiers, and NULL ordering?","exampleUseCases":[{"title":"SQLite upsert pattern reference","prompt":"I keep mixing up ON CONFLICT upsert and INSERT OR REPLACE in SQLite — can you get me a clear explanation of when to use each one and what the syntax looks like?"},{"title":"Recursive CTE hierarchy walkthrough","prompt":"I need to model a folder tree in SQLite using recursive CTEs — can you fetch the guide that explains how to build hierarchical queries?"},{"title":"SQLite JSON function quick reference","prompt":"How do I pull specific fields out of a JSON column in SQLite using json_extract and iterate over arrays with json_each? Can you get me the cookbook entry on that?"}],"resultDescription":"A JSON object containing a guide record with fields: id (unique identifier), t (title), c (category), body (full guide text covering SQLite upsert conflicts, recursive CTEs, JSON functions, date modifiers, and NULL ordering), price, and license identifier.","failureModes":["404 if the guide slug is not found or has moved","402 Payment Required if the x402 micropayment is not included or processed","500 server error if the knowledge base backend is unavailable","Empty or malformed JSON body if upstream content store fails"],"whenToPreferThis":"Choose this endpoint when an agent or developer needs a concise, authoritative reference on SQLite-specific advanced query patterns — particularly upsert conflict resolution, recursive CTEs for tree data, JSON column extraction, date arithmetic, and NULL sort behavior — and wants a pre-written, structured guide rather than generating an answer from scratch.","instructions":null,"reviewSummary":null,"reviewSummaryHighlights":null,"reviewSummaryConcerns":null,"reviewSummaryGeneratedAt":null,"activationCount":0,"lastUsedAt":null,"lastSuccessfullyRanAt":null,"lastHealthCheckAt":"2026-10-30T00:38:12.536Z","isFirstParty":false,"canonicalSlug":"saylor-innovations-guide-library-sqlite-cookbook-466f8c58"}