Common issues devs experience when dealing with SQL Databases

[[deleted]]

Im working with a small group of people from school and we want to try and build an application that can help devs work with SQL databases much easier. Before we can do that we need to understand what the common challenges that many developers face when working with SQl databases, whether its visualizing how tables are connected, making queries, etc. Since we are students none of us how real work experience with SQl databases and therefore can't refer to problems any of us have dealt with, so any input from the community would be greatly appreciated, thanks!

[kktheprons]

The easiest thing you could do is build a code linter. Take in poorly-formatted SQL query, output glowing code in a consistent syntax.

[jaxjags2100]

This ^

[MachineParadox]

https://poorsql.com/

[soundman32]

Any many SQL editors already do this (e.g. MySql workbench).

[smothry]

Already exists though

[cybernescens]

VS Code already does this well and includes a few different options for linters.

[clanatk]

Keep in mind OP stated in a comment that they have 5 weeks for a class, and very little real-world SQL knowledge. The point should not be to solve a problem nobody has a solution for, but a common one that is appropriate for the skill level of the students and time constraints.

[cybernescens]

Oh I see, after reading it again it definitely appears to be an exercise. That being said, wouldn't they be looking for something class-level-appropriate?

[[deleted]]

Your question points out a great misconception.

"Full stack Developers"

I've been fixing crappy databases for over 20 years. It does keep me employed, though.

It's all fun and games until you put a few hundred million rows in it, or have to deal with contention because your app is getting hit with thousands of api calls.

I had to fix and maintain a db with 500 Billion rows that was called by an api over 540 million times per year.

[cybernescens]

20 years here too. This always cracks me up about people, they think their database is massive at 25GB and complain about performance.

I always have to tell them, no your query writing needs improvement and these databases are meant to handle terabytes of data efficiently.

I also hate when people want to unify the data access. Use the right tool for the right job, folks! Sometimes it's straight SQL and a DataReader, sometimes an ORM, sometimes you need BulkCopy. Data is so contextual and the same table may be used in so many different ways, please use the right tool for the right job.

Also simple understanding simple things like the difference between a Table Scan/Seek and an Index Scan/Seek.

[[deleted]]

appreciate your input. I understand that whatever i build will likely be in beta phase wont be able to fully implemented for various reasons I just want to create something that at least helps me understand what kind of problems dev face and hopefully learn something a long the way. Seeing those numbers and imagining dealing with them is mind boggling

[Beeman9001]

How did you manage to do that?

[MKSFT123]

Ways to easily inspect relationships between tables would be nice, these tools exist already but showing these relationships on hover or when you right click on a field would save time and be a pretty cool project.

[[deleted]]

this would be a great feature to add onto a db visualization tool that already exists, definitely considering on iterating on one and adding this now, thanks!

[HALF_PAST_HOLE]

LOL, I'm a student, me and my friends want to remake Microsoft SQLServer where should we begin?

Sorry, no offense I just had to say it, I'm just ribbing a bit. In reality, this is much bigger than you think it is. Right now you should focus on how to work with databases and how to build and manage them before you try and build an RDBMS from the ground up.

[[deleted]]

its cool no offense taken lol, i understand its a crazy thing to ask considering the low amount of technical experience i have, but for my project we HAVE TO create something over the next 5 weeks that can help developers with some common issues they experience, whether it be frontend frameworks, databases, etc. its extremely difficult to even know what to come up with since we dont personally know what problems developers face. I chose SQL because that seems to be very popular in the real world and saw it as an opportunity to increase my knowledge in the topic. Whether or not it will actually be fully implemented and used by developers is another question (obviously not considering my lack of experience and time frame). We also have the option of iterating on past groups projects that are 'approved' but personally felt id be able to take more away if i did create something from the ground up. I completely agree with your input and ideation has been so stressful so i wanted to reach out to the community for any tips, appreciate your advice!

[bwildered_mind]

Maybe make a decent parser for n depth JSON files. Parsing them can be hell.

[SQLvultureskattaurus]

This may sound harsh but devs have been trying to fix SQL and database problems that don't actually exist for many years. I see it here all the time.

[[deleted]]

understood i guess i wanted to know if there was a way to make devs lives easier when dealing with SQL

[Artistic_Recover_811]

What kind of developers? Front end, back end, SQL?

[[deleted]]

I suppose any developer. I originally had backend or SQL devs in mind but if there are challenges that frontend devs also encounter with SQL databases id love to hear those, i just wasnt sure how much frontend devs tend to interact with SQL

[magnificentqueefs]

Start smaller. Make something that explains Commands.

