Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Prefer strict tables in SQLite

evanhahn.com

Post reply on HN
Print view View original

101–110 of 188 posts

Re: Prefer strict tables in SQLite

#101

Post by frollogaston »

win311fwg wrote: post #60
frollogaston wrote: post #49

Yeah my DB is the one place I want strict types. Well also RPCs. But SQLite is a somewhat different set of use cases, so maybe I'd understand https://sqlite.org/flextypegood.html more if I were using it. Like there's a point about random scripts not made for SQLite happening to work with it, which isn't normally a consideration for other DBMSes.

> Yeah my DB is the one place I want strict types. Well also RPCs. Which is why in most cases you are going to show at compile time that your code adheres to the typed structure. The SQLite schema you are developing alongside provides the type information for static analysis. There is no real benefit in also double checking again at runtime. Your code isn't going to magically mutate in a way that it starts inserting…

But one DB one app is fairly common with Postgres too, particularly if you're adhering to a services deliniation. Guess if your code enforces types at DB insert time, the DB doesn't need to, but one of those is more likely to change than the other.

Re: Prefer strict tables in SQLite

#103

Post by frollogaston »

blixt wrote: post #14

I think I can see how dynamic data types make sense (eg flat key/value store), but my question would be: What is least surprising? That INTEGER implicity accepts 'hello world' without error, or that you can't insert such a value unless you use a keyword like NONSTRICT or a type like ANY? I would wager the vast majority of SQLite users if asked would probably not expect it to work.

It's probably because SQLite intends to be untyped but also wants the statements to look like standard SQL. This matches their other note about wanting code designed to work with other DBMSes to accidentally work with SQLite too.

Otherwise, yeah, it's very surprising to explicitly put INTEGER and still be able to insert text. It's not like the user left the type out.

Re: Prefer strict tables in SQLite

#104

Post by panzi »

sheept wrote: post #99
panzi wrote: post #50

Earlier quoted context omitted.

Well, I would also like a proper datetime/timestamp datatype that isn't just a string.

Why not store them as integers? Representing dates is a UI responsibility

I also want to be able to debug the database in an SQL console with nice looking date-times.

Re: Prefer strict tables in SQLite

#105

Post by frollogaston »

petilon wrote: post #78
frollogaston wrote: post #49

Yeah my DB is the one place I want strict types. Well also RPCs. But SQLite is a somewhat different set of use cases, so maybe I'd understand https://sqlite.org/flextypegood.html more if I were using it. Like there's a point about random scripts not made for SQLite happening to work with it, which isn't normally a consideration for other DBMSes.

> https://sqlite.org/flextypegood.html Both the advantages and disadvantages section is missing key arguments. Key argument in favor of flexible typing: Easy to evolve schema. When you are using SQLite to store data in your embedded database and your requirements change, do you want to create a new database and migrate data each time? Evolving the schema in-place is much easier, and if your application is the only on…

Can't say I've done a SQLite migration before. Changing int col to text in Postgres could be done with a couple of ALTER statements, or more safely, add a new col with a different name and switch over.

Re: Prefer strict tables in SQLite

#106

Post by frollogaston »

coldtea wrote: post #93

Too many people missing the point entirely and wanting to make SQLite Postgres or Oracle.

SQLite has to be one of the most stubborn software projects around, in a good way. Everything about it breaks what you learn in school and ignores trends, but it thrives.

First time I used it was in high school, when I was a newbie to C and didn't know how to link in libraries, and SQLite was the only thing that offered all the code as a single .c file https://sqlite.org/amalgamation.html

Re: Prefer strict tables in SQLite

#108

Post by m0nacle »

i think braindead developers (most people in this thread) have become way too typescript pilled and as such think that types are something you can’t live without. grow up. use another of the 4000 databases out there or stop fucking bitching that you can’t manage your data without going peepee in your pampers

Re: Prefer strict tables in SQLite

#109

Post by m0nacle »

sherburt3 wrote: post #48

I really hate this trend of turning every piece of software into this kafkaesque monstrosity that demands you jump through 100 hurdles to do the simplest thing. I mean yeah its good for LLMs but as a human it gets kind of annoying. I honestly love that if you hand SQLite garbage it will do its best.

this 100%

Re: Prefer strict tables in SQLite

#110

Post by echelon »

redman25 wrote: post #63
echelon wrote: post #53

Earlier quoted context omitted.

Someone should fork it in Rust using Claude and rename it SQRite. Strict type all the things.

A large portion of the tests are closed source unfortunately which would make it tough to create a port.

What the hell? This is the first I'm learning of this.

Why did they do that? Is it owned by a private company?

Post reply on HN