CRUD with Crystal and Mysql

2 minute read

After understanding how to integrate crystal with mysql, then this time we will learn about CRUD deeper, make sure before this you have follow this article Here are some crystal-db statements you can use,

1. db.query

You can retrieve 1 or more results with this statement, the results are in the form of data in order, and you must specify the data type correctly.

2. db.query_one

You can only retrieve 1 result data from here, but of course you have to add limit 1 on your query sql, the example of its usage is

name, city = "select name, city from indonesian_people where job='webdev' limit 1", as:{String, String}

3. db.exec

Very suitable for sql query like, insert update and delete.

Overall example

require "./jihantoro-cr-mysql/*"
require "db"
require "mysql"

db = DB.open "mysql://username:password@localhost:3306/jihantoro_crmysql"
at_exit { db.close }

friend = "Rouf"

puts "inserting data..."
db.exec "insert into friend (name) values (?)", friend # insert (Create) method

puts "reading data..."
myfriend = db.query_one "select name from friend limit 1", as:{String} # read (Read) method
puts "Hi " + myfriend

puts "updating data..."
newfriend = "Alficha"
db.exec "update friend set name=? where name=?", newfriend, myfriend # update (Update) method

puts "deleting data..."
db.exec "delete from friend where name=?", newfriend

puts "We want more..."
friends = ["Ary","Isacc","Fridgerator","Serdar","BrigeBOT xD"]
(0...5).each do |i|
  name = friends[i].to_s
  db.exec "insert into friend (name) values (?)", name
  puts "Hi " + name
end

puts "i forgot all of my friend, :O"
puts "reading memory. . .."
db.query "select name from friend" do |data|
  data.each do
    friend = data.read(String)
    puts friend + " is your friend"
  end
end

one = db.query_one? "select name from friend where name='Ary' limit 1", as:{String}
if one
  one + " is still my friend"
end

db.exec "delete from friend"

puts "Bye..."


Repo for this article

Leave a Comment