Hacker News Reader

Comment by danso | Hacker News Reader

I teach SQL to non-techie students. I used to give the the option of doing either MySQL or SQLite, but not only did I underestimate how different the syntaxes were, I also underestimated how not-trivial it is for students to successfully install and run both the MySQL server and client. These are students who can't even use a spreadsheet well, not that that makes a huge difference in understanding databases.

I've moved everything to SQLite and couldn't be happier. Not only is it easier to distribute assignments (e.g. a single SQLite file, instead of CSVs that need to be manually imported), it does everything I need it to do to teach the concepts of relational databases and join operations. This typically just needs read-only access, so our assignments can involve gigabytes of data without issue.

View on HN

"I used to give the the option of doing either MySQL or SQLite"

SQLite is good choise for absolute beginners.

Later, when teaching "real" multi-user RDBMSes, although MySQL may be more popular, it makes more sense to teach PostgreSQL as "default open source database". Both will do the job, but PostgreSQL has got more stuff right from the beginning, which is especially important when learning. Think PHP vs. Python, PHP may cut few corners but it's not ideal language for teaching generic concepts.

>Think PHP vs. Python, PHP may cut few corners but it's not ideal language for teaching generic concepts.

Maybe not even Python, suggest go or rust. My generation we started learning CS with Pascal and moved to C, when we had a bit more understanding.

I think it's "Python will teach you more about programming, C will teach you more about computers".

I'm not intimate with Go, but I figure its clean and simple design makes it a good first language; and in particular a good preparation for C, because of its focus on memory layout.

Rust? NO! One of the worst choices for a first language. Similar to C++, Rust is really heavy on concepts.

SQLite teaches virtually all of the necessary concepts to jump right into a multi-user RDBMS, even most of the syntax, with the exception of being more lax about datatypes. My audience is not people intending to build applications, but to do data analysis beyond what they can do in your typical spreadsheet. I don't think the PHP vs. Python analogy is particularly accurate. I don't feel that PHP is easier to learn than Python unless the main goal is to produce something public-facing on the web. Nevermind that the syntax is significantly different.

If it's just too learn SQL then would a LibreOffice database do, perhaps they removed it but you used to be able to use (or perhaps just import?) a spreadsheet as a DB and run SQL against it. Definitely did joins and order-bys using standard SQL syntax.

Libreoffice now use firebird under the hood I think.

Firebird has a more complete support for sql than sqlite has. It also has the advantage of being able to be used both in a manner analogous to sqlite or as a full blown server database.

I actually think SQLite could probably be used from beginning to advanced level, depending on what you're teaching (relational theory for example), but perhaps I'm not aware of its' limitations in that regard.

That being said have you considered using docker (or other popular container tech) for making installation of MySQL easier?

And I should also add - what about cheap cloud SQL instances/services as well? Perhaps the students could learn a bit about how simple it is to use some cloud resources too without the management overhead?

Note: http://postgresapp.com/ is trivial to install, although it only works on macs, is still more involved than a file, etc etc...

The one thing missing from SQLite is stronger type casting...however, SQLite does have a solid, cross-platform GUI in DB Browser [0]. It's not as nice as Sequel Pro and some of the commercial clients for MySQL. Postgres, unfortunately, doesn't have near the variety of reliable clients that MySQL and SQLite do.

However, SQLite follows most of the standard syntax that Postgres does, so having students move right into CartoDB, which lets you run raw Postgres, has never been a problem.its just the self hosting that's a pain :).

One more thing that I wish I could have from Postgres as a teacher: how it returns an error when you include non-aggregated columns in the SELECT clause. In MySQL and SQLite, selecting extra columns won't throw an error, and the results look close enough to correct to be fairly dangerous for novices. Better to just throw an error as Postgres does.

[0] http://sqlitebrowser.org/

Another good cross-platform GUI for SQLite that I've been using for years is TkSQLite (http://reddog.s35.xrea.com/wiki/TkSQLite.html)

Sqlite was a postgres fork, off of 8.something, IIRC

No. This is incorrect.

It owes something to PostgreSQL but it's the syntax, not the source code. I quote from https://en.wikipedia.org/wiki/SQLite#History

    Hipp based the syntax and semantics on those of PostgreSQL 6.5. In August 2000, version 1.0 of SQLite was released, with storage based on gdbm (GNU Database Manager).

According to these slides from Sqlite founder, Sqlite was a "spin-off" of Postgres: http://www.pgcon.org/2014/schedule/attachments/319_PGCon2014...

According to this, "SQLite was originally written from PostgreSQL 6.5 documentation" https://www.pgcon.org/2014/schedule/events/736.en.html

So, "fork" wasn't the right word. Apologies.

Yet another free, open source and multiplatform GUI is SQLiteStudio ( http://sqlitestudio.pl/ ).

Postico (https://eggerapps.at/postico/) is a really nice PostgreSQL client for OS X.

Cool timing. :)

We (DB Browser for SQLite team) put this one-pager up yesterday about our upcoming "SQLite in the Cloud" project. :)

https://dbhub.io

While that may be true for Apple owners, it's very untrivial to install and properly configure (permissions, etc.) on Windows and Linux.

SqLite usually "just works", can easily be reset if you do something wrong (just delete file!) and has better GUI browsers and tools.

Linux? We're okay thanks. Apt and YUM (and the rest) make installing both MySQL and Postgres very easy.

I was rather surprised to find that installing PostgreSQL on Windows was loads faster and easier than installing MS SQL Server.

Reply on news.ycombinator.com