DEV Community

Pacharapol Withayasakpunt
Pacharapol Withayasakpunt

Posted on

I wrote an ORM for SQLite

It starts with inter-op with mongoose, and inspired from Typegoose.

It is an ORM that

  • Can query by Mongo-like query
  • Auto-convert back-and-forth for JSON <=> TEXT, Date <=> INTEGER and Boolean <=> INTEGER

For Mongo-like query, it can be tested here -- https://q2search.herokuapp.com/LiteORM (The actual Mongo-like query is in the console)

Still, there are something that would be hard to do / cannot do...

  • Case-sensitive LIKE (LIKE in SQLite is case-insensitive)
  • Query by RegExp (I use LIKE '%'||$identifier||'%' instead. RegExp queries need re-compile.)
  • Limited update / delete (like LIMIT 1 ==> I would have to re-compile SQLite)

GitHub logo patarapolw / liteorm

A simple wrapper for sqlite; with typings based on TypeScript decorators and reflect-metadata. With async eventemitter (emittery). Focusing on JSON, Date, and MongoDB interop.

liteorm

A simple wrapper for sqlite; with typings based on TypeScript decorators and reflect-metadata.

npm version

  • Async eventemitter (emittery)
    • I make sure that you can intercept query objects and raw SQL (as well as their parameters) in an async way
  • Auto-define _id as PRIMARY KEY INTEGER AUTOINCREMENT (Use _id as default name for primary key)
    • I use ROWID, instead.
  • Auto-append createdAt, updatedAt if @Table({ timestamp: true })
  • JSON, Date, Boolean, and MongoDB interop
  • Additional type StringArray, inspired by Anki schema
  • Query with JSON, and tested with https://q2search.herokuapp.com/LiteORM, using MongoDB-like languages, with some differences (for example, $regex is currently not supported, use $like, $nlike, $substr, $nsubstr instead.)
  • JSON querying is supported via JSON1 extension. I made it easy to query using dot notation, just like MongoDB
    • So, you can use data.a
  • Multiple SQLite databases, with cloned schemas or different schemas. Strongly-typed in the…

Top comments (0)