4 ms·
When working directly with SQL instead of an ORM, how do you elegantly handle things like parameterizing table names or having flags that turns on or off filter
by wodenokoto 6y ago
When working directly with SQL instead of an ORM, how do you elegantly handle things like parameterizing table names or having flags that turns on or off filters?
In non-ORM codebasese I am seeing patterns where sql queries are "copy-pasted" together and I am not sure I like it.
Just imagine the following example had hundreds of lines of SQL and several optional filters, some of which could themselves be several lines long:
def get_data(table1, table1, use_subset=True):
if use_subset:
filter_query = "AND column2="subset_value"
else:
filter_query = ""
query = f"""
SELECT
*
FROM
{table1}
JOIN
{table2}
WHERE
column1="value"
{filter_query}
"""
- ashtonian 6y agoI have come to the same conclusion as the author. In go I use rql: https://github.com/a8m/rql https://github.com/a8m/rql Which I ported to c# for use in .NET: https://github.com/Ashtonian/RQL.NET https://github.com/Ashtonian/RQL.NET
- strken 6y agoYou could use a query builder for this, rather than a full ORM. Programmatically generating SQL is a subset of what an ORM does, and it's one of the less contentious parts.
- ipiz0618 6y agoThis is basically how I use ORMs. Only as a query builder. Writing plain SQLs in code can be very messy and hard to debug, and this works similar to an API the author mentioned in his article. IMO ORMs can spare some duplication, but relying too much on that is quite painful.
- zwack 6y agoIn quma this would look like this (when using the default mako templating). example.sql SELECT * FROM %(table1)s JOIN %(table2)s WHERE column1 = "value" % if use_subset: AND column2=%(filter_value)s % endif def get_data(table1, table1, use_subset=True): if use_subset: filter_value = "subset_value" else: filter_query = "" data = cur.example(table1=table1, table2=table2, use_subset=use_subset, filter_value=filter_value).all() https://github.com/ebenefuenf/quma/ https://github.com/ebenefuenf/quma/ https://quma.readthedocs.io/en/latest/templates.html https://quma.readthedocs.io/en/latest/templates.html
- pkphilip 6y agoEDIT: Removed the reference to SimpleJdbcTemplate. A good alternative (if you are using Java) is to use something like Spring's JDBCTemplate. This provides the following benefits over using Hibernate/JPA: 1. You craft the queries yourself and so there is no intermediate ORM language which you need to use. 2. It parameterises the queries so that you don't have to append strings to get to the final query. https://dzone.com/tutorials/java/spring/spring-simple-jdbc-template.html https://dzone.com/tutorials/java/spring/spring-simple-jdbc-t... <code> @Override public void insertForum(Forum forum) { String query = "INSERT INTO FORUMS (FORUM_ID, FORUM_NAME, FORUM_DESC) VALUES (?,?,?)"; jdbcTemplate.update(query, forum.getForumId(), forum .getForumName(), forum.getForumDesc()); } @Override public Forum selectForum(int forumId) { String query = "SELECT * FROM FORUMS WHERE FORUM_ID=?"; return jdbcTemplate.queryForObject(query, new ParameterizedRowMapper<Forum>() { @Override public Forum mapRow(ResultSet resultSet, int rowNum) throws SQLException { return new Forum(resultSet.getInt("FORUM_ID"), resultSet.getString("FORUM_NAME"), resultSet.getString("FORUM_DESC")); } }, forumId); } </code> 3. It fetches only the columns that you need it to fetch and across multiple tables (if need be) without fetching the "relationships" between tables unnecessarily. The example given below shows how to use RowMappers and to fetch only the columns you need: <code> String query = "SELECT FORUM_ID, FORUM_NAME, FORUM_DESC FROM FORUMS WHERE FORUM_ID=?"; return (Forum) jdbcTemplate.queryForObject(query, new Object[] { Integer.valueOf(forumId) }, new RowMapper() { public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException { return new Forum(resultSet.getInt("FORUM_ID"), resultSet.getString("FORUM_NAME"), resultSet.getString("FORUM_DESC")); } });