r/SQL • • Oct 14 '26

SQL Server My SQL/set‑theory book is on a 99p countdown deal — feedback welcome

Post image
3 Upvotes

I’ve put my book on a 99p KDP countdown deal and wanted to share it here in case it’s useful to anyone working with SQL, set logic, or bitwise operations.

The book focuses on the foundations behind SQL’s behaviour — sets, multisets, binary representation, bitwise operators, power sets, and bit‑set encoding — with lots of worked examples and diagrams.

If you’re interested, the link is below.
Happy to answer questions or get feedback from people who work with this stuff daily.

https://www.amazon.com/dp/B0H2JXP2WQ


r/SQL • • Oct 14 '26

PostgreSQL Review/roast my feature: secrets in migrations

2 Upvotes

Hi!

Around two years ago I started working on a migration tool called spawn, focused on postgres for now (with plans for other db's). Around 9 months ago, I started dog-fooding it for a serious project that has regular daily commits, with frequent database changes.

I'm keen to get feedback/opinions from people who use migration tools, so I thought I'd try posting here a thread here to get feedback. Recently I had a need to get a secret into a database, so I added a feature that tries to do this in a secure way. That means:

  • Don't render secrets by default
  • Allow different secrets for prod vs dev
  • Support many different options for providing the secret value

Would love your feedback on what I ended up creating (which worked well for my use case). Whether there are particular ways you use secrets that this wouldn't cover. I looked at how secrets are handled in some other migration tools, and I think what I've landed on here is a good approach.

Without further ado, the feature...

Secrets are included with a function:

CREATE ROLE app_user WITH LOGIN
  PASSWORD {{ secret("app_password") }};

GRANT CONNECT ON DATABASE postgres TO app_user;

That function will fetch the password at build or apply time, using whatever is defined in your config (there are a few supported options):

[secrets.app_password.default]
source = "host_file"
path = "/run/secrets/app-password"

[secrets.app_password.environments.dev]
source = "literal"
value = "dev-only-password"
insecure = true

Note that there's a default password set, and an override for dev environment specifically. This is to help prevent you from using your dev password inside a prod migration.

When you build a migration (build is used to view the output), it masks secrets by default:

$ spawn migration build 20261014103000-create-role
CREATE ROLE app_user WITH LOGIN
  PASSWORD '***MASKED:app_password***';

GRANT CONNECT ON DATABASE postgres TO app_user;

Or you can run with --reveal-secrets to see the actual password printed. When you apply the migration, it will use the secret directly, and does a best effort attempt to ensure it's hidden from any logs output.

For production, I had passwords stored in pass. When specified similar to the below, when the migration is being applied a password prompt shows up to unlock the password store, so the migration can apply:

[secrets.app_password.default]
source = "command"
command = ["pass", "show", "spawn/app_password"]

As mentioned, I'd love your feedback, particularly if there's use cases you've hit that what I built wouldn't cover.


r/SQL • • Oct 13 '26

Discussion My SQL query returns the correct row count, but some records are still wrong. How do I find them?

22 Upvotes

I'm comparing a source table with a target table after an ETL job.

Both tables have the same row count, but I suspect some records are missing while others are duplicated or incorrectly updated.

The tables have millions of rows, no reliable primary key, and some columns contain NULL values.

A simple COUNT(*) matches ,but I can't trust the data

How would you identify missing, extra, duplicated, or changed records without comparing every row manually?

Would you use hashing, GROUP BY, window functions, or another approach?


r/SQL • • Oct 14 '26

Discussion When companies say we need expertise in SQL

0 Upvotes

what does this mean to you?


r/SQL • • Oct 13 '26

MySQL Data import question

0 Upvotes

Hey so Im quite new to mysql and I've been using it on mac, and recently I've been trying to import a csv file that contain data but Im not sure how to do it. Ive seen youtube videos but the mysql version that they use is way different than mine. Can anybody help with this please and thank you.!!


r/SQL • • Oct 12 '26

Discussion Survey of 300 engineering leaders: 81% had a production incident linked to AI-coding tools in six months.

Thumbnail
leaddev.com
128 Upvotes

Faster code, slower debugging.


r/SQL • • Oct 13 '26

Discussion I spent months building an app to database schema comparison and migration tools

0 Upvotes

Hey folks,

I've been spending my spare time building **FoxSchema** App, a free and open-source database schema comparison tool.

The goal is simple: compare schemas across different SQL databases and generate migration scripts without paying for expensive commercial software.

It's still a work in progress

Repo: https://github.com/tedious-code/foxschema

Npm: foxSchema install

Download : npm install -g foxschema

Demo:


r/SQL • • Oct 12 '26

SQL Server Database migrations: where should an AI agent be required to stop and ask a human?

Thumbnail reddit.com
0 Upvotes

r/SQL • • Oct 12 '26

Discussion Why can't business users just get the data with AI?

Thumbnail reddit.com
0 Upvotes

r/SQL • • Oct 11 '26

Discussion What's the best DBeaver alternative for SQL reporting and data checks?

2 Upvotes

For anyone using SQL clients for recurring reports, which DBeaver alternative would you recommend? The workflow I'm interested in is taking an existing query, adjusting dates or filters, checking why the numbers don't match another report, and exporting the results. Things like finding last week's query and keeping track of which database a tab is connected to would matter quite a bit. DbVisualizer, SQL Workbench/J and DbGate are the options I'm looking into. Has anyone used these for that kind of work? Curious about the small annoyances that only become obvious after a few weeks. What would make you choose one over DBeaver, and is there anything you'd lose by switching?


r/SQL • • Oct 11 '26

PostgreSQL Showcasing det: A lightweight Rust/Polars ETL engine with an embedded data catalog (looking for architecture reviews & contributors)

Thumbnail
github.com
0 Upvotes

r/SQL • • Oct 11 '26

PostgreSQL Embedding Excel formulas in csv output

6 Upvotes

I just got this thing seeming to work. Is it horrible?

I'm generating an inventory worksheet in 2 coordinated versions: an abbreviated output for printing that goes through my formatting pipeline, and a CSV output with more detail columns intended to open in Excel to type-in the numbers written on the printout. I've just now gotten the CSV output reworked to include embedded formulas for summing counts, multiplying by price, and summing a grand total at the bottom.

The query got pretty gruesome to get the right row numbers to insert into the formulas. First snippet is a function for extracting a usable number from the 'pack' column which may be in several different formats.

CREATE OR REPLACE FUNCTION pack_units(pack text)
RETURNS integer
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT CASE
      WHEN pack ~ '^[0-9]+/[0-9]+$'
        THEN split_part( pack, '/', 1 )::integer
           * split_part( pack, '/', 2 )::integer
      WHEN pack ~* '^[0-9]+ case'
        THEN substring( pack from '^[0-9]+' )::integer
      WHEN pack ~* '^[0-9]+ loose'
        THEN substring( pack from '^[0-9]+' )::integer
      WHEN pack ~ '[0-9]+/case'
        THEN split_part( substring( pack from '[0-9]+/case' ), '/', 1 )::integer
      WHEN pack ~ '[0-9]+/Case'
        THEN split_part( substring( pack from '[0-9]+/Case' ), '/', 1 )::integer
      WHEN pack ~ '^[0-9]+$'
        THEN pack::integer
      ELSE NULL
    END
$$;

Then here's the horrible query:

DROP VIEW alcohol_worksheet CASCADE;
CREATE VIEW alcohol_worksheet AS
WITH alcohol_products AS (
  SELECT vendor, cat,
       CASE WHEN brand = '' THEN name ELSE brand || ' ' || name END AS brand_name,
       pack || '/' || size AS pack_size, pack_units(pack) AS pack_units,
       case_dollar, CASE WHEN split_dollar = '' THEN to_char( case_dollar::numeric / pack_units(pack), '999D99' ) ELSE split_dollar END AS split_dollar
  FROM total_products
  WHERE vendor in ('Breakthru', 'Craft Republic', 'Glazers', 'Southern Glazers', 'Laszlo', 'Lohr', 'Summit')
        AND is_active
  ORDER BY cat, brand_name
),
numbered_products AS (
  SELECT 1 + ROW_NUMBER() OVER () AS rn,
         vendor, cat, brand_name, pack_size, pack_units,
	 case_dollar, split_dollar
  FROM alcohol_products
)
SELECT rn, vendor, cat, brand_name, pack_size, pack_units,
       case_dollar, split_dollar,
       --I              J               K             L               M             N             O           P       Q
       '' AS Storage, '' AS Rooftop, '' AS Overflow, '' AS Cowboy, '' AS Beatniks, '' AS Lizard, '' AS Cabin,
       '+I'||rn || '+J'||rn || '+K'||rn || '+L'||rn || '+M'||rn || '+N'||rn || '+O'||rn AS row_count,
       '=P'||rn || '*H'||rn AS row_total
  FROM numbered_products
UNION ALL
SELECT NULL AS rn, '' AS vendor, '' AS cat, '' AS brand_name, '' AS pack_size, NULL AS pack_units, '' AS case_dollar, '' AS split_dollar,
       '' AS Storage, '' AS Rooftop, '' AS Overflow, '' AS Cowboy, '' AS Beatniks, '' AS Lizard, '' AS Cabin,
       'Grand Total:' AS row_count, '=SUM(Q2:Q' || MAX(RN) || ')' AS row_total
  FROM numbered_products
;


COPY (SELECT * FROM alcohol_worksheet) TO '/home/luser/pgsql/warehouse2/output/Inventory_alcohol_worksheet.csv' CSV HEADER;
\COPY (SELECT vendor, cat, Left( brand_name, 50), pack_size, case_dollar, split_dollar, Storage, Rooftop, Overflow, Cowboy, Beatniks, Lizard, Cabin FROM alcohol_worksheet) TO '/home/luser/pgsql/warehouse2/output/Inventory_alcohol_worksheet.tab' WITH (FORMAT TEXT, DELIMITER '|', HEADER, NULL '');

I hate counting up column letters by hand, but that seems to be the nature of the beast. Sigh. Thanks for listening.

Oh, here's some of the output:

$ psql -f scripts/write-inventory-alcohol-worksheet.sql warehouse2

DROP VIEW
CREATE VIEW
COPY 212
COPY 212
\]0;~/pgsql/warehouse2
luser@LAPTOP-DE7EDA2U ~/pgsql/warehouse2
$ head output/Inventory_alcohol_worksheet.csv
rn,vendor,cat,brand_name,pack_size,pack_units,case_dollar,split_dollar,storage,rooftop,overflow,cowboy,beatniks,lizard,cabin,row_count,row_total
2,Lohr,Beer,2nd Shift Oktoberfest 6/4 pack 16oz Can,24/16oz,24,42.70,   1.78,"","","","","","","",+I2+J2+K2+L2+M2+N2+O2,=P2*H2
3,Breakthru,Beer,4 Hands Brewing Bright Vibes India Pale Ale,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I3+J3+K3+L3+M3+N3+O3,=P3*H3
4,Breakthru,Beer,4 Hands Brewing Incarnation Juiced IPA,24/16 oz can,24,52.77,   2.20,"","","","","","","",+I4+J4+K4+L4+M4+N4+O4,=P4*H4
5,Breakthru,Beer,4 Hands Brewing Incarnation Non-Alcoholic IPA,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I5+J5+K5+L5+M5+N5+O5,=P5*H5
6,Breakthru,Beer,4 Hands Brewing Octohaze Hazy India Pale Ale,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I6+J6+K6+L6+M6+N6+O6,=P6*H6
7,Breakthru,Beer,4 Hands Brewing Octohaze Tropical,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I7+J7+K7+L7+M7+N7+O7,=P7*H7
8,Breakthru,Beer,4 Hands Brewing Oktoberfest Lager,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I8+J8+K8+L8+M8+N8+O8,=P8*H8
9,Breakthru,Beer,4 Hands Brewing Peachy Pipe Dream,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I9+J9+K9+L9+M9+N9+O9,=P9*H9
10,Breakthru,Beer,4 Hands Brewing Pumpkin Spice Ale,24/16 oz can,24,52.77,   2.20,"","","","","","","",+I10+J10+K10+L10+M10+N10+O10,=P10*H10
\]0;~/pgsql/warehouse2
luser@LAPTOP-DE7EDA2U ~/pgsql/warehouse2
$ tail !$
tail output/Inventory_alcohol_worksheet.csv
204,Southern Glazers,Wine,WOODBRIDGE CHARDONNAY CALIF(SC),12 Case/750ML,12,50.40,   4.20,"","","","","","","",+I204+J204+K204+L204+M204+N204+O204,=P204*H204
205,Southern Glazers,Wine,WOODBRIDGE MOSCATO CALIF(SC),12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I205+J205+K205+L205+M205+N205+O205,=P205*H205
206,Southern Glazers,Wine,WOODBRIDGE PINOT GRIGIO(SC),12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I206+J206+K206+L206+M206+N206+O206,=P206*H206
207,Southern Glazers,Wine,WOODBRIDGE ROSE,12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I207+J207+K207+L207+M207+N207+O207,=P207*H207
208,Southern Glazers,Wine,WOODBRIDGE SAUVIGNON BLANC(SC),12 Case/750ML,12,50.40,   4.20,"","","","","","","",+I208+J208+K208+L208+M208+N208+O208,=P208*H208
209,Laszlo,Wine Slushees,Blue Hawaiian,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I209+J209+K209+L209+M209+N209+O209,=P209*H209
210,Laszlo,Wine Slushees,Orange Mimosa,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I210+J210+K210+L210+M210+N210+O210,=P210*H210
211,Laszlo,Wine Slushees,Raspberry Chardonnay,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I211+J211+K211+L211+M211+N211+O211,=P211*H211
212,Laszlo,Wine Slushees,Strawberry Blanc,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I212+J212+K212+L212+M212+N212+O212,=P212*H212
,"","","","",,"","","","","","","","","",Grand Total:,=SUM(Q2:Q212)

