4 ms·

Plus PG and SQLite3 have fundamentally different approaches to types. SQLite3 has very few types, you cannot create new types, and is duck-typed. PG has many

Plus PG and SQLite3 have fundamentally different approaches to types. SQLite3 has very few types, you cannot create new types, and is duck-typed. PG has many built-in types, you can create new types, and is (mostly) statically typed. You really have to know this going in. If you have to support both, you'll probably have to write two versions of your SQL -- or you'll end up using an ORM that supports the two RDBMSes, and then you may get stuck with the ORM. SQLite3 is amazing, but it's mainly amazing because of how easy it is to embed in apps. If that's what you need, use SQLite3. If you need more power, if you're building a server, then think long and hard about why not just go straight to PG. EDIT: SQLite3 is amazing for other reasons too.

m-p-3 4y ago
And SQLAlchemy made the process of migrating from one to another quite simple too. https://www.sqlalchemy.org/ https://www.sqlalchemy.org/
password4321 4y ago
> As of version 3.37.0 (2021-11-27), SQLite provides STRICT tables that do rigid type enforcement https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- m-p-3 4y ago
  Thanks! Here's the link to the actual details https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
  - cryptonector 4y ago
    I'm aware of this, and it's a very welcomed addition, though the types supported are still very limited, but... > though new types might be added in future releases of SQLite Intriguing! Can't wait for SQLite3 to have `CREATE TYPE`!
anothercat 4y ago
Is it really duck typed if no static type checking is performed on the SQL? Duck typing to me seems to imply checking quacks and waddles at "compile" time. I would think it's at most loosely typed and at worst dynamically typed. EDIT: Without the new strict mode of course, where it is not duck typed but typed by name. EDIT 2: Actually maybe duck typing is fine to use for dynamic languages too. The affinity of INT keys and stuff is sort of duck typing I guess.
- kjeetgill 4y ago
  You're getting downvoted because the most well known duck-typed languages, and probably where people first learned the term from, are all examples of very dynamic languages like python, ruby, and even js. I'm sure there are others but the main language anyone knows static duck-typing for is go. In go at least, the interfaces get checked against implementations based what instances are bound to what interface variables or parameters at compile time.
  - kevin_thibedeau 4y ago
    Just to add more detail. SQLite was designed for Tcl interop. Its typing model reflects that.
  - crabmusket 4y ago
    TypeScript is another mainstream programming language that allows, or even encourages, static duck typing.
    - throwaway932345 4y ago
      [dead]
      - crabmusket 4y ago
        I'd say that structural typing is a type system feature/paradigm that enables you to use duck typing in a statically-verifiable way. Duck typing is a "pattern" which you can do in dynamic languages (because there's nobody checking), or in static structurally typed languages. I'm not sure why you'd call duck typing "dogma"? It's just a way of writing code. https://www.typescriptlang.org/play?#code/JYOwLgpgTgZghgYwgAgCIFcEGtgGcAWyA3gFDLIDucANlgBQCUAXMgG4D2wAJmcgI7pE9Zm048AviRIJqcXLmQBBBGEHUM2Yryq1GxZAnYhc7ahAB01dgHM6Acipcu1UNbsNkk8gKF6iBoxMzSxt7H2wAQndPEklpWXlkAAVoExBUKAh5CC5FXA0sLXIdYX1DY1MLK1s7AAdMyBAuV2Qwdko4JzNor35BbD8AiuDq+2AAW1b2MBpqAE9kOGQuTEL8OHWe2KkYdBAVYCNkdFwIArouFgK8fA9Sci5zEsZeR-DhbZITs9W6EAgKEoVGpzgwGF9Tud-oCUlA0hksqdcvlfmCgA https://www.typescriptlang.org/play?#code/JYOwLgpgTgZghgYwgA...
silversmith 4y ago
This is so 'on brand' for HN: author for once actually uses the main selling point of ORM concept and changes the underlying database by updating one config variable and nothing else in application code. One of the first comments warns "you may get stuck with the ORM".
- andrewstuart2 4y ago
  You're right. Calling out tradeoffs transparently in the comment section is 'on brand' and that's precisely why I keep coming back to HN and the comment section, specifically. Blog articles are helpful but most often are a single person's perspective and no one person is perfect. I love HN because I can get some great anecdotes (or sometimes response articles) from many perspectives.
- ignoramous 4y ago
  tbf GP went, You really have to know this going in. If you have to support both, you'll probably have to write two versions of your SQL. That is, the problem isn't just the ORM but the very different characteristics of the databases behind the ORM. Any serious production app and I bet no one is touching that config. Especially, if it has been running for months with one db or the other. And now, you're stuck with an extra database and a ORM.
- cryptonector 4y ago
  I work with (and occasionally have made contributions to) a proprietary Rails (ORM) app that in principle supports SQLite3 and PG but, in practice, left SQLite3 behind long ago. The main problem is that the app in question needed something like SQLite3's `ANY` type in PG but all there was back then was `hstore` (and even now, all there is is `hstore`, `json`, and `jsonb`), so `hstore`-using code metastasized and no one kept the SQLite3 bits working. Nowadays, of course, SQLite3 has JSON support, and PG has JSON and JSONB, and so it should be possible to fix that, but it would be quite the migration, so no one bothers. This is real life with a multi-RDBMS ORM: the promise of RDBMS neutrality always fails in some way as the differences between the RDBMSes bleed through the ORM's abstractions, and that bleeding occurs the moment you need more expressive power than the ORM provides out of the box for dead-simple apps. It's the same old story: dev meets ORM, ORM makes things easy (at first), dev needs more power, dev bumps up against ORM's limitations, dev works around those limitations, dev now has a frankenapp, dev wants to switch RDBMSes but can't, or dev wants to ditch the ORM but it's a lot of work. ORMs are a nice crutch to get started, but that's it.
