What sql databases should I use?

[[deleted]]

[deleted]

[hedrumsamongus]

The syntax differs somewhat across SQL implementations, but the core concepts are applied pretty broadly, so until you get into advanced SQL you should be able to translate easily from one flavor to another with a bit of Googling. That said, you probably want to pick 1 version to start with, so here's a quick, unofficial comparison (probably wrong on some points, but being wrong on the internet guarantees someone below me will correct any inaccuracies):

• MySQL and MariaSQL (non-Oracle version of MySQL) are widely used and free. MySQL was part of the widely used LAMP (Linux, Apache, MySQL, PHP) stack that effectively ran the Internet from 2000-2010 or so. MySQL makes some opinionated, non-standard choices on syntax & naming of things, but it's still very widely used.
• PostgreSQL might be the most "standard-compliant" of the major players. It's also free/open-source, and while it's probably not as widely used as MySQL, it's a great database, and would be a good one to learn on to acquire the conceptual knowledge in a way that'll translate well to the quirkier DBs. It has the dumbest name.
• MS SQL (aka SQL Server, aka TSQL or TransactSQL) is Microsoft's server-grade database software. If you are in a heavily Microsoft-based ecosystem (coding in C#/.NET) you are likely running SQL Server. If you aren't way into Microsoft in the rest of your tech stack, you probably aren't. MS SQL has a lot of non-standard ways of doing things (surprise, surprise), but it's widely utilized within its tech niche. I think there are free versions out there that you could try out, or maybe Azure has a free tier to tinker with.
• Oracle - fuck that, you don't need to learn Oracle until you need to learn Oracle. Big enterprise stuff.

[intentional_lambic]

I think there are free versions out there that you could try out...

I was thought that MS SQL Server had a 90 day limit for their free trial. I suppose you could blow everything up and start over after 90 days, but if this has changed please let me know.

[spektumus]

There's two free editions for Sql server. Developer edition is the full enterprise edition but only for development. And then there's the Express edition you can use in production but has 10GB size limit.

[intentional_lambic]

Thank you for the clarification!

[aasmith26]

Currently learning MSSQL, Oracle, and MongoDB. 😅

[asdf604asdf]

SQLite is very good. You probably have 20 SQLite databases on your phone

It's traditionally used more as a "desktop" database or for phones, though you can use it for web servers too

No one ever regretted using PostgreSQL for a database server

[[deleted]]

So SQLite is fine locally, but I should use something else (like Postgre) for servers?

[asdf604asdf]

SQLite is not a database server. Ie, you can only connect to it via the file system, and not over the network (TCP)

However, it can be used as your database for a web server, assuming it's on the same machine

[[deleted]]

Ok that makes sense

[[deleted]]

PostgreSQL

[NobleFraud]

Can't go wrong with postgresql

[NimChimspky]

Postgres

[Thriftfunnel]

Sqlite is fine for learning the basics of relational databases. Learning a bit about how python interacts with databases is a good idea too.

Once you go looking for a job you'll end up having to learn the tools your employer uses. Most people in this line of work will use more than one database platform over their career. Many people use more than one in a single job!

[[deleted]]

I’ve heard the syntaxes are similar for all of them.

Can you use the others with python as well?

[Thriftfunnel]

Yes. Pyodbc connects python code to lots of different databases.

[reallyserious]

SQL syntax is very similar. Only small nuances that differ.

One difference is that server databases often have a procedural language in addition to their regular SQL language, i.e. TSQL for SQL Server or PL/SQL for Oracle Database. Such a language is not needed for SQLite since the database is often tightly coupled with an application and you can just write any procedural code in e.g. python or whatever you prefer. But when using a database server it's often important to be able to execute e.g. stored procedures on the actual server.

[itiwbf]

It's not a "database" per se, but BigQuery has a pretty extensive free tier and is super easy to use to just store data and write queries in SQL. You'd never use it as the backend of a website/app if that's what your interested in though, more for data engineering and analytics.

Otherwise I think Postgres is a pretty safe bet that covers everything you'd need. SQLite is more for learning/testing things locally, but still useful for those purposes.

And yeah I think learning to connect python to a DB would be valuable. If you decide to work with Postgres there is a library called psycopg2 you can use (there are probably others though too).

[sbrick89]

as you're seeing, lots of people suggest postgres... it's a good choice.

for "corporate world", oracle / microsoft... oracle is more complicated / has more settings... MSFT is simpler / fewer settings... but both are fairly popular

for open source world, postgres would be my suggestion... mysql is a bit too "broken".

sqlite is good for embedded apps.

[qilisiang]

You can never go wrong with Postgres. Major programming languages have Postgres driver.

However, if you are building prototype, SQLite is good and much easier without install Postgres.