标签：#sql

SQLite ON CONFLICT DO SELECT Proposal, PostgreSQL 19 Features & SQLite Critical Bug Today's Highlights This week in databases, a proposal seeks to expand SQLite's ON CONFLICT clause to match PostgreSQL 19's DO SELECT for advanced conflict resolution. Concurrently, an early look at PostgreSQL 19 highlights key features improving performance and data management, while a critical out-of-bounds read bug in SQLite's fossildelta.c extension reminds us of the importance of low-level code security. Request: Support "ON CONFLICT DO SELECT" to match Postgres 19 (SQLite Forum) Source: https://sqlite.org/forum/info/81840ccfecf0885ba4418152d6c7f164de00d189b2cf7c682690151b0 This forum post proposes extending SQLite's ON CONFLICT clause to include a DO SELECT action, mirroring a feature anticipated in PostgreSQL 19. Currently, SQLite supports DO NOTHING and DO UPDATE for handling unique constraint violations. The proposed DO SELECT would allow an application to retrieve existing rows that caused the conflict, providing more granular control over conflict resolution beyond simply ignoring or updating the data. This feature would be particularly useful in complex data pipelines or replication scenarios where knowing which existing data caused a conflict is necessary for subsequent application logic, such as logging, merging, or initiating alternative processing paths. The discussion highlights the growing convergence of SQL features across different database systems and the desire for enhanced compatibility and expressiveness in SQLite. Implementing DO SELECT would empower developers to build more robust and intelligent conflict resolution strategies directly within their SQL statements, reducing the need for multi-step application-side logic involving separate SELECT queries after a failed INSERT or UPDATE attempt. Such an addition could streamline data ingestion processes and improve transactional integrity for embedded SQLite applications. Comment: This feature would be a game-ch

Stop writing SQL strings that look like a ransom note. Here's how to write queries that are readable, safe, and maintainable. The Problem With "Good Enough" SQL Formatting Most Python developers start here: user_id = 5 query = " SELECT * FROM users WHERE id = " + str ( user_id ) cursor . execute ( query ) It works. Until it doesn't — and when it breaks, it breaks badly : SQL injection, cryptic errors from mismatched types, and queries that take 45 minutes to debug at 2am. Let's fix that, permanently. 1. Never Concatenate User Input — Use Parameterized Queries This is rule #1 and it's non-negotiable. ❌ The Wrong Way (SQL Injection Waiting to Happen) username = request . args . get ( " username " ) # Could be: ' OR '1'='1 query = f " SELECT * FROM users WHERE username = ' { username } '" cursor . execute ( query ) If username is ' OR '1'='1 , your entire users table just got exposed. ✅ The Right Way: Parameterized Queries username = request . args . get ( " username " ) # psycopg2 (PostgreSQL) cursor . execute ( " SELECT * FROM users WHERE username = %s " , ( username ,)) # sqlite3 cursor . execute ( " SELECT * FROM users WHERE username = ? " , ( username ,)) # SQLAlchemy Core from sqlalchemy import text result = conn . execute ( text ( " SELECT * FROM users WHERE username = :name " ), { " name " : username }) The database driver handles escaping. You never touch it. This pattern is immune to SQL injection by design. Gotcha: Note the trailing comma in (username,) . Without it, Python treats the string as an iterable and passes each character as a separate parameter. This is one of the most common beginner bugs. # 💥 Bug: passes ('a', 'l', 'i', 'c', 'e') instead of ('alice',) cursor . execute ( " SELECT * FROM users WHERE username = %s " , ( username )) # ✅ Correct: single-element tuple cursor . execute ( " SELECT * FROM users WHERE username = %s " , ( username ,)) 2. Multi-Line Queries: Triple Quotes + Consistent Indentation For anything longer than one clause, use tri

