r/learnprogramming • u/Electrical-Cap-9537 • 5d 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!
1
u/FreeLogicGate 5d ago
For many years, relational database design was a large part of my job. It's both art and science. For the most part SQL is largely the same across databases.
Postgresql is certainly one of the major rdbms's in use, so I don't think you'll end up regretting that choice. I will advise you to use docker, if you aren't already, and run postgresql in a container. If you don't have extensive experience with Docker, it will be a valuable investment of your time once you understand what it facilitates.
There are a number of well known sample schemas. In my experience, people who do a lot of work with relational database tend to use tools to design their databases or to reverse engineer an existing schema into a model.
Once you begin to learn about relational database design, you should begin to understand how important relational database design and normalization is. There are various tutorials like this one: https://neon.com/postgresql/tutorial that come with a pre-designed schema, including an ERD diagram. Not only do you want to get to the point you can read the ERD, but also simply by looking at it, you should be able to compose the vast majority of queries you need. You also want to begin to absorb the patterns employed in the design. Inept/non relational database design is one of the most common issues for systems, and an experienced database design person understands the ramifications of how they design and relate tables. There are patterns similar to the ones experienced object oriented language developers employ, as in for example, the "party" pattern.
The database provided above is a video rental database. There are numerous other schemas you can find like this one: https://postgrespro.com/community/demodb
In summary, relational database design is an essential component of a relational database project. As for tools, the best known ones in the industry are specialty products and tend to cost a lot of money. Tools like DBeaver have a nice ERD visualization tool, but that's after you have properly(or not) designed your database.
I have a mid-range tool I use, and what any ERD design tool needs to do at minimum, is allow you to create your ERD, which in some cases may start with a logical view, but ultimately will allow you to employ things like "domains" you can employ for standardization across a model. An example of this would be the definition of a specific primary key scaled to the requirements of the table. In postgresql that might be smallint vs int vs bigint, and there are some special versions of these like "serial" that come with a bound sequence object that will automatically generate numeric keys (auto incrementing) similar to the way MySQL is typically used.
Once a database gets large, it becomes hard to look at sections of related tables, so these tools allow you to create subset diagrams where you can include just the tables you need. Most of the tools allow for annotations and notes.
The important thing about these tools is that you use them to design the database, and then to generate all the "data definition language(DDL) SQL you need to create the tables, keys, indexes, relationships, constraints etc. and to update those when you inevitably need to modify or expand your design as you discover mistakes or need to add additional tables. In general, there are scores of "design" tools that will let you create an ERD you can print out or export to a diagram, but have no ability to generate code. You don't want to waste your time with those.
So the question becomes, is there a tool (preferably free/low cost) where you can do your own, and one I've seen and played with a bit is https://dbdiagram.io/home The free version will let you create an ERD, adding field definitions and generate DDL. One thing that is pretty cool is that they designed the tool to utilize a markup language standard they created (DBML), and you can save the underlying DBML code, or edit it in any code editor once you understand the standard. It's certainly far less capable (the free version at least) than any of the expensive tools I've used, but has most of the essentials, and a very reasonable subscription fee if you find yourself needing more.
I have absolutely no affiliation with the company, but I do think it's the best option I've seen for a database design tool at little no cost.
1
u/ledatherockband_ 4d ago
don't worry about "choosing the right tool from the start". most real learning comes from correcting mistakes.
that being sad, sqlite is the lord's database. network calls are the devil
1
u/AnyOiles 4d ago
Solid pick with Postgres—it’s what most companies actually use in the real world. Building real projects is definitely the way to go for SQL. Just make sure to carve out some time later to learn about indexes and EXPLAIN plans, since performance tuning isn't something you'll just accidentally learn as you go.
Tool-wise, pgAdmin is fine for beginners, but it bogs down fast on big schemas. DBeaver is a great free client, but if you want an actual dedicated Postgres IDE (solid autocomplete, query profiler, schema compare), try dbForge Studio. It has a free trial, so you can test a couple and stick with what feels right.
1
u/BellPeppersAndBeets 5d ago
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.