12 ms·
Flyweight: An ORM for SQLite
- jackbravo 4y agoIt would be great to have some benchmarks against better-sqlite and the regular sqlite libraries, like in https://github.com/WiseLibs/better-sqlite3 https://github.com/WiseLibs/better-sqlite3
- unemployable 4y agoFlyweight parses SQL statements to generate a TypeScript API, uses convention to automatically map SQL into hierarchical data structures, and combines this with a simple CRUD API.
- freeqaz 4y agoThat's pretty great because getting types with SQL is a massive pain in the rear! I have looked at some other libraries in the past but they all tend to use a build step to generate their types.
- unemployable 4y agoWhen you want to update the types, you have to run something to update the one file that contains the types (or put it in watch mode to do it automatically). You don't necessarily have to update the types straight away though as I use proxies to make everything dynamic. The other tools I have seen that try to figure out SQL types do not accurately create types for things like left joins.
- lf-non 4y agoSo does this tool. There is a code-generation step for ts types. When the types are coming from an external source that may or may not be available at compile time, I can't think of any way to prevent codegen and also retain type-safety. Some additional integration with build system will be needed.
- MichaelCollins 4y agoconst fights = await db.fights.get({ cardId: 9, titleFight: true }); translates to select * from fights where cardId = 9 and titleFight = 1; Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL.
- imtringued 4y agoYour code example is just wrong. You didn't use parameter binding. You didn't even execute the query. You didn't even store the returned rows in a usable structure.
- valenterry 4y agoYeah, they suck and we have moved on. Nowadays we still use libraries to abstract over SQL dialects and generate SQL in a typesafe and convenient way, but it's not an ORM in the sense that it maps from the object oriented domain into the relational one and back.
- michaelcampbell 4y agoProbably partially because you (like me) know SQL pretty well. I'm dealing now with an application at $currentjob whose employees are really, really good at Ruby on Rails' ActiveRecord, and the Rails/AR code they come up with seems to me INCREDIBLY complex, taking (I think) more lines than the equivalent SQL would. And not really any more readable. But they're very much into do it the Rails way because Rails says you should. I think it's one reason that I'm leaving at the end of next week.
- chaostheory 4y agoOne of the major benefits of ORMs was that you’d write a query once and it would work on any relational database or nosql data store. Imo it’s kind of pointless when an ORM only targets one specific database. Your example is a bit disingenuous. A SQL query isn’t native in most programming languages, so you’re missing a lot more boilerplate code
- JodieBenitez 4y agoIt's more about the Mapping than the querying. Luckily good ORMs let you fetch your objects using... SQL, which is a fine language for querying (doh): for p in Person.objects.raw('SELECT * FROM myapp_person'): print(p)
- pgt 4y agoMost engineers who say they want an ORM, really want query composition.
- yunohn 4y agoWhat’s a good solution for that without a classic ORM?
- nicoburns 4y agoConditional text interpolation with named parameter bindings works quite well. We’ve basically abandoned the ORM for select queries at work as we find this approach more readable.
- yunohn 4y agoThat sounds… flakey. Variable interpolated query strings are not even close to a substitute for an ORM?
- pgt 4y agoResults are easily decomposed if you composed the query.
- icedchai 4y agoI think the data mapping part is pretty important: a programmatic way to map from SQL result sets to objects. That stuff is incredibly tedious (and error prone) when you do it manually.
- pgt 4y agoDecomposing the result set is much simpler if the caller composed the query. Harder if you have to parse SQL to know what to expect back.
- icedchai 4y agoIt depends. I've written a few half baked object mappers and never needed to parse SQL, since I've always used metadata from the result set (column names, data types, etc.)
- hardwaresofton 4y agoDoes anyone know of a library similar to slonik[0] for SQLite in the NodeJS space? I generally reach for TypeORM and have tried MikroORM lately but didn’t really like it. But what I really want is something like slonik which is more focused on querying than relational mapping. [0]: https://github.com/gajus/slonik https://github.com/gajus/slonik
- lf-non 4y agoYou can use ts-sql-query [1]. It has a complete query builder API, but you can also use sql fragments similar to slonik. SQlite is supported along with most other mainstream databases. [1] ts-sql-query.readthedocs.io/
- deleted 4y ago[deleted]
- Rapzid 4y agoMikroOrm is a pretty fantastic project IMO. It uses Knex as the query builder so maybe you can just use that directly: https://knexjs.org/ https://knexjs.org/ Although, I would use Mikro still to manage schema and migrations. Then drop down to Knex: https://mikro-orm.io/docs/query-builder#using-knexjs https://mikro-orm.io/docs/query-builder#using-knexjs
- hrdwdmrbl 4y agoWhy a whole new ORM and not an SQLite adapter for an existing ORM?
- Aperocky 4y agoit's npm. Jokes aside, as someone who wrote a python sqlite ORM (shameless plug: `pip install sqlitedao`), my reason was to have a minimal ORM for personal project, the entire active source is contained in one file and it works for majority of the use cases (i.e. insert_item, get_item, etc).
- Aperocky 4y agoHere's a python (pip) version of the same concept: https://github.com/Aperocky/sqlitedao https://github.com/Aperocky/sqlitedao https://pypi.org/project/sqlitedao https://pypi.org/project/sqlitedao Same concept, huge speed boost to personal projects. ORM is great because you can abstract items in memory directly into persistence, and define the relation in programming language instead of SQL.
- resoluteteeth 4y agoThis doesn't look the same at all. The point of flyweight seems to be use code generation based on sql code; sqlitedao seems to be more like a normal orm.
- fithisux 4y agoSince it featured today, is it supported under deno?
- tyingq 4y agoI don't like ORMs much, admittedly mostly because I did a fair amount of development with SQL before they existed. But, there was one that I played with that did have appeal to me, RedBeanPHP. Forgetting that it's PHP for a minute...that's not the main point. It was cool because it had a fluid way of working. It automatically generates the database, tables and columns... on-the-fly, and infers table relations based on naming conventions and how you interact with code. No config files at all. So, you would iterate in dev solely by writing code, and end up with a schema including foreign relationships. Then, you can "freeze" the schema for prod, turning off all the dynamic stuff. Their quick tour explains it well: https://redbeanphp.com/index.php?p=/quick_tour https://redbeanphp.com/index.php?p=/quick_tour Note: I'm sure it has notable downsides over time, but the approach was really nice starting from scratch.
- Aperocky 4y agoORM is a great way to scale yourself.
- runevault 4y agoORMs where you don't write sql make me nervous, though it doesn't help the main version of this I used was raw linq-to-sql (not Entity Framework), and it could be very hard to convince linq to generate the correct sql for what I was doing (I once had to write my relationships backwards else it kept generating sub queries). But .NET also has Dapper where it lets you write all the SQL and then it just handles the binding of data into objects, which having that handled for me is great.
- tyingq 4y agoIt does allow for query access also: https://redbeanphp.com/index.php?p=/querying https://redbeanphp.com/index.php?p=/querying But, yeah, that's not the normal path.
- somenameforme 4y agoI had a positive experience with Linq2db: https://github.com/linq2db/linq2db https://github.com/linq2db/linq2db I mention because I had something of the opposite experience with it. It not only ended up yielding the correct queries, but I saw a significant increase in performance. And the neat thing about it, beyond ORM and linq-to-sql, is a common interface amongst providers - so you can do things like swap from SQLite to Postgres with 1 line* of code, so long as you're not using provider specific extensions.
- astrobe_ 4y agoI've heard that ORMs are the "Vietnam" of CS [1]. The article is pretty old, is it still the case? [1] https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t...
- dimgl 4y agoYes. Yes it is. So much so that I will, as much as possible, try to not use an ORM that creates queries for me. Simple ORMs that map columns in rows to attributes or properties in an object are fine. ORMs that handle complex relationships and migrations and the rest (a la Entity Framework, Hibernate, ActiveRecord), are all pretty much a vote of no confidence from me in any project.
- SadWebDeveloper 4y agoKinda prefer Prisma when working with js/node or ts /deno but might try it if i need something more lightweight than prisma for a new toy project. As for the ORM debate, not applicable for SQLite but if m using a database with better support for stored procedures (like sql server, postgresql or oracle), i just prefer a minimal DAO or an ORM that just built from stored procedure calls. Unfortunately sometimes, specially in a big diverse team we do prefer an ORM so devs focus on other things a let the DBA guys try to guess why my code calls select everytime it refers to "entries" object just to get one entry.
- bob1029 4y agoORM is usually a bad idea if you are trying to reduce the overall complexity of a solution. Only in the happiest of cases does an ORM solve all of your problems without creating a multitude of new ones. My experience with ORMs is very similar to my experience with web frameworks. I view them both as a way to offload cognitive burden while you learn about other aspects of the problem space. Once you reach mastery in those other areas, you can begin to dispense with the frameworks and resume more ownership over these areas. Surrendering a little bit of control up-front makes a lot of sense when you are trying to work through a difficult & new problem. I would definitely prefer the computer do some sub-optimal, blind-mapping of my objects until I could settle on a final schema. Managing a bunch of raw SQL queries while your relational model is still in flux is not something I would look forward to.
- lambdafourtwo 4y agoPeople usually switch to ORMs because they want to use in-language primitives for their application of choice. It's jarring and inelegant to switch between SQL and javascript or something like that. The problem with an ORM is that it's a high level abstraction On top of what is ALREADY a high level abstraction: SQL. ANd it's not even a one to one abstraction... they are very different and this actually adds more complexity when it comes to optimizing SQL. You optimize SQL with hacks to get it to compile into an efficient query. With an ORM you have to hack the ORM in order to hack the sql in order for it to compile into an efficient query. It's nuts. The fix for this problem is to not use an ORM. You want to use in language primitives? Make an abstraction that is one to one with SQL. A library that gives primitives that directly represent SQL language primitives. That's what we actually all want. We don't actually want an orm.
- hnfong 4y ago> Make an abstraction that is one to one with SQL Which SQL?
- lambdafourtwo 4y agoThe answer to your question is rather obvious. But if you really like ORMs then you likely are hoping your comment serves as some form of revelation to me and this blinds you to the answer. Either way the answer is this: All the popular versions of SQL. I mean what else could the answer be? Your question was rhetorical. Trying to expose a flaw. See the sqlalchemy expression language in sqlalchemy core. It's already very similar to what I'm proposing. It exposes something that is one to one to ansi sql. Then the underlying implementation translates that expression language into different sql languages. This is the key: Underlying implementations of ORMs typically ALREADY handle all versions of SQL languages. What I'm proposing is a realistic extension of that. Separate APIs for EVERY sql language. You already have separate implementations... thus separate APIs is not such a crazy unrealistic extension of that. All sql languages have common language primitives, thus an API could have two sets of libraries. One for common primitives and the other for language specific primitives. But that common library can end up being a rabbit hole, so if it's possible to do cleanly... sure, but if not then just seperate APIs for every sql language is fine. I mean does code really need to be portable across sql databases? I wrote ORM code for postgresql, do I suddenly need that code working with SQLlite? It might be a convenience, but it's not a huge requirement.
- alexfromapex 4y agoNot to be confused with Flyway, maybe the pun is intentional?
- geenat 4y agoReally similar to https://github.com/ahopkins/mayim https://github.com/ahopkins/mayim in the Python world.
- gexla 4y agoAren't ORMs supposed to give you one way to interact with a DB over different SQL dialects? Doesn't an ORM for one dialect defeat the purpose of an ORM?
- int_19h 4y agoIf I'm reading this correctly, it doesn't handle object graph traversal - if you need it, you have to write your own JOIN manually, and then the library will wrap that. I would argue that graph traversal is one of the basic features of an ORM.
- unemployable 4y agoIn an ORM, you will write include: ['posts'] in SQL, you will write join posts on p.authorId = a.id I would argue an ORM is more about getting the flat structure of the result set into an hierarchical set of objects with more complex types.