Now that we have our model laid out, we should create some object. Let us assume that we have a database connect set up already. We first need to create our tables in the database:

Note: the file examples/clsql-tutorial.lisp contains view class definitions which you can load into your list at this point in order to play along at home.

(clsql:create-view-from-class 'employee)
(clsql:create-view-from-class 'company)

Then we will create our objects. We create them just like you would any other CLOS object:

(defvar company1 (make-instance 'company
			      :companyid 1
			      :presidentid 1
			      :name "Widgets Inc."))
			      
(defvar employee1 (make-instance 'employee
			       :emplid 1
			       :first-name "Vladimir"
			       :last-name "Lenin"
			       :email "lenin@soviet.org"
			       :companyid 1))

(defvar employee2 (make-instance 'employee
			       :emplid 2
			       :first-name "Josef"
			       :last-name "Stalin"
			       :email "stalin@soviet.org"
			       :companyid 1
			       :managerid 1))

In order to insert an objects into the database we use the UPDATE-RECORDS-FROM-INSTANCE function as follows:

(clsql:update-records-from-instance employee1)
(clsql:update-records-from-instance employee2)
(clsql:update-records-from-instance company1)

After you make any changes to an object, you have to specifically tell CLSQL to update the SQL database. The UPDATE-RECORDS-FROM-INSTANCE method will write all of the changes you have made to the object into the database.

Since CLSQL objects are just normal CLOS objects, we can manipulate their slots just like any other object. For instance, let's say that Lenin changes his email because he was getting too much spam from the German Socialists.

;; Print Lenin's current email address, change it and save it to the
;; database.  Get a new object representing Lenin from the database
;; and print the email

;; This lets us use the functional CLSQL interface with [] syntax
(clsql:locally-enable-sql-reader-syntax)

(format t "The email address of ~A ~A is ~A"
	(first-name employee1)
	(last-name employee1)
	(employee-email employee1))

(setf (employee-email employee1) "lenin-nospam@soviets.org")

;; Update the database
(clsql:update-records-from-instance employee1)

(let ((new-lenin (car (clsql:select 'employee
	                :where [= [slot-value 'employee 'emplid] 1]))))
      (format t "His new email is ~A"
	  (employee-email new-lenin)))

Everything except for the last LET expression is already familiar to us by now. To understand the call to CLSQL:SELECT we need to discuss the Functional SQL interface and it's integration with the Object Oriented interface of CLSQL.