Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Hot answers tagged

109

Checking for uniqueness and then setting is an antipattern; it can always happen that the ID is inserted concurrently between checking time and writing time. Databases are equipped to deal with this problem through mechanisms like constraints and transactions; most programming languages aren't. Therefore, if you value data consistency, leave it to the expert ...


90

Absolutely! SQL is still the lingua franca of databases and although you may do a lot with ORMs you have to understand SQL to understand the decisions ORMs make and the SQL they generate. Also, there are still lots of things that you have to do with custom sql and stored procedures as well. Sorry, no free lunch.


83

There's a is a fairly large and varied set of conceptual and technical difficulties when trying to approach a relational database from an object oriented angle. These difficulties are collectively known as object-relational impedance mismatch and the related Wikipedia article is extremely informative. The article identifies quite a few, I don't see any ...


63

This is hard to explain to a lot of programmers, because if you only know basic SQL then it really doesn't give you much of an advantage over the crutch of an ORM. The more advanced SQL concepts, however, are a crucial part of the difference between applications that just work vs. applications that are high quality (in particular, fast and reliable). I'm ...


47

Stored procedures are bad, they're often slow and approximately as efficient as ordinary client side code. [The speedup is usually due to the way the client and stored procedure interface is designed and the way transactions are written as short, focused bursts of SQL.] Stored procedures are one of the worst places to put code. It breaks your application ...


42

This is akin to asking "is a power drill an anti-pattern?". ORMs earned a good place in my toolbox, reducing my boilerplate code and I am still able to use custom SQL if necessary. So if it is an anti-pattern, which pattern does it go against? My answer is no, there are plenty of mature ORMs out there that make your life a lot easier and makes your code ...


38

Consider what you're trying to achieve. Typically, the Command Query Response Segregation model works well for complex domains. The reason is that you're trying to do one of two things typically: Create/Update/Delete some complex domain entities Run analytic fetch queries (i.e. summation/aggregation queries) Hibernate works well for case 1 allowing you to ...


37

I think what you call “fail fast” and what I call it is not the same. Telling the database to make a change and handling the failure, that is fast. Your way is complicated, slow and not particularly reliable. That technique of yours is not fail fast, it is “preflighting”. There are sometimes good reasons, but not when you use a database.


31

What you are describing as "Inline SQL" should really be called "string concatenation without parameterization," and you don't have to do that to use a Micro ORM safely. Consider this Dapper example: string sql = "SELECT * from user_profile WHERE FirstName LIKE @name;"; var result = connection.Query<Profile>(sql, new {name = "%"+name+"%"}); It's ...


30

Personally, I've tried making one huge schema for all my entities on a fairly complex but small project(~300 tables) . We had an extremely normalized database (5th form normalization (I say that loosely)) with many "many to many" relationships and extreme referential integrity enforcement. We also used a "single instance per request" strategy which I'm not ...


25

Yes, you still need to know SQL. ORMs are a very leaky abstraction, and do not provide access to the full power of SQL. For toy applications you may be ok with limited SQL knowledge. For enterprise applications, you will have to understand the database to get decent performance from the ORM. Also there are a great many tasks that are much more easily ...


20

Stored procedures are good, they're fast and very efficient and are the ideal place to put your data-related code. Moving all that code to the client will make things slightly easier for you as a client developer (slightly, as you'll still have to update the schema and ORM model when commit changes them) but you will lose all that existing code, and make ...


20

This is actually a very difficult question to answer and I have found it to be a very controversial subject. As Yannis Rizos pointed out in his answer, having the constraint logic in both the database and the ORM layer would seem to violate DRY, which "can lead to maintenance nightmares, poor factoring, and logical contradictions". However, removing the ...


20

Android does not play as nicely with other frameworks as it could. Its recommended style of development assumes you build everything from its API, without other libraries. The UI layer is very tightly coupled to the model. This style is ideal for writing smaller, modular apps, not for complex applications. You need to give some thought as to whether you ...


19

MyBatis is SQL centric. It heps you calling SQL statements and mapping results (tables) to object trees. The main benefit is that it is not an ORM. It does not map tables to object so does not suffer the orm impedance mismatch. Fits well for complex or legacy databases or to use db features like stored procedures, views and so. It is quite simple and easy ...


18

