8 ms·
Anko SQLite, a library to simplify working with SQLite on Android
- cageface 9y agoThere's also Room from Google that tries to solve the same problems: https://developer.android.com/topic/libraries/architecture/room.html https://developer.android.com/topic/libraries/architecture/r...
- BoorishBears 9y agoFor those not using Kotlin, or using Kotlin and Java, check out Room: https://developer.android.com/topic/libraries/architecture/room.html 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
- logcat 9y agoOne 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.
- le-mark 9y agoSQLite 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 9y agoOne problem we've ran into with it, is tables with > 400 columns.
- Diederich 9y agoCan you expand on that?
- andrewguenther 9y agoIsn't 400 columns expanded enough?
- bluedino 9y agoIt's simply creating a local cache of a remote database that runs on Oracle.
- Diederich 9y agoI mean...what kinds of problems did you run into? Thanks.
- revelation 9y agoWell, don't do that, normalize your scheme.
- phn 9y agoWhen 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 :)
- striking 9y agoSounds to me like you're using RDBMS wrong.
- tyfon 9y agoNot 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."
- alttab 9y agoSpiceworks 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.
- dhd415 9y agoI 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 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.
- tiffanyh 9y agoFossil-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 https://www.fossil-scm.org
- bensummers 9y agofossil 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.
- mritun 9y agoSQLite 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.
- russell_h 9y agoOpenStack Swift (it's S3-like service) uses SQLite to store account and bucket metadata.
- hasenj 9y agoI 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.
- akvadrako 9y agoSQLite 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.
- ageofwant 9y agoSQLAlchemy with transactions on SQLite should get you very close, probably close enough.
- infecto 9y agoWhy 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?
- marktangotango 9y agoWhoa! 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.
- alvil 9y agoHere are some comments from SQLite itself: https://www.reddit.com/r/PHP/comments/59na74/sqlite_as_the_only_database_for_website/d99us21/ https://www.reddit.com/r/PHP/comments/59na74/sqlite_as_the_o...
- evanspa 9y agoIn 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 https://itunes.apple.com/us/app/riker/id1196920730?mt=8 Riker on Android is currently in-progress...
- lungureanu 9y agoI 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.
- evanspa 9y agoYou 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 :)
- edwinyzh 9y agoFor question #1, an approach is to build a full-fledged Web/REST server wrapping around SQLite: https://github.com/synopse/mORMot https://github.com/synopse/mORMot The performance is high!
- pier25 9y agoCheck out Realm: https://realm.io/ https://realm.io/
- seanalltogether 9y agoI 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.
- veeti 9y agohttps://developer.android.com/topic/libraries/architecture/room.html https://developer.android.com/topic/libraries/architecture/r...
- 762236 9y agoGolden 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.
- BoorishBears 9y agoWhat 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
- lmm 9y ago> 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.
- isuckatcoding 9y agoI'd probably still use Realm
- Gipetto 9y agoWhen was SQLite ever not cool?
- jmfayard 9y agoWhen the Android SDK wrapped it into an horrible api
- hota_mazi 9y agodatabase.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.
- maxpert 9y agoI 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 9y agoThese 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.
- shujito 9y agoYou 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.
- hasenj 9y agoI 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>()) }