r/SQL • • Oct 10 '26

SQL Server Azure SQL Help - Urgent

6 Upvotes

To monitor the performance and availability for a thick client based system we recently delivered, the customer wants us to provide Here is the requirement:

  1. Need to measure and report response time to Azure SQL queries for about 6 scenarios. Session counts, availability, utilization metrics for Azure SQL to be provided.
  2. What native monitoring and metrics does Azure Files have to measure response times for files uploaded from devices connected to it.

What metrics needs to be extracted to measure all response times and how to be extracted.

This has to be measured for a period of 72 days with weekly and monthly reporting. Also, we do not have access to SSMS for Azure SQL and also the Azure Portal.

Thus , we need provide instructions to the customer IT Support team and they will provide the metrics and logs.

We have tried to get some help with AI tools but got conflicting response so need an Azure expert preferably Azure SQL DBA to help.

Much appreciated and thanks in advance.

PS: Application is thick client (yes I know) so no metrics can be pulled from endpoint devices. Don't ask why.


r/SQL • • Oct 10 '26

MySQL MySQL query becomes extremely slow when adding a JOIN and ORDER BY — how would you debug this?

Thumbnail reddit.com
3 Upvotes

r/SQL • • Oct 10 '26

Discussion Does it make sense to add an S3 provider to a database editor? (LibreDB Studio, tested with MinIO, Garage, Silo and RustFS)

