Hacker News Reader

Comment by reacharavindh | Hacker News Reader

I have only worked with PostgreSQL and when the wife(Accounting Professor) needed a DB to teach their students SQL, I heartily took her down the SQLite path because of its simplicity. It may not have been the best decision as I see her now fighting with its warts like

1. No type checking! Declare a column as int in DDL, and insert a text, SQLite happily stores it without an error :-(

2. No full outer join... instead my wife taught the students the standard SQL syntax and told them to use UNION with SQLite because it does not support that syntax. This is where it gets annoying.

I can’t recollect, but there were issues with views as well which sounded really weird.

I now think the choice of SQLite is more nuanced than I believed it to be.

View on HN

> No full outer join..

I'm kind of shocked that it doesn't offer a full outer join, as it is such a basic part of relational algebra, really in some ways the conceptually simplest kind of join... but I also am not sure that I have ever even once actually wanted a full outer join.

You really do mean full outer join there? Or, quite possibly, is it me who's become confused about what a full outer join is?

    No type checking! Declare a column as int in DDL, and
    insert a text, SQLite happily stores it without an 
    error :-(

This is the one that absolutely baffles me. They very explicitly view it as a "feature, not a bug" but I am not sure why -- I've not seen a deeper rationale behind this decision.

https://www.sqlite.org/faq.html#q3

Nonetheless though, SQLite is excellent overall and I'm so, so thankful for it.

Duckdb is like sqlite but with the postgres SQL parser.

Does it also have the features? Like a returning clause? Or is it similarly gimped and restricted to a half-assed thread-incoherent function?

edit: I'll take the downvotes as a no, and apparently last_insert_rowid somehow has fans.

The Database Design course I took in college started on Access and moved to SQL Server which I found to be quite nice, may not be an option for your wife though since we already had student licenses through the college.

Maybe there's a PaaS or SaaS option that would work for her? Something like Jupyter notebooks maybe.

try h2database.com if you don't mind Java, has pretty full SQL support (except for weak support for CTEs)

Disclaimer: H2 dev

H2 is great. Thanks for your work on it.

We used it on my last project as an in memory database for running interaction tests between Dropwizard resources and the database in our CI pipeline. We had Liquibase running migrations during the setup for each test and DbUnit setting up test data.

H2 rocks. It is fantastic. Thank you.

I don’t think MySQL has/had full outer join either?

Reply on news.ycombinator.com