Is there life without an ORM?

After switching to clojure in our projects, we needed to find a substitute for the usual database tools.



In clojure, there is a standard work library with the clojure.java.jdbc database and several libraries based on it that allow you to write queries in the eDSL provided by them. But for “daily use,” we wanted something that, for convenience, resembled jpa and worked with it in the IDE.



We thought, why not write our own library, which would be ideally suited to our requirements. And the requirements were as follows:

')

• autocompletion of tables, fields;
• autocompletion of constants from certain tables (later);
• convenient eDSL requests;
• the ability to retrieve values ​​from tables linked by a foreign key (later) without a manual query.

Then it would be as convenient to work with DB as before. So, after 2-3 days the library was ready.



The following public functions have been implemented:

- generate-table-column-names : generation of table names and information about their fields as variables in a specific namespace;

- generate-column-value-constants : generation of constants from the fields of a given table in a particular namespace;

- with-db : a macro that creates a new connection and a transaction with which it executes its body; when an exception occurs, a rollback occurs;

- select : sample using current connection;

- select-with-db : selection with the creation of a new connection according to the specified description;

- select-deep : selection with the creation of a new connection according to the specified description with linking to other tables by foreign keys;

- get-field-from-row : getting the field from the record with the ability to work with a chain of table fields connected by a foreign key;

- update , insert .

Usage example

For example, we have a connection description:

( def db { : classname "com.mysql.jdbc.Driver"

: subprotocol "mysql"

: subname "// localhost: 3306 / clj_query"

: user "clj"

: password "clj" } )

We generate the working database tables, their fields and some constants from the tables:

user > ( require ' [ libs . db . gentablecolumns : as gen ] )

nil

user > ( require ' [ libs . db . gencolumnvalues : as genval ] )

nil

user > ( gen / generate-table-column-names db )

nil

user > ( genval / generate-column-value-constants db table-recordtypes ( : name recordtypes- name ) )

nil

As a result, we have the following sources:



entities.clj:

( ns db . entities )



;;;; players

( def table-players "players" )

( def players-id { : type { : size 10 ,: name "INT UNSIGNED" } ,: table "players" ,: name "id" } )

( def players- name { : type { : size 255 ,: name "VARCHAR" } ,: table "players",: name "name" } )

( def players-type_id { : type { : size 10 ,: name "INT UNSIGNED" } ,: table "players ",: name "type_id" } )



;;;; playertypes

( def table-playertypes "playertypes" )

( def playertypes-id { : type { : size 10 ,: name "INT UNSIGNED" } ,: table "playertypes",: name "id" } )

( def playertypes- name { : type { : size 255 ,: name "VARCHAR" } ,: table "playertypes",: name "name" } )



;;;; records

( def table-records "records" )

( def records-id { : type { : size 10 ,: name "INT UNSIGNED" } ,: table "records" ,: name "id" } )

( def records-type_id { : type { : size 10 ,: name "INT UNSIGNED" } ,: table "records ",: name "type_id" } )

( def records-score { : type { : size 19 ,: name "BIGINT" } ,: table "records" ,: name "score" } )

( def records-player_id { : type { : size 10 ,: name "INT UNSIGNED" } ,: table "records ",: name "player_id" } )



;;;; recordtypes

( def table-recordtypes "recordtypes" )

( def recordtypes-id { : type { : size 10 ,: name "INT UNSIGNED" } ,: table "recordtypes",: name "id" } )

( def recordtypes- name { : type { : size 255 ,: name "VARCHAR" } ,: table "recordtypes",: name "name" } )

recordtypes.clj:

( ns db . recordtypes )



( def name-kills-per-round-id 1 )

( def name-kills-per-round "kills per round" )

( def name-kills-per-game-id 2 )

( def name-kills-per-game "kills per game" )

( def name-longest-undead-time-id 3 )

( def name-longest-undead-time "longest undead time" )

Suppose we want to know the user or the bot has a record of the highest number of kills during the round:

user > ( require ' [ db . recordtypes : as rectypes ] )

nil

user > ( use 'db . entities )

nil

user > ( def record ( first ( q / select-deep db table-records

: join [ [ table-recordtypes [ : = recordtypes-id records-type_id ] ] ]

: where [ : = recordtypes-id rectypes / name-kills-per-round-id ] ) ) )

# 'user / record

We get the player type through the table linked by the foreign key:

user > ( q / get-field-from-row record players-record_id players-type_id playertypes- name )

"bot"

Result: the owner of the record - "bot".

And most importantly, the addition has worked everywhere (not complemented by only: keywords),



Pluses library



- autocompletion of constants, tables and fields;

- inserting in a certain way the above generation into macros when changing the names of tables, fields or constants allows to get errors during compilation, and not at the execution stage;

- convenient eDSL (update, insert, select -..., with-db), able to work with the generated data.



As a result, work with databases in our clojure projects has become easier, more flexible and more convenient than it was before.