PostgreSQL Error 2201X: invalid row count in result offset clause PostgreSQL error code 2201X ( invalid_row_count_in_result_offset_clause ) is thrown when the value provided to an OFFSET clause is not a valid non-negative integer. This commonly surfaces in applications that implement pagination using dynamic queries or user-supplied parameters, where the offset value may be negative, NULL, or a non-integer type. Top 3 Causes 1. Negative OFFSET Value The most frequent cause is a miscalculated page offset. When computing (page - 1) * page_size , a bad page number can produce a negative result. -- Triggers error 2201X SELECT * FROM orders ORDER BY created_at DESC OFFSET - 5 LIMIT 20 ; -- ERROR: invalid row count in result offset clause -- Fix: Use GREATEST to clamp the value to 0 SELECT * FROM orders ORDER BY created_at DESC OFFSET GREATEST ( 0 , - 5 ) LIMIT 20 ; 2. NULL Passed as OFFSET When an application fails to initialize or bind the offset parameter, NULL gets passed to the query. This often happens with ORMs or query builders where optional parameters are not explicitly set. -- Triggers error 2201X SELECT * FROM products ORDER BY id OFFSET NULL LIMIT 10 ; -- ERROR: invalid row count in result offset clause -- Fix: Use COALESCE to provide a default value SELECT * FROM products ORDER BY id OFFSET COALESCE ( NULL , 0 ) LIMIT 10 ; -- Parameterized query with safe fallback SELECT * FROM products ORDER BY id OFFSET COALESCE ( $ 1 :: BIGINT , 0 ) LIMIT COALESCE ( $ 2 :: BIGINT , 10 ); 3. Non-Integer Type (Float or String) Passing a float or a string that cannot be cleanly cast to a whole number causes this error. This typically happens when Python float values or unvalidated user input strings are interpolated directly into a query. -- Triggers error 2201X SELECT * FROM employees ORDER BY last_name OFFSET 10 . 7 LIMIT 5 ; -- ERROR: invalid row count in result offset clause -- Fix: Use FLOOR and explicit cast to BIGINT SELECT * FROM employees ORDER BY last_name OFFSET F

SQL is arguably the most widely used language in software engineering, yet it is often the least carefully written. Most teams enforce strict linting on their application code but leave SQL queries as a free-for-all. This guide covers the formatting rules that separate maintainable, team-friendly SQL from query spaghetti that haunts on-call rotations. Why Poorly Written SQL Is a Real Engineering Problem Unformatted SQL is not just an aesthetic issue - it is a correctness risk. Dense, run-on queries make it nearly impossible to spot accidental Cartesian products, missing GROUP BY clauses, or WHERE conditions that silently bypass indexes. By the time a performance problem surfaces in production, tracing it back to the root cause becomes a painful exercise in reading someone else's stream of consciousness. Rule 1: Keyword Capitalization SQL engines treat select and SELECT identically, but human readers do not. Always uppercase reserved keywords such as SELECT, FROM, WHERE, JOIN, GROUP BY, and ORDER BY. Keep table names, column names, and aliases lowercase. This single habit immediately creates a visual boundary between the logic structure of the query and the underlying data it operates on. Rule 2: Indentation and Clause Alignment Think of SQL clauses as layers in a data pipeline. Each major clause - SELECT, FROM, WHERE, GROUP BY, ORDER BY - should start at the left margin. Columns and filter conditions beneath them should be indented by 4 spaces (or 1 tab, as long as your team is consistent). This structure lets any reviewer skim the query top-to-bottom and understand the data flow at a glance. Rule 3: Trailing vs. Leading Commas This is a genuinely debated topic on data teams. Leading commas (placing the comma at the start of each new line) make version control diffs significantly cleaner when columns are added or removed. Trailing commas look more natural for developers coming from JavaScript or Python. Neither approach is wrong - what is wrong is mixing both styles

lakehouse has two storage areas ; Files and Tables Files Store structured, queryable data by sql Supports schema definitions and ACID transactions Tables Stores Raw or semi-structured data(CSV, parquet, JSON) No schema support Flexible for data explorations Schema allows for logical ordering of data on business functions or domain (sales,marketing etc) A dbo schema is enabled by default once a lakehouse is created Schema-enabled lakehouses also support schema-level permissions and cross-workspace queries using the four-part namespace Lakehouse mode : Lakehouse Explorer and SQL analytics endpoint Lakehouse Explorer: Allows managing, Update, create, upload of data.You can switch between tables in the lakehouse SQL anlytics endpoit : Does not allow modifying of the underlying data. You can query using TSQL at read only mode. Loading data into lakehouse: Upload data into files/ folders on the explorer Load into delta tables (no code) Transform using power query in dataflow gen2 INgest into notebooks using apache spark (programmatically) Use Copy data to move data into differnt sources using data factory pipelines -Shortcuts allow you to reference external data reducing copies. Access is managed by One Lake. Schema shortcuts map an entire schema to a folder of Delta tables in another lakehouse. SQL analytics endpoint provides read-only access to lakehouse tables using T-SQL queries. SQL USE CASES : adhoc queries, BI connections to power bi or azure data studio, Data validation You can use SQL views to store reusable query logic. Views are useful when you need to apply business rules, simplify complex joins, or provide curated data for downstream consumers. You can use Spark SQL for SQL-like queries or PySpark for programmatic data manipulation in Notebooks. Spark SQL works well for familiar SQL patterns. PySpark provides greater flexibility for complex transformations and integration with Python libraries. Power BI is the business intelligence and reporting layer in Fabr

