@seancorfield Validation that a user name is 50 chars max, would you check it in code or column (varchar(50))? What about valid email?

And don't you think that validating in multiple places may result in duplication, while validating in the db is only once and for every operation?

@itaied If the username is input from outside your system (an API, a web form) and the application model requires that it be 50 chars or less, then I would validate it where it came into the system.

I would have a spec for it -- so one definition of the rule -- and s/conform it as it comes in and check s/invalid? on the result.

Here's how we do that, specifically:

(defn username-valid?
  "Given a potential username, return true if it passes all our rules.
  Must be between 4 and 100 characters.
  Must not include whitespace, @, <, >, ', or \" characters."
  [username]
  (and (<= 4 (count username) 100)
       (not (re-find #"[\s@'\"<>]" username))))

(s/def ::username (s/with-gen username-valid?
                    ;; generate a restrictive set of possible usernames
                    (wgen/fn-string-from-regex #"[-_a-zA-Z0-9]{4,100}")))

Yes of course, I'm just wondering, if you have multiple places (or endpoints) where a username may come as an input, you have to s/conform and s/invalid? it multiple times, and may forget in some places, whereas in the db you have to check it only once and you can never forget

I would argue you should validate any external data that enters your system, at the boundary where it enters. How many places are you going to have the same data coming into your system?

Writing a spec for each web form or set of API parameters etc is no big deal and you reuse the exact same spec (`::username`) in all of them.

I'm just playing around with clojure, so I'm developing a really simple web app (you can actually check it out [here](https://gitlab.com/itaied246/xchange)), and I guess I'll only have a few (if not one), I'm just thinking about large scale systems, where multiple developers touching the code, entry points, data acess etc

one may forget to execute the spec validation

The size of our code base

Clojure build/config 43 files 2149 total loc
Clojure source 242 files 55241 total loc,
    3088 fns, 701 of which are private,
    370 vars, 41 macros, 46 atoms,
    451 specs, 19 function specs.
Clojure tests 144 files 19188 total loc,
    23 specs, 1 function specs.

Powering 100+ dating sites in 14 languages.

We validate web form data, URL data, API parameters etc -- all as they come into our system, so we can be (reasonably) sure we're only ever dealing with "correct" data inside our application model.

Ok I think I understand, when it comes to working with the data it makes sense, I was thinking just about the db as the next step. Out of curiosity,how do you validate the data into the db? And how do you handle migrations? Thanks a lot for you time

@itaied For the most part, since our data is validated in our "Model", we can just safely insert! into the database without too much worry. There are some edge cases -- as I hinted at yesterday -- where we anticipate an insert! might fail due to a uniqueness constraint violation and we'll have a specific function for inserting that data which has a try/`catch` to account for handling the constraint violation, but those are pretty rare (I can think of maybe half a dozen across our entire code base).

i am getting error Illegal character in query at index 110: and character on that position is } that is in the password of the db url services use it no problem i can connect to db BUT clojure.java.jdbc gives me that error and i cant make function for insert/update could anyone help?

@lepistane That sounds very strange that it thinks the character is in your query... Can you share a bit more of your code and the stack trace too?

coming up

Also, just to check, is this with clojure.java.jdbc (which I maintain and support) or something else...?

(let [db "db url"]
  (j/db-do-prepared db
                    "insert into clients_details (client_details_id, client_id, device_id, email, display_name, sign_up_type, country, guest) values (? ? ? ? ? ? ? ?)" [(java.util.UUID/randomUUID) (java.util.UUID/randomUUID) "123" "" "Mihai" "email" "ROU" false]))

Unhandled java.net.URISyntaxException
   Illegal character in query at index 110:
   "db url"

                  URI.java: 2848  java.net.URI$Parser/fail
                  URI.java: 3021  java.net.URI$Parser/checkChars
                  URI.java: 3111  java.net.URI$Parser/parseHierarchical
                  URI.java: 3053  java.net.URI$Parser/parse
                  URI.java:  588  java.net.URI/<init>
                  jdbc.clj:  259  clojure.java.jdbc/get-connection
                  jdbc.clj:  193  clojure.java.jdbc/get-connection
                  jdbc.clj:  815  clojure.java.jdbc/db-do-prepared
                  jdbc.clj:  795  clojure.java.jdbc/db-do-prepared
                  jdbc.clj:  806  clojure.java.jdbc/db-do-prepared
                  jdbc.clj:  795  clojure.java.jdbc/db-do-prepared
                      REPL:  117  mobytron.db.market.client/eval13648
                      REPL:  116  mobytron.db.market.client/eval13648
             Compiler.java: 6927  clojure.lang.Compiler/eval
             Compiler.java: 6890  clojure.lang.Compiler/eval
                  core.clj: 3105  clojure.core/eval
                  core.clj: 3101  clojure.core/eval
                  main.clj:  240  clojure.main/repl/read-eval-print/fn
                  main.clj:  240  clojure.main/repl/read-eval-print
                  main.clj:  258  clojure.main/repl/fn
                  main.clj:  258  clojure.main/repl
                  main.clj:  174  clojure.main/repl
               RestFn.java: 1523  clojure.lang.RestFn/invoke
    interruptible_eval.clj:   87  clojure.tools.nrepl.middleware.interruptible-eval/evaluate/fn
                  AFn.java:  152  clojure.lang.AFn/applyToHelper
                  AFn.java:  144  clojure.lang.AFn/applyTo
                  core.clj:  646  clojure.core/apply
                  core.clj: 1881  clojure.core/with-bindings*
                  core.clj: 1881  clojure.core/with-bindings*
               RestFn.java:  425  clojure.lang.RestFn/invoke
    interruptible_eval.clj:   85  clojure.tools.nrepl.middleware.interruptible-eval/evaluate
    interruptible_eval.clj:   55  clojure.tools.nrepl.middleware.interruptible-eval/evaluate
    interruptible_eval.clj:  222  clojure.tools.nrepl.middleware.interruptible-eval/interruptible-eval/fn/fn
    interruptible_eval.clj:  190  clojure.tools.nrepl.middleware.interruptible-eval/run-next/fn
                  AFn.java:   22  clojure.lang.AFn/run
   ThreadPoolExecutor.java: 1149  java.util.concurrent.ThreadPoolExecutor/runWorker
   ThreadPoolExecutor.java:  624  java.util.concurrent.ThreadPoolExecutor$Worker/run
               Thread.java:  748  java.lang.Thread/run

OK, so it's failing on parsing your connection string -- so that's your problem (and it's java.net.URI that says it's invalid, not the Clojure lib).

The best way to specify DB connections is to use a hash map, rather than attempting to construct the JDBC connection string yourself. Is there some reason you can't use the hash map approach?

(BTW, I saw you mention clj-jdbc elsewhere -- that's a different library altogether so be careful of confusing folks)

yea on beginners my mistake someone pointed out that too, i will be more precise from now on

(specifically if you search for clj-jdbc you get a library that started out life being illegally copied from clojure.java.jdbc with all the copyright notices stripped out!)

the way this repo is setup (this could be bad approach i am just faced with using and adding this to it) it has env variables set (one of which is db string) and when you cider-jack-in or start the program it gets those strings and passes it around where they are needed

'k... well, the problem is the format of the connection string, not actually being a valid URI in Java...

So you need to find a way to format it such that java.net.URI thinks it is valid.

gonna google that

=> (java.net.URLEncoder/encode "foo}bar")
"foo%7Dbar"

Also bear in mind if you use a raw connection string in every JDBC call, it's going to have to be parsed and then a new DB connection spun up and then a transaction run on that new connection and committed, then the connection closed. That gets expensive if you're doing a lot of DB operations.

so i should use j/with-db-transaction if i am accessing db often?

another good option is a connection pool

those are not mutually exclusive of course

Depends on the pattern of use. If you have one invocation of your app/lib/api and multiple DB operations within that invocation, using with-db-connection or with-db-transaction are going to be a big improvement.

BTW https://www.dropbox.com/s/ylw0rygyw6sycaz/Screenshot%202018-01-11%2015.19.32.png?dl=0

You can see that it's java.net.URI complaining (before clojure.java.jdbc even gets the value back).

I haven't tried using URL-encoded values in JDBC strings myself but that should get you further (thanks @noisesmith!)

db-spec {:subname
   "db url,
   :subprotocol nil} is missing a required parameter

That's not a valid map for a spec.

If you're stuck with a string, use it. If you're not stuck with a string the hash map is much easier with :dbtype, :dbname, :user, and :password

Then you don't need to know anything about the format of connection strings.

how do you connect to db? you said something about hashmaps? do you have example of that?

In the docs...

http://clojure-doc.org/articles/ecosystem/java_jdbc/reusing_connections.html

and here's the entry point http://clojure-doc.org/articles/ecosystem/java_jdbc/home.html

got it

Those docs are linked from the clojure.java.jdbc GitHub repo as well as http://clojure.github.io/java.jdbc/ -- if there's additional places I can get them posted so you could have found them more easily, let me know.

oki sure, thank you gonna try the hashmap way

JDBC connection strings are arcane... I hate them...

i could be fatigued but what am i doing wrong?

(def db-spec
{:classname "org.postgresql.Driver"
:subprotocol "postgresql"
:subname "url/db"
:user "usr"
:password "psw"})

Connection refused (Connection refused)

PlainSocketImpl.java: -2 java.net.PlainSocketImpl/socketConnect
AbstractPlainSocketImpl.java: 350 java.net.AbstractPlainSocketImpl/doConnect
AbstractPlainSocketImpl.java: 206 java.net.AbstractPlainSocketImpl/connectToAddress
AbstractPlainSocketImpl.java: 188 java.net.AbstractPlainSocketImpl/connect
SocksSocketImpl.java: 392 java.net.SocksSocketImpl/connect
Socket.java: 589 java.net.Socket/connect
PGStream.java: 61 org.postgresql.core.PGStream/<init>
ConnectionFactoryImpl.java: 121 org.postgresql.core.v3.ConnectionFactoryImpl/openConnectionImpl
ConnectionFactory.java: 66 org.postgresql.core.ConnectionFactory/openConnection
AbstractJdbc2Connection.java: 127 org.postgresql.jdbc2.AbstractJdbc2Connection/<init>
AbstractJdbc3Connection.java: 29 org.postgresql.jdbc3.AbstractJdbc3Connection/<init>
AbstractJdbc3gConnection.java: 21 org.postgresql.jdbc3g.AbstractJdbc3gConnection/<init>
AbstractJdbc4Connection.java: 41 org.postgresql.jdbc4.AbstractJdbc4Connection/<init>
Jdbc4Connection.java: 24 org.postgresql.jdbc4.Jdbc4Connection/<init>
Driver.java: 414 org.postgresql.Driver/makeConnection
Driver.java: 282 org.postgresql.Driver/connect
DriverManager.java: 664 java.sql.DriverManager/getConnection
DriverManager.java: 208 java.sql.DriverManager/getConnection
jdbc.clj: 272 clojure.java.jdbc/get-connection
jdbc.clj: 193 clojure.java.jdbc/get-connection
jdbc.clj: 848 clojure.java.jdbc/db-query-with-resultset
jdbc.clj: 818 clojure.java.jdbc/db-query-with-resultset
jdbc.clj: 874 clojure.java.jdbc/query
jdbc.clj: 854 clojure.java.jdbc/query
jdbc.clj: 867 clojure.java.jdbc/query
jdbc.clj: 854 clojure.java.jdbc/query
REPL: 99 mobytron.db.market.client/eval13662
REPL: 99 mobytron.db.market.client/eval13662
Compiler.java: 6927 clojure.lang.Compiler/eval
Compiler.java: 6890 clojure.lang.Compiler/eval
core.clj: 3105 clojure.core/eval
core.clj: 3101 clojure.core/eval
main.clj: 240 clojure.main/repl/read-eval-print/fn
main.clj: 240 clojure.main/repl/read-eval-print
main.clj: 258 clojure.main/repl/fn
main.clj: 258 clojure.main/repl
main.clj: 174 clojure.main/repl
RestFn.java: 1523 clojure.lang.RestFn/invoke
interruptible_eval.clj: 87 clojure.tools.nrepl.middleware.interruptible-eval/evaluate/fn
AFn.java: 152 clojure.lang.AFn/applyToHelper
AFn.java: 144 clojure.lang.AFn/applyTo
core.clj: 646 clojure.core/apply
core.clj: 1881 clojure.core/with-bindings*
core.clj: 1881 clojure.core/with-bindings*
RestFn.java: 425 clojure.lang.RestFn/invoke
interruptible_eval.clj: 85 clojure.tools.nrepl.middleware.interruptible-eval/evaluate
interruptible_eval.clj: 55 clojure.tools.nrepl.middleware.interruptible-eval/evaluate
interruptible_eval.clj: 222 clojure.tools.nrepl.middleware.interruptible-eval/interruptible-eval/fn/fn
interruptible_eval.clj: 190 clojure.tools.nrepl.middleware.interruptible-eval/run-next/fn
AFn.java: 22 clojure.lang.AFn/run
ThreadPoolExecutor.java: 1149 java.util.concurrent.ThreadPoolExecutor/runWorker
ThreadPoolExecutor.java: 624 java.util.concurrent.ThreadPoolExecutor$Worker/run
Thread.java: 748 java.lang.Thread/run