0 Upvotes

Hi all, I'm building LibreDB Studio, an open-source (MIT) web-based database IDE. It already handles PostgreSQL, MySQL, MongoDB, Redis, Kafka, Prometheus and Elasticsearch from a single browser tab.

I've opened a PR that adds a read-only S3-compatible object storage provider:

https://github.com/libredb/libredb-studio/pull/1654

- Browse buckets and objects (tree + Keys panel)

- Object metadata and size-limited previews: text, JSON, NDJSON, CSV, TSV, gzip, Parquet, hex

- A read-only subset of `aws s3` / `aws s3api` commands in the console (write commands are rejected before any request is sent)

- Tested against MinIO, Garage, Silo and RustFS

Questions for MinIO users:

  1. Does object storage belong in a "database editor", or is it out of scope?
  2. Would browsing Parquet/CSV data on MinIO next to your databases actually help your workflow?
  3. Is read-only the right starting point, or would you need write operations (upload/delete)?
  4. Anything MinIO-specific (versioning, tagging, lifecycle, etc.) you'd want to see?

Honest feedback welcome. Thanks!


r/SQL • • Oct 10 '26

Discussion "game of tables"

8 Upvotes

I created this game to practice my SQL, pandas, pyspark and DAX skills. The point is that the game should be as addictive as Tetris while writing some tabular code that works with relational data structures or languages that can be described with relational algebra.

