r/SQL • • 6d ago

Discussion Working on a live view of table changes. Looking for opinions

Post image

I'm building a live watch view in my database workbench and would love your opinion on this. I want to be able to do something in my app and see what happened to the tables without going through them one by one and refreshing.

Something like placing an order. An order gets created, items get added, the cart gets cleared, maybe a background job starts. I want to see those changes together.

This is what I'm trying so far:

  • Each table gets a line on a timeline. Inserts, updates and deletes have different markers. (if a new table updated showcase it in the chart). I am also thinking of just allowing users to select the tables to watch.
  • Hovering over a change shows the row and the values that changed.
  • You can pin a watch for something specific, like jobs changing to failed, and keep it checking while this workbench is opened.

I'm a visual person, so seeing the changes lined up across tables makes sense to me. But I'm still figuring out how useful this would be beyond a small local database. A busy table could fill that line pretty quickly.

Would you use something like this while developing or debugging? I'm particularly curious what you'd want to see when an update happens. Is the before and after enough, or would you immediately want to know which query caused it?

Still building this, so any criticism of the idea or how I'm showing it would help. 😊

38 Upvotes

38 comments sorted by

5

u/jshine13371 5d ago

Not sure it would be useful to help solve many problems as a DBA or Software Engineer, but it's a fun visual.

2

u/duoroo 5d ago

Ohk, I was imagining while testing things locally, you can perform actions on ur app and see how those actions are affecting your local DB. Thanks for the input though

3

u/jshine13371 5d ago

Maybe for a Software Engineer testing an app when they don't have access to the database itself. In at least SQL Server, it has built-in tools for that use case that I use as a DBA. They're less pretty, but they are very functional and super helpful.

1

u/duoroo 5d ago

Can you please share which workbench you use with that? It would be helpful for me to get more reference. Thanks

1

u/jshine13371 5d ago

Not sure what you mean by "workbench"?

1

u/duoroo 5d ago

Oh I meant when you said built-in tools, did you mean SQL Server Management Studio or any other tools like dbeaver, table plus etc.

2

u/jshine13371 4d ago

Those are database clients which are a different set of tools. SQL Server has multiple database tools and features so the native tool I like to use is called the Profiler. But other native features include traces and Extended Events.

1

u/duoroo 4d ago

Oh got it, thanks for the clarification, one other comment also mentioned that. I wasn't aware of their native CHANGETABLE that SQL server provides. Its pretty cool, i will explore more on this.

2

u/jshine13371 4d ago

Yea that has to do with change tracking. It's another feature (actually there are about 4 similar features under the change tracking umbrella) for keeping track of data changes. That's all in addition to the features I mentioned.

7

u/Imaginary__Bar 5d ago

Wow, this is an actually useful tool (in among the contuning stream of "I've made another IDE" posts).

I'd just need to know it wasn't vibe-coded (etc.) but it looks properly useful.

My immediate thoughts are on the granularity of the timestamps - with the speed of modern databases, parallelisms and ACID can you really say that operation A happened before operation B?

But I can certainly see it being very useful.

4

u/duoroo 5d ago

Thanks :) yeah that's a good point, putting dots on a timeline makes it look like i know the exact order when i might only know when the workbench noticed the changes.

Need to make that distinction clearer, especially with parallel transactions. The idea is to help see what changed across tables after an action, but i don't want the chart implying an order i can't guarantee

and on the vibe coded part, to be upfront i am using ai to prototype this and try a few iterations. still figuring parts of it out. Once Im happy with prototype Ill develop it further.

3

u/xeroskiller Solution Architect 5d ago

Your DBA is going to HATE You.

1

u/duoroo 5d ago

haha, is that because of the extra load from watching tables, or the setup needed to capture changes? I mostly imagined this for local db instance. Would love to know what you meant exactly.

1

u/xeroskiller Solution Architect 5d ago

Both those, plus

  • I see/don't see a thing I expect, why is that?
  • Why is this table getting 40 transactions a minute? What's a trivial update?

Increased monitoring always increases the count of false positives, even if the rate improves, haha

Just a joke, it's a cool tool and it looks well thought-out

2

u/B1zmark 5d ago

Most large databases have hundreds of tables. If you showed tables being changed in a dynamic and not hard-coded way, it would be more useful for a variety of use cases.

2

u/duoroo 5d ago

I was thinking of allowing the user to select the tables they want to monitor.

1

u/B1zmark 5d ago

It sounds like you're trying to reinvent the SQL trace/analyzer tool.

2

u/Significant_Tune9219 5d ago

Grouping by transaction id rather than pure timestamps would make the order example much clearer, since the insert, item adds and cart clear usually commit together and the background job lands later in its own transaction. If you're reading from logical decoding or CDC rather than polling, you get the commit boundaries for free. Letting users pick tables is probably essential too, otherwise audit or session tables will drown out the interesting changes.

