Anko SQLite, a library to simplify working with SQLite on Android | Hacker News Reader

Anko SQLite, a library to simplify working with SQLite on Android

kotlindevelopment.com

108 pointsandraskindler69 comments

For those not using Kotlin, or using Kotlin and Java, check out Room:

https://developer.android.com/topic/libraries/architecture/r...

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

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?

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

Can you expand on that?

Isn't 400 columns expanded enough?

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

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

Well, don't do that, normalize your scheme.

When your application grows, eventually you need to de-normalize data if you want to keep having reasonable performance for some operations.

You can reach a point where it's not feasible to have everything done by joins.

EDIT: I mean in relational DBs in general. In this case, 400+ columns probably mean SQLite may not be the engine to use, regardless of schema :)

Sounds to me like you're using RDBMS wrong.

Not that it necessarily relates to SQLlite, but I've seen this _many many_ times in real world databases in the banking industry.

Personally I don't like it and I am luckily not in IT, but as an analyst I have seen many weird database structures. One can only imagine the business requirements that gave birth to some of them.

To quote a solutions architect to the head of marketing in a previous job: "You asked for a monster, you got a monster."

Is there something about SQLite's architecture that would make such a schema less problematic in MySQL/PgSQL/etc?

Spiceworks has a network management app that runs on a rails server on your network and uses sqlite to store everything and run the multi-tenant help desk. I was always surprised how well it worked.

I have but I'd say that SQLite is not intended for use in that scenario. It's an in-process library for persisting data to disk in a generally relational format with a SQL interface. It starts to degrade under highly concurrent read and write workloads that you can experience in client-server applications. At that point, a typical RDBMS with more robust concurrency support starts to be a better choice. I experienced that when using SQLite and we eventually moved the application to a full RDBMS which was more complex but also performed and scaled much better.

Note that this is very much in line with their recommendations from the docs (http://sqlite.org/whentouse.html):

>>SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.

Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

SQLite does not compete with client/server databases. SQLite competes with fopen().<<

It's worth noting that when considered as competition to fopen(), SQLite is quite good.

Fossil-SCM.

I've always wondered why the creator of SQLite (who's also the creator of Fossil SCM) will state that SQLite is not meant for client/server scenarios ... yet his Fossil SCM (which is client/server) uses SQLite.

https://www.fossil-scm.org

fossil on the server gets low numbers of writes, and not many reads. And being a distributed system, most of the SQLite action is on the client.

SQLite does not have a client-server interface. That’s ok because the creator says it is supposed to be an embedded library that gives a better fopen interface. That is, you don’t need to invent file formats (think JSON, YAML, etc). SQLite is not just a database, it’s a persistence layer that happens to have SQL interface.

It places no restrictions whatsoever on the applications that uses it. That will be pointless. Fossil-SCM uses SQLite and is itself client server.

Fossil is the server in this case, not SQLite.

It is fine to use SQLite as the local storage in the implementation of some custom server like Fossil, as there is a separate server (Fossil) that sits in between the client and the data. In fact, SQLite excels at this and usually works better than traditional client/server databases. The scenario you want to avoid is where the client attempts to access SQLite data directly across the network, with no intermediary server. The proverb is: "Always send your query to the data, not the data to the query."

It will work to access an SQLite database across a network filesystem. Just remember that the amount of information that flows between the SQL engine and the storage medium is much greater than the amount of information that flows between the SQL engine and the application. So if the storage is separated by from the application by a relatively slow network, you want to position that network on the low-bandwidth link to obtain the best performance. That means that the SQL engine needs to be on the same side of the network as the storage - hence a client/server database. If you use SQLite to access a network file, the SQL engine will be on the application side of the network and a lot more content will need to traverse the relatively slow network link.

And so, while SQLite will work in a client/server situation, you might be disappointed by the resulting network load and/or performance of the system.

In the case of Fossil, Fossil itself is the "client" for SQLite and it is on the same machine as the data, which is exactly what you want with SQLite. The client of the Fossil server might well be across a network, but that does not matter to SQLite.

@SQLite

So how is what you described above architecturally different than a web application architecture?

Which is a use case I thought you did not recommend.

I'm running into a weird case where even within an application, concurrency has become relevant because of parallel processing that the application wants to do over the data stored in the database.

But, our desire for both simplicity and obscenely low latency means that SQLite is still the better choice for us at the moment. In the future, though, I can see robust concurrency support being important even in single application use-cases.

If you're in-process, though, you can just lock when you need exclusive access to the datastore.

Ideally, I'd have several separate threads each with fine grained (table-level, maybe even row-level with deferred index updating) control on reading and writing. I admit that I haven't gone sufficiently down this path to know exactly what I'd want though, as it isn't the top priority right now. (We are "fast enough" without concurrency, ... for now.)

