
Anko SQLite, a library to simplify working with SQLite on Android - andraskindler
https://www.kotlindevelopment.com/anko-sqlite-database/
======
BoorishBears
For those not using Kotlin, or using Kotlin and Java, check out Room:

[https://developer.android.com/topic/libraries/architecture/r...](https://developer.android.com/topic/libraries/architecture/room.html)

It's in their list, at half the size and method count of Anko (with no need to
pull in the Kotlin runtime if you're not using Kotlin). It offers all the same
features, really awesome migration testing, and RxJava integration.

For me these days the options that make sense in Android's world of infinite
DBs/Orms/Wrappers are down to SQLBrite+SQLDelight, Realm, or Room depending on
what you want

------
le-mark
SQLite is such a fantastic database. I've always wondered, is anyone using it
at scale in a client server application? How do people handle syncing
online/offline in mobile apps?

~~~
bluedino
One problem we've ran into with it, is tables with > 400 columns.

~~~
Diederich
Can you expand on that?

~~~
bluedino
It's simply creating a local cache of a remote database that runs on Oracle.

~~~
Diederich
I mean...what kinds of problems did you run into? Thanks.

------
seanalltogether
I really wish the android team would create an officially supported ORM
package to wrap sqlite like CoreData on iOS. Global data access is even more
important on android since Activities are stateless, and data access always
seems to be to failure point in so many of our projects.

~~~
762236
Golden rule of UI development: do not block the UI thread with I/O.

With ORM packages, the degree of I/O becomes proportional to the app's feature
growth, and the UI eventually stutters. I've been on several successful app
teams, and everyone that started with ORMs had to throw them away to solve
stutter, and since the data and threading models of the apps were written
around ORMs, they had to be mostly rewritten. Going through this, I also
noticed that the before and after ORM code was about the same in line numbers,
and it didn't save time to use the ORM (because ORMs have lots of negatives
that required time to work around, e.g., distancing you from control over the
use of indices).

For a great example of successful UI code, see Chromium, which explicitly
outlaws blocking I/O on the UI thread.

~~~
lmm
> Golden rule of UI development: do not block the UI thread with I/O.

This doesn't have to mean avoiding ORMs; it means separating frontend from
backend with an explicit, narrow interface between the two, but that's no
reason not to use an ORM in the backend piece.

> I've been on several successful app teams, and everyone that started with
> ORMs had to throw them away to solve stutter, and since the data and
> threading models of the apps were written around ORMs, they had to be mostly
> rewritten.

Even in that kind of case (which doesn't match my experience) that doesn't
mean the ORM was a mistake; 90% of apps fail, so if you can save time on
getting to the point where you can verify product/market fit one way or
another, that's well worth doing even if it leads to more work in the cases
where you do want to develop the app further.

> Going through this, I also noticed that the before and after ORM code was
> about the same in line numbers, and it didn't save time to use the ORM
> (because ORMs have lots of negatives that required time to work around,
> e.g., distancing you from control over the use of indices).

Not my experience. Or rather, that matches my experience on teams that tried
to maintain manual control over the database while using an ORM, but teams
that were willing to embrace the ORM and use the database in an ORM-first way
(i.e. the ORM is the source of truth about what the schema looks like, and the
DDL is generated from that) have been able to save a significant amount of
code and have a lower defect rate.

------
cageface
There's also Room from Google that tries to solve the same problems:

[https://developer.android.com/topic/libraries/architecture/r...](https://developer.android.com/topic/libraries/architecture/room.html)

------
maxpert
I am planning to do a blog post on SQLite pitching it as one of modern
engineering marvels. With SQLite4 things might be even better from performance
perspective due to LSM engine under the hood (Shameless plug I have ported the
engine to windows [https://github.com/maxpert/lsm-
windows](https://github.com/maxpert/lsm-windows) ). SQLite was never _uncool_!

------
jorgemf
These type of libraries are cool for small and non-complicated things. None of
them can compete with the expressiveness of SQL, as it is based in relational
algebra.

~~~
jpfr
Which are "these" libraries and what do they do?

Leaving some technicalities aside, SQLite __is __SQL. So I guess we 're not
talking the same here.

~~~
jorgemf
For example the one of the article, which basically get rid of all the SQL
code. Read the article.

------
logcat
One more dsl for SQL is cool, but not maintainable, not approachable or
scalable.

SQL scripts in assets/ folder would be not cool or sexy, but everybody knows
what are they.

------
hasenj
I really think trying to create ORMs is a misguided endavour.

Instead what we really need is a way to map a row from an sql query to a
struct (or similar).

Luckily, the SQLite helpers from Anko do provide this ability, and it's pretty
much the only part I use.

    
    
        data class UserRow(val id: Long, val name: String);
        // .... open db .. etc
        var users = db.query<UserRow>("select id, name from users where ....."); // where clause content omitted
    
        // now users is a list of structs (as close to structs as you can get in Kotlin).
    

Where I have an extension method `query`:

    
    
        inline fun <reified T : Any> SQLiteDatabase.query(sql: String, vararg args: String): List<T> {
            this.rawQuery(sql, args).parseList(classParser<T>())
        }

------
shujito
You don't have to close your database every time if you manage it with a
ContentProvider. I've used SQLite on Android like that without many issues,
although the boilerplate can be too much using SQLite as is. You can also
create views to avoid including queries in java code.

------
hota_mazi

        database.use {
            insert(Book.TABLE_NAME, Book.COLUMN_ID to 1, Book.COLUMN_TITLE to "2666", Book.COLUMN_AUTHOR to "Roberto Bolano")
        }
    

That's pretty bad design: you're throwing out type safety with this API.

------
Gipetto
When was SQLite ever not cool?

~~~
jmfayard
When the Android SDK wrapped it into an horrible api

------
isuckatcoding
I'd probably still use Realm