If you're building any kind of personal-memory layer on top of SQLite — Claude Code conversation history, notes app, indexed knowledge base — there's a sharp edge in FTS5 that takes most people by surprise the first time they hit it. The default tokenizer ( unicode61 ) silently drops most Japanese substring queries. The fix is one line of SQL. But the failure mode is invisible enough that you can ship a personal search tool, use it for weeks, and never realize half your content is unreachable. This post walks through: The failure, reproducible in 20 lines of Python The one-line fix ( tokenize='trigram' ) and what it actually does under the hood A two-layer Git + SQLite design that uses this index in production for ~800 Claude Code conversations A separate FTS5 footgun around the - character that breaks time-blocking -style queries A free GitHub sample at the end if you want to run the same approach against your own data The failure, reproducible in 20 lines Spin up a fresh SQLite FTS5 table with the default settings and insert a single multilingual sentence: import sqlite3 conn = sqlite3 . connect ( " :memory: " ) conn . execute ( """ CREATE VIRTUAL TABLE notes USING fts5(content) """ ) conn . execute ( """ INSERT INTO notes(content) VALUES ( ' Tried time-blocking with the new 朝の運用フロー — ' ' the 9-11 slot worked but the 午後 part collapsed again. ' ) """ ) for q in [ " time " , " blocking " , " 朝の運用 " , " 午後 " ]: hits = conn . execute ( " SELECT count(*) FROM notes WHERE content MATCH ? " , ( q ,) ). fetchone ()[ 0 ] print ( f " { q !r} : { hits } hit(s) " ) Output: 'time': 1 hit(s) 'blocking': 1 hit(s) '朝の運用': 0 hit(s) '午後': 0 hit(s) Same row. Same content. English queries land, Japanese substring queries don't. That's not a bug, it's the default tokenizer behavior — and the default doesn't print a warning about it. The reason: unicode61 segments text on whitespace and unicode word-break properties. English words have spaces between them, so individual tokens are reco

DuckLake Spec, pg_background 2.0, and pgsql_tweaks 1.0.3 Enhance Database Ecosystem Today's Highlights This week's highlights include DuckDB's new DuckLake specification for simplified dataframe integration with data lakes, alongside key updates from the PostgreSQL community. We cover pg_background 2.0 for safer asynchronous SQL execution and the release of pgsql_tweaks 1.0.3 for enhanced monitoring and performance tuning. The DuckLake Spec Is so Simple, Even a Clanker Can Build One for Dataframes (DuckDB Blog) Source: https://duckdb.org/2026/05/04/ducklake-dataframe.html The DuckDB team has unveiled the DuckLake v1.0 specification, a significant step towards simplifying data lake interactions with dataframes. This specification aims to provide a robust yet straightforward framework for reading and writing dataframes directly from and to data lake storage, emphasizing ease of implementation. The announcement highlights the specification's simplicity, so much so that even AI can be leveraged to generate compatible dataframe reader/writer tools. This initiative promises to democratize data lake access, allowing developers and data engineers to integrate DuckDB's powerful analytical capabilities with their data lake architectures more seamlessly. By defining a clear standard, DuckLake facilitates the creation of a vibrant ecosystem of tools and connectors, enabling efficient data processing directly within the data lake context without complex ETL pipelines. This development positions DuckDB as an even more versatile tool for analytical workloads, bridging the gap between local data processing and large-scale data lake environments. The ability to easily build data lake connectors, potentially even with AI assistance, marks a notable shift towards more accessible and integrated data workflows. This could streamline operations for data scientists and analysts who frequently work with large datasets stored in various data lake formats, allowing them to leverage DuckDB's