I used sqlite as a backend for scraping 160 million websites a day. It works extraordinarily well if you hold it right - in my case, that meant devoting a database file to each thread. (Arguably in this context, it's competing with flat files, but it does a really good job there.)

OpenStack Swift (it's S3-like service) uses SQLite to store account and bucket metadata.

I really don't understand why majority of websites use mysql or even postgres for that matter. The vast majority of websites would run perfectly fine on sqlite.

Not only would you get rid of network latency, you would also vastly reduce your dependencies.

I have a side project to try to see if I can build a minimal micro-blogging type server application on sqlite and try to test how many requests it can handle per second (reads and writes).

I have a hunch it will be able to handle quite a lot. Specially if the server code is written in a fast language that compiles to native code (e.g. D) with some caching of the most commonly requested items in memory.

SQLite does not work smoothly in many languages (like python) in regards to concurrent access. Without a controlling process (or thread) it's synchronisation possibilities are limited, so it can never scale too far.

Nonetheless I think it's a great idea to build your own single-threaded layer over SQLite and scale that, using a message bus or whatever.

But it's _not_ a drop-in replacement for MySQL based apps.

SQLAlchemy with transactions on SQLite should get you very close, probably close enough.

Why get very close when I can just spin up a single micro RDS instance of MySQL or Postgres and not have to worry about it?

Whoa! That's nearly $20 a month, if you're past the free tier, which you will be if you're not already. Edit that's t1.micro, t2 is $13.

Not sure how other people do it but 1 micro RDS serves many projects for me. Well worth the money for me.

Yea my hunch/hypothesis does not apply to slow languages like python. That's why I said a fast language that's compiled to native binaries. For example, D, Swift, or Go.

Concurrent writes is a problem since the locking is at database level[1]. For a small to medium-low website might be OK depending on the writes volume.

[1] https://www.sqlite.org/lockingv3.html

As the header of your page denotes, things got significantly improved years ago with the addition of a WAL as the default mode rather than an exclusive write lock.

Also, I think most websites don't even need constant access to the DB, because the data doesn't change often.

Compile a static site from SQLite when someone enters new data, push it somewhere to be accessible and be done with it.

I'd be willing to bet that 99% of the sites/apps on the web have low to medium writes and reads. Developers like to plan on needing Facebook and Uber scale for their new task list and subscription box services though. SQLite benchmarks are impressive, they beat out commercial RDMS in a lot of comparisons.

It's only a few lines of terraform to spin up a mysql or pg RDS instance. It can't get any easier, so why would I jump through extra hoops to use sqlite?

Its free to host compared to other RDMS which aren't cheap on most IaaS, and spinning up an instance is as easy as deploying it alongside your code.

terraform itself is a monster I want to avoid ..

I want no dependencies .. and no dependency manager.

Network latency is only really a problem if you host your db on another machine. An Unix socket has something like 1ns. As for dependencies, the package manager takes care of that.

  > An Unix socket has something like 1ns.

That seems implausible, given that you're lucky to get a context switch down to 1µs if the cache gods smile on you.

Sorry, I meant 1 microsecond.

With the complicated aws-based deployment schemes people are using these days, what's the chance that the db is on the same machine?

Here are some comments from SQLite itself:

https://www.reddit.com/r/PHP/comments/59na74/sqlite_as_the_o...

In my strength-tracking iOS app Riker, I use SQLite directly (not CoreData) as my local data store, and support full offline-mode. The backend is Postgres.

In the app, for each relation (e.g., a "workout set"), I have 2 tables: a master and a scratchpad. When a user saves a set, a row is written to the scratchpad table. When the user syncs it with the server, a row is written to the master table and deleted from the scratchpad table. When the user wants to edit the record, I first copy it down from the master table to the scratchpad table. All local editing impacts the scratchpad row. When the user wants to sync, only if a 200 response is returned will I copy-up the scratchpad row to the master row. If the set was edited on another device and the local copy is out-of-sync, the server would have responded with a 409 (http conflict code), and the body would contain the server copy, which is then written to the master table. The user can then figure how they want to merge the scratchpad row and the master row.

Anyway...trying to do all this with CoreData would have been a pain, so I use SQLite directly, and works great.

Or to summarize, I handle offline mode, syncing and conflict detection using "updated_at" timestamp columns along with logic in my REST API to returned appropriate HTTP status codes, interpret "if-unmodified-since" headers, etc.

https://itunes.apple.com/us/app/riker/id1196920730?mt=8

Riker on Android is currently in-progress...

I find it difficult to get full offline-mode + sync working correctly. Don't know if you have duplicates in the remote db but I think your approach has a problem: the app send the data (from scratchpad table) to server, the server saves it in DB but the connection drops on device (ex bad connection, user turn off WIFI/3G while syncing) so the app will never get a 200 response. In this case your new data was saved on server but it remains on scratchpad table. On next sync the data will be uploaded as being new: this will cause duplicates.

You are right. The way I solved for this scenario is that when a record is first created on the device, a GUID is created for it (and stored using another column of course). When POSTing new records to the server for syncing, the server will check the GUID and see if the record already exists in its database, and if so, can ignore it (so the duplicate isn't written).

But yes, you're right overall - full offline mode w/syncing, etc is a big pain :)

For question #1, an approach is to build a full-fledged Web/REST server wrapping around SQLite: https://github.com/synopse/mORMot

The performance is high!

Check out Realm:

https://realm.io/

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.

https://developer.android.com/topic/libraries/architecture/r...

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.

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

What aspect of an ORM encourages blocking the UI thread anymore than direct DB access? Most Android ORMs explicitly make it easier not to block the UI thread by offering callbacks and reactive streams.

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

Android explicitly outlaws network I/O on the UI thread by default, and can be configured to block disk I/O too via StrictMode

Getting rid of your ORM because you get stutters is silly: just find out where the stutters are and address these surgically, either by using more specialized features of your ORM or just dropping back to straight SQL.

ORM's are still a great solution for a wide majority of scenarios.

Activites are not stateless. They can simply be torn down and restored at any time. You should be using bundles to store and restore your activity state. You don't want to persist the view state to disk anyway.

you can't put many bytes in bundles or else you run into problems. The correct approach is to just store the id or the minimum needed in order to restore the view, most of the info will either come from the server, a cache or a database..

They did, it's called Room and it's part of architecture components.

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

https://developer.android.com/topic/libraries/architecture/r...

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 ). SQLite was never uncool!

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.

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.

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

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.

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>())
    }

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.

    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.

When was SQLite ever not cool?

When the Android SDK wrapped it into an horrible api

I'd probably still use Realm

Reply on news.ycombinator.com