[HN Gopher] Flyweight: An ORM for SQLite
___________________________________________________________________
Flyweight: An ORM for SQLite
Author : unemployable
Score : 78 points
Date : 2022-09-29 12:16 UTC (10 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| hardwaresofton wrote:
| Does 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
| lf-non wrote:
| You 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]
| Rapzid wrote:
| MikroOrm is a pretty fantastic project IMO.
|
| It uses Knex as the query builder so maybe you can just use
| that directly: 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
| hrdwdmrbl wrote:
| Why a whole new ORM and not an SQLite adapter for an existing
| ORM?
| Aperocky wrote:
| it'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).
| bob1029 wrote:
| ORM 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 wrote:
| People 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.
| pgt wrote:
| Most engineers who say they want an ORM, really want query
| composition.
| yunohn wrote:
| What's a good solution for that without a classic ORM?
| pgt wrote:
| Results are easily decomposed if you composed the query.
| nicoburns wrote:
| Conditional 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 wrote:
| That sounds... flakey. Variable interpolated query strings
| are not even close to a substitute for an ORM?
| Aperocky wrote:
| Not really, they (I) wanted `insert(Item)` that just inserted
| the item into the table.
|
| I don't really care about the query, just that it's inserted
| correctly, along with other operations that an ORM provide
| (get/delete/update,etc)
| icedchai wrote:
| I 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.
| astrobe_ wrote:
| I'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-t...
| dimgl wrote:
| Yes. 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.
| tyingq wrote:
| I 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
|
| Note: I'm sure it has notable downsides over time, but the
| approach was really nice starting from scratch.
| mgkimsal wrote:
| I loved redbean for a while. Sort of a mix of 'nosql' up front,
| but it's being baked in to a relational DB. I did a number of
| small projects extremely quickly with redbeanphp. You're making
| me want to go explore it again :)
|
| EDIT:... well... I see it's still got an aim of being PHP5
| compatible. Upgrades for PHP 8.1, but that seems off a bit. I
| seem to remember composer was an issue... and there still is.
| Impressive that the author is still evolving and supporting it
| :)
| JodieBenitez wrote:
| Redbean is really nice. Long ago I wrote a python/sqlite
| implementation as an exercise. Module here:
| https://gist.github.com/johndoe46/40c0f993a641fe7a3eb74bd6d6...
|
| Also tests:
| https://gist.github.com/johndoe46/17eacf0f12772dfb870732479b...
|
| (This is proof of concept quality, don't use for real work)
| runevault wrote:
| ORMs 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 wrote:
| It does allow for query access also:
| https://redbeanphp.com/index.php?p=/querying
|
| But, yeah, that's not the normal path.
| somenameforme wrote:
| I had a positive experience with 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.
| Aperocky wrote:
| ORM is a great way to scale yourself.
| Aperocky wrote:
| Here's a python (pip) version of the same concept:
|
| https://github.com/Aperocky/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.
| fithisux wrote:
| Since it featured today, is it supported under deno?
| unemployable wrote:
| Flyweight 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 wrote:
| That'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.
| lf-non wrote:
| So 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.
| jackbravo wrote:
| It would be great to have some benchmarks against better-sqlite
| and the regular sqlite libraries, like in
| https://github.com/WiseLibs/better-sqlite3
| SadWebDeveloper wrote:
| Kinda 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.
| alexfromapex wrote:
| Not to be confused with Flyway, maybe the pun is intentional?
| geenat wrote:
| Really similar to https://github.com/ahopkins/mayim in the Python
| world.
| MichaelCollins wrote:
| const 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.
| randito wrote:
| I think it's intrinsic to the problem. It was a popular topic
| back in the day.
| https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...
| robomartin wrote:
| > Confession: something about ORMs has never clicked with me..
| none of them ever seem simpler than SQL
|
| I'm with you on that. I use ORM's, yet, being perfectly
| comfortable with SQL my perspective ends-up in a range between
| "this complexity isn't worth it" and "why not just write this
| in SQL?".
|
| One of the arguments for ORM's is being able to move to
| different db engines. Frankly, I can't remember the last time I
| had to do that for a mature/released application.
|
| Once again, I'll admit my perspective is biased because SQL
| isn't a problem for me. When I look at ORM code it looks and
| feels very detached from the database. I look at SQL and
| everything is clean and clear. In addition to that, you don't
| have to create and manage a bunch of objects that take-up
| memory and slow things down. Adding layers of abstraction isn't
| always the best idea.
| valenterry wrote:
| Yeah, 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.
| mrweasel wrote:
| You're coming at it from a slightly wrong angle. You are
| completely right that for queries, there's really no gain, you
| just end up having to learn both SQL and whatever your ORMs DSL
| is.
|
| Where ORMs are useful is once you have your objects. The
| usefulness of an ORM is being able to say:
| user.email = 'new@example.com'
| user.groups.append('admin') user.save()
|
| Also being able to work on your data in objects or structure
| native to your programming language means that you can leverage
| both the strengths of the language and the database server.
| Some operations are much simpler to do in Python or C#,
| compared to SQL. In other scenarios you really need to let the
| database do its thing. Again, you do have a point, because
| believing that you can skip learning SQL and just rely on the
| ORM will get you into trouble.
|
| One other feature of ORMs is that they allow you to write code
| that will run on multiple databases, but at the cost of not
| being able to use the more advanced database features. For many
| CRUD applications that is a price worth paying though.
| rad_gruchalski wrote:
| > Where ORMs are useful is once you have your objects. The
| usefulness of an ORM is being able to say:
|
| That example you give here doesn't require an orm, though. In
| ColdFusion world they use(d to use?) a pattern called dao,
| data access object.
| sgt101 wrote:
| I've never bought this :
|
| why not
|
| user.updateGroups('new@example.com','admin')
|
| Sure you have to write the updateGroups method and use SQL to
| do it, but that's trival. On the otherhand when you want to
| do something more complex this is when ORM's inject all sorts
| of subtle and dangerous bugs into your code base.
|
| I've had some terrible experiences with them...
| JustLurking2022 wrote:
| That is an atrocity on so many levels.
|
| For one, you're creating a hard coupling to a specific
| flavor of SQL. And that's not too mention the fact that
| you're taking an otherwise purely data object and embedding
| persistence logic into it - a horrifying abuse of OO.
| asalahli wrote:
| > you're creating a hard coupling to a specific flavor of
| SQL.
|
| I fail to see why this is a problem. Switching databases
| is a costly move, and is pretty rare as far as I know.
| When it does happen, it is usually from one type of db to
| another type, not between two RDBMSs.
|
| IMO it doesn't, by itself, justify sticking to an ORM
| rather than raw SQL.
| ajgrover wrote:
| Many/most ORMs (the ones that follow the ActiveRecord
| pattern) do this as well though. I prefer to avoid mixing
| concerns and use datamapper-based ORMs myself, but what
| GP wrote is fundamentally not that different than what a
| lot of ORMs do.
| JustLurking2022 wrote:
| I'd dispute "most", as it does not match my experience
| across a number of languages. Yes, active record sorta
| does it but, as best I recall, those methods are all
| implemented generically as mixins, so not actually a part
| of the data object. This seems to be advocating for data
| specific queries to be added to each class.
| ajgrover wrote:
| Rails and Django are both AR, and are still probably the
| two most widely used backend frameworks, for better or
| for worse.
|
| I don't disagree with the overall point you're making,
| I'm just pointing out that it's very common
| gwbas1c wrote:
| > you're creating a hard coupling to a specific flavor of
| SQL
|
| You're just trading one coupling (specific flavor of SQL)
| to another (your ORM.)
|
| Assuming your application is layered correctly, when you
| write your own queries, all of your SQL queries are in a
| single place and can be updated.
|
| BUT: If you're using an ORM, and you let your data bound
| objects leak into all layers, the coupling is much much
| much harder to fix if you chose to change your ORM. IE,
| if you do things like lazy loading, or construct your
| queries in business logic, switching ORMs will be
| extremely painful.
|
| I've done it both ways (write my own SQL and use an ORM)
| and I would say the single biggest mistake is to assume
| that you absolutely should (or shouldn't) use an ORM.
| JustLurking2022 wrote:
| Not at all - in most architectures, your data objects
| know nothing about how they are being stored, instead
| delegating that to a repository class or something
| similar.
|
| Adding persistence logic to a data object adds all kinds
| of bloat - it has to have a connection to the database,
| which now makes unit testing a pain in the neck and
| introduces all kinds of weirdness around serialization.
| Now it has a bunch of CRUD methods, so developers have to
| know which methods are for business logic versus
| persistence. Also, data objects with persistence logic
| aren't really suited to be published in a shared library
| as clients should not access your database directly.
|
| All around, it's just a really terrible idea.
| gwbas1c wrote:
| > Adding persistence logic to a data object adds all
| kinds of bloat
|
| That's not what you do if you write your own queries.
| That's what a novice does after learning object oriented
| programming.
|
| For a more accurate example, look at things like
| Hibernate / NHibernate / Entity Framework and lazy
| loading. They inherit from classes at runtime and will
| transparently run queries as business logic navigates
| relationships on an object graph. It's "not wrong," but
| it can lead to all kinds of problems and painful
| refactors. (But it's totally "worth it" in prototypes,
| one-offs, and throwaway code.)
|
| If you don't do lazy loading, Entity Framework still
| requires that you pre-declare which relationships you
| will traverse. It's intended that your business logic
| uses Entity Framework APIs to say what part of the object
| graph it will use. (Thus tightly coupling your
| application to your ORM, which means your trading tightly
| coupling to a database to tightly coupling to a framework
| / library.)
|
| But, keep in mind that the ORMs I mentioned tie the
| objects to the database connection. It isn't quite as
| intense as "Adding persistence logic to a data object",
| but they do track that, if you modify an object, it can
| only be saved on the transaction that it was loaded from;
| or if you're using lazy loading, lazy loading only works
| if the transaction is still open.
|
| If you build a layer around your data access code that
| fully shields your business logic from your ORM, you've
| "done it right." But, at that point your ORM's value
| becomes negligible, because from the perspective of your
| business logic, it doesn't matter if your data access
| layer has hand-optimized SQL or an ORM; but you've lost
| one of the real selling points of an ORM, which is that
| you can easily do your data access from within your
| business logic. Which is why I say that the biggest
| mistake is assuming you should, or shouldn't, use an ORM.
| capn_duck wrote:
| It's not as if you're expected to use ORMs to do ad hoc
| querying of a database. Just fire up a db connection and start
| writing sql.
|
| If you work on a large application with lots of transactional
| processing, it makes sense. I've worked on apps with 10's of
| 1000's of lines of Ibatis scripts. And on applications where
| you're expected to just invoke stored procedures for every
| little operation. I'll take Hibernate over that situation any
| day.
|
| People really overstate the pitfalls of ORM's. If you're
| running reports against a data warehouse, don't use an ORM. If
| you are trying to optimize a complex query, don't use an ORM.
| For an app that is write heavy, with very many simple updates
| to a complicated object graph, it makes sense.
| nh23423fefe wrote:
| but orms and sql aren't competing at the same task, so
| comparison isn't really meaningful is it?
|
| one thing is trying to present an internal dsl for context
| dependent data access, the other is a generic text api to
| represent queries and commands.
|
| the analogy would be comparing aws java sdk, and commenting
| that HTTP rest apis are so simple why would anyone use the sdk?
| applejacks wrote:
| Sure, this is a simple example -- and you are right, if you are
| just writing a few selects, you may not find any value.
|
| ORMs (or query-builders, as some like to draw a distinction
| there) become more valuable as you use them to compose and
| transform queries. SQL is decidedly not composable.
| 10000truths wrote:
| You can compose SQL queries with subqueries.
|
| My experience with ORMs is that they're a minefield of
| performance cliffs. It's easy to accidentally generate
| suboptimal SQL or introduce additional round trips unless
| you're very careful about the code you write, at which point
| you might as well write SQL directly and be at the mercy of
| one less black box.
| applejacks wrote:
| If you compose SQL with subqueries you tend to do less
| composition in general (because you are restricted in what
| you can compose), and you are more at the mercy of the
| query planner, which is its own black box.
|
| I'd rather simply understand how my ORM generates queries,
| and then use the ORM to get the full power of my language
| of choice in creating the right SQL queries.
| vladsanchez wrote:
| I said it once many years ago and I still say it: "ORMs are for
| people who don't know SQL"
|
| However, I understand the problem it tries to solve: Object-
| Relational Impedance Mismatch. I only wish there were other
| idioms, not the convolution of classes and mappers.
| ttymck wrote:
| I've never said this before but I'll say it now: "'ORMs are
| for people who don't know SQL' is usually said by people who
| have never written a complex application."
|
| I agree that ORMs are dangerous and clumsy for a number of
| use cases, but the query-building aspect is indispensable in
| many scenarios. Unfortunately, the query builder is usually
| tightly coupled to the ORM, but _if you do know SQL_ then you
| can use the ORM sensibly and performantly.
| smitty1e wrote:
| One big point is the breaking of the chicken/egg cycle on
| whether or not the DDL has been run.
|
| This is expanded when migrations are brought into the project.
|
| We can train the data model to have a single management point,
| that being the file where the ORM classes are defined.
| michaelcampbell wrote:
| Probably 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 wrote:
| One 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
| chrsig wrote:
| I _mostly_ agree. I think sql 's achilles heel in this regard
| is where prepared statement parameters are needed but aren't
| supported, or need a better representation
|
| - during bulk inserts, having to generate a list of values.
| It'd be wonderful to be able to just supply a single `?`, or
| use some other symbol to note that it's a value list. Making
| the user generate a bunch of (?,?),(?,?)... is not at all
| friendly, and something _everyone_ has to do. and the cherry on
| top is that there can 't be a dangling comma at the end, so
| it's gotta be chopped off, or omitted.
|
| Not at all a hard problem. It's an annoying problem that I
| don't understand why it hasn't been solved at the prepared
| statement level.
|
| - things like database, table, or column identifiers that may
| be variable based on application context
|
| Basically anywhere that currently winds up getting interpolated
| should have a way to be parameterized.
|
| without those two, i think it's inevitable to arrive at one of:
|
| - an orm
|
| - a sql query template renderer
|
| - a bunch of functions to do very specific string
| interpolations
| MichaelCollins wrote:
| Absolutely agree with this, particularly the (?,?,?,...)
| issue. SQL has a lot of little pain points, but generally
| ORMs feel like they throw the baby out with the bath water.
| matharmin wrote:
| With SQLite, I've started using json_each on a JSON parameter
| for bulk inserts or updates. Other SQL databases should have
| something similar.
|
| It's much cleaner than generating SQL, and doesn't run into
| issues with exceeding the maximum number of parameters.
| plugin-baby wrote:
| This sounds very neat. Do you have an example handy?
| lambdafourtwo wrote:
| I don't like ORMs but there is a benefit here. Your where
| clause is re-usable. You can assign it to a variable and use it
| again somewhere else.
| nikeee wrote:
| That's what I feel like, too. Every ORM that I've worked with
| is a separate DSL that I need to learn. Also, abstracting away
| the database is something I don't get - why abstract away
| something that I explicitly chose because it does something
| different than the other alternatives?
|
| I've yet to encouter a project where I'd need to switch to a
| different database. Even if that happens, there is likely some
| raw SQL that someone wrote because the ORM didn't do something
| as expected. Or some part of the code uses DB-specific ORM
| features that don't map to different databases. The only thing
| I can imagine where this would be useful is when you don't have
| control about what DB is being used, for example, when building
| a product that should be compatible with Postgres and MariaDB
| (and each is getting used). However, in the age of
| containerization, this isn't a big problem any more.
|
| In some ORMs, I need to create types that the result of a query
| containing JOINs is mapped to. Others don't support them _at
| all_. In TypeORM, there is a query builder which forces you to
| put in _some_ SQL for things like "WHERE a in (b, c)". Most
| ORMs I've used have a cumbersome handling of relations, for
| example when I need to specify which relation should be fetched
| eagerly.
|
| I created a proof of concept of a different approach: Just
| embrace SQL and provide static typing based on the query. The
| return type of a query is whatever that thing is that the query
| returns in the context of the database schema. It's possible to
| do in TypeScript, by parsing the SQL query at development time:
|
| https://github.com/nikeee/sequelts
|
| One benefit is that it does not need any runtime code, as it's
| just a type layer over SQL. You don't have to rely on some
| type-metadata that TypeScript emits. That's why it also works
| with JavaScript only. You don't have to fit every result into
| some type - it just returns an interface that can be used
| wherever you want. That's especially useful because TS's type
| system is structural.
|
| One major downside is that it's rather complicate to implement
| a parser and evaluation of the result type in TypeScript's type
| annotations. A different story is debugging type-level code;
| it's basically try-and-error. Providing error messages in case
| a query is wrong is also something that needs work. That's why
| it's only a PoC.
| pmelendez wrote:
| >Confession: something about ORMs has never clicked with me..
| none of them ever seem simpler than SQL.
|
| You are not alone on that sentiment. The opaque nature of the
| translation often result in poor performance, and to me the
| apparent ergonomics gains are not worth the trouble either (at
| least in most cases).
| randomdata wrote:
| ORM is orthogonal to SQL. The purpose of ORM is to transform
| relations (sets of tuples) to object graphs and back again. ORM
| toolkits provide some kind of declaration method to describe
| how that mapping should occur to save you the slog of doing it
| by hand.
|
| SQL is the usual mode for receiving those relations, and so
| many ORM toolkits also include query builders to help with that
| level of abstraction, but theoretically an ORM toolkit could
| require you to write SQL in the raw.
| zzzeek wrote:
| it is _so_ refreshing to see the correct answer stated so
| succinctly, even though it 's buried in the middle of yet
| another one of these "duh, ORMs suck, write raw SQL" threads
| (isn't everyone here _bored_ of these discussions yet?).
| congrats on being one of so very few who gets it.
| JodieBenitez wrote:
| It'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)
| MichaelCollins wrote:
| This sort of thing can be very good in the right hands, but
| it also facilitates the antipattern of select * with no
| conditions, then doing the real selection of columns and
| filtering of rows in code.
| openasocket wrote:
| They are indispensable when you need to build queries
| dynamically. Suppose you have some sort of list UI where users
| can query for data and filter down on rows they are interested
| in. They can filter on one of a dozen different columns in any
| combination. They can also sort on whatever column they want.
| There's no way you can implement this without building the SQL
| select statement dynamically, adding to the WHERE clause based
| on what the user wants to filter things. So you either start
| appending strings or you reach for an ORM.
|
| I agree that often the full ORM isn't very helpful, I prefer
| something that's more of a light layer over SQL to enable
| dynamic query building and that's it.
| darau1 wrote:
| Having worked with a decade+ old, hand-engineered, framework-
| less, codebase, for a profitable international business, I'd
| say they are anything but indispensable.
|
| They are _very nice to have_ , and I'll implement one the
| first chance I get, but "indispensable" is a stretch, IMO
| uup wrote:
| You would really like sqldelight[1] then. It takes the concept
| of an ORM and flips it on its head. Instead of mapping function
| calls to SQL statements, it lets you write SQL statements and
| then generates classes for you that have methods for those
| statements.
|
| For instance, you could have a SQL statement like
| getCardsForFight: select * from fights where cardId = ? and
| titleFight = ?, and it would generate a class that has a method
| getCardsForFight(cardId: number, titleFight: number).
|
| [1]: https://github.com/cashapp/sqldelight
| metadat wrote:
| Note: sqldelight currently only supports generating classes
| for Kotlin.
|
| With something like this existed for Golang!
| metadat wrote:
| *Wish
| pstuart wrote:
| Would this do? https://github.com/kyleconroy/sqlc
| metadat wrote:
| Yes, thank you!
| heisenbit wrote:
| My understanding from reading the page was that flyweight
| translates trivial table access to SQL. For anything beyond
| that is seems to do exactly what you describe i.e. you write
| the query and it is creating a TypeScript API for the query.
| Which saves you the work of defining the interface and
| ensures some level of correctness of the mapping.
___________________________________________________________________
(page generated 2022-09-29 23:02 UTC)