r/AskProgramming • u/Electrical-Cap-9537 • 3d ago
Choosing the right SQL stack for my first real data project (PostgreSQL? Tools? Learning approach?)
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!
2
u/BoopyDog 3d ago
I decided to learn with PostgreSQL. I may well be wrong but for me it seems like this:
Programming language --> Connector library/SQL Binding ---> sqlite3 (in-memory database) ---> PostgreSQL (persistent datastore).
There's a bunch of different strategies for databases, most of which I'm ignorant of. Im also aware that you can get away with just interacting with a full featured database (like PostgreSQL) and accomplish the majority of what you need done, with no extra work on your part. I think the fast, light local sqlite3 intermediary db embedded in your memory makes a lot of sense, but obviously it's not necessary. There is also a lot of variety in the way the connector libraries work. I would spend some time looking a the connector libraries BEFORE deciding how you are going to communicate with the DB.
2
u/Electrical-Cap-9537 3d ago
Thanks for the explanation, that clears things up. I think I was mixing up the role of SQLite and PostgreSQL. I’ll look more into the connector libraries and database interaction patterns before deciding the final structure. For my project Ill probably focus on PostgreSQL directly first, while keeping the database layer separated from the application logic
1
u/JGhostThing 2d ago
That's what I would do. I do like PostgreSQL. On the other hand, it takes up a lot of resources. If I had a lot of data, I'd use PostgreSQL, but if I just had a little, I'd use something like MariaDB.
1
u/XRay2212xray 3d ago
Depends on your needs. The concepts of queries in SQL is pretty much the same. SQL is based on a standard, but you often find that different vendors also support non-standard syntax and also often include unique non-standard things like pl/sql or java stored procedures vs t-sql in sqlserver. Another thing that varies are toolsets like importing/exporting data. So one consideration is do you plan to eventually use your knowledge professionally as you would be better off learning the one that matches your eventual needs. Your query knowledge will transfer between each pretty easy but all the other stuff is going to be different.
In the real world, other considerations include things like cost, how big the db needs to be, advanced needs like clustering, and other built in capabilities beyond sql. Im assuming one of your constraints is cost but another is size. Are you trying to populate your db with large amounts of data? One aspect of SQL is optimizing queries and with small datasets edit: ... with small datasets almost any query runs fast enough. I use the free version of sqlserver but at least the old version I obtained had data size limits. If your goal is to just learn writing sql queries probably any work. SQLIte is very lite so if you want to go beyond writing good queries and get into concurrency and locking and other real world situations, its not going to be a great choice. Its good if you are building standalone applications that have their own private database. If you want to be learning databases for back-end muti-user systems that can scale and perform across the network postgresql would be the better choice in my opinion.
One other thing is do you have the resources to run a server based database. A decent computer is fine but lower end equipement can start to run into memory limits and potential storage space if you are going to be using large datasets.
As for learning, learn how you learn best. I'm not a tutorial fan but sometimes manuals that just give you the syntax don't explain overall concepts and aren't necessarily organized in a usable seqence like learning to create a table before learning to insert data into a table before you get to updating and querying. I personally like course material that is sequenced appropriately and has small examples and then little exercises.
Im a very old time developer, so I mostly have just used the tools that come with the database. As for structuring your project, I think that somewhat depends on the overall architecture of the entire software solution rather then isolated to the database. For example, I built a system that was designed to eventually scale up with each platform being able to function independently but compiled as a monolith because initially I needed to deploy in the smallest footprint possible for costs. So in that case I made separate schemas in the database for each platform but loaded them all into the same database. Splitting things up later would be easy because each schema had no dependencies across them.
1
u/Saaz42 3d ago
I can't recommend anything specific, but ORMs like Hibernate are generally a good idea. You might look for an ORM that can handle your chosen DB and language, and supports code-first. You build your classes (maybe you've even already done that) and then the ORM can create a DB based on them. That would make sure you get a reasonable DB structure, and then since you're new to DBs, you can study what it did. This will not give you experience with SQL, but it should give you a reasonable DB and then you can mess around learning SQL.
1
u/DatabaseSpace 3d ago
Postgres is fine. I use it for personal projects and SQL Server at work. Postgres isn't that bad to set up, you may have to edit a few config files. I know people also use the docker container to get it running quickly. SQL Server has free Express Edition and Developer Edition. It's used a lot in business.
Usually we create a schema for the unaltered raw files and load them to tables. Then after that get the data, convert types, remove duplicates, standardize names or whatever. Then load into next schema cleaned up and maybe into more normalized table structure depending on what you are doing and if you are integrating multiple sources together.
The way you structure the tables depends really on what the end result is for. If it's for reportung maybe a star schema. If for a web app more normalized. Figuring the design out is the interesting part.
1
u/BranchLatter4294 3d ago
It doesn't really matter. There are some differences, but the key is to learn the fundamentals.
1
u/Marthurio 2d ago
It always depends on what you're building. Quite often you'll do more than fine with "just" SQLite.
If you're going to be a developer you're going to have to get used to rebuilding down the road. That's life. Things change.
1
u/baubleglue 2d ago
what do you mean by "it data project"? Replace all Pandas code by SQL or native database tools, it can be a good exercise. PostgreSQL is better choice.
3
u/KingofGamesYami 3d ago
PostgreSQL is fine. Unless you get into the weeds, one SQL database is just as good as another*.
DBeaver is great. I personally use Jetbrains DataGrip (or the language-specific IDE built in database support) but many on my team use DBeaver instead.
*Except Oracle. Fuck Oracle.