Many small queries are efficient in SQLite

Re:Many small queries are efficient in SQLite by reacharavindh on Tuesday February 16, 2021 @06:08AM (#26152851)

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.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by jrochkind1 on Tuesday February 16, 2021 @12:51PM (#26156643)

> 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?

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by JohnBooty on Tuesday February 16, 2021 @11:22AM (#26155446)

    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.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by m1sta_ on Tuesday February 16, 2021 @06:15AM (#26152884)

Duckdb is like sqlite but with the postgres SQL parser.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by masklinn on Tuesday February 16, 2021 @09:32AM (#26154154)

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.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by Wohlf on Tuesday February 16, 2021 @10:31AM (#26154769)

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.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by grandinj on Tuesday February 16, 2021 @06:22AM (#26152917)

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

Disclaimer: H2 dev

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by alimbada on Tuesday February 16, 2021 @10:43AM (#26154920)

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.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by listenallyall on Wednesday February 17, 2021 @05:25AM (#26164837)

H2 rocks. It is fantastic. Thank you.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by hifly on Tuesday February 16, 2021 @05:45PM (#26159752)

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

[ Parent | on HN ]