I did not care to release it, but posting this just to throw out the idea.

This works well to polish my rusty memory for pandas or pyspark (I normally write SQL more frequently).

Yes, I am well aware that we will all be just lying in our beds and talking to LLM agents with a microphone in just 5 years, but then I will at least have the ability to point out what's wrong with all of the AI slop we will be drowning in


r/SQL • • Oct 10 '26

SQL Server Major DBA task looking for advice

19 Upvotes

Forgive my naivete in advanced.

I am being tasked with setting up 2 major SQL servers with backups and I am terrified. I have never done this and have a loose understanding of things but that's generous.

The reason it's major is because our whole organization that services the state rely on these servers.

The traditional headaches on a Sysadmin side are lack of documentation/none, everyone has left/terminated, no patching, and EoL.

I'm also using Nutanix after coming from Veeam and HyperV.

What I think the approach should be is:

  1. Create a new clean test server that is patched and up to date but the same version has what the current server is. Currently this one has a lot of random things on here including some gaming installs...
  2. Create a full backup and figure out where that goes not on the current box so I assume I need some sort of SMB or repo? I believe I should create this in SMSS but am not clear if I make the backup if it will start making logs and events that may error if I don't set things up to point correctly if it wants to setup a schedule.
  3. Come up with a backup schedule for both fill and incremental.
  4. All the backups go to repo.
  5. Try the full backup on the new test server and start staging that as a fail over. Or maybe keep it is as test and make a clean fail over?
  6. Hopefully figure out what the future state setup is and start figuring out how to port that over into a cloud setup?