When I started building OpinioZone , my goal was simple: create a platform where users could compare products, read reviews, and make informed buying decisions. At first, it seemed like a straightforward web application. Store products, display specifications, and allow users to browse information. However, as the platform grew, I quickly discovered that building a review and comparison website involves many technical and architectural challenges. Choosing the Technology Stack I selected ASP.NET Core as the primary framework because of its performance, flexibility, and long-term support. For data storage, I chose SQL Server since it provides strong reliability and works well with complex relationships between products, categories, reviews, ratings, and specifications. This combination allowed me to build a scalable foundation while keeping development manageable. Designing the Database One of the biggest challenges was designing a database structure that could support multiple product categories. A smartphone and a car have very different specifications, but the platform needed to handle both efficiently. Instead of creating completely separate systems, I designed a flexible structure that could store category-specific attributes while maintaining a consistent user experience. This decision made it easier to add new product categories without major database changes. Building Product Comparisons The comparison feature became one of the most important parts of the platform. Users expect side-by-side comparisons to load quickly and display meaningful differences between products. To achieve this, I had to optimize queries and carefully structure specification data. Performance became increasingly important as the number of products grew. SEO Challenges For a content-driven website, SEO is critical. Every product page requires: Unique titles Descriptions Structured content Internal linking Fast page loading One lesson I learned early was that technical SEO and content q

​We just deployed a major update to our SQL Server web sandbox, moving from an individual tool to a collaborative environment: ​✅ Group Rooms (Beta): Create private rooms using security tokens to chat and write code together in real time. ✅ Simulated Linked Servers (Beta): Connect with your friends' sandboxes to run cross-queries strictly in read-only mode to preserve performance. ✅ Gamification & XP: Earn experience points and unlock technical badges as you run queries or build database schemas. ✅ Social Layer: Manage your friends list and customize your public developer profile to showcase your achievements. ​Currently, all features are completely free to use during this phase of the platform. Try it out with your colleagues at tsqlsandbox.online and leave your feedback below!

PostgreSQL Error 2200D: invalid escape octet The 2200D: invalid escape octet error occurs in PostgreSQL when a bytea value contains an invalid escape sequence. This typically happens with the legacy escape format for binary data, where octet values must be represented as three-digit octal numbers in the range \000 to \377 . If the escape sequence falls outside this range or uses non-octal digits, PostgreSQL raises this error immediately. Top 3 Causes 1. Out-of-range octal values in bytea escape literals The bytea escape format only accepts octal values from \000 to \377 (decimal 0–255). Using values like \400 or non-octal digits like \9 will trigger this error. -- BAD: \400 exceeds valid octal range (max is \377) SELECT E ' \\ 400' :: bytea ; -- ERROR: invalid escape octet -- BAD: \9 is not a valid octal digit SELECT E ' \\ 9AB' :: bytea ; -- ERROR: invalid escape octet -- GOOD: valid octal escape sequences SELECT E ' \\ 377' :: bytea ; -- decimal 255 SELECT E ' \\ 101' :: bytea ; -- 'A' character SELECT E ' \\ 000' :: bytea ; -- null byte 2. Using escape format strings with hex output format Since PostgreSQL 9.0, the default bytea_output is hex . Applications that mix hex -format output back into escape -format input processing can generate malformed escape sequences. -- Check current bytea output format SHOW bytea_output ; -- GOOD: Use hex format (recommended for all new projects) SELECT ' \x DEADBEEF' :: bytea ; SELECT ' \x 48656C6C6F' :: bytea ; -- 'Hello' -- GOOD: Use encode/decode for safe conversions SELECT encode ( ' \x DEADBEEF' :: bytea , 'hex' ); -- output as hex string SELECT encode ( ' \x DEADBEEF' :: bytea , 'base64' ); -- output as base64 SELECT decode ( 'deadbeef' , 'hex' ); -- hex string → bytea SELECT decode ( 'SGVsbG8=' , 'base64' ); -- base64 → bytea 3. Incorrect escaping during data migration or manual SQL When migrating binary data from other databases (Oracle, MySQL) or writing raw INSERT statements manually, developers often confuse octal and