I would hestitate to call something an "anti-pattern" which was first called a pattern by Martin Fowler and has since been embraced in nearly every modern programming language. (See the Wikipedia article on ActiveRecord.) A good ORM can lead to much less code (and much less repetition) in a project, and nothing is as strongly correlated with bugs as ...


16

ORMs are not mutually exclusive with Stored Procedures. Most ORMs can utilize stored procedures. Most ORMs generate Stored Procedures if you so choose. So it the issue is not either or. ORMs may generate unacceptable SQL (in terms of performance) and you may sometimes want to override that SQL with hand-crafted SQL. One of the ways to accomplish this is by ...


16

Your application should still be modelled from domain driven design principles. Whether you use an ORM, straight JDBC, calling SPs (or whatever) should not matter. Hopefully a thin layer abstracting your model from the SPs should do the trick in this case. As another poster stated, you should view the SPs and their results as a service and map the results ...


16

Your guiding principle should be Don’t Repeat Yourself: In software engineering, Don't Repeat Yourself (DRY) is a principle of software development aimed at reducing repetition of information of all kinds, especially useful in multi-tier architectures. The DRY principle is stated as "Every piece of knowledge must have a single, unambiguous, authoritative ...


15

Dispite the current trend to recommend Doctrine, I need to say otherwise. Keep in mind that also, my personal preferences are oriented to my personal experiences, but how @Dan said, they're both very potent. I don't like Doctrine for several of the reasons you stated before, like the size and the whole magic methods thingy are the deal breakers with me. So, ...


14

SQL skill is a must have skill in IT today. LINQ is a Microsoft Only technology. SQL usages go beyond web and client/server application development. You can't model databases and do ETL if you are not good with SQL. You may not need to master SQL dialects used in ORACLE and SQL Server for their Data Warehous products, but you should know standard SQL. SQL ...


14

This started as a comment but grew too large. No, as the other answers have stated, this pattern should not be used.* When dealing with systems that use asynchronous components, there will always be a race condition where the database (or file system, or other async system) may change between the check and the change. A check of this type is simply not a ...


13

You seem to be trying to lead your team from one extreme (stored procedures and datasets) to another (a full-blown ORM). I think there are other, more incremental changes you can set about implementing to improve the code quality of your data access layer, which your team might be more willing to accept. The half-baked active-record implementation code you'...


13

ORMs are not intended to completely take over access to your database. Use them for that 80% of code that's CRUD, the stuff that's too tedious to write on your own. Use stored procedures, dynamic SQL, or whatever you want for the remaining 20% that needs to be carefully optimized.


13

ORMs often assume that the database exists to serve the ORM. But usually the database exists to serve the company, which might have hundreds and hundreds of apps written in multiple languages hitting it. But it's only a case of "ORM vs. Stored Procedures" if you're using an ORM that can't call a stored procedure. Otherwise, it's a case of deciding where to ...


13

There is definitely object-relational impedance. I had to deal with this for years on a product that kept its data on a database but was OO when it ran. As a vastly simplified example, consider three DB tables. Two are just lists, one containing people and one containing jobs. The third is a link between people and jobs, with a column for each. (And all ...


12

Benefits: Similar performance to a raw SqlCommand with DataReader and parsing. No need to roll your own conversion layer for the DataReader. That's pretty much it, to be honest. You've got a very lightweight wrapper to your sql connections that will do the object conversion for you. You can, obviously, fine-tune the queries without having to deal with any ...


12

so it would have been impossible to switch out to another ORM (not that we wanted to)). That seems wrong. A major advantage of the repository pattern is that you hide the data access logic and that it is easily exchangeable. So far it feels as though I put my business logic in my domain model and via repositories I would work with the ORM (which ever ...


12

Mark Seemann has an excellent blog post about this subject: IQueryable is Tight Coupling. He sums it up nicely in the final part (emphasis mine): You may think this is all a theoretical exercise, but it actually does matter. When writing Clean Code, it's important to design an API in such a way that it's clear what it does. An interface like this ...


12

Let me start by simple clarification: I don't have experience with such large database so the rest of my answer is not based on the real world example. So you have a BIG database and you want to use it with ORM / EF. I would go with the second choice. Here is my simple explanation why: Mapping adds complexity. There is no need to add complexity with ...


Only top voted, non community-wiki answers of a minimum length are eligible