{"id":83649,"date":"2020-06-02T07:42:21","date_gmt":"2020-06-02T05:42:21","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/database-as-sode-experience"},"modified":"2020-06-02T07:42:21","modified_gmt":"2020-06-02T05:42:21","slug":"database-as-sode-experience","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/database-as-sode-experience","title":{"rendered":"\"Database as Code\" Experience","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\""Database<\/p>\n

<\/p>\n

SQL, what could be simpler? Each of us can write a simple query \u2014 just type select<\/em><\/strong>, list the required columns, then from<\/em><\/strong>, the table name, add a few conditions in where<\/em><\/strong> and that's it \u2014 we have useful data at our fingertips, (almost) regardless of which DBMS is running behind the scenes (or maybe it's not even a DBMS at all ). As a result, working with virtually any data source (relational or otherwise) can be viewed through the lens of regular code (with all the implications \u2014 version control, code review, static analysis, automated tests, and all that). This concerns not just the data, schemas, and migrations, but the overall lifecycle of the repository. In this article, we will discuss everyday tasks and challenges of working with various databases through the prism of \"database as code\".<\/a><\/noindex>). As a result, working with virtually any data source (relational or otherwise) can be viewed in terms of regular code (with all its implications \u2014 version control, code review, static analysis, automated testing, and all that). This applies not only to the data themselves, schemas, and migrations but also to the overall operation of the repository. In this article, we will discuss the everyday tasks and challenges of working with various databases through the lens of 'database as code'.<\/p>\n

<\/p>\n

ORM . The first battles of \"SQL vs ORM\" were noted back in<\/a><\/noindex>. The first battles of 'SQL vs ORM' were noted back in Object-Relational Mapping<\/a><\/noindex>.<\/p>\n

<\/a><\/noindex><\/p>\n

ORM proponents traditionally value the speed and simplicity of development, independence from the DBMS, and code cleanliness. For many of us, the code for working with the database (and often the database itself)<\/h5>\n

<\/p>\n

usually looks something like this\u2026<\/p>\n

<\/p>\n

usually looks something like this\u2026<\/b><\/p>\n

@Entity\n@Table(name = \"stock\", catalog = \"maindb\", uniqueConstraints = {\n        @UniqueConstraint(columnNames = \"STOCK_NAME\"),\n        @UniqueConstraint(columnNames = \"STOCK_CODE\") })\npublic class Stock implements java.io.Serializable {\n\n    @Id\n    @GeneratedValue(strategy = IDENTITY)\n    @Column(name = \"STOCK_ID\", unique = true, nullable = false)\n    public Integer getStockId() {\n        return this.stockId;\n    }\n  ...<\/code><\/pre>\n
<\/p>\n
\"SQL hatred\" \"SQL hate\"<\/a><\/noindex>.<\/p>\n
<\/p>\n
On the other side of the barricade, proponents of pure 'handmade' SQL emphasize the ability to squeeze every drop from their DBMS without additional layers and abstractions. As a result, 'data-centric' projects emerge, where a specially trained group of individuals (also known as 'database experts', 'database specialists', and so forth) handle the databases, leaving developers to simply 'pull' ready-made views and stored procedures without delving into details.<\/p>\n
<\/p>\n
Yesql . I'll provide a couple of lines from the overall concept in my loose translation, but you can get to know it in more detail.<\/a><\/noindex>I will provide a couple of lines from the general concept in my free translation, and you can get to know it in more detail. here<\/a><\/noindex>.<\/p>\n
<\/p>\n
Clojure is a great language for creating DSLs, but SQL is already a powerful DSL by itself, and we do not need another one. S-expressions are beautiful, but they add nothing new here. Ultimately, we end up with parentheses for the sake of parentheses. Don't agree? Then wait until the abstraction over the database springs a leak, and you'll start fighting with functions. (raw-sql)<\/em><\/p>\n
So what to do? Let's keep SQL as regular SQL \u2014 one file for one query:<\/p><\/blockquote>\n
<\/p>\n
-- name: users-by-country\nselect *\n  from users\n where country_code = :country_code<\/code><\/pre>\n
<\/p>\n
\u2026 and then read this file, turning it into a regular Clojure function:<\/p><\/blockquote>\n
<\/p>\n
(defqueries \"some\/where\/users_by_country.sql\"\n   {:connection db-spec})\n\n;;; A function named `users-by-country` has been created.\n;;; Let's use it:\n(users-by-country {:country_code \"GB\"})\n;=> ({:name \"Kris\" :country_code \"GB\" ...} ...)<\/code><\/pre>\n
<\/p>\n
By adhering to the principle of 'SQL separately, Clojure separately', you get:<\/p>\n