Last year, I examined RUM indexes within this series on multi-key indexing, demonstrating that they cannot substitute MongoDB's compound indexes for sorted queries. A year later, Microsoft has fixed this in the DocumentDB extension for PostgreSQL with an Extended RUM index that preserves the ordering of the keys, allowing an ordered scan rather than a bitmap scan. Let's revisit our pagination query to see how it performs now. I start a container with the latest DocumentDB (version v0.112-0 from May 26, 2026): docker run -d --name documentdb-local -p 10260:10260 -p 9712:9712 ghcr.io/documentdb/documentdb/documentdb-local:latest --username franck --password franck --start-pg I can connect to PostgreSQL on port 9712, where many extensions are installed, including the extended RUM index: docker exec -it documentdb-local psql -p 9712 postgres psql ( 17.10 ( Debian 17.10-1.pgdg13+1 )) Type "help" for help. postgres = # \dx List of installed extensions Name | Version | Schema | Description ------------------------- +---------+------------+------------------------------------------------------------ documentdb | 0.112-0 | public | API surface for DocumentDB for PostgreSQL documentdb_core | 0.112-0 | public | Core API surface for DocumentDB on PostgreSQL documentdb_extended_rum | 0.112-0 | public | DocumentDB Extended RUM index access method pg_cron | 1.6 | pg_catalog | Job scheduler for PostgreSQL plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language postgis | 3.6.3 | public | PostGIS geometry and geography spatial types and functions tsm_system_rows | 1.0 | public | TABLESAMPLE method which accepts number of rows as a limit vector | 0.8.2 | public | vector data type and ivfflat and hnsw access methods ( 8 rows ) postgres = # I can also connect to the MongoDB-compatible API: docker exec -it documentdb-local mongosh -u franck -p franck 'mongodb://localhost:10260/?tls=true&tlsAllowInvalidCertificates=true' Current Mongosh Log ID: 6a0b3b537d2a1c3471d1a7ba Connecting to: mo

For two years, our Sponsored Placements service booked limited ad inventory through Redis: a counter in Redis, a Redlock around the decrement, and a TTL key per hold. It oversold. Not catastrophically — consistently. 40–60 double-booked placements a month , each one a manual refund and an apology email to an advertiser. The root cause was never one bug. It was the architecture: two sources of truth that could not be made atomic with each other. The count lived in Redis; the ownership lived in SQL. No transaction spans both. The Redlock only ever protected the Redis half. The one mental shift SKIP LOCKED turns a contended table into a concurrent work queue. Instead of every request fighting over one counter, each request grabs different rows and ignores the ones someone else is holding. FOR UPDATE alone serializes — that's the experience that scares people off SQL locking. FOR UPDATE SKIP LOCKED is the opposite: a transaction that would have blocked instead skips the locked row and takes the next free one. One row per reservable unit, then: START TRANSACTION ; SELECT id FROM inventory_unit WHERE placement_id = 42 AND ( status = 'available' OR ( status = 'held' AND hold_expires_at < NOW ( 3 ))) -- self-healing expiry ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED ; -- the whole trick UPDATE inventory_unit SET status = 'held' , reservation_id = 'uuid' , hold_expires_at = NOW ( 3 ) + INTERVAL 10 MINUTE WHERE id IN ( 1107 , 1108 ); INSERT INTO reservation (...) VALUES (...); COMMIT ; Two concurrent requests for the same pool lock different rows. Neither waits. The claim, the hold, and the reservation are one transaction — there is nothing to reconcile because there is nothing else. The numbers (8 weeks before vs 8 weeks after) Metric Redis + Redlock MySQL SKIP LOCKED Oversells / month 40–60 0 Reservation p95 210 ms 34 ms Reservation p99 540 ms 61 ms Throughput / instance ~600 RPS 1,400 RPS Lock-wait timeouts / day ~900 <5 Nightly reconciliation 9–14 min deleted Redis cluster

