Using Postgres transactions

Help & Support
1

Is there an example of using postgres transactions with crystal/db and crystal/pg? Does it simply work if all the statements starting from BEGIN to COMMIT are put in the db.exec string argument? If yes, what is the way to know if the transactions were successful or not?

Thank you.

2

@nsuchy requested the same thing this week . It’s time to make an entry in https://github.com/crystal-lang/crystal-book/tree/master/database explaining transactions :sweat_smile:

Use Database#transaction to start a top level transaction. From the result you will have access to a specific connection to perform the queries.

Nested transactions are also supported / implemented.

3

Hi @curious2learn I actually wrote about this in my blog post (https://nsuchy.me/2019/06/01/how-i-parsed-huge-json-files-into-an-sqlite-database-in-under-a-second-using-the-power-of-crystal-language/) where you can see a code example.

You’re able to write it out as (see the linked blog post for context to better understand this code):

require "json"
require "sqlite3"
require "./cve_data_entity.cr"
 
module MyProgram
  VERSION = "0.1.0"
 
  filepath = "./src/example-json-files/example-full-2019-dataset.json"
  myobject = CVE_Data_Entity.from_json(File.read(filepath))
 
  DB.open "sqlite3://./src/example-json-files/dbname.sqlt" do |db|
    db.transaction do |tx|
      tx.begin_transaction
      myobject.try(&.cve_items).try(&.each do |item|
        # Insert General Information into the Database
        cve_item_id = item.try(&.cve).try(&.cve_data_meta).try(&.id) || "Not available"
        data_type = item.try(&.cve).try(&.data_type) || "Not available"
        data_format = item.try(&.cve).try(&.data_format) || "Not available"
        data_version = item.try(&.cve).try(&.data_version) || "Not available"
        published_date = item.try(&.publishedDate) || "Not available"
        last_modified_date = item.try(&.lastModifiedDate) || "Not available"
        tx.connection.exec("INSERT INTO GENERAL_INFORMATION (\"ID\", \"DATA_TYPE\", \"DATA_FORMAT\", \"DATA_VERSION\", \"PUBLISHDATE\", \"LASTMODIFIEDDATE\") VALUES (?, ?, ?, ?, ?, ?)", [cve_item_id, data_type, data_format, data_version, published_date, last_modified_date])
 
      end)
      tx.commit
    end
  end
end
4

Doesn’t this inherently handle the begin/commit? Versus having to manaully call tx. begin_transaction, and tx.commit?

1 Like
5

I’m not sure, that would be nice to have. Manually calling it seemed to be the most consistent and easy to use behavior for me.

6

yes it does! The semantic is implicit commit unless there is an exception raised.

If the exception is DB::Rollback it is swallowed, otherwise it is re-raised.

3 Likes
7

Thank you very much @nsuchy, @Blacksmoke16 and @bcardiff. Appreciate your responses.

1 Like