5 ms·
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 bec
by reacharavindh 6y ago
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.
- m1sta_ 6y agoDuckdb is like sqlite but with the postgres SQL parser.
- masklinn 6y agoDoes 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.
- grandinj 6y agotry h2database.com if you don't mind Java, has pretty full SQL support (except for weak support for CTEs) Disclaimer: H2 dev
- alimbada 6y agoH2 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.
- listenallyall 6y agoH2 rocks. It is fantastic. Thank you.
- Wohlf 6y agoThe 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.
- JohnBooty 6y agoNo 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 https://www.sqlite.org/faq.html#q3 Nonetheless though, SQLite is excellent overall and I'm so, so thankful for it.
- jrochkind1 6y ago> 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?
- hifly 6y agoI don’t think MySQL has/had full outer join either?