4 ms·

This is an example of what I call "embracing PostgreSQL". Roughly speaking it means ditching the ORM layers and using a simple API to run any SQL query that you

This is an example of what I call "embracing PostgreSQL". Roughly speaking it means ditching the ORM layers and using a simple API to run any SQL query that you can think up. Then your application can leverage the many features that exist in PostgreSQL and not just treat it as a dumb SQL server. Using functions as an intermediary to raw queries, also decouples the SQL details from the application. What I mean is that if the application calls an SQL function to read, update, insert data then you can revise the SQL functions independently of application code. In other words, if you refactor an SQL function, you can deploy the change to the database without rebuilding and deploying the application. This is especially helpful in companies that have an onerous verification and validation process for application changes, because often "performance improvements" are classified as administrative changes which can be done with less bureaucracy. So, embrace PostgreSQL as a permanent part of your application and reap the benefits of its many powerful features.

Klathmon 10y ago
Our application uses JSON throughout, and i've found the JSON functions in postgres to be invaluable! I recently refactored several functions which kind of organically grew from several different functions each with it's own SQL query into one big query. Not only did performance increase by a significant amount, the whole function is basically just "run this query and return the result" as the JSON functions in postgres allow me to leave the actual processing in the database and to not have to do the always annoying "loop over the 30,000 rows that were returned to build a JSON file" thing which is never fast.
- mozumder 10y ago
  JSON in Postgres is so fast... it's definitely not used widely enough. I'm not even sure why people would use something like Django Rest Framework, when Postgres outputs JSON directly from a query.
  - keshab 10y ago
    Because it is easier to maintain Python code rather than SQL.
  - icebraining 10y ago
    Because they want a RESTful endpoint, not JSON-over-SQL. Would you open that up as a public API with free registration? How do you do access control using OAuth? How would you do stuff like convert images to thumbnails, extract text from PDFs or send an email?
    - mixedCase 10y ago
      http://postgrest.com/ http://postgrest.com/ Anything more complicated than that, and I'd recommend a simple Go application.
      - icebraining 10y ago
        Both of those are adding a layer over Postgres; they are still "something like Django Rest Framework". Whether it's written in Python, Haskell or Go is not really the point I was making.
        mixedCase 10y ago
        There's a lot of substance to the stack chosen. The performance Haskell or Go offer over Python let alone Django, a fat framework, make them far better picks for thin wrappers around an RDBMS.
rtpg 10y ago
As someone who's thought about embracing over using the ORM, here's a couple issue that come up: - Version control. The SQL functions end up just being data in your DB. This feels like an anti-feature in the world of continuous integration and code review. - Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic. - Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations. - I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice. To be honest, you citing that it's easy to get around the validation process when using Postgres is a major red flag. Why would I want to circumvent code review for things that are most undoubtably code?
- grzm 10y ago
  Version control. The SQL functions end up just being data in your DB. This feels like an anti-feature in the world of continuous integration and code review. Functions can be treated the same way as migrations. With PostgreSQL you have transactional DDL, so you can atomically deploy and rollback functions. There plenty of different script packages to help manage this. At the end of the day it’s deploy and rollback SQL scripts in text files, which are easily managed with version control. Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic. The trade off here is that you’re trusting the ORM to produce an optimal query (or queries) for whatever you’re trying to do compared with writing the SQL yourself, with knowledge of your schema. Like any trade off, its one you need to decide for yourself. You may have filtering on the data that you’re returning from the database that you chose not to do in the query itself. At this point, you should just be dealing with data structures. I don’t think this would be different whether you’re using an ORM or not. Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations. I’m not sure what you’re getting at here, so I’m not sure if this addresses your concerns: If you’ve got clean separation of your code, you should only need to update a single location for any given piece of logic. Single source of truth, and all that. I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice. SQL scripts are generally text files, so anything that supports some kind of SQL syntax highlighting is likely all you need. I suspect PyCharm will work. The Jetbrains folks are pretty sharp. Why would I want to circumvent code review for things that are most undoubtably code? Since you can have your scripts under version control, you can likey use the same code review tools that you currently do. You can also use libraries such as pgtap[0] to test your schema and functions indepedent of the rest of your application, if you so choose. These are all issues I've dealt with in a large production PostgreSQL environment. They're good things to think about, and you're right to consider them. At the end, you need to make a decision you and your team is comfortable with. [0]: http://pgtap.org http://pgtap.org

