(connect '("localhost" "" "username" "passwd") :database-type :mysql)

  (create-database :users)

  (define-view-class user ()
    ((id     :initarg :id    :accessor user-id :type integer
             :db-constraints :key :not-null :auto-increment)
    ((name   :initarg :name  :accessor user-id :type string)
     (passwd :initarg :passwd :accessor user-passwd :type string)))

   (create-view-from-class 'user)

Then in the REPL

  CL-USER 1> (list-tables)
  ("users")

Now for some interaction:

  CL-USER 2> (make-instance 'user :name "Popeye" :passwd "sailorM0n")

  #<user>     ; <- instantiating an object creates a users.user record

Some time later, perhaps after a machine restart

  CL-USER 1> (select 'user)
  ((#<user>))
  CL-USER 2> (select 'user :where [= :name "Popeye"])
   (#<user>)

Say, you wanna change Popeye's password.

  CL-USER 3> (setf (user-passwd 
                      (select 'user :where [= :name "Popeye"]))
                   "new-password")

But typically you would just define a function, say, FIND-USER which will get you an object corresponding with a user record.

  (defun find-user (by val)
    (caar (select 'user :where (sql-= by val))))

That way you can find an object by ANY slot (or in SQL, find a record by the value of a column.

  CL-USER> (user-name (find-user 'id 0))
  "Popeye"
  CL-USER> (user-id   (find-user 'name "Popeye"))
  0
  CL-USER> (user-passwd (find-user 'name "Popeye"))
  "new-password"

That last value is due to our last update.

So you're bored with this class, and the auto-incremented primary key ID is MySQL only. Fine, redefine the ID slot mapping its value to a sequence

  CL-USER 3> (create-sequence 'user)
  CL-USER 4> (defclass user ()
                ((id  :initform (sequence-next 'user)))

The rest of the class definition stays the same.

  mysql > show tables;
  user  _clsql_sequence_user

:-) so you're bored with the database. drop the classes/tables one by one or just drop the whole database.

   CL-USER 5> (drop-sequence 'user)
   NIL
   CL-USER 6> (drop-view-from-class 'user)
   NIL
   CL-USER 7> (drop-database 'user)
   NIL

You can use the same code in MySQL, Postgres, DB2, Oracle, ODBC, Sqlite?, and an in-memory Sqlite3 implementation. This shit is industrial-strength eye candy. Scan the manual:

http://clsql.b9.com/manual/

Tutorial:

http://www.ravenbrook.com/doc/2002/09/13/common-sql/

It comes in fully open source and corporate supported versions: The OSS one is called CLSQL and its compatible with LispWorks' CommonSQL.