Autogenerated primary key in postgresql with clsql

Question

I'm trying to make a simple database with Common Lisp ORM. I use PostgreSQL and CLSQL. I could create classes and generate tables, but it doesn't work when I want to insert a value without primary key in order to get a generated value. It seems that it works with mysql databases. Is it possible to do that with PostgreSQL?

I define the primary key as:

(id :db-kind :key
    :db-type "serial" 
    :db-constraints (:not-null :unique)
    :type integer
    :initarg :id)

And I get this error:

While accessing database #<POSTGRESQL-DATABASE localhost/cl_ormex/postgres OPEN {1004FCC403}>
  with expression "SELECT currval ('NIL')":
   Error 42P01 / relation "nil" does not exist
   LINE 1: SELECT currval ('NIL')
                           ^  
 has occurred.
   [Condition of type SQL-DATABASE-DATA-ERROR]

I use PostgreSQL 9.5.2 with SBCL 1.3.1.

edit

Here's an example:

(require 'clsql)
(defpackage :orm-ex (:use :cl :clsql))
(in-package :orm-ex)
(file-enable-sql-reader-syntax)
(enable-sql-reader-syntax)
(setf *default-caching* nil)
(connect '("localhost" "examp" "postgres" "postgres")
     :database-type :postgresql)

(def-view-class person ()
  ((id :db-kind :key
       :db-type "serial"
       :db-constraints (:not-null :unique)
       :type integer
       :initarg :id
       :accessor person-id)
   (name :type (varchar 30)
     :initarg :name
     :accessor person-name)))

(defparameter person1
  (make-instance 'person
         :name "Matt"))

(dolist (c '(person)) (create-view-from-class c))
(update-records-from-instance person1)

I don't really understand this error, but the row seems to be inserted in the database.

Answer 1

It seems that this will not work. it has a todo file that says this:

• Test that ":db-kind :key" adds an index for that key. This is complicated by different backends showing autogenerated primary key in different ways.

So maybe it doesn't work with Postgres. I believe that you have many possibilities.

Use other frameworks a little bit more updated like cl-dbi, take a look here:

cl-dbi provides a uniform interface to the various database server-specific libraries (cl-postgres, cl-mysql, etc.). SxQL provides a DSL for building safe, automatically parametrized SQL queries.

There are two fairly complete ORMs: Crane, by yours truly, and Integral, by the author of cl-dbi.

Consolidation:
Discourage using anything other than cl-dbi.

Future Work:

Bindings for other database systems, e.g. Oracle, exist. Writing drivers for cl-dbi would be the best course of action and help consolidation.

I find easy to generate ids that can be ordered in ascending or descending order by creation time easily with timestamp, or also you can use generators or take in account the last number you insert

but this will do trick, the universal time and also add a random number, for creating many entities at the same time and having a low rate of collision

(format nil "~12,'0d-~6,'0d" (get-universal-time) (random 1000000))

Answer 2

From Loving Lisp by Mark Watson - db-constraints need to be defined with :auto-increment.
Note - the book version as of today (25/10/2019) is not correct, but the downloaded code is:

(clsql:def-view-class article ()
  ((id
    :db-kind :key
    :db-constraints (:auto-increment :not-null :unique)
    :type integer
    :initarg :id)
   (uri
    :accessor uri
    :type (string 60)
    :initarg :uri)
   (title
    :accessor title
    :type (string 90)
    :initarg :title)
   (text
    :accessor text
    :type (string 500)
    :nulls-ok t
    :initarg :text)))