Klathmon 10y ago

Our application uses JSON throughout, and i've found the JSON functions in postgres to be invaluable! I recently refactored several functions which kind of organically grew from several different functions each with it's own SQL query into one big query. Not only did performance increase by a significant amount, the whole function is basically just "run this query and return the result" as the JSON functions in postgres allow me to leave the actual processing in the database and to not have to do the always annoying "loop over the 30,000 rows that were returned to build a JSON file" thing which is never fast.

mozumder 10y ago
JSON in Postgres is so fast... it's definitely not used widely enough. I'm not even sure why people would use something like Django Rest Framework, when Postgres outputs JSON directly from a query.
- keshab 10y ago
  Because it is easier to maintain Python code rather than SQL.
- icebraining 10y ago
  Because they want a RESTful endpoint, not JSON-over-SQL. Would you open that up as a public API with free registration? How do you do access control using OAuth? How would you do stuff like convert images to thumbnails, extract text from PDFs or send an email?
  - mixedCase 10y ago
    http://postgrest.com/ http://postgrest.com/ Anything more complicated than that, and I'd recommend a simple Go application.
    - icebraining 10y ago
      Both of those are adding a layer over Postgres; they are still "something like Django Rest Framework". Whether it's written in Python, Haskell or Go is not really the point I was making.
      - mixedCase 10y ago
        There's a lot of substance to the stack chosen. The performance Haskell or Go offer over Python let alone Django, a fat framework, make them far better picks for thin wrappers around an RDBMS.

rtpg 10y ago

As someone who's thought about embracing over using the ORM, here's a couple issue that come up: - Version control. The SQL functions end up just being data in your DB. This feels like an anti-feature in the world of continuous integration and code review. - Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic. - Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations. - I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice. To be honest, you citing that it's easy to get around the validation process when using Postgres is a major red flag. Why would I want to circumvent code review for things that are most undoubtably code?

grzm 10y ago
Version control. The SQL functions end up just being data in your DB. This feels like an anti-feature in the world of continuous integration and code review. Functions can be treated the same way as migrations. With PostgreSQL you have transactional DDL, so you can atomically deploy and rollback functions. There plenty of different script packages to help manage this. At the end of the day it’s deploy and rollback SQL scripts in text files, which are easily managed with version control. Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic. The trade off here is that you’re trusting the ORM to produce an optimal query (or queries) for whatever you’re trying to do compared with writing the SQL yourself, with knowledge of your schema. Like any trade off, its one you need to decide for yourself. You may have filtering on the data that you’re returning from the database that you chose not to do in the query itself. At this point, you should just be dealing with data structures. I don’t think this would be different whether you’re using an ORM or not. Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations. I’m not sure what you’re getting at here, so I’m not sure if this addresses your concerns: If you’ve got clean separation of your code, you should only need to update a single location for any given piece of logic. Single source of truth, and all that. I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice. SQL scripts are generally text files, so anything that supports some kind of SQL syntax highlighting is likely all you need. I suspect PyCharm will work. The Jetbrains folks are pretty sharp. Why would I want to circumvent code review for things that are most undoubtably code? Since you can have your scripts under version control, you can likey use the same code review tools that you currently do. You can also use libraries such as pgtap[0] to test your schema and functions indepedent of the rest of your application, if you so choose. These are all issues I've dealt with in a large production PostgreSQL environment. They're good things to think about, and you're right to consider them. At the end, you need to make a decision you and your team is comfortable with. [0]: http://pgtap.org http://pgtap.org