SQL tuning is the process of making a database query run faster and cheaper — cutting response time while minimizing the system resources it burns. Here's the playbook I actually use, from "this query is slow" to "this query is fixed," with the traps that bite people in the middle. The loop Tuning is iterative. The shape is always the same: Identify the problem. Find the slow query (logs, profiler, or user feedback) and measure a baseline — execution time and resource usage. You can't claim an improvement you didn't measure. Analyze & rewrite. Review the SQL for redundant joins, unnecessary work, and complex subqueries. Tighten the WHERE , select only the columns you need, convert subqueries to joins where it helps. Read the execution plan. Understand how the engine actually runs the query; find inefficient join orders and needless full scans. Revisit indexes. Evaluate whether existing indexes help; add or restructure as needed. Consider schema changes. If a column is updated so often that indexing it hurts, split it out. Sometimes the model is the bottleneck. Tune settings/hardware if it comes to that. Re-test and repeat. Apply changes, re-check the plan, confirm the gain, monitor. Reading an execution plan The execution plan shows how the DB will run your query — table scans, index access, join methods. Read it well and you can pinpoint where the time goes. Most engines expose it: EXPLAIN (MySQL/PostgreSQL), EXPLAIN PLAN FOR (Oracle), SET SHOWPLAN_ALL ON (SQL Server). Operators to know: Full Table Scan — reads every row. Happens when there's no suitable index, or the query can't use one. Index Scan — scans via an index; usually cheaper than a table scan. Index Seek — jumps to specific key values; very efficient, reads only the rows it needs. Nested Loops / Hash Join / Merge Join — the three ways to join two tables (more below). Sort — orders data; excessive sorting is a common performance drag. Three numbers that matter: Cost — estimated resources a step will cons

DuckDB 1.5.3 & Quack Protocol Release; PostgreSQL File Descriptor Tuning Today's Highlights This week's database news highlights significant advancements for DuckDB, including a feature-packed 1.5.3 release and the innovative Quack client-server protocol. We also delve into a critical PostgreSQL performance tuning guide on managing file descriptors. DuckDB 1.5.3: Not an Ordinary Patch Release (DuckDB Blog) Source: https://duckdb.org/2026/05/20/announcing-duckdb-153.html DuckDB has announced the release of version v1.5.3, a "patch release" that, despite its designation, delivers a substantial upgrade to the ecosystem. While the core DuckDB engine sees limited bugfixes, the true power of this release lies in the significantly upgraded extensions that ship alongside it. These extensions introduce a wealth of new features that enhance DuckDB's capabilities across various data processing tasks, making it much more than a routine update. Key among the new features is the integration of the Quack client-server protocol, which is highlighted as a major advancement. This allows DuckDB instances to communicate and operate in more distributed, concurrent environments, expanding its utility beyond purely embedded scenarios. Developers are encouraged to explore the updated extensions for improved functionality, ranging from new data formats to enhanced analytical operations. This release underscores DuckDB's commitment to continuous innovation through its modular extension system, providing users with powerful new tools without requiring major core engine overhauls for every new feature. Comment: This release is a great example of how DuckDB's extension model brings rapid innovation. Developers should check the extension changelogs, as that's where the real new features are. Quack: The DuckDB Client-Server Protocol (DuckDB Blog) Source: https://duckdb.org/2026/05/12/quack-remote-protocol.html The DuckDB team has introduced Quack, a new client-server protocol designed to enable s

Every headless WordPress conversation starts the same way — someone draws an architecture diagram with arrows pointing from a REST API to a shiny Next.js frontend, and it looks clean. Too clean. This is a post about what happens when you close the whiteboard and open the actual codebase. The Stack Decision: GraphQL vs. REST vs. Direct MySQL This is usually the first fork in the road. For this build, the client already had a well-indexed WooCommerce site. The product catalog, slugs, and taxonomy structure were already doing heavy SEO work. So the constraint was simple: nothing about the data layer changes, only how we consume it. WPGraphQL was a real option — but it meant adding a plugin dependency to a WordPress install we were actively trying to slim down. The WP REST API was already there, no installation required, and exposed exactly what we needed: products, categories, pages, and media — all queryable by slug. The decision: WP REST API, consumed server-side via Next.js fetch in Server Components. // Fetching a product by slug — preserving the existing URL structure const res = await fetch ( ` ${ process . env . WP_API_BASE } /wp/v2/product?slug= ${ params . slug } &_embed` , { next : { revalidate : 3600 } } ); const [ product ] = await res . json (); No new dependencies on the WordPress side. The legacy install runs as a lean shell — no active theme, minimal plugins, just the REST API and the data. The Site Kit Problem: Bridging Familiar Workflows This is where most migrations quietly fail the client. The previous team lived inside WordPress admin. Google Site Kit gave them traffic stats, Search Console data, and Analytics — all surfaced in a UI they knew. Ripping that away and telling them "just use Google Analytics directly" is a workflow regression, not an upgrade. The pivot here was building a lightweight admin dashboard as part of the Next.js project — not a full replacement for Site Kit, but a mirror of the metrics they actually checked daily: Page views