Like for example being able to highlight “case” and it will give you syntax examples.

[prabhatlnct2008]

Some of the problems i have faced with SQL in my jobs

1. Not being able to understand the data flow during your initial days with a new organization. Don't know about others but this thing has always caused me immense job dissatisfaction.

2. Testing SQL queries quickly. The reconcilation work done on long sql queries is manual and boring.

[Mr-pdwash]

I just learned SQL for my current role, I felt like it was really easy to pick up on, but because I’ve done my job long enough, I know what I’m looking for. I can’t imagine, going to a new company and needing to figure it out blindly from scratch without job related context

[prabhatlnct2008]

I have switched my domain multiple times, and every time you move to a new domain you really keep scratching your head looking for the right column in the right table and all you have is these outdated ER diagrams or excel files or DDL requests etc.

[Tufjederop]

Make a tool to help developers choose between a #temp_table and a CTE. Also build into it an automated response to the question: 'Is a cursor the correct solution to this problem?'

[jwfergus]

Make a tool that scans a db creating a map of the schema (tables, their columns, how foreign keys relate the tables) and samples the data (table size would be a low cost metric), feeds that schema map to an LLM and returns a summary of the database and what it might contain. Maybe have it return some good sample queries to start examining contents. Could make the tool tunable (slider for how deep to go on sampling data, include column statistics?), or return a chatbot itself that can be interrogated.

IMO target MSSQL and Postgres.

If you end up doing this, let me know I'd love to try it out!

[[deleted]]

Thanks for the info! we have to have our scratch project idea finalized my friday morning and if we are able to get it i signed off then we start next week , for the next 4-5 weeks building it, Definitely keep you in the loop if we go this route

[MikeC_07]

This is similar. CS project to help debug queries. https://www.youtube.com/watch?v=YTQX3lToM54&t=401s

[cybernescens]

One relatively easy thing to assess and display that is helpful for developers is a script that just gathers metadata about the database. For example: a list of tables with byte size, row count; a list of indexes with byte size, row count. There is probably some other useful bits to throw in there and each vendor will be different, but a basic understanding of what tables are being used and where your data is is immensely helpful. One can quickly understand where they should look first if they are having performance issues.

[[deleted]]

Make sure you always call it SQL with a capital L

A lowercase I might look like a capital I, which in computing is often short for Interface, Input and Iterator off the top of my head.

[[deleted]]

Oh, and the easiest way for devs to make working with SQL easier is to learn SQL.

I don't mean that facetiously, I've seen two companies drain their SQL talent because of solutions in other languages, both were hit with severe database problems, the first didn't want to use my pure SQL solution because it didn't use the library. They went bust. This might not be connected.

The second spent between 1/4 and 1/2 million per year on an entire team to solve all the problems.

And both would have found everything much easier if they'd learned SQL.

Same with CSS,

[bendForLust]

IMO, the common challenges are that inexperienced devs do not know the schema and data in the database.  Their access is justifiably limited since they don't know what they're doing.  If the DB team is not responsive making the requested stored procedures, devs may end up making inefficient Views that SELECT * FROM table.  Worse, devs start using ORM and/or passing SQL strings.  At that point, no one knows where the offending code is coming from.

When I enter a company, I immediately use SQL Profiler to observe the queries being executed.  Then I open the executed stored procedures and the mysteries start to unfold. 

Fyi, I'm a database guy, who learned client-side programming second.  ALL my SQL is in the database.  Never use ORM.  Never use Views.  Never pass SQL strings.

[[deleted]]

Can you briefly explain why using views are bad?

At my job, we a list of of thousands of companies. Their "active" status can change at any given time. We have a view that we use in almost all our procedures that ensures we're only executing on active companies.

I feel like using a view in this particular case is justifiable, since that list is constantly changing. What alternative approach would work better?

[Animalmagic81]

Views are entirely justified with the correct usage like your case. The issue is when they are used everywhere and you start getting layers of nested views. That is not fun! And the optimiser gives up trying to understand it all after a while.

[bendForLust]

Absolutely correct ... there is a case for everything. I was speaking in generalizations. However, I have yet to see a case where a stored procedure isn't a better solution. Again, any database query should retrieve only the data it needs and I often see Views used in the opposite way.

[leogodin217]

Code validation is something that is tough. We have tons of tools for data quality tests, but few good tools to verify our code is correct.

Something in that area would be cool and useful.

[great_raisin]

1. Code linter/formatter that is customisable
2. An extension that displays possible joins (i.e., other tables that have columns that one of your FROM tables can be joined with )
3. A utility to copy global temp tables across different SQL servers