> For example, how do I apply DRY and use SQL ?

Lots of ways. Defining appropriate views is one of the key ones, but not the only one.

> How do I apply type safety principles?

That's too vague of a question to present a concrete answer to.

> It feels like this ends up with building some sort of ORM.

Anytime you are using a database from an external language, you are going to have some code to translate back-and-forth between database objects and operations and application language objects and operations; you might call it an ORM, but if you are using SQL more robustly, it may not look much like most things that go under the ORM label, and some popular ORMs are fairly opinionated in ways which fight effective use of SQL (ActiveRecord in particular at least used to be; I haven't done much with it in quite a while and am not sure if that's still the case.)

When I can do my ideal best, I write a remote facade to my database that provides functions and views on the data as an API, which can be adapted to an ORM or mapped nearly 1:1 to a DAO.

This API lends itself to TDD, and yes, you must test your database components the same as any other code. Preferably using database code. For PostgreSQL I really liked PGTap. Include this testing in CI and other unit test executions.

My sniff-test for a well-done schema is that developing application(s) around it should be clean.

This link is about Redis but I apply the same approach to databases: http://michaelxavier.net/posts/2014-04-27-Cool-Idea-Free-Mon...

The downside is that you need to express the command language, so you lose the ad-hoc querying capability that SQL gives you. But something more structured and more closely corresponding to the domain is generally want I want anyway.

> something more structured and more closely corresponding to the domain

If the needs have changed, the API should be upgraded. It seems like ad-hoc querying is mainly to get around shortcomings in the API, right?

Sometimes the business requirements really are that ad-hoc that you need something as general as SQL (in which case there's no point reimplementing it yourself as an "inner platform").

There's no inner platform. If the business requirements obligate the schema to change, that has a cascade effect on the remote facade and any ORMs using it or DAOs written against it. The database is part of the product, but if you treat it as a foreign component on its own change schedule, I understand how it looks like an inner platform.

I meant in the sense of http://thedailywtf.com/articles/The_Inner-Platform_Effect . If you try to create a platform that has all the power and flexibility of SQL queries, you end up reimplementing SQL, poorly.

Stored procedures, parametrization of queries and views will eliminate many errors.

DRY is the #1 reason I use an ORM. I also think that it's a factor that is rarely if ever considered by people who choose not to use an ORM.

Your database is just about the worst place in the world to be strict about DRY. It turns out, your model and validations don't actually prevent broken data. They just make you think you're safe from it...

...until your broken data starts biting you and your customers in places that don't typically see sunlight.

>Your database is just about the worst place in the world to be strict about DRY.

There are no places in any code base at all where it doesn't pay to be rigorous about DRY.

>It turns out, your model and validations don't actually prevent broken data.

A) DRY isn't about preventing broken data.

B) Validations are for preventing broken data. That's what they do.

DRY is the reason I write a remote facade. Security (e.g. sanitizing inputs) is less-repeated at the database boundary than at myriad application/ORM implementations.

>DRY is the reason I write a remote facade.

Remote facade sounds like a lot of boilerplate to me.

It truly varies. If all you're doing is sanitizing a simple INSERT, it's almost pure boilerplate, but that is not a very interesting or common case.

I'll offer some of the reasoning that got me to building remote facades. The database needed to sanitize its own inputs; we could not trust application developers with that. There would be multiple client-languages (Ruby, Python, Java, for starters) and I wanted to keep domain adapters for those languages simple as possible. The database would be changing regularly, and many changes could be simply absorbed in the remote facade, so applications depending on the remote facade via their client-language libraries would be somewhat insulated.

Now, it wasn't a perfect regime, and nothing is. But it paid the bills for years and my schemas are still in play at that place.

The 'slick' library for scala ticks a lot of these boxes. It allows you to write type-safe, idiomatic scala code to query your database, transform results into proper objects while avoiding the weight of an ORM.

I disagree. It sure gives you all of those things, but it is still a heavy ORM, that requires a whole lot of slick specific magic do simple things. You can let it own your table definitions, which is a bad idea if you ever want to update it in place, you can spend a lot of time writing the definitions by hand, or you can use their code generator, which writes code that is very verbose, full of useless comments and hard to read.

For a large org, Slick also adds to the already significant Scala learning curve for Java devs. Scala itself is good and pretty, but when the first task you give someone new is to write a CRUD-like rest service using slick on one end, and spray on the other, they don't get to spend any time learning actual scala, and instead domain specific languages that are hard to reason about without serious Scala experience. No, this is not speculation: We are going through this problem at work right now, with dozens of developers learning Scala, and only a few of us that really started with significant experience.

If I have to use slick, I use the few direct SQL bits. From my perspective, is the one saving grace of the library.

We went through a similar migration process and it sounds like we had different experiences. We started with the devs taking some time just to study and learn Scala basics then added the frameworks on top of that. I think if you go that way and you're using Slick > 2.0 it really starts to look like idiomatic Scala and everything in Slick looks like List manipulations. The other bonus is that a lot of the SQL generated perfors well and is easy to read.

Fair enough, thanks for the perspective!

What library would you use instead of slick?

You could try https://github.com/njeuk/dbmapper . I use this for my Scala projects, having rejecting Slick and it's unwanted data DSL.

In the Java world checkout MyBatis it lets you write "named" SQL queries in a config file and makes it available to you in Java as a type-safe interface. Been using it recently and love it.

Months since last I used JPA but I'm well beyond 90% sure I did this in JPA as well.

You can. It's just not mundane JPA. Hibernate had the same feature before JPA borrowed it. I was the only person that new Hibernate well enough to do that. JPA targets normal CRUD->POJO apps for it's common case. Under most circumstances you don't need raw SQL for that.