I've been building a database engine from scratch in Rust, and I recently finished the lexer. The lexer itself wasn't the most interesting part. What I found more valuable was how my design evolved as I learned more about Rust and how compilers and database systems are typically implemented. My First Approach When I started, I stored the input as a Vec<char> . It felt straightforward because I could access characters directly without worrying about UTF-8 boundaries. I also represented identifiers like this: Ident ( String ) At first glance, this seems perfectly reasonable. Every identifier token carries its own text, making it easy for the parser to consume. The Problem As the lexer grew, I started asking myself a simple question: The identifier already exists in the original SQL query. Why am I allocating another string and copying the same data into every token? For a query like: SELECT username , email FROM users ; the source text already contains: username email users Creating separate String allocations for each identifier means duplicating data that already exists. I also learned an important detail about Rust enums. The size of an enum is influenced by its largest variant. Once variants start carrying additional data, every token instance becomes larger than it otherwise needs to be. Moving to a Span-Based Design Instead of storing identifier text directly inside tokens, I switched to storing only the token kind: Ident along with source location information: Span { start , end , line , column , } Now the token only answers two questions: What is this token? Where did it come from? If the parser needs the actual identifier text, it can recover it directly from the original SQL source using the stored byte range. Replacing Vec<char> with &str The second design change was moving away from: Vec < char > and operating directly on: & str using lifetimes. Instead of creating another collection containing the entire input, the lexer now walks over borrowed source tex

I've been frustrated with SQL learning resources for a while. Most are either: Dry reference docs Toy exercises with no context ("SELECT * FROM employees") Paid platforms with paywalls after level 3 So I built SQLwak — a free, browser-based SQL game where you're hired as a Graduate Analyst at Lion City Bank , a fictional Singapore bank. How it works Instead of abstract exercises, every challenge is a real business request from a colleague: "The Operations team needs all Central region branches for an upcoming audit." "Risk wants customers with credit scores below 600 who have active loans." "Finance needs vessels ranked by cargo revenue — use window functions." You write actual SQL against a realistic 9-table banking database and get immediate feedback. 57 levels across 4 tiers Tier Skills 🟢 Foundational SELECT, WHERE, ORDER BY, LIMIT 🟡 Intermediate JOINs, GROUP BY, HAVING, subqueries 🔴 Advanced CTEs, multi-table aggregations ⚫ Expert Window functions (RANK/DENSE_RANK OVER PARTITION BY), UNION ALL, compound CTEs The database schema Lion City Bank has two divisions: Retail Banking: customers, accounts, transactions, loans, branches, products Maritime Trade Finance (Advanced/Expert levels): vessels, cargo_shipments, trade_finance_facilities — covering voyages between Singapore, Port Klang, Bangkok, Jakarta, and Ho Chi Minh City. The maritime division exists because Singapore is a major trade hub. It makes the Expert levels genuinely interesting — you're ranking vessels by cargo revenue and analyzing trade finance utilisation rates, not just counting rows. Technical details Next.js 15 + TypeScript + Tailwind CSS SQLite via WebAssembly — all query execution is client-side, no backend needed Deployed on Vercel Fully open source: github.com/martinl5/sqlwak No signup. No download. Just SQL. Open the link and start writing queries: sqlwak.vercel.app Would love feedback on difficulty progression, new level ideas, or schema additions. What SQL concepts do you wish you'd pract

DuckDB Integrates Lance Lakehouse; SQLite CVE Fix; Postgres 19 Beta on K8s Today's Highlights This week, DuckDB introduces integrated vector and hybrid search with the Lance lakehouse format, enabling advanced AI workloads directly from SQL. Meanwhile, SQLite addresses a significant security vulnerability with a recent fix, and a new guide helps users test PostgreSQL 19 beta efficiently in Kubernetes clusters. Test-Driving the Lance Lakehouse Format in DuckDB (DuckDB Blog) Source: https://duckdb.org/2026/05/21/test-driving-lance.html DuckDB has announced a new integration allowing users to test-drive the Lance lakehouse format, a design specifically geared towards AI workloads. This collaboration between LanceDB and DuckLabs brings fast vector and hybrid search capabilities directly into DuckDB SQL, significantly streamlining data analysis for machine learning applications. The integration means users can perform complex queries involving vector embeddings and traditional tabular data without needing to move data between different systems or tools. This is a crucial development for data scientists and engineers who rely on efficient data access and processing for large-scale AI projects. The ability to perform sophisticated searches within the familiar DuckDB environment enhances productivity and reduces the operational overhead typically associated with managing specialized vector databases. This new feature democratizes access to advanced data structures for AI, making it simpler to build and prototype machine learning models on massive datasets. By supporting an open lakehouse format, DuckDB continues to position itself as a versatile analytical database, bridging the gap between traditional data warehousing and emerging AI data needs. Users are encouraged to try out this integration, which promises to unlock new possibilities for data exploration and feature engineering using a unified SQL interface. It's an excellent example of how embedded analytical databases

A post titled "Learn SQL Once, Use It for 30 Years" hit the front page of r/programming this week (307 points, 48 comments). The claim sounds like the kind of thing a database vendor would put on a billboard, so I went looking for the part that holds up. It turns out the longevity is not marketing. It is a property of how the language was designed, and it is the reason SQL is one of the few skills on a developer's resume that does not quietly expire. I run a site that compares developer tools, which means I spend a lot of time watching technologies rise, peak, and get replaced. Most of what you learn in this field has a half-life measured in single-digit years. The framework you mastered in 2019 is legacy by 2024. SQL is the strange exception, and the reasons are worth understanding before you decide where to spend your next month of learning. Where the staying power comes from SQL did not start as a language. It started as a math paper. In 1970, Edgar Codd published "A Relational Model of Data for Large Shared Data Banks," which proposed organizing data into tables of rows and columns with formal rules for combining them. IBM built a query language on top of that model in the mid-1970s, called it SEQUEL, and later renamed it SQL after a trademark conflict. The important detail is the order: the model came first, the language second. SQL is a surface over a mathematical foundation that has not needed to change. That foundation is why the skill compounds instead of decaying. When you learn SQL, you are not memorizing one vendor's API. You are learning the relational model, and the model is the same whether the data sits in Postgres, MySQL, SQLite, Oracle, or SQL Server. A join is a join everywhere. Move from one database to another and the syntax shifts at the edges, but the way you think about the problem carries over intact. Compare that to a frontend framework, where moving stacks means relearning how to think, not just how to type. Declarative is the whole trick

SQLite Optimizer Deep Dive, Change-Set Internals & Azure PostgreSQL Architecture Today's Highlights This week, we explore SQLite's query planner optimizations, delve into a critical flag for change-set replication, and dissect the architectural choices behind Azure's managed PostgreSQL. These insights offer valuable perspectives on performance, data integrity, and cloud database deployment strategies. Extend "Omit OUTER JOIN" optimization to COUNT(*) (SQLite Forum) Source: https://sqlite.org/forum/info/b949c721db6f0289104db944d6a7e3bbb94b7770915c42e5ae89f67fe6be6d84 A recent discussion on the SQLite forum highlights a potential enhancement to SQLite's query optimizer regarding OUTER JOIN clauses combined with COUNT(*) . Currently, SQLite can sometimes omit an OUTER JOIN if it determines that the LEFT JOIN semantics are not required for the query result, for instance, when only columns from the left table are selected. The proposed extension seeks to apply this optimization even when COUNT(*) is used, which can be more complex due to the way COUNT(*) inherently handles NULLs from unmatched rows. This optimization is crucial for improving the performance of analytical queries that often involve counting records across joined tables. By intelligently removing unnecessary OUTER JOIN operations, SQLite can reduce the amount of data processed and improve query execution times. Developers often encounter scenarios where they use LEFT JOIN out of caution, but if the optimizer can determine it's effectively an INNER JOIN for the given projection, significant speedups are possible. This discussion delves into the intricacies of the query planner's logic, revealing how subtle changes can lead to substantial performance gains in real-world applications. Understanding these internal mechanisms allows developers to write more efficient SQL and anticipate SQLite's behavior. Comment: This directly impacts how efficiently SQLite executes analytical queries, making it vital for anyon