Re: Introducing Norm: a Nim ORM
I'm proud to announce the first release of **Norman** , the migration manager for Norm: [https://moigagoo.github.io/norman/norman.html](https://moigagoo.github.io/norman/norman.html) With Norman, you can keep your DB config separate from the models in a project config and generate, apply and undo migrations. There are no tests at the moment, but they're on the way. Feel free to add Norman to your existing projects that use Norm, it should improve your developer experience.
Re: Introducing Norm: a Nim ORM
I've always searched for ORM's in Nim they are likely sequelize in nodejs (used sequelize years ago). Your project comes closest to sequelize, I like your approach. Nice work!
Re: Introducing Norm: a Nim ORM
'''You mean connection pool? There's an open issue for it, but I'm not planning on taking on it any time soon. I plan to focus on the migration manager for Norm.''' yes
Re: Introducing Norm: a Nim ORM
> Did you think, open several connections > so if yes you can have a > transactional connection and a logical connection only for reading ... You can connect to several DBs with [withDb and withCustomDb](https://moigagoo.github.io/norm/norm.html#reference-guide-connection), which allows you to use one connection for reading and another for writing. > Did you think, open several connections You mean connection pool? There's an [open issue](https://github.com/moigagoo/norm/issues/50) for it, but I'm not planning on taking on it any time soon. I plan to focus on the migration manager for Norm.
Re: Introducing Norm: a Nim ORM
Bonjour, Hello 887/5000 I am passionate about DB ... Did you think, open several connections so if yes you can have a transactional connection and a logical connection only for reading ... I work this way it allows me a lot of flexibility. I worked on a wrapper (which works in C ++) based on Postgresql libpq. I also did one with ECPG ... History of making a lib for each table containing access allowing to make the management a very functional test and a correct speed, this table in lib ... finally it was the materialization of fields and functional records identical to the AS400 DB2 I had thought of translating everything into nim, I stumbled on ECPG which demands absolutely "c" then converted to its sauce ... So I did not find it very nice for someone who uses nim Bon voyage in your adventure
Re: Introducing Norm: a Nim ORM
This is awesome and lovely work! Thank you.
Re: Introducing Norm: a Nim ORM
It is my great pleasure to announce Norm version 1.1.0 release: [https://moigagoo.github.io/norm/changelog.html](https://moigagoo.github.io/norm/changelog.html) The biggest change addition are the procs to write migrations. With those in place, I can focus on writing a migration manager that will make Norm even more useful for real life usage.
Re: Introducing Norm: a Nim ORM
I just want to clarify that there's no problem with plain Datetime, m just init it explicitly like I do in tests and norm-sample-webapp. It's none Datetime that is the real issue.
Re: Introducing Norm: a Nim ORM
... checked the range. `Time` is good to the year 292277026596 and presumably backwards that far. So, you will have to worry about the Y292277026K problem.
Re: Introducing Norm: a Nim ORM
In the expansion of `norm` to support mongodb that I'm developing, I've defaulted to only supporting `Time`. The `DateTime` type breaks things on so many levels, mostly because `DateTime()` generates a runtime error. Fortunately, the `Time` function, though based on the traditional Unix timestamp (seconds from Jan 1, 1970), is signed and 64 bit. So, the actual time range is massive and includes time before 1970.
Re: Introducing Norm: a Nim ORM
Right, i had DateTime field in the User type. Commented it out, and there are no errors :)
Re: Introducing Norm: a Nim ORM
What are the types of your model fields? I'm getting these warnings when using Option[Datetime] because Datetime doesn't have a default value. To make things worse, you can't solve it even with manual initialization: `none Datetime` still doesn't initialize a Datetime object which throws the warning.
Re: Introducing Norm: a Nim ORM
With nim 0.20.2 or newer theres an error: template/generic instantiation from the withDb macro and getOne/getMany procs It compiles, but its very frustrating because nimsuggest marks them as errors.
Re: Introducing Norm: a Nim ORM
In Norm 1.0.15 (released just now), you can split DC schema definition across multiple modules. In order to do that. wrap the type section you want to use in schema in `dbTypes`` macro and use ``dbFromTypes`` instead of ``db`. See example in the docs (scroll down to “Alternatively to defining...”): [https://moigagoo.github.io/norm/norm.html](https://moigagoo.github.io/norm/norm.html)
Re: Introducing Norm: a Nim ORM
Here's a PR I'm developing this feature in: [https://github.com/moigagoo/norm/pull/26](https://github.com/moigagoo/norm/pull/26) Feel free to comment.
Re: Introducing Norm: a Nim ORM
Sorry, I don't think I follow your point. Could you please try to illustrate it with some examples? Or we could connect directly and chat about it in Russian if you want.
Re: Introducing Norm: a Nim ORM
Did you try to work directly with objects by implementing the _[object](https://forum.nim-lang.org/postActivity.xml#object) database? ORMs looks for me like the dog's fifth leg -- factically _most times we need persistent object [storage](https://forum.nim-lang.org/postActivity.xml#storage), but again and again, we are trying to push object model into storage does not follow this model but designed to storing huge bunches of absolutely structured data. As an example, let's look at any XML-packed data structure. It consists of arbitrary fields, all of them is optional from the viewpoint of the data storage format. Every time we are trying to put it in RDBMS we must use sparse tables with most fields nullable.
Re: Introducing Norm: a Nim ORM
It turned out to be trickier than I thought. The thing is, we're injecting id field into every type definition under db macro. This is necessary so that we can safely rely on objects having ids, which is necessary for getOne. If we're allowing to define types separately from db macro, we can't just inject id field somewhere in another module. The solution I'm leaning towards is: * allow to create db schema from types defined elsewhere only if those types have id field, which is also int and readonly * add db pragma to help people with the previous step: types with db pragma will be injected with id * disallow mixing the two approaches: you either define the schema as body for db macro, or you pass a list of db-annotated types to a different macro (e.g. dbFromTypes).
Re: Introducing Norm: a Nim ORM
There's an open PR that implements dbAddObject proc that allows to add type definitions from different module to the global model definition. This may be the thing that could help you: define types in modules as you'd normally do, then glue them together in models.nim module. I'll try to implement this proc for SQLite and PostgreSQL backends tomorrow.
Re: Introducing Norm: a Nim ORM
Do you have an example for using Norm across multiple modules? I'd like to have my Users and Posts in separate .nim files, but I can't figure out an easy way to do that.
Re: Introducing Norm: a Nim ORM
Keeping you updated on Norm progress. * bool fields can be stored without custom parser or formatter. In SQLite, they are stored as 1 and 0, in PostgreSQL native BOOLEAN type is used. * times.DateTime fields are now also supported out of the box! In SQLite, they are stored as epoch timestamps, and in PostgreSQL native TIMESTAMP WITH TIME ZONE is used. Changelog: [https://github.com/moigagoo/norm/blob/develop/changelog.md](https://github.com/moigagoo/norm/blob/develop/changelog.md) As before, you are free to use a custom parser and formatter for any type. These are just sane defaults. Also, one breaking change was introduced in formatIt pragma for SQLite backend: the expression in the pragma must explicitly evaluate to DbValue, implicit conversion has been removed. Thanks everybody who helped by opening issues, sharing feedback, and contributing code to the project. The next big milestone is supporting NULL values in PostgreSQL, which is unfortunately blocked by ndb progress.
Re: Introducing Norm: a Nim ORM
I have some great news: Norm 1.0.11 supports inserting and retrieving NULL values for SQLite databases. This was made possible thanks to the great [ndb](https://github.com/xzfc/ndb.nim) module. The transition from std db_sqlite to ndb/sqlite was really smooth. I can't wait to see ndb replace all db_* modules in stdlib. As soon as ndb supports PostreSQL, we'll be able to port norm/postgres to it as well, which will make NULL support universal.
Re: Introducing Norm: a Nim ORM
@moigagoo Thanks for this, I've been using Norm in developing a little microblogging thing, and I like it's simplicity.
Re: Introducing Norm: a Nim ORM
A quick update about the project. Thanks to [alaviss](https://github.com/alaviss), SQLite backend supports onUpdate and onDelete pragmas, foreign key handling now works (even with multiple FKs). getOne can retrieve a record not only by ID, but also by condition. getMany and getOne procs now support safe value substitution when constructing queries, so you can safely pass user-provided data to it. There's a breaking change: instead of `where` and `orderBy`, we now have a single `cond` arg. Feel free to use, report issues, and contribute: [https://github.com/moigagoo/norm](https://github.com/moigagoo/norm)
Re: Introducing Norm: a Nim ORM
Thanks for the idea! Sounds easy to implement. How do I store NULLs in an object though? Maybe, allow fields to be nil besides their own type?
Re: Introducing Norm: a Nim ORM
> I don't know what the right solution here is. Wrapping all values in Option > seems like an overkill. Returning 0 when NULL is returned where an int is > expected sound incorrect. Any ideas? Ormin doesn't use the `seq[string]` idea at all, side-stepping the problem. But here's a different idea: Instead of `seq[Option[string]]` a row could be `tuple[data: seq[string], missing: seq[bool]]`. Then you can easily ignore the 2nd tuple component when you know there are no NULLs in your schema (and there shouldn't be NULLs!).
Re: Introducing Norm: a Nim ORM
Hi all, regarding the discussion last year @ThomasTJdev mentioned I came up with my own solution of a rdbms-backend. It´s preminilary but stable for sqlite3 : [https://github.com/mikra01/nimdb](https://github.com/mikra01/nimdb) . Suggestions and PR´s welcome - I hope it´s intuitive. Sqlite's incremental blob api and rowid-handling (for inserts) is missing. I planned further examples with the chinook database. At the moment I am working at the odbc-part so it could be possible that I have to change the API a little bit..
Re: Introducing Norm: a Nim ORM
Thanks for the links! I didn't know about nim_sqlbuilder, I may switch to it instead of using my own `rowutils` module.
Re: Introducing Norm: a Nim ORM
Hi @moigagoo I've also had some trouble with NULL values. My question can be found in this thread: [4320](https://forum.nim-lang.org/t/4320) I ended using the library here [nim_sqlbuilder](https://github.com/ThomasTJdev/nim_sqlbuilder), which substitute a type with `= NULL`. @mashingan also provided a [good solution](https://forum.nim-lang.org/t/4320#26937) using options in the forum thread. There's also a [github issue](https://github.com/nim-lang/Nim/issues/9453) about the missing API to precompile the query and the bind the data later, which @mikra01 is working on.
Re: Introducing Norm: a Nim ORM
I just want to make it clear that Norm is my first attempt to ORM and that decisions I've made may be wrong. Any advice from seasoned developers is highly appreciated. Also, I'd be happy to see Norm evolve into a usable tool, so input from its potential users is vital.
Re: Introducing Norm: a Nim ORM
> Are all the incoming types from the database strings? I'm not sure I understand the question. Row a.k.a. `seq[string]` is converted into an object based on the types of the object's fields. If the fields are int, float, and string, then a row `@["123", "123.321", "foo"]` is converted into 123, 123.123, "foo". AFAIK Nim db modules' rows are always sequences of string regardless of the underlying DB types. So I'm relying on the target object's types. You can see the code that does the conversions in [rowutils](https://github.com/moigagoo/norm/blob/develop/src/norm/rowutils.nim) module. > How does this handle NULL? I haven't tested it with NULL values bth. I should though. How it's handled depends on how Nim's db modules handle NULL. I think it's returned as an empty string.
Re: Introducing Norm: a Nim ORM
Are all the incoming types from the database strings? How does this handle NULL?
Re: Introducing Norm: a Nim ORM
> You can probably use custom pragmas: > > {.pragma: dbTime, dbType: "INTEGER",
> parseIt: timeparser, formatIt: timeformatter.}
You sure can, thanks for the tip!
Just a little sidenote: `parseIt` pragma is used to set an `it` expression
whereas `parser` is used to set a parser proc. So the correct code sample would
be: `{.pragma: dbTime, dbType: "INTEGER", parser: timeparser, formatter:
timeformatter.}`.
Re: Introducing Norm: a Nim ORM
Thanks for the positive comment! It means a lot to me, for real.
Currently, there's no way to shorten or reuse field definition, and `DateTime`
is considered a special case, so _right now_ , unfortunately, it's `birthDate
{.dbType: "INTEGER", parseIt: it.parseInt().fromUnix().local(), formatIt:
$it.toTime().toUnix().}: DateTime` repeated multiple times in your DB schema.
It's the only place you'll have to have this ugly code repetition though.
However, I've been thinking a lot about adding default parser and formatter for
`DateTime` type. In SQLite, they'd be stored as `INTEGER` and in PostgreSQL as
`TIMESTAMP`. Sounds like reasonable defaults.
DateTime is a common type and I can imagine developers rewriting the same
parsers and formatters over and over. Do you think it's a good idea?
Re: Introducing Norm: a Nim ORM
You can probably use custom pragmas:
`{.pragma: dbTime, dbType: "INTEGER", parseIt: timeparser, formatIt:
timeformatter.}`
Re: Introducing Norm: a Nim ORM
This looks pretty neat! Is it possible though to define a conversion and apply
it to multiple fields? Say I had a lot of dates in my database it could get
tedious fast to write {.dbType: "INTEGER", parseIt: timeparser, formatIt:
timeformatter.}.
Re: Introducing Norm: a Nim ORM
Here's an example of a DB schema definition (from
[https://github.com/moigagoo/norm-sample-webapp/blob/develop/src/models.nim)](https://github.com/moigagoo/norm-sample-webapp/blob/develop/src/models.nim\)):
db("app.db", "", "", ""):
type
Owner* = object
firstName*: string
lastName*: string
birthDate* {.
dbType: "INTEGER",
parseIt: it.parseInt().fromUnix().utc(),
formatIt: $it.toTime().toUnix()
.}: DateTime
proc getOwnerById(id: string): Owner =
result = Owner(birthDate: now())
withDb:
result.getOne parseInt(id)
type
Pet* = object
name*: string
birthDate* {.
dbType: "INTEGER",
parseIt: it.parseInt().fromUnix().utc(),
formatIt: $it.toTime().toUnix()
.}: DateTime
owner* {.
dbCol: "ownerId",
dbType: "INTEGER",
fk: Owner
parser: getOwnerById,
formatIt: $it.id
.}: Owner
Run
