Using an optional sqlite database in a cli app

Help & Support
1

Hi,
I am working on a CLI application using a sqlite3 database for data management, and, optionally, an external sqlite3 database as a reference for some specific commands. So, the connection to this 2nd database may exist or not, depending on the existence of its name in a config file.
I am using the Crystal-db shard for Sqlite3, and the DB.connect method.
The problem I am faced with is that, if the 2nd database does not exist, the connection will have ‘DB::Connection | Nil’ as type and I’ll have to cast it to ‘DB::Connection’ every where it is used.
How can I avoid that?

2

You don’t need to cast, do your conditions like this:

if conn = second_connection
  # conn is now your second DB::Connection (not nil)
  do_some_work_with_connection(conn)
end

https://crystal-lang.org/reference/1.12/syntax_and_semantics/if_var.html

Or do you want to avoid these conditions as well?

3

In present code, in the Config module, I use a property to save the connection across different commands (Config.conn2). So, yes, I’d like to keep this solution.

4

You can use property! (or class_property!) instead of property and then use Config.conn2? for conditions and Config.conn2 for accessing directly as DB:Connection.

https://crystal-lang.org/api/1.12.1/Object.html#property!(*names)-macro

Also, if you want to do work with second connection only if it’s available and ignore work silently if it’s not available, you can do something like this:

class Config
  property conn2 : DB::Connection?
  # ...
  def do_work_with_conn2 # or def self. ... if you have this functionality on the class side
    if c = @conn2
       yield c
    end
  end
end

And usage:

config.do_work_with_conn2 do |conn|
  # this will be executed only if second connection is active
  conn.execute "insert into blah blah..."
end

…and so, in various ways…

2 Likes
5

Thanks @pfischer, I’ll explore your ideas.

[Edit]

Finally, I solved my problem using the class_property! macro.

in Config module

class_property! conn2 : DB::Connection
...
if File.exists?(Config.dbfile2) && File.size(Config.dbfile2) > 0
  # We must check dbfile is ok before connection, as a connection request  
  # to Sqlite3 automatically creates an empty dbfile if it does not exist
  Config.conn2 = DB.connect("sqlite3://#{Config.dbfile2}")
end

and, before each command needing a connection to Config.dbfile2

if Config.conn2?.nil?
  raise Exception.new "Secondary database not available, aborting..." 
else
  # get rid of Nil !
  Config.conn2 = Config.conn2.as(DB::Connection)
end

Thanks again for your help

6

That assignment doesn’t seem to make much sense… :thinking:
Anyway, you can prevent the explicit casting by assigning to a local variable as @pfischer has shown above.

if conn = Config.conn2?
  # type of conn is DB::Connection
else
  raise Exception.new "Secondary database not available, aborting..." 
end