BatteryMountain 4y ago
Thank you for highlighting this. I learned this about a year ago when I tried porting my storage layer from postgres to sqlite.. loads of little small things that don't quite work the way you expect it to or require a bit more code as workarounds.
- cryptonector 4y ago
  Normally you expect the reverse migration, from SQLite3 to PG!

m-p-3 4y ago

And SQLAlchemy made the process of migrating from one to another quite simple too. https://www.sqlalchemy.org/ https://www.sqlalchemy.org/

password4321 4y ago

> As of version 3.37.0 (2021-11-27), SQLite provides STRICT tables that do rigid type enforcement https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html

m-p-3 4y ago
Thanks! Here's the link to the actual details https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- cryptonector 4y ago
  I'm aware of this, and it's a very welcomed addition, though the types supported are still very limited, but... > though new types might be added in future releases of SQLite Intriguing! Can't wait for SQLite3 to have `CREATE TYPE`!

anothercat 4y ago

Is it really duck typed if no static type checking is performed on the SQL? Duck typing to me seems to imply checking quacks and waddles at "compile" time. I would think it's at most loosely typed and at worst dynamically typed. EDIT: Without the new strict mode of course, where it is not duck typed but typed by name. EDIT 2: Actually maybe duck typing is fine to use for dynamic languages too. The affinity of INT keys and stuff is sort of duck typing I guess.

kjeetgill 4y ago
You're getting downvoted because the most well known duck-typed languages, and probably where people first learned the term from, are all examples of very dynamic languages like python, ruby, and even js. I'm sure there are others but the main language anyone knows static duck-typing for is go. In go at least, the interfaces get checked against implementations based what instances are bound to what interface variables or parameters at compile time.
- kevin_thibedeau 4y ago
  Just to add more detail. SQLite was designed for Tcl interop. Its typing model reflects that.
- crabmusket 4y ago
  TypeScript is another mainstream programming language that allows, or even encourages, static duck typing.
  - throwaway932345 4y ago
    [dead]
    - crabmusket 4y ago
      I'd say that structural typing is a type system feature/paradigm that enables you to use duck typing in a statically-verifiable way. Duck typing is a "pattern" which you can do in dynamic languages (because there's nobody checking), or in static structurally typed languages. I'm not sure why you'd call duck typing "dogma"? It's just a way of writing code. https://www.typescriptlang.org/play?#code/JYOwLgpgTgZghgYwgAgCIFcEGtgGcAWyA3gFDLIDucANlgBQCUAXMgG4D2wAJmcgI7pE9Zm048AviRIJqcXLmQBBBGEHUM2Yryq1GxZAnYhc7ahAB01dgHM6Acipcu1UNbsNkk8gKF6iBoxMzSxt7H2wAQndPEklpWXlkAAVoExBUKAh5CC5FXA0sLXIdYX1DY1MLK1s7AAdMyBAuV2Qwdko4JzNor35BbD8AiuDq+2AAW1b2MBpqAE9kOGQuTEL8OHWe2KkYdBAVYCNkdFwIArouFgK8fA9Sci5zEsZeR-DhbZITs9W6EAgKEoVGpzgwGF9Tud-oCUlA0hksqdcvlfmCgA https://www.typescriptlang.org/play?#code/JYOwLgpgTgZghgYwgA...

silversmith 4y ago

This is so 'on brand' for HN: author for once actually uses the main selling point of ORM concept and changes the underlying database by updating one config variable and nothing else in application code. One of the first comments warns "you may get stuck with the ORM".

andrewstuart2 4y ago
You're right. Calling out tradeoffs transparently in the comment section is 'on brand' and that's precisely why I keep coming back to HN and the comment section, specifically. Blog articles are helpful but most often are a single person's perspective and no one person is perfect. I love HN because I can get some great anecdotes (or sometimes response articles) from many perspectives.
ignoramous 4y ago
tbf GP went, You really have to know this going in. If you have to support both, you'll probably have to write two versions of your SQL. That is, the problem isn't just the ORM but the very different characteristics of the databases behind the ORM. Any serious production app and I bet no one is touching that config. Especially, if it has been running for months with one db or the other. And now, you're stuck with an extra database and a ORM.
cryptonector 4y ago
I work with (and occasionally have made contributions to) a proprietary Rails (ORM) app that in principle supports SQLite3 and PG but, in practice, left SQLite3 behind long ago. The main problem is that the app in question needed something like SQLite3's `ANY` type in PG but all there was back then was `hstore` (and even now, all there is is `hstore`, `json`, and `jsonb`), so `hstore`-using code metastasized and no one kept the SQLite3 bits working. Nowadays, of course, SQLite3 has JSON support, and PG has JSON and JSONB, and so it should be possible to fix that, but it would be quite the migration, so no one bothers. This is real life with a multi-RDBMS ORM: the promise of RDBMS neutrality always fails in some way as the differences between the RDBMSes bleed through the ORM's abstractions, and that bleeding occurs the moment you need more expressive power than the ORM provides out of the box for dead-simple apps. It's the same old story: dev meets ORM, ORM makes things easy (at first), dev needs more power, dev bumps up against ORM's limitations, dev works around those limitations, dev now has a frankenapp, dev wants to switch RDBMSes but can't, or dev wants to ditch the ORM but it's a lot of work. ORMs are a nice crutch to get started, but that's it.

BatteryMountain 4y ago

Thank you for highlighting this. I learned this about a year ago when I tried porting my storage layer from postgres to sqlite.. loads of little small things that don't quite work the way you expect it to or require a bit more code as workarounds.

cryptonector 4y ago
Normally you expect the reverse migration, from SQLite3 to PG!