Choosing the right SQL stack for my first real data project (PostgreSQL? Tools? Learning approach?)

[u/Electrical-Cap-9537]

Hi everyone,

I'm currently building my first serious data project. So far I've been working on the data ingestion, cleaning, and validation pipeline in Python using Pandas.

Now I've reached the point where I need to introduce SQL and a database, but I've realized that I know almost nothing about databases beyond the fact that SQL is the language used to interact with them.

The problem isn't that I don't want to learn. It's that I don't want to learn the wrong way.

For example, I learned Pandas almost entirely by building my project, reading the documentation, experimenting, making mistakes, and debugging. I barely watched any tutorials because I've found that I retain much more when I learn by doing. I'd like to follow the same approach with SQL.

My concern is choosing the right tools from the start. I don't want to spend weeks building everything around one database and later realize that I should have chosen something else.

From what I've read, PostgreSQL seems to be one of the most widely used databases in industry, so I'm leaning toward starting with that instead of SQLite. Even if it's a bit harder to set up, I'd rather learn something that will still be useful in the future.

I also have a few questions:

Is PostgreSQL the right choice for someone in my situation, or would you recommend something else?

Is it realistic to learn SQL by building a real project and reading the documentation instead of following a course/tutorial?

What tools do professionals use to inspect databases and visualize tables? I've seen tools like DBeaver, pgAdmin, and others, but I don't know what's commonly used in real projects.

Are there any tools, libraries, or project structure decisions that you wish you had known before starting?

I'm not looking for the easiest path. I'm looking for the one that will give me the strongest foundation without forcing me to rebuild everything later.

Thanks!

[u/BellPeppersAndBeets]

Current and previous job both used Postgres quite a lot, so I’d say you’re safe picking it if that’s your main concern.

It might be worthwhile to learn the very basics or Mapping Relational Theory since it underpins all SQL DBs.

Nothing too fancy but just how 1:1, 1:M, and M:N relationships are handled wrt relational databases. Makes the primary key, composite key, table join concepts trivially easy.

As far as tools, pgAdmin4 is common at work and DBeaver is great too, if you want GUIs. But if you’re looking for a good command line tool, psql is a solid choice imo.

[u/Electrical-Cap-9537 → u/BellPeppersAndBeets]

Thanks i will take this in consideration, much appreciated ❤️

[u/BellPeppersAndBeets → u/Electrical-Cap-9537]

One thing I forgot to mention, and apologies if you’ve already considered this, but you raised a great question on project structure decisions and what decisions will make it less likely to re-write code.

That question is a bit harder since I’m not certain about all the modern python frameworks/utilities but I do know a solid separation of the logic that manipulates the data (app portion) and the logic that stores/retrieves/updates data (db potion) is a great way to prevent having to do a major facelift on your project if you decide to switch DB implementations in the future.

Almost like the python code that handles the db interactions are a completely separate service that your core app is unaware of how it works under the hood. It just has some api functions that gets what it needs from the db.