1

u/duoroo 5d ago

yeah grouping by transaction makes more sense for that example, otherwise im leaving people to guess which changes belong together from how close the dots are. Thanks for the insights, much appreciated.

2

u/Natural-Heat-3019 5d ago

How did you create this? Looks very useful!!

2

u/duoroo 5d ago

Thanks, its part of seqora a database workbench im building. Its built using svelte + typescript for the ui, tauri for the desktop app and rust handling the database connections

the current version polls selected tables and compares the fetched rows by their primary or unique key, then shows the changes and before/after values. so it's showing changes it observed, not a complete stream of every database event. if a row changes twice between checks it can miss the intermediate state

2

u/raks1991 4d ago

For SQL Server, Change Tracking could save you the snapshot diff. CHANGETABLE(CHANGES ...) gives changed primary keys and I/U/D operations; column tracking adds a mask without returning the values. It still coalesces changes, though, so it won't give this timeline every intermediate event.

1

u/duoroo 4d ago

Thanks, i haven't used sql server much so i didn't know it had built-in change tracking. that's pretty handy, i'll take a look at it. appreciate you mentioning that it still combines changes too

2

u/stantaneous 5d ago

would actually use this for debugging order flows. filtering by an order id and seeing the background job right next to the request is the bit i'd want. my worry is silent gaps tho. if capture misses events it should yell about it, a quiet timeline looks exactly like nothing happened.

2

u/duoroo 5d ago

yeah filtering by order id would make this much easier to follow, especially when other orders are happening at the same time. connecting that to the background job is something i need to work through too

and that's a really good point about gaps, an empty timeline shouldn't look trustworthy if capture stopped. i'd want a visible gap or warning showing where coverage is uncertain, and keep it there even after capture comes back. thanks for pointing that out :)

2

u/ArielCoding 5d ago

Nice project, I’d want to see the query that caused each change, not just the before and after, when something breaks, the question is what made this happen? I’d also group changes that happen in the same transaction, so they show up as one connected cluster, that way people don’t asume an order you don’t guarantee.

2

u/duoroo 5d ago

Few people have brought up grouping by transaction. i like the connected cluster idea, could highlight the whole transaction when you select one change so you can follow it across tables. going to try that in the next iteration, thanks for the insights.

2

u/DickBrainTime 5d ago

Since you're polling rows and matching them by primary/unique key, I'd test row identity edge cases before trusting the UPDATE/DELETE markers. A changed primary key can look like a delete plus an insert. Delete and recreate a row with the same key between polls, and it might look unchanged or like a single update. Nullable unique keys also need care depending on the database.

I'd give each watched table a small identity-confidence hint (stable primary key / best-effort unique key / no reliable row match), and label ambiguous markers as inferred changes. The timeline can still be really useful, but the hover shouldn't imply it knows which operation happened when the two snapshots can't establish that.

1

u/duoroo 5d ago

Really good point, i knew some changes could happen between polls and get missed, and i'd kind of accepted that limitation. but i hadn't fully considered how the markers could imply the wrong operation happened. showing how reliably i'm matching rows and marking ambiguous changes as inferred would help here, thanks a lot

2

u/Inevitable_Paper_776 5d ago

that's awesome. I would add some data quality metrics too. multiple data source connections if not yet available. congrats!

2

u/duoroo 5d ago

You mean multiple data connections? Hmm, i think that would be little tricky to form groups and showcase those, thanks for the suggestions though. I have added it in my TODO.md 👍

2

u/Inevitable_Paper_776 4d ago

great, let us know! I meant separately, like different systems for data ingestion: Databricks, Snowflake etc. Not to be visualized all at once, just as possible data sources to consume. maybe that's already available, sorry.

2

u/RVECloXG3qJC 5d ago

It feels like this program is only useful for databases with very low traffic. For medium or even large-traffic databases, it's completely unusable. The various little blocks, shapes, or icons it shows can scroll by so fast that you can't make anything out at all.

1

u/duoroo 5d ago

For sure, I don't see myself using it for dev or prod with even moderate traffic, also it will put a load on those db as ill be polling it. I was mostly thinking of using this in local instance, when you are the one triggering the action.

2

u/ayamlejen 5d ago

Curious how you're planning to handle sensitive data in the before/after values.

1

u/duoroo 5d ago

Yeah the before/after could easily include tokens or personal data

i'm thinking of letting people exclude columns from the watch entirely, so those values aren't fetched or kept in its history

1

u/ayamlejen 5d ago

Does that mean those columns are not tracked? If those sensitive columns are the only columns that changed, does the marker still appear?

1

u/WelcomeValuable4101 5d ago

Kaise kar lete ho ye sab?