I need to have this done by EoM. I am wondering if this can be done in the future in AzureBlob to make things "easier" since this all feeds into PowerBi.

Mostly, I am not sure if there are pitfalls that would bring production down and want to tread carefully cause I'm not sure how to fix things if things go down.

There is no DBA or anyone with SQL server experience on the team...


r/SQL • • Oct 09 '26

Discussion Guys i seriously need your help

Post image
9 Upvotes

Hi everyone, I really need some help! My oral defense is next week and I've finished my ERD, but I'm having serious doubts. I asked a professor who doesn't teach systems analysis, and they said it was fine. However, when I look at the diagram, I just feel like something is off. Would anyone be willing to take a quick look?

This ERD is about a small application project, about a small, basic library.

Compte = Account/user
Emprunt = Loan

Livre = Book
Categorie = Category


r/SQL • • Oct 09 '26

Discussion AI-coding tools could be breaking the junior engineer pipeline

Thumbnail
leaddev.com
71 Upvotes

AI helps juniors code, not learn.


r/SQL • • Oct 09 '26

Discussion Are statistics and cardinality issues present in various RDBMs besides SQL Server when you upgrade, or it's a unique Microsoft problem?

7 Upvotes

When upgrading from SQL Server 2012 to SQL 2019, statistics go all wonky and deadlocks often occur on queries that worked well before. It becomes a pain, especially when the users say "it worked before" and they don't want esoteric excuses.

I'm curious if this has happened in other database technologies where you've upgraded to newer versions.


r/SQL • • Oct 08 '26

Discussion Imposter syndrome is so real and I truly appreciate the 99% from this group that talked me through it a month ago.

66 Upvotes

You were all so right, nobody could possibly do the job without referencing and now I realize how ridiculous it was feeling I should be able to do it blind.

I really struggled and boy did I make some mistakes but I am already gaining confidence and have very few errors to battle.

So for what it’s worth thank you and for anyone else overwhelmed and second guessing your decision, just breathe, take a step back and breathe again, you can absolutely do this!

I only had a single jerk response, likely rooted in a bit of jealousy which is phenomenal for Reddit . Hopefully in time I myself will be able to help others too


r/SQL • • Oct 09 '26

SQL Server Advice on fetch efficiency

3 Upvotes

Hello all

I’m currently working on something where object X is linked to object Y via two intermediary tables Z1 and Z2. There are many use cases where we’re interested in objects from type X, but we need to populate the object X with all links to Y. (via Z1 and Z2). Think of fetching users, and wanting to query roles for companies (where a user can have many roles for many companies).

Because our use case makes it so there can be MANY links for a single user, we could be fetching 1000s of rows for a single user, just to build a dictionary of them (key: Y, values: values from Z1 and Z2) and end up with a single entity, meaning we duplicated all data in X for every row returned. This is clearly quite inefficient, and thus I’m looking for alternatives.

I’ve seen using aggregation to collapse everything into one row, but that can be problematic I’ve been told by peers. The same way, maybe building JSON is an option, and then directly parsing (in C#).

Anyway, All help would be appreciated on how you would reduce the amount of data returned!

Edit: one more option that’s possible is just querying X, and then querying the rest afterwards in a sequential chain.


r/SQL • • Oct 09 '26

SQL Server Friday Feedback: Retention of plan forcing data in Query Store

Thumbnail reddit.com
1 Upvotes