How to use Julia databases

Julia allows us to connect and handle data stored in various databases like any other programming language. Julia can be linked with several databases like MySQL, SQLite, PostgreSQL, etc., by connecting to the Database Independent Interface (DBI). We can effectively leverage this functionality to communicate with our data and perform CRUD operations. For this Answer, we’ll play around with SQLite.jl.

Communicating with SQLite.jl using Julia

To kickstart using Julia inside a Jupyter Notebook, we’ll first install Julia on our systems and access the Julia REPL.

Installation

We’ll include Julia’s package manager Pkg and, with it, install the packages required for this Answer: IJulia, SQLite, and DataFrames.

IJulia: The installation of this package creates a kernel specification telling Jupyter Notebook how to launch Julia.
SQLite: This package installs SQLite.jl into our current session.
DataFrames: The DataFrames package is the Julia version of pandas DataFrame in Python.

The following slideshow shows how to include these packages in Julia:

Upon launching a Jupyter Notebook, we’ll be able to see a Julia kernel in the Jupyter Notebook. This allows us to select the Julia kernal and write Julia code in that Notebook.

Using Julia databases

Now, let’s look at how we can create, update, retrieve, and delete the SQLite.jl database using Julia. For that we’ll observe the file given below.

You will see some errors, but that will indicate the table has been successfully removed by showing they do not exist. Please see the comments for those as well in the code.

using Pkg
Pkg.add("SQLite")
Pkg.add("DataFrames")
using SQLite
using DataFrames
educatives_system_design_courses = SQLite.DB("educatives_system_design_courses")
#ensures if table already exists than drop it and create a new one

SQLite.execute(educatives_system_design_courses, "DROP TABLE educatives_system_design_courses")

# Create system design courses table
SQLite.execute(educatives_system_design_courses, "CREATE TABLE educatives_system_design_courses(course_id INT NOT NULL PRIMARY KEY, course_name TEXT NOT NULL , course_description TEXT NOT NULL, course_creator TEXT NOT NULL)") 
SQLite.tables(educatives_system_design_courses)

# Insert into system design courses database
SQLite.execute(educatives_system_design_courses,"INSERT INTO educatives_system_design_courses VALUES (1,'Grokking system design interview for engineers, mentors and managers', 'A course designed to test your system design skills with an AI interviewer','Ibrahim Nadir Khan')")

SQLite.execute(educatives_system_design_courses,"INSERT INTO educatives_system_design_courses VALUES (2,'System design crash course','A course for a speedy system design interview prep', 'Bismillah Jan')")
# Retrieve system design courses data from table

educatives_courses = SQLite.DBInterface.execute(educatives_system_design_courses,"SELECT * FROM educatives_system_design_courses")
educatives_courses_data_frame = DataFrames.DataFrame(educatives_courses)

SQLite.execute(educatives_system_design_courses,"UPDATE educatives_system_design_courses SET course_creator = 'Umer Daraz' WHERE course_id = 1 ")
educatives_courses = SQLite.DBInterface.execute(educatives_system_design_courses,"SELECT * FROM educatives_system_design_courses")
educatives_courses_data_frame = DataFrames.DataFrame(educatives_courses)

# Drop system design courses table
SQLite.execute(educatives_system_design_courses, "DROP TABLE educatives_system_design_courses")
educatives_courses = SQLite.DBInterface.execute(educatives_system_design_courses,"SELECT * FROM educatives_system_design_courses")
educatives_courses_data_frame = DataFrames.DataFrame(educatives_courses)

SQLite.jl CRUD operations

Code explanation

Lines 1–5: We import the Julia package manager Pkg and install the SQLite and DataFrames packages. We then import them by using the statement using SQLite.
Lines 6–9: Next, we connect to the database called educatives_system_design_courses on line 6 using the SQLite.DB(“educatives_system_design_courses”) constructor, passing it the database name as argument. Then, we run the SQLite.execute command as the database deletion query for the database educatives_system_design_courses. We want to ensure that if a database with this name exists, it is dropped, and a new one is created from the later code sections. In a nutshell, we want to start afresh.
Lines 11–13: By running the database creation command, we create a new table called educatives_system_design_courses with the fields course_id, course_name, course_description, and course_creator. All stated fields are text-based strings except for course_id, which is an integer. However, none of the fields can be NULL, as indicated by the NOT NULL specification. SQLite.tables(educatives_system_design_courses) retrieves information about the newly created database.
Lines 15–26: We insert dummy data into our database on lines 16 and 18. To verify whether the data has been correctly inserted, we run SELECT * FROM educatives_system_design_courses. This will retrieve all the rows from our table and display them as a DataFrame using the DataFrames.DataFrame() method.
Lines 28–31: Lastly, we’ll execute the database deletion and retrieval commands again, respectively. After dropping the table, we won’t be able to retrieve anything. Therefore, an error message will be displayed.

Conclusion

Julia is a diverse programming language that allows developers to play around with various databases. In this Answer, we thoroughly discussed how to perform CRUD operations on the SQLite.jl database. But that’s not the only database that can be managed via Julia. Julia can connect with many more, such as MySQL, PostgreSQL, etc.

Free Resources