Is it possible to get an assistant professor position that does not require doing research?
The US, at least, is chock full of smaller colleges and a few universities that are devoted almost entirely to teaching. The phrase you are looking for is "teaching college" (or occasionally "teaching university"). The expectations for "scholarly activity" from the faculty at these institutions are much lower than at major research universities.

The downsides include:


Generally a higher teaching load. Count on at least twelve credit-hours per term.
A lack of money, space, time, equipment and assistance if you do want to do a little research.
No graduate students---these places generally only offer bachelor's degrees---and perhaps a less prepared or motivated undergraduate population from which to draw perspective research students.
Generally lower pay. Sometimes quite a bit lower.


Departments are sometimes smaller in size which means a good opportunity to develop a relationship with the major students and to mentor more than a few of them. To the degree that incoming students are less prepared but just as able you'll have a chance to really help them grow.



There have been some comments about the preparation of the student body: my "perhaps" above is a bit of a weasel word. Teaching colleges span a wide range of prestige and competitiveness, and their intake varies accordingly.

Some that are highly competitive and prestigious and are able to select a very able student body.  I'll offer the opinion that the expectations at the most competitive teaching schools looks more like those of research universities than like those of a generic "teaching college", though you should pay close attention to these questions on a case by case basis. The breadths of expectations described by the people I met at a recent teaching workshop was staggering.



The incoming student body at my school are an interesting bunch. The range of preparation and ability is very broad, and we do have some students with real potential but in most cases there is a reason they didn't get into a more competitive school nearby.

Some breezed through secondary school never learning how to work at their schooling. Many are non-traditional students with heavy family commitments. Some have never been in an environment where learning was valued and have never found out what they are capable of in that realm. A few are determined slackers. Some are just not academically gifted and won't be going on to further schooling after they get the credential they are here for.

As I indicated above, I'm finding this a very rewarding environment but choices in students with the ability and motivation to engage in research are limited and the institutional support is limited.