sql Archives - ITNotes

Time Travel in SQL: Using Temporal Tables in MariaDB and PostgreSQL

July 12, 2026

Don't let accidental updates ruin your night. Discover how to implement 'Time Travel' queries in MariaDB and PostgreSQL to recover data instantly and automate your audit trails.

Security

Building a Self-Hosted EDR with osquery on Linux: Monitor Processes, Network, and File Changes Using SQL

June 30, 2026

osquery lets you query your Linux system like a database — every process, network socket, and file event becomes a SQL row. After six months running it in production as a self-hosted EDR, here is a complete setup guide covering installation, scheduled monitoring queries, file integrity detection, and a simple alert pipeline.

Database

PostgreSQL pg_trgm: Build Fuzzy Search, Typo-Tolerant Queries, and Autocomplete with Trigram Indexes

June 28, 2026

Learn how to use PostgreSQL's pg_trgm extension to build fuzzy search that handles typos, powers autocomplete, and accelerates LIKE queries with trigram indexes. Includes practical GIN index setup, similarity threshold tuning, and hybrid search patterns.

Machine Learning Directly in PostgreSQL: Train and Predict Using SQL

June 25, 2026

Stop moving data. Learn how to train and deploy XGBoost and Scikit-learn models directly inside PostgreSQL using PostgresML and standard SQL queries.

Database

ERD Design with dbdiagram.io: From Concept to Auto-Generated SQL

June 24, 2026

Jumping straight into SQL without a plan leads to messy schemas that are painful to refactor later. This guide walks through designing an ERD with dbdiagram.io using DBML syntax, then automatically exporting production-ready SQL — no boilerplate required.

Database

SQLFluff on Linux: Automate SQL Linting and Code Standards in Your CI/CD Pipeline

June 15, 2026

SQLFluff is a SQL linter and auto-formatter for Linux that catches style inconsistencies, enforces keyword casing, and integrates cleanly into GitHub Actions and GitLab CI pipelines. This guide walks through installation, configuration for MySQL/PostgreSQL dialects, CI/CD setup, and practical tips for rolling it out to an existing codebase.

Database

PostgreSQL Database Design Patterns: Polymorphic Association vs EAV vs JSONB for Flexible Schema

June 13, 2026

Comparing Polymorphic Association, EAV, and JSONB in PostgreSQL for flexible schema design — from a real 2 AM production incident. Includes migration guide from EAV to JSONB and practical code examples.

Programming

Type-safe SQL Queries with Kysely: The Lightweight Alternative to Bulky ORMs

June 9, 2026

Kysely offers the power of raw SQL with the safety of TypeScript. Learn how to replace heavy ORMs with a lightweight, type-safe query builder for your Node.js projects.

Database

SQL Joins in Production: How We Fixed Our 10-Second Query Problem

June 1, 2026

A production-focused guide to SQL Joins. Learn the differences between INNER, LEFT, and FULL OUTER joins, and discover how to optimize query performance using indexing and CTEs based on real-world experience.

Database

Why SurrealDB is the Only Database You Need for Your Next Project

May 30, 2026

Tired of syncing SQL and NoSQL? Learn how SurrealDB combines document, graph, and relational models into one ultra-fast database with built